Updating multiple columns in SQLite

[mozaharul]

I’m using a table control to make change in multiple columns and want to update those changes in the database by the click event of a button. The numbers of columns to be changed are arbitrary (some time 2 columns, some time 4 columns are to be changed). The statement to update a single column :

cmd.CommandText = "update table-name set column1 = '"&txtbox1.Text&"' where column2= '"& txtbox2.Text& "';"

Then do I have to use as many textboxes as the numbers of columns I want to update? Or there is a way to update the whole table at once without using textboxes?

Please give some guideline.

Thanks and regards

[Erel]

Where is the new data stored (textbox, variable...)?

[mozaharul]

I changed the table's column value directly through a textbox like:

Sub Btnchange_Click
If txtfilter.Text="" Then
Msgbox("No column to change")
Else If Table2.SelectedRow < Table2.RowCount Then
Table2.Cell(Table2.SelectedCol,Table2.SelectedRow) = txtfilter.Text
End If
btnupload.Enabled=true
End Sub

In this way I can change as many columns as it is required.
... ...
... ...

Then can update a single column like:

Sub Btnupload_Click
cmd.CommandText = "update geo set ed = '"&txtfilter.Text&"'where hpid = '"& txtsearch.Text& "';"
cmd.ExecuteNonQuery
End Sub


But the changes made to other columns are not updated.

Thanks for your valuable time.

[Erel]

You can update multiple columns by using:
"UPDATE geo SET col1 = 'value1', col2 = 'value2', col3 = 'value3' WHERE ..."
Use Table.Cell to read the values from the table.

[nl1007]

It's a good idea to add Parameters for each value; then you don't need to worry about delimiting strings, and escaping "'" characters. Also, if you're using values the user has typed in, it avoids "SQL insertion" (adding ";" and extra SQL commands).

Sub AddCmdParams
' Parish,Street,Position,BarCode,Support,CType,Mater ial,Protection,..
Cmd.AddParameter("Parish")
Cmd.AddParameter("Street")
...
then:
sSQL = "Update tblColumns Set "
Conn.BeginTransaction
sTmp = txtParish.Text ' get value from form textbox (or combo)
tblData.Cell("Parish",Row) = sTmp ' update table from form
sSQL = sSQL & "Parish=@Parish, "
Cmd.SetParameter("Parish",sTmp)
(repeat for additional fields; omit final comma!)
now add condition (primary key):
sTmp = " WHERE (UN_Unit='" & Unit_ID & "')"
sSQL = sSQL & sTmp
cmd.CommandText = sSQL
iTmp = Cmd.ExecuteNonQuery
If iTmp <> 1 Then Msgbox(iTmp & " records updated") ' should be only 1 record!
Conn.EndTransaction

I used panels to display and update groups of fields; the table control was in the background, and could be saved to a CSV file, as well as the database.