UPDATE query problem

[limepebblez]

Hi people, I'm having a small problem in ASP.NET + Microsoft Access. Here is my problem:

1) I have a table (item_table) with 3 fields
2) f1-ID, f2-Item, f3-Total
2) In my asp page, I am having 1 dropdownlist (DropDownList1) and a button (btn1)
3) DropDownList1 contains values from 0-10.

After selecting a value from DropDownList1, I hit btn1. What I want to do after clicking btn1 is, to update f3(Total) in item_table by adding the selected value from DropDownList1 with current value from Total.
I need the UPDATE command. Currently I'm using this code which gives syntax error:

'==========================================================
Dim sqlupdate as String = " UPDATE item_table SET Total = Total + '" & DropDownList1 & "' "
Dim myupdateexe as New OleDb.OleDbCommand
myupdateexe = New OleDb.OleDbCommand(sqlupdate , con)
myupdateexe .ExecuteNonQuery()

'==========================================================

How do I insert value taken from my dropdownlist into the update query?
Any help? Thank you in advance =)

[SheSaidImaPregy]

Well, first of all I would suggest adding the total before inserting it into the SQL, just my opinion. This way you can also do validation if needed. But since validation is not what you are after at this moment, here you go. I am also assuming that this isn't the complete code and that con was declared and set.. Below is some changed code:

Dim sqlupdate as String
Dim myupdateexe as OleDbCommand
Dim sqlupdate = "UPDATE item_table SET Total = Total + " & DropDownList1.SelectedItem.Value & ""
'Do not make the mistake of putting it in 'string' as that indicates it as a string. since you are adding integers together, you cannot use 'integer', it must just be integer. If you are using strings, then add back the 'string'.
myupdateexe = New OleDbCommand(sqlupdate , con)
myupdateexe.ExecuteNonQuery()

[limepebblez]

Hi,
I have tried with the codes you suggested but it is still giving syntax error. I think the error is at this particular line:

Dim sqlupdate as String = " UPDATE item_table SET Total = Total + '" & DropDownList1 & "' "

the code after Total = Total + ..... is the problem I guess. It wud be easy to calculate the total before updating the database, but in my case, I need to be able to update the cumulative total into the database right away without extracting the current values or what-so-ever. Are there any SQl queries which takes a new value from say a dropdownlist or a textbox, and another value which is the current total of row 1 from column A, then adds up both of them and updates row 1 column A?

[SheSaidImaPregy]

Well, please make sure that you have tried it without the " ' " in your codes. You may be having an error incase your query is trying to add two values that aren't numbers, or if one is null. Add this line to your code to check it, then update it.

IsNull(Total, 0)

What this does it check whether or not Total has a value, if it doesn't, it sets it to zero. I recommend you pulling the information from the database via ExecuteScalar() method (just the total), then do some validation. After your validation has passed, set the new total and then update. The code above, to me, will not work but that is because of the ' before the " and after it also. Because you are adding integers together, if you add the ' before, it treats it as a string, and you cannot add strings together that way. Make sure you use the code exactly as below. It works on my computer, must be able to work on yours. If it doesn't, then set your Total column equal to a number.

Dim sqlupdateAs String = "Update item_table SET Total=Total+" & DropDownList1.SelectedItem.Value & ""

You can always switch .Value to .Text to see if it works for you better. Otherwise, please use the code below with modifications:

Dim cmdSelectTotal As OleDbCommand
Dim conString As String ''your connection string
Dim sqlupdate As String
Dim intTotal As String
Dim conn As OleDbConnection
conString = "yourconnectionstringorreferencetoweb.config"
conn = New OleDbConnection( conString )
sqlupdate = "SELECT Total FROM item_table"
'For the above line, you should have some comparison for WHERE
'you're pulling the Total from, like a SessionID or UserID, or as
'long as you retrieve only one record you're fine.
cmdSelectTotal = New OleDbConnection ( sqlupdate, conn )
intTotal = cmdSelectTotal.ExecuteScalar()
if IsNull(intTotal) then
  intTotal = 0
end if
if IsNumeric(DropDownList1.SelectedItem.Value) then
  if intTotal >= 0 and IsNumeric(intTotal) then
    intTotal += DropDownList1.SelectedItem.Value ''or .Text
    sqlupdate = "UPDATE item_table SET Total=" & intTotal & ""
    cmdSelectTotal.ExecuteNonQuery()
  else
    intTotal = DropDownList1.SelectedItem.Value ''or .Text
    sqlupdate = "UPDATE item_table SET Total=" & intTotal & ""
    cmdSelectTotal.ExecuteNonQuery()
  end if
else
  lblError.Visible = True
  'The above line assumes you have some kind of label to represent
  'an error incase it shows, which happens from time to time.
  lblError.Text = "We're sorry for the inconvenience, but the table
  was not updated correctly. Please repeat your steps and try again.
  'OR JUST set the intTotal to a new value, but this depends on what
  'the code is being used for.
end if

[limepebblez]

Hi mate,
THanks alot for your help. I've tried your initial methods or eliminating ' and it still didnt work. But strangely enough, with my old coding, I've changed the update query to this and it worked:

Dim updatequery as String = " UPDATE table1 SET table1.columnA = table1.columnA + '" & dropdownlist1.selectedvalue & "' "

I changed nothing except the "table1.columnA" part, and it's working fine actually. Thanks but I've taken account your tips on checking the null value and assigning it to 0 method. Appreciate it alot. =)