Starting to hate ASP.NET....pls help
-
Right, I have connection all working and have returned a record count from a table called User, I'm using a SQL server 2000 database. However I'm trying to bring back data using a sqlDataReader. However I'm getting data conversion error "Sytnax error converting the varchar value to 'true' to a column of data type int"
I know the SQL works...but crashes when it gets to SqlDataReader.Read method
Right...the variable I'm using to return the value is type Integer and the ID in the database is type int(4), so why isn't it working.....Any ideas?
Dim sqlusercmd As New SqlCommand
Dim userData As SqlDataReader
Dim strUser As String
Dim data_User_ID As Integer
strUser = "SELECT User_ID"
strUser = strUser & " FROM [User]"
strUser = strUser & " WHERE (User_ID = '" & receivedValue & "')"
strUser = strUser & "GROUP BY User_ID"
sqlusercmd.Connection = SqlConnectionMain()
sqlusercmd.CommandText = strUser
'Put results in reader
userData = sqlusercmd.ExecuteReader
'Read the values
userData.Read()
data_User_ID = userData.GetValue(0)
userData.NextResult()
userData.Close()
Thanks in advance,
Brenden
-
On which line are you getting this error (read is the error coming from sql server or asp net).
Can you post the strUser value before executing?
-
Did you try eliminating the single quotes around the UserID in the SQL String?
strUser = strUser & " WHERE (User_ID = " & receivedValue & ")"
instead of
strUser = strUser & " WHERE (User_ID = '" & receivedValue & "')"
I'm not sure, but the SQL may not actually get executed until the .Read method is called.
Mark -
Thanks for you replys, the program crashes at
userData.Read()
Have tried changing the sql statement to not include the single quotes, but it gives me a invalid column error if I do that
, what data type do you have to use in ASP.NET to store a sql server int variable, it is integer right?? Thanks,
Brenden
-
If taking the single quotes out creates an error, then the data type must not be integer, it must be char or varchar. So are you getting an error trying to load a string into a integer field without any conversion?
Mark -
Some suggestions:
1) Check to see if the reader has any rows:
if userData.HasRows then
2) Perform a specific conversion
data_User_ID = userData.GetInt32(0)
I generally get my data using a loop such as:
Dim entries as ArrayList
if userData.HasRows then
while userData.Read
entry = new [Class of your own definition]
entry.IntegerValue = userData.getInt32(userData.GetOrdinal(Int Column Name)
entry.StringValue = userData.getString(userData.GetOrdinal(Int Column Name)
end while
end if
userData.Close()
Its slightly slower as the routine has to make two calls to the reader to get the column number for the column name, but it can cut down on working out which column is which.
-
Hey guys,
Thanks for your sugguestions.....mark you were kind of right, I inputting the wrong datatype into the SQL statement
, which was completely stupid....no wonder it came up with a data conversion error. Thanks again for you help,
Brenden
-
You maywant to consider using the Microsoft Data application block. http://support.microsoft.com/default.aspx?scid=kb;en-us;829028 This way you don't need to worry about how to format and use datareaders, sql connection objects etc. This code block encapsulates everything you need to call stored procedures (or god forbid T-SQL code
) It works great. There is also an Exception management application block so you don't have to format and worry about what to do with error messages. You just pass a string to the code and indicate what you want done with it eg. print this on the screen or send this to the event log etc. Application blocks are completely free and seeing as you will need to develop something like this anyway why not reuse tested code.Francis
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply