none
Qurey must be updateable error occur error show. RRS feed

  • Question

  • Query must be updateble error show when i publish my website on the remote server(ie www). but its works fine in the local server of ASP.NET.  please replay ans very quickly. My coding is as follow:

    Dim is_ok As Boolean = False
            Dim cmnd As String = "INSERT INTO PAPERS (Title_of_paper,Name_of_Jornal,Volume,No_paper,Month_pub,Year_pub)"
            cmnd &= " VALUES ("
            cmnd &= "'" & TextBox2.Text & "',"
            cmnd &= "'" & TextBox3.Text & "',"
            cmnd &= "'" & TextBox4.Text & "',"
            cmnd &= "'" & TextBox5.Text & "',"
            cmnd &= "'" & DropDownList2.SelectedItem.Value & "',"
            cmnd &= "'" & DropDownList1.SelectedItem.Value & "')"
            Try
                cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("data.mdb") & ";")
                cn.Open()
                cmd = New OleDbCommand(cmnd, cn)
                dr = cmd.ExecuteReader()
                is_ok = True
            Catch Ex As Exception
                error_Msg.Text = Ex.Message
                Exit Sub
            End Try
            cn.Close()

    Friday, November 26, 2010 6:03 AM

Answers

  • Most likely application generates that exception in a case if concatenated input makes SQL statement invalid. Using concatenation in SQL statements is not the best idea, since it leads to errors and potential SQL injection vulnerability. You would need to pass values to SQL statement as parameters. Your code should be something like

    Dim is_ok As Boolean = False
            Dim cmnd As String = "INSERT INTO PAPERS (Title_of_paper,Name_of_Jornal,Volume,No_paper,Month_pub,Year_pub)"
            cmnd &= " VALUES (?,?, ?, ?, ?, ?)"

     

    Create parameters and and pass values to them here

     


            Try
                cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("data.mdb") & ";")
                cn.Open()
                cmd = New OleDbCommand(cmnd, cn)
                dr = cmd.ExecuteReader()
                is_ok = True
            Catch Ex As Exception
                error_Msg.Text = Ex.Message
                Exit Sub
            End Try
            cn.Close()


    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by liurong luo Friday, December 3, 2010 1:48 AM
    Friday, November 26, 2010 11:21 AM
    Moderator
  • I would suspect a permissions issue. Keep in mind that the authenticated user account (or Network Service account if Impersonation is not enabled) of the ASP.NET application must have full permissions to the folder where the database is located when working with an Access database.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by liurong luo Friday, December 3, 2010 1:47 AM
    Friday, November 26, 2010 2:19 PM

All replies

  • Most likely application generates that exception in a case if concatenated input makes SQL statement invalid. Using concatenation in SQL statements is not the best idea, since it leads to errors and potential SQL injection vulnerability. You would need to pass values to SQL statement as parameters. Your code should be something like

    Dim is_ok As Boolean = False
            Dim cmnd As String = "INSERT INTO PAPERS (Title_of_paper,Name_of_Jornal,Volume,No_paper,Month_pub,Year_pub)"
            cmnd &= " VALUES (?,?, ?, ?, ?, ?)"

     

    Create parameters and and pass values to them here

     


            Try
                cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("data.mdb") & ";")
                cn.Open()
                cmd = New OleDbCommand(cmnd, cn)
                dr = cmd.ExecuteReader()
                is_ok = True
            Catch Ex As Exception
                error_Msg.Text = Ex.Message
                Exit Sub
            End Try
            cn.Close()


    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by liurong luo Friday, December 3, 2010 1:48 AM
    Friday, November 26, 2010 11:21 AM
    Moderator
  • I would suspect a permissions issue. Keep in mind that the authenticated user account (or Network Service account if Impersonation is not enabled) of the ASP.NET application must have full permissions to the folder where the database is located when working with an Access database.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by liurong luo Friday, December 3, 2010 1:47 AM
    Friday, November 26, 2010 2:19 PM
  • Hello,

    How about your issue now? Have you tried the suggestions?

    Can the following KB article help you?
    http://support.microsoft.com/kb/q316675/

    As you may see, this error may happen due to the aspnet account in different environment. Please take a look at the Resolutin section for workarounds.

     


    Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Monday, November 29, 2010 10:01 AM