Can we use both Insert and Update in the same button operation?

Question

I have a Table name as sales and another is balance. I want to use insert command in sales and update for the balance table. What can I do?

     cmd.CommandText = " INSERT INTO SALES VALUES('" & ComboBox1.Text & " ' , " & SILVER & " ," & GOLD & ",'" & ComboBox2.Text & "'," & KILO.Text & ", " & TOUCH.Text & " ," & TOTKILO.Text & "," & TextBox3.Text & "," & TextBox8.Text & "," & KGOLD & "," & KSILVER & "," & TextBox9.Text & " ," & TextBox10.Text & "," & TextBox11.Text & "," & TextBox12.Text & " , " & TextBox13.Text & " )"
     Dim NB As Double
    NB = TextBox11.Text
    ST = ComboBox1.SelectedValue.ToString
    cmd.CommandType = " UPDATE BALANCE SET OBBALANCE = " & " " & NB & " " & " WHERE         CUSTOMERNAME =  " & " '" & ST & "'" & " "
    cmd.Connection = con
    cmd.ExecuteNonQuery()
    con.Close()

It shows an error as Conversion from string " UPDATE BALANCE SET OBBALANCE = " to type 'Integer' is not valid.

Little Bobby Tables (http://xkcd.com/327/) would be proud. Use parameterized queries instead of using concatenation. — Thomas, Apr 27 '11 at 15:38
Not directly related to the question...but [SQL Injection vulnerability](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain) is an issue here — Matt Wilko, Apr 27 '11 at 15:40

score 3 · Answer 1 · edited May 23 '17 at 12:18

3

You could add a semi colon at the end of the INSERT statement to split them into two separate statements. But your error is that your are setting your CommandType to a string and this is an enumeration: CommandType Enumeration

edited May 23 '17 at 12:18

Community

1
1

answered Apr 27 '11 at 15:35

Matt Wilko

26,994
10
93
143

score 1 · Answer 2 · answered Apr 27 '11 at 15:34

CommandType is an enum which tells what a value in CommandText is: a query, a table name etc.

You can't put the query text there.

Instead, you should do either of the following (from best to worst):

Put both commands into a stored proc on server side and call the stored proc. This would be a better way.
Append the UPDATE command to the INSERT command.
Create another instance of ADO.Command and run it again in the same transaction.

score 0 · Answer 3 · answered Apr 27 '11 at 15:38

You can send both to the server using ;:

cmd.CommandText="insert into ...(...) values(...); update ... set ..."
cmd.Connection=con
cmd.ExecuteNonQuery()

This is also how you'd get back the identity value used, you'd append a ;select @@identity after the insert.

The others covered why CommandType is the wrong property to touch so I'll leave it at that.

Can we use both Insert and Update in the same button operation?

3 Answers3