Update acess database from vb 2005?

[04petersric]

Hi, i've been trying to figure out how to update a row in my database, using a set of fields from the current form. The code which i have made so far is shown below, however it always comes up with an error message:

 Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
        Dim SQL As String
        Dim updatelogin As New OleDbCommand
        Try
            Dim ObjectToedit As String
            ObjectToedit = txtusername.Text
            conn.Open()
            SQL = "UPDATE login.Username ('" = txtusername.Text & "' and set Password = '" & txtpassword.Text & "' and set title = '" & cbxtitle.SelectedIndex & "' and set Firstname = '" & txtfirstname.Text & "' and set Lastname = '" & txtsurname.Text & "' and set Age = '" & txtage.Text & "'WHERE ((username=" & ModifyUsers.lblselectedusername.Text & "')"
            updatelogin = New OleDbCommand(SQL, conn)
            updatelogin.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Somthang broke Pa! : " & ex.ToString)
        End Try
        conn.Close()
End sub

Please could you give me any pointers as to get it working? the connection to the database itself works, and this is the connection string:

 Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = cricket.mdb")

I'm hoping to update the row, based upon what is shown in the text box: ModifyUsers.lblselectedusername.Text

Thanks for any help

[Hack]

Moved To VB.NET

[stanav]

Your update statement is not correct. It should look something like this:

Dim SQL As String = String.Format("UPDATE login SET (Username = '{0}', Password = '{1}', Title = '{2}', Firstname = '{3}', Lastname = '{4}', Age = {6} WHERE Username = '{7}'", _
                                          txtusername.Text, txtpassword.Text, cbxtitle.SelectedItem.ToString, txtfirstname.Text, txtsurname.Text, CInt(txtage.Text), ModifyUsers.lblselectedusername.Text)

However, I strongly suggest you to use parameters in your queries instead of string concatenation. There are many tutorials and examples on this site about parameterized queries. Just do a search and you should find them.

[04petersric]

Thanks for your answer and your advice i need a quick fix and it looks as though what you said should work... however, i tried implementing it, and the error came up with:
Index(zero based) must be greater than or equal to zero and less than the size of the argument list'

do you know what this means? i'm relatively new to vb so your help is much appreciated! thanks

[stanav]

Change Age = '{6}' to Age = '{5}' and Username = '{7}' to Username = '{6}'.
That is, if you look at the whole sentence, the numbers should goes from 0 to 6. Right now, it goes from 0 to 4 then jumps to 6 (skipping 5).