Update acess database from vb 2005?
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:
Code:
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:
Code:
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 :)
Re: Update acess database from vb 2005?
Re: Update acess database from vb 2005?
Your update statement is not correct. It should look something like this:
Code:
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.
Re: Update acess database from vb 2005?
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
Re: Update acess database from vb 2005?
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).