Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    2

    Unanswered: Cannot update record

    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

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    What is the locktype of your recordset? If it is a read-only or forward-only recordset then you cannot update it.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    May 2004
    Posts
    2
    i do not think it is in read only mode. I am just using microsoft access db

  4. #4
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    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.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •