Newbie update database help

[sbushway]

Dumb question, I'm sure, but I'm asking it anyway

I want to update a record in my SQL database based on the variables that are passed to the function.

Here's my function:

Public Function UpdateInfo(ByVal intID as Integer, ByVal strSuic as String, ByVal strOuic as String, ByVal strAns as String)

Dim updateSQL as String = "update ordertbl set suic = " & strSuic & ", ouic = " & strOuic & ", answers = " & strAns & "where orderid = " & intID

Dim myConn as SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString&quot)
Dim myComm as SqlCommand = New SqlCommand(updateSQL, myConn)
myConn.Open()

Dim myDR as SqlDataReader = myComm.ExecuteReader(CommandBehavior.CloseConnection)
myDR.Read()

End Function


It's not updating the record - I'm not getting an error or anything, it's just not updating.

Any help would be greatly appreciated.

Thanks
suzanne

 

[stnkyminky]

First of all verify the variable you're using in the where statement. Is it a valid orderid?

Second...


Dim myConn as SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString&quot)
Dim myComm as SqlCommand = New SqlCommand(updateSQL, myConn)
myConn.Open()
'You're not returning and records so use ExecuteNonQuery
myComm.ExecuteNonQuery

Scott
Programmer Analyst
<{{><

 

[sbushway]

I made my code look like this:

Dim updateSQL as String = &quot;update ordertbl set suic = '&quot; & strSuic & &quot;', ouic = '&quot; & strOuic & &quot;', answers = '&quot; & strAns & &quot;' where orderid = &quot; & intID

Dim myConn as SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings(&quot;ConnectionString&quot)
Dim myComm as SqlCommand = New SqlCommand(updateSQL, myConn)
myConn.Open()
'You're not returning and records so use ExecuteNonQuery
myComm.ExecuteNonQuery

But adding in the 'ExecuteNonQuery' didn't do anything - the record is still not updating.

And I started debugging and stepped through the commands to see if updateSQL was getting all of the variables correct. The statement is correct (I ran it in SQL Query Analyzer just to make sure) and looks like this:

updateSQL = update ordertbl set suic = '00001', ouic = '00011', answers = 'Y' where orderid = 5

Any more help/suggestions would be greatly appreciated.

Thank you in advance,
Suzanne

 

[stnkyminky]

Hmmm... Are you using a development and production database? Make sure you're looking/testing in the correct one.

Also, you might want to add myComm.CommandType = CommandType.Text just below where you declared the variable. However, if this were the cause I would think an error would have been raised. Unless I'm over looking something simple, your code looks right.

Scott
Programmer Analyst
<{{><

 

[sbushway]

I tried that but it didn't work. Shoot!

I'm using a development database, and I'm pretty sure I've got it set up right, but I'll double-check that since you think the code looks right.

Thank you for your time on this - I really appreciate it!

~Suzanne