David Peruso
asked on
Problem with the SQL update command using WHERE in vb.net(15)
I have tried various combinations for the WHERE that I found on the net but didn’t fix the problem
Here is a code snipit
'PcbNumber is Key field
‘-------------------------
oConn = New OleDbConnection(strSQLpath
'Open the connection
oConn.Open()
oQuery = "UPDATE PcbBom SET pq1= 222222 WHERE PcbNumber = 2990"
'oQuery = "INSERT INTO PcbBom (PcbNumber, pq1) VALUES (2875a, 555555)"
oComm = New OleDbCommand(oQuery, oConn)
oComm.ExecuteNonQuery()
'dsDdsMrpDatbaseWorking.Ta
oConn.Close()
‘-------------------------
The insert command works, I can add a new row, I can update all pq1 columns if I don’t use the WHERE statement,
but when I try to update the field “pq1” using the WHERE I get this error message:
AT SUB/FUNCTION LOCATION: FunSaveBom 0
System.Data.OleDb.OleDbExc
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at DDS_MRP.ModuleSubs.FunSave
As a side note I’m connecting to multiple tables in the database and for some reason I can only use the dataadaptor.update on one table, if I try to use the dataadaptor.update method on any other table I get errors so I decided to try using the SQL method, using SQL I can add columns and rows. Now all I need to do is to be able to edit specific fields.
when I connect to the database:
sqlString = "SELECT * FROM PcbBom"
This is one of the five adaptors I’m using
daPcbBomWorking = New OleDb.OleDbDataAdapter(sql
daPcbBomWorking.Fill(dsDds
Here is a code snipit
'PcbNumber is Key field
‘-------------------------
oConn = New OleDbConnection(strSQLpath
'Open the connection
oConn.Open()
oQuery = "UPDATE PcbBom SET pq1= 222222 WHERE PcbNumber = 2990"
'oQuery = "INSERT INTO PcbBom (PcbNumber, pq1) VALUES (2875a, 555555)"
oComm = New OleDbCommand(oQuery, oConn)
oComm.ExecuteNonQuery()
'dsDdsMrpDatbaseWorking.Ta
oConn.Close()
‘-------------------------
The insert command works, I can add a new row, I can update all pq1 columns if I don’t use the WHERE statement,
but when I try to update the field “pq1” using the WHERE I get this error message:
AT SUB/FUNCTION LOCATION: FunSaveBom 0
System.Data.OleDb.OleDbExc
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at DDS_MRP.ModuleSubs.FunSave
As a side note I’m connecting to multiple tables in the database and for some reason I can only use the dataadaptor.update on one table, if I try to use the dataadaptor.update method on any other table I get errors so I decided to try using the SQL method, using SQL I can add columns and rows. Now all I need to do is to be able to edit specific fields.
when I connect to the database:
sqlString = "SELECT * FROM PcbBom"
This is one of the five adaptors I’m using
daPcbBomWorking = New OleDb.OleDbDataAdapter(sql
daPcbBomWorking.Fill(dsDds
ASKER
Thanks,
That worked, I thought I tried that before.
Sometimes you just get wrapped around the axial.
That worked, I thought I tried that before.
Sometimes you just get wrapped around the axial.
You should also convert them to BIND variables.
hmm, sure that
'oQuery = "INSERT INTO PcbBom (PcbNumber, pq1) VALUES (2875a, 555555)"
Verify the data type of the "pq1" column. Or just add quotes around the 222222 also, since quotes can't hurt, but could help tremendously if applicable:
oQuery = "UPDATE PcbBom SET pq1= '222222' WHERE PcbNumber ='2990' "
oQuery = "UPDATE PcbBom SET pq1= '222222' WHERE PcbNumber ='2990' "
>>since quotes can't hurt
The "can" for the same reason the update didn't work to begin with. Some times implicit data type conversions don't go as expected.
You should get in the habit and ALWAYS do explicit type conversions and use strings with strings, numbers with numbers and dates with dates. Then you can NEVER go wrong (I know I'll regret saying NEVER).
The "can" for the same reason the update didn't work to begin with. Some times implicit data type conversions don't go as expected.
You should get in the habit and ALWAYS do explicit type conversions and use strings with strings, numbers with numbers and dates with dates. Then you can NEVER go wrong (I know I'll regret saying NEVER).
>>since quotes can't hurt
The "can" for the same reason the update didn't work to begin with. Some times implicit data type conversions don't go as expected.
I don't see how it could hurt, since char/varchar is the LCD format. How could it hurt SQL performance? SQL will implicitly convert varchar to a higher precedence data type if/when needed.
What could seriously hurt is specifying a numeric comparison value to a varchar column: then SQL must implicitly convert every column value to numeric before comparing, OUCH!
I was commenting on "pq1= '222222'" If pq1 is a number column, adding the quotes is unnecessary and silly.
>>then SQL must implicitly convert every column value to numeric before comparing, OUCH!
Yes, if one explicitly converts all the time, no ouch.
I stand by my statement on getting into the habit of always using explicit data type conversions.
>>then SQL must implicitly convert every column value to numeric before comparing, OUCH!
Yes, if one explicitly converts all the time, no ouch.
I stand by my statement on getting into the habit of always using explicit data type conversions.
And I stand by using varchar for literal value when it's at all possible the column data type will change. Otherwise, you risk having to do a lot of re-work to avoid errors.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Try:
oQuery = "UPDATE PcbBom SET pq1= 222222 WHERE PcbNumber = '2990'"