View Full Version : Writting to a database troubles

05-08-2007, 09:06 PM
I have a database with a table called CDTest and the fields CDName, CDPrice, DateAdded and UName. I’m having trouble with my code, I cant quite figure out whats wrong and I was hoping someone could help. Its supposed to take CDIDString and find a match in the database and allow the user to update CDPrice and UName. Here’s the code…

Dim cnn As Data.SqlClient.SqlConnection
Dim cmd As Data.SqlClient.SqlCommand
Dim param As Data.SqlClient.SqlParameter
Dim strSQL As String
strSQL = "Insert Into CDTestTBL(CDPrice, UName) Values(@CDPrice, @UName) WHERE CDID = '" & CDIDString & "'"
Dim conn As String
conn = "Data Source=.\SQLEXPRESS;AttachDbFilename=""|DataDirectory|\aspnetdb.mdf"";Integrated Security=True;User Instance=True"

cnn = New Data.SqlClient.SqlConnection(conn)
cmd = New Data.SqlClient.SqlCommand(strSQL, cnn)

param = New Data.SqlClient.SqlParameter("@CDPrice",Data.SqlDbType.VarChar)
param.Value = CDPriceString

param = New Data.SqlClient.SqlParameter("@UName",Data.SqlDbType.VarChar)
param.Value = UNameString


An error appears when I run the code saying “Incorrect syntax near the keyword 'WHERE'.” and points to…

Could anyone lend a hand?

05-08-2007, 10:11 PM
When using real parameters, you don't need single quotes.
That's part of the beauty of them. :)

05-09-2007, 01:59 PM
Your right I've changed it now and I've also changed it from Insert to Update. It seems to update the data fine at the moment. Thanks for the help.

05-19-2007, 12:07 AM
Don't believe that you need the where clause on an insert as you are inserting a new record. Now if you were doing an UPDATE then the where clause would be valid.

strSQL = "Insert Into CDTestTBL(CDPrice, UName) Values(@CDPrice, @UName) WHERE CDID = '" & CDIDString & "'"

should probably be

strSQL = "Update Into CDTestTBL(CDPrice, UName) Values(@CDPrice, @UName) WHERE CDID = '" & CDIDString & "'"

semper fi...

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum