Hi,

I am trying to update fields of record customer when the customer wishes to change some of his information . I am facing two problems. First, Whenever i try to update all the fields, it gives me Update syntax error. So i tried to update just one field to test out, there was no error but the record in database didn't get updated.

Can anyone guide me? I must had done something wrong. Thanks alot in advance


[Access Database Layout]

userid password first_name last_name dob designation addr m_addr home_no hp_no email country usertype passport gender


[Flow of logic]

1)<asp:Button CommandName="update" oncommand ="Button_Command" Text="Update" runat="Server" />

2) Sub Button_Command ( s As Object, e As CommandEventArgs)

if e.CommandName = "login" then

If CheckLogin(SeekUsername.Text, SeekPassword.Text) Then
Session("LoginUsername") = SeekUsername.Text
Response.Redirect("/austravel/TravelGuide/TravelAd.aspx")
Else
Response.Redirect("/austravel/member/forget.aspx")
End If

else if e.CommandName = "update"

If UpdateData(Session("LoginUsername"), txtfirstname.Text, listgender.SelectedItem.Text, txtlastname.Text , txtdob_day.Text+txtdob_mth.Text+txtdob_year.Text, listDesignation.SelectedItem.Text, txtaddr.Text, txtmailaddr.Text, txtcontactH.Text, txtcontactHP.Text, txtemail.Text, listcountry.SelectedItem.Text ,txtpassport.Text) Then
lblError.Text = "Particulars have been updated successfully."
'Else
'Response.Redirect("/austravel/member/updatefail.aspx")
End If

End If
End Sub

3) Function UpdateData(ByVal userid As String, ByVal first_name As String,ByVal gender As String, ByVal last_name As String, ByVal dob As String, ByVal designation As String, ByVal addr As String, ByVal m_addr As String, ByVal home_no As String, ByVal hp_no As String, ByVal email As String, ByVal country As String, ByVal passport As String) _
As Boolean

Try

Dim myConn As New OleDbConnection( _
System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_STRING_connAUSTravel"))


'This gives update syntax eror

Dim strSQL as string = "UPDATE customer SET [first_name] = '" & first_name & "' ," & _
"[last_name] = '" & last_name & "' ," & _
"[email] = '" & email & "' ," & _
"[gender] = '" & gender & "' ," & _
"[dob] = '" & dob & "' ," & _
"[country] = '" & country & "' ," & _
"[passport] = '" & passport & "' , " & _
"[designation] '" & designation & "' ," & _
"[addr] = '" & addr & "' , " & _
"[m_addr] = '" & m_addr & "' , " & _
"[home_no] = '" & home_no & "' , "& _
"[hp_no] = '" & hp_no & "' , "& _
"WHERE userid = '" & userid & "';"


'This gives no error but record did not get updated

Dim strSQL as string = "UPDATE customer SET first_name='" & _
first_name & "' WHERE userid='" & _
userid & "';"
Dim myCmd As New OleDb.OleDbCommand(strSQL, myConn)
Dim myResult As Object

If myConn.State = ConnectionState.open Then
myConn.close()
End If


myConn.open
myResult = myCmd.ExecuteNonQuery
myConn.Close()
If CInt(myResult > 0) Then
Return True
Else
Return False
End If

lblError.Text = "Particulars have been updated successfully."

Catch oleDbException As System.Data.OleDb.OleDbException

lblError.Text = oleDbException.Message

End Try
End Function

What is the locktype of your recordset? If it is a read-only or forward-only recordset then you cannot update it.

i do not think it is in read only mode. I am just using microsoft access db

Whenever I write SQL queries through VB or VBA I always test the resulting query and then run it on the database. In your case do this:

Response.Write(strSQL)

Copy the resulting string and try it out does it give the results you are looking for? It may not be a syntax error but just there are no records that match your criteria.