I used SQLDataReader to assign a OrderID to a text box. OrderID is varchar type. I got cast error. How to fix it? Me.txtOrderID.Text = myDataReader.GetString("OrderID")
In my database I have this code: Do While sqlDataReader.Read() lblTitle.InnerText = sqlDataReader.GetString(2) Loop
And this will get my sql's 3rd column ( its #2 because its 0-indexed ), but I am throwing this error when I try to print this sqlDataReader.GetString(2) if this particular value is a int. How do I cast it to a String.
I have already done these two things: sqlDataReader.GetString(2).ToString() & CStr(sqlDataReader.GetString(2))
I am currently using the following code to populate fields in a form EditUserNotes.Text = EditUserReader.GetString(2); which works just fine (the datatype in the database is 'text') as long as the data stored in the database is not null. If the data is null, I get the error: Data is Null. This method or property cannot be called on Null values. which is really annoying as there are times when the data stored in the database is legitimately NULLI can use the following code to fix the problem, but it seems less elegant EditUserNotes.Text = EditUserReader.GetValue(2).ToString() is there really a difference between the GetString and the GetValue(x).ToString() methods or are they interchangeable?
HI I am having problem with my Execute Reader. I am trying to insert values from 2 different tables into another table. SqlCommand comm2; SqlDataReader reader2; /* Grabs the stuff out of the database */ comm2 = new SqlCommand("SELECT HiraganaCharacter,HiraganaImage FROM Hiragana", getConnection()); /* opens the database */ comm2.Connection.Open(); /* starts the reader */ reader2 = comm2.ExecuteReader(); /* goes through the first array list */ for (int i = 0; i < checkedLetters.Count; i++) { /* find the data by using the array list value as a where clause */ comm2.CommandText = "SELECT HiraganaCharacter,HiraganaImage FROM Hiragana WHERE HiraganaCharacter ='" + checkedLetters[i] + "'"; /* reads through the data */ reader2.Read(); /* puts the ID- this id was set somewhere else */ CommQuickLinksItems.Parameters["@QuickLinkID"].Value = QuickLinkId; CommQuickLinksItems.Parameters["@CharacterName"].Value = reader2["HiraganaCharacter"].ToString(); CommQuickLinksItems.Parameters["@CharacterImagePath"].Value = reader2["HiraganaImage"].ToString(); CommQuickLinksItems.ExecuteNonQuery(); } for (int j = 0; j < checkedLettersKata.Count; j++) { comm2.CommandText = "SELECT KatakanaCharacter,KatakanaImage FROM Katakana WHERE KatakanaCharacter ='" + checkedLettersKata[j] + "'"; reader2.Read(); CommQuickLinksItems.Parameters["@QuickLinkID"].Value = QuickLinkId; /* line it dies on */ CommQuickLinksItems.Parameters["@CharacterName"].Value = reader2["KatakanaCharacter"].ToString(); CommQuickLinksItems.Parameters["@CharacterImagePath"].Value = reader2["KatakanaImage"].ToString(); CommQuickLinksItems.ExecuteNonQuery(); } CommQuickLinksItems.Connection.Dispose(); CommQuickLinksItems.Dispose(); comm2.Connection.Dispose(); comm2.Dispose(); My first question is there a better way to setup a SqlCommand to just get the connection and wait on the Command object text? Right now I am doing comm2 = new SqlCommand("SELECT HiraganaCharacter,HiraganaImage FROM Hiragana", getConnection());Which is kinda pointless since in the for loop I change the command to something different right away. At the same time though I don't really want to make a new SqlCommand object in the for loop since then everytime it goes through the loop it would then re grab the connection what I find pointless tooNow the problem How I have it right now it does not grab the right stuff. The first for loop works great and everything gets inserted. The next loop does not work It seems like it it trying to take the data from the first for loop and insert that stuff again since I get this error System.IndexOutOfRangeException was unhandled by user code Message="KatakanaCharacter" Source="System.Data" StackTrace: at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at System.Data.SqlClient.SqlDataReader.get_Item(String name) at Practice.QuickLinks() in g:WebsiteJapanesePractice.aspx.cs:line 385 at Practice.btnQuickLink_Click(Object sender, EventArgs e) in g:WebsiteJapanesePractice.aspx.cs:line 411 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) InnerException: Basically what I did was for the first loop I chose 2 items and for the 2nd loop I chose 3 items. When it died on this line CommQuickLinksItems.Parameters["@CharacterName"].Value = reader2["KatakanaCharacter"].ToString();The value was "i" but that was one of the values I choose for the first for loop. It should have been either u,e,o. So I am not sure what I am doing wrong. I thought as long as I change the Command text I would not need to do anything else but it seems like I am missing something.
when I execute the line: reader = comm.ExecuteReader(); Is there a way to get a count of the number of records returned (the query is a SELECT with no count in it)? I want to vary the display of the results set based on the number of records returned. For example if no records are returned I want it to display nothing, if one, I want the header to be in the singular, but if more than one record is returned, I want it to display the header in plural form. Here is my code snippet with further explanation of what I am trying to do:int Inumber = 0;foreach (string item in menuHeaders) {string title = menuHeaders[Inumber]; sp.Value = menuHeaders[Inumber]; Inumber++; conn.Open();reader = comm.ExecuteReader(CommandBehavior.CloseConnection); //Get the culture property of the thread.CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture; //Create TextInfo object.TextInfo textInfo = cultureInfo.TextInfo; // WHAT I AM TRYING TO DO....... Here I would like to wrap this with an if statement, if Records returned by the reader are 0, skip while loop and header display // If one, then display in singular and if 2 add an s to the title. Convert to title case and display.content.Text += "<H3>" + textInfo.ToTitleCase(title) + "</H3>";while (reader.Read()) { content.Text += "<a href='" + reader["website"] + "'>" + reader["f_name"] + reader["l_name"] + "</a>"+ ", " +reader["organization"]+"<br />"; } //Close the connection. reader.Close(); conn.Close(); }
Hi all,I have a script which I am running to get the minimum date from a database table.I've connected to the database and run the sql but when I try to get the result i get an error saying "No data exists for the row/column."This is the code I have for it at the moment.1 Dim mySql As String = "SELECT MIN(LOSS_DATE) AS minDate FROM dbo_CLAIMS" 2 Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|NexusHolding.mdb;Persist Security Info=True" 3 Dim dbCon As New OleDbConnection(connectionString)4 5 dbCon.Open()6 7 Dim dbComm As New OleDbCommand(mySql, dbCon)8 Dim dbRead = dbComm.ExecuteReader()9 Dim minDate As String = dbRead.GetValue(0)10 11 Response.Write(minDate)Thanks in advance for any help.
Hi. I am executing a stored procedure. The stored procedure raises an error and all I need is to catch this error. Pretty simple, but it only works with an ExecuteNonQuery and not with an Executereader statement. Can anybody explain to me why this happens?
Here's the sp:
CREATE PROCEDURE dbo.rel_test AS select 1 raiserror ('My error.', 11, 2) return GO
Here's the ASP.Net page:
<% @Page Language="VB" debug="True" %> <% @Import Namespace="System.Data.SqlClient" %> <script runat="server"> Public Function RunSP(ByVal strSP As String) As SqlDataReader Dim o_conn as SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring")) AddHandler o_conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
o_conn.Open
Dim cmd As New SqlCommand(strSP, o_conn) cmd.CommandType = System.Data.CommandType.StoredProcedure Dim rdr as SqlDataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection) rdr.Close() cmd.Dispose()
Response.Write(o_conn.State)
End Function
Private Sub OnInfoMessage(sender as Object, args as SqlInfoMessageEventArgs) Dim err As SqlError For Each err In args.Errors Response.Write(String.Format("The {0} has received a severity {1}, state {2} error number {3}" & _ "on line {4} of procedure {5} on server {6}:{7}", _ err.Source, err.Class, err.State, err.Number, err.LineNumber, _ err.Procedure, err.Server, err.Message)) Next End Sub
Sub Page_Load(sender as Object, e as EventArgs) RunSP("rel_test") End Sub </script>
I am not seeing why this is not executing the reader, it just goes right by it when stepping through the code... command.CommandType = CommandType.StoredProcedure; // course command.Parameters.Add( "@courseId", courseId ); // Parameter: LessonName SqlParameter sLessonName = command.Parameters.Add( "@lessonName", SqlDbType.VarChar ); sLessonName.Size = 256; sLessonName.Direction = ParameterDirection.Output; // error code SqlParameter pErrCode = command.Parameters.Add( "@errCode", SqlDbType.Int ); pErrCode.Direction = ParameterDirection.Output; // execute the stored procedure SqlDataReader spResults; conn.Open(); spResults = command.ExecuteReader(); while( spResults.Read() ) // It never steps into the while statement like the reader is completed { RetrieveObjId objNames = new RetrieveObjId( spResults.GetString( 0 )); searchResults.Add( objNames ); } spResults.Close();And the stored procedure is.....CREATE PROCEDURE dbo.retrieveLessonNames @courseId VARCHAR(20), @lessonName VARCHAR(256) OUTPUT, @errCode INT OUTPUT ASBEGIN SELECT @lessonName = objName FROM objStructure WHERE courseId = @courseId SET @errCode = 0 RETURN @errCode HANDLE_APPERR: SET @errCode = 1 RETURNHANDLE_DBERR: SET @errCode = -1 RETURNENDGOSuggestions?Thanks all,Zath
I have VS 2005 and SQL CE 3.0. I sometimes get the a FileNotFoundException when I first use ExecuteReader. I believe this is because a dll has not been copied across because if I restart the emulator I can get it to work again.
Do I need to add a cab file/dll to my project to stop this happening?
I am currently tryinh to have this variable declared : Dim SQLLecteur As SqlDataReader = Command.ExecuteReader()And receiving the following error : 'ExecuteReader' is not member of 'String'.1. The ExecuteReader was not present in the list following the Command.2. The variable is declared from a : Public Shared Sub3. This sub is located in a code library referenced in the web.config as a namespace : <add namespace="PAX20070409" />4. If used directly in the .vb file within this sub : Protected Sub btnConnection_Click, I am not receiving any errors about the Dim.It is pretty clear why the code is not working, but I have not been able to find a way to fix the problem. I am currently trying to find a way to make the Dim work from within my code library. If you have any idea on how this could be achieve, it would be greatly apreciated.Thank you :)RV3
I'm writing my first vb.net app. Have a default page that uses a persons network login to query a database to get all their timekeeper id, firstname, last name, etc. But I keep getting this error. (My code is below) What am I missing??? ExecuteReader: Connection property has not been initialized. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: ExecuteReader: Connection property has not been initialized.Source Error:
Line 21: conn.Open() Line 22: Line 23: reader = comm.ExecuteReader() Line 24: If reader.Read() Then Line 25: EmployeesLabel.Text = reader.Item("tkinit") <script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)Dim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReaderDim connectionString As String = ConfigurationManager.ConnectionStrings("xxxConnectionString").ConnectionStringcomm = New SqlCommand("Select top 1 tkinit, tklast, tkfirst +' '+ tklast as fullname from txxx WHERE login = @login)", conn)comm.Parameters.Add("@Login", Data.SqlDbType.VarChar)comm.Parameters("@Login").Value = Me.User.Identity.Name.Substring(User.Identity.Name.IndexOf("") + 1)conn = New SqlConnection(connectionString)conn.Open()reader = comm.ExecuteReader()If reader.Read() ThenEmployeesLabel.Text = reader.Item("tkinit")FirstLastName.Text = reader.Item("fullname")End Ifreader.Close()conn.Close()End Sub</script>
I have a web form that is generating an error and I can't seem to figure out why for the life of me. Below is the code:
Private Sub VerifyNoDuplicateEmail() Dim conn As SqlConnection Dim sql As String Dim cmd As SqlCommand Dim id As Guid sql = "Select UserID from SDCUsers where email='{0}'" sql = String.Format(sql, txtEmail.Text) cmd = New SqlCommand(sql, conn) conn = New SqlConnection(ConfigurationSettings.AppSettings("cnSDCADC.ConnectionString")) conn.Open() Try 'The first this we need to do here is query the database and verify 'that no one has registed with this particular e-mail address id = cmd.ExecuteScalar() Response.Write(id.ToString & "<BR>") Catch Response.Write(sql & "<BR>") Response.Write("An error has occurred: " & Err.Description) Finally If Not id.ToString Is Nothing Then 'The e-mail address is already registered. Response.Write("Your e-mail address has already been registered with this site.<BR>") conn.Close() _NoDuplicates = False Else 'It's safe to add the user to the database conn.Close() _NoDuplicates = True End If End Try End Sub
Web.Config <appSettings> <!-- User application and configured property settings go here.--> <!-- Example: <add key="settingName" value="settingValue"/> --> <add key="cnSDCADC.ConnectionString" value="workstation id=STEPHEN;packet size=4096;integrated security=SSPI;data source=SDCADC;persist security info=False;initial catalog=sdc" /> </appSettings>
I have written a CLR Function in C#. The function works as expected except that I am trying to read data some data during the function call and get the following error:
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_SLARemaining":
System.InvalidOperationException: ExecuteReader: Connection property has not been initialized.
System.InvalidOperationException:
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
I am accessing SQL2005 with C# code using OleDbConnection.
A try and catch block catches the following error once a while between the Open() and Close() of the connection:
ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
I do not even have any idea where to start to debug this. The ExecuteNonQuery() runs a delete SQL query. It works 99.9% of the time. I do not see anything wrong when this error happens.
