Help with update

hendrikbez

I am not getting my program to update my access 2003 database. What the hek am I doing wrong. This is the only problem that I still have in my program Here is my code (function)

Public Function Edit() As String
Try

        Dim OleDbConn As OleDbConnection = New OleDbConnection(ConnString)
        OleDbConn.Open()

        Dim MyOledbCommand As OleDbCommand = New OleDbCommand()

        Dim String1, String2, String3, String4, String5, string6, string7, string8, string9, string10, string11, string12, string13 As String

        String1 = TxtLuidraad.Text
        String2 = txtA1.Text()
        String3 = txtA2.Text()
        String4 = txtA3.Text()
        String5 = txtA4.Text()
        string6 = txtA5.Text()
        string7 = txtA6.Text()
        string8 = txtA7.Text()
        string9 = txtA8.Text()
        string10 = txtA9.Text()
        string11 = txtA10.Text()
        string12 = txtA11.Text()
        string13 = txtA12.Text()

        MyOledbCommand.CommandText = "UPDATE Tblokkies SET WHERE leidraad = ?, A1 = ?, A2 = ?, A3 = ?,A4 = ?, A5 = ?, A6 = ?, A7 = ?, A8 = ?, A9 = ?, A10 = ?, A11 = ?, A12 = ?"
        
        MyOledbCommand.Parameters.AddWithValue("@string1", Me.TxtLuidraad.Text)
        MyOledbCommand.Parameters.AddWithValue("@string2", Me.txtA1.Text)
        MyOledbCommand.Parameters.AddWithValue("@string3", Me.txtA2.Text)
        MyOledbCommand.Parameters.AddWithValue("@string4", Me.txtA3.Text)
        MyOledbCommand.Parameters.AddWithValue("@string5", Me.txtA4.Text)
        MyOledbCommand.Parameters.AddWithValue("@string6", Me.txtA5.Text)
        MyOledbCommand.Parameters.AddWithValue("@string7", Me.txtA6.Text)
        MyOledbCommand.Parameters.AddWithValue("@string8", Me.txtA7.Text)
        MyOledbCommand.Parameters.AddWithValue("@string9", Me.txtA8.Text)
        MyOledbCommand.Parameters.AddWithValue("@string10", Me.txtA9.Text)
        MyOledbCommand.Parameters.AddWithValue("@string11", Me.txtA10.Text)
        MyOledbCommand.Parameters.AddWithValue("@string12", Me.txtA11.Text)
        MyOledbCommand.Parameters.AddWithValue("@string13", Me.txtA12.Text)


        MyOledbCommand.Connection = OleDbConn

        MyOledbCommand.ExecuteNonQuery()
        OleDbConn.Close()

        DisableTextboxes()
        NeroBar1.Value = 0

        FillDataGrid("Select \* from Tblokkies")

Pete OHanlon

hendrikbez wrote:

MyOledbCommand.CommandText = "UPDATE Tblokkies SET WHERE leidraad = ?, A1 = ?, A2 = ?, A3 = ?,A4 = ?, A5 = ?, A6 = ?, A7 = ?, A8 = ?, A9 = ?, A10 = ?, A11 = ?, A12 = ?" MyOledbCommand.Parameters.AddWithValue("@string1", Me.TxtLuidraad.Text)

Okay - at least part of your problem lies here. You use positional parameters (?) in your statement, but named parameters in your parameters list. They need to be consistent.

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys | Onyx

hendrikbez

even if I use this

   MyOledbCommand.CommandText = "UPDATE Tblokkies SET leidraad = @string1, A1 = @string2, A2 = @string3, A3 = @string4,A4 = @string5, A5 = @string6, A6 = @string7, A7 = @string8, A8 = @string9, A9 = @string10, A10 = @string11, A11 = @string12, A12 = @string13"

I can not get it to save to the database

Lost User

I think that the WHERE part would be moved to the end of the statement, and you might want to check the parameters;

"UPDATE Tblokkies
SET A1 = @string2,
A2 = @string3,
A3 = @string4,
A4 = @string5,
A5 = @string6,
A6 = @string7,
A7 = @string8,
A8 = @string9,
A9 = @string10,
A10 = @string11,
A11 = @string12,
A12 = @string13
WHERE leidraad = @string1"

Enjoy :)

I are troll :)

hendrikbez

Thanks, now I am getting a answer back, that it did not save it to database, before, I did not get anyting back Edit Ps sometimes it says that is have saved the info, but there it did not sdaved it in the data base

Lost User

hendrikbez wrote:

Ps sometimes it says that is have saved the info, but there it did not sdaved it in the data base

That might happen when there are no records matching the parameter @string1; in that case it will return with succes, since it updated all records matching the criteria (iow, no records at all). Try the query outside of your code, and verify whether it works as intended :)

I are troll :)

hendrikbez

My string1 and string2 will always have info in, I only want to add string3 today, and later string4 and so on, so when I click on my listbox it chooses the info and put it in my textboxers, then I add a new word to string3, then it must update, but it don't

Lost User

It will only update the row in the table that has "@string1". That means that the row has to exist before you can update it. Can you put a breakpoint there and verify that "string1" is filled with the correct key? In other words, I'm interested in the values that you're writing to the database.

I are troll :)

hendrikbez

With my Breakpoint. 1. Click on button "Edit info" 2. Say I must choose a leidraad to edit 3. Choose number 4 from listbox On this code

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

    Dim SqlStr As String

    SqlStr = "Select \* from tblokkies where leidraad = """ & ListBox1.Items(ListBox1.SelectedIndex) & """"

    FillTextBox(SqlStr)
End Sub

On F8 SqlStr = "Select * from tblokkies where leidraad = """ & ListBox1.Items(ListBox1.SelectedIndex) & """" SqlStr ========"Sqltr "Select *from tblokkies where leidraad = "'n uitroep"" ListBox1.SelectedIndex)=========="Listbox1.Seletectedindex 3 FillTextBox(SqlStr) ========"Filltextbox Nothing It then fills my textboxes with this info 4. info in this from row in my table ====Luidraad ='n uitroep" A1=gits, A2=aag,A3=SA (My textboxes luidraad,A1,A2,A3,) Want to put info now in A4=test" (textbox A4) 4. Click on Save button. On this code after F8

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

    If SaveOrEdit = "Save" Then

        Dim add As DialogResult

        add = Cls\_MessageBbox.Show("Is jy Seker jy will Leidraad" & vbCrLf & vbCrLf & TxtLuidraad.Text & vbCrLf & vbCrLf & "Byvoeg", "Byvoeg.", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

        If add = Windows.Forms.DialogResult.Yes Then
            AddNew()
        Else
            ListBox1.Text = "Geen leidraad is Bygevoeg nie"

        End If

    ElseIf SaveOrEdit = "Edit" Then

        Edit()
    Else

        StatusLabel.Text = " Geen Leidraad is bygevoeg"

    End If

End Sub

If SaveOrEdit = "Save" Then =====Saveoredit "" Then jumbs to ElseIf SaveOrEdit = "Edit" Then =======Saveordeit "" StatusLabel.Text = " Geen Leidraad is bygevoeg"=========Statuslabel.text "Leidradd :'n Uiroep: geselkteer" After F8 StatusLabel.Text = " Geen Leidraad is bygevoeg"=========Statuslabel.text "Geen Leidraad is bygevoeg" This is where it stops.

Lost User

I understood that the btnAdd_Click handler is the save-button. Somehow it does trigger this for the first three textboxes (as you said they were saved correctly), but number four is out. The variable SaveOrEdit hasn't got the value "Edit", which would trigger the Edit() function. The question remains why it works for the first three textboxes differently then for the fourth. It 'feels' like there's something wrong with the program-flow, but I can't point where the pain precisely is :sigh:

I are troll :)