...

View Full Version : Writting to a database troubles



CaJack
05-08-2007, 10:06 PM
Hi,
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
cmd.Parameters.Add(param)

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

cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()


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

cmd.ExecuteNonQuery()
Could anyone lend a hand?

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

CaJack
05-09-2007, 02: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.

javabits
05-19-2007, 01: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