Updating multiple columns in SQLite

Discussion in 'Questions (Windows Mobile)' started by mozaharul, Apr 7, 2008.

  1. mozaharul

    mozaharul Active Member Licensed User

    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
     
  2. Erel

    Erel Administrator Staff Member Licensed User

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

    mozaharul Active Member Licensed User

    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.
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    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.
     
  5. nl1007

    nl1007 Member Licensed User

    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,Material,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.
     
Loading...