Asking about Updating SQL Database row

drexler_kk

Dear all, I found a very weird problem here with my Point-Of-Sales System. I'm using VB.NET 2003 connect MySQL 4.0 database for this system. I really can't figured out what happy to the following situation. I'm trying to update my database for my system but it failed. I have checked the same SQL query using SQLyog and it works. But when I put into my code,it doesn't perform the UPDATE query as I need. This is the code I used to test using SQLYog:

UPDATE pos_runningno SET Prefix='01',LastNo='80',Length='8' WHERE LocationCode='KK' AND TerminalCode='CTR01' AND Source='RECEIPTNO';

This is the code I put into my VB.NET code:

Try

        ObjComm = New OdbcCommand("UPDATE pos\_runningno SET Prefix='01',LastNo=' " & strReceiptNo & " ',Length='8' WHERE LocationCode =' " & TerminalLocate & " ' AND TerminalCode='" & TerminalID & "' AND Source='" & strSource & " '", ObjConn)
        ObjConn.Open()
        ObjComm.ExecuteNonQuery()
        ObjConn.Close()

And I have checked it by changing the data 1 by 1 to the variable inside my system and I found that TerminalLocate is the problem. I have set TerminalLocate is s string and the string value is KK. But it doesn't not update at all and doesn't provide any error message from the system. This is the structure of my database table for this row which I need to use to update:

Field Type Collation Null Key Default Extra Privileges Comment

LocationCode varchar(10) latin1_swedish_ci NO PRI select,insert,update,references
TerminalCode varchar(10) latin1_swedish_ci NO PRI select,insert,update,references
Source varchar(20) latin1_swedish_ci NO PRI select,insert,update,references
Prefix varchar(5) latin1_swedish_ci YES (NULL) select,insert,update,references
LastNo int(11) (NULL) YES 0 select,insert,update,references
Length int(11) (NULL) YES 0 select,insert,update,references

Anyone can tell me why this weird things could happen? And how to solve this matter? Thanks for reading and hope to get someone help to solve it. Regards Dre

Johan Hakkesteegt

When any sql update statement does not affect any rows at all, and no error is thrown then it is almost always due to the WHERE clause. With the conditions that are set specifically in the code, no rows will be updated. It is likely that not all of your variables actually return exactly the values that you tested with. I suggest you put a textbox somewhere (if possible) and write the entire UPDATE statement to it. Someting like:

dim SqlUpdateStatement as string = "UPDATE pos_runningno SET Prefix='01',LastNo=' " & strReceiptNo & " ',Length='8' WHERE LocationCode =' " & TerminalLocate & " ' AND TerminalCode='" & TerminalID & "' AND Source='" & strSource & " '"
ObjComm = New OdbcCommand(SqlUpdateStatement, ObjConn)
TextBox1.Text = SqlUpdateStatement
ObjConn.Open()
ObjComm.ExecuteNonQuery()
ObjConn.Close()

That way you can take a copy of the actual statement your code is trying to run and test that statement in SQLYog, or at least see exactly what parameters your code is feeding to the sql statement. Hope it helps, Johan

My advice is free, and you may get what you paid for.

drexler_kk

Yeah,thanks for your idea Mr.Johan. I have solve this problem. Its just a weird problem actually. I solve it as simple as just remove the space within the ' " & TerminalLocate & " ' to >> '"& TerminalLocate &"' solve the problem. I think its a spacing problem,Right? Thanks for your effort anyway,its a good way to check the error for me too. Regards Drex

Johan Hakkesteegt

Hi Drex,

drexler_kk wrote:

' " & TerminalLocate & " ' to >> '"& TerminalLocate &"'

Actually the problem was apparently as I described, but you did indeed solve it. Your variable TerminalLocate was correct, but because of the spaces between the single quotes and the double quotes, your code executed the UPDATE statement with the parameter value: " KK " instead of "KK" as you needed. In other words, in your code the update statement became:

UPDATE WhatWasYourTablesNameAgain SET WhatWasThatFieldsNameAgain = 'blablabla' WHERE TerminalCode = ' KK '

instead of:

UPDATE WhatWasYourTablesNameAgain SET WhatWasThatFieldsNameAgain = 'blablabla' WHERE TerminalCode = 'KK'

So yes, it was because of the spaces... ;)

My advice is free, and you may get what you paid for.