locked
Problem Inserting new data on Database RRS feed

  • Question

  • User-780133558 posted

    Hey there!

    I have an ASP.Net webpage which uses a Microsoft Access database file (MDB). However, I'm having troubles in inserting new data on the MDB file. Here's the code for registering a new user:

    Dim MySQL As String = "Insert Into Users(User,Password) VALUES(@user,@password)"

    Dim myConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=e:\home\bellsistemas\Dados\Test.mdb;Persist Security Info=False")

    myConn.Open()
    Dim Cmd As New OleDbCommand(MySQL, myConn)
    Cmd.Parameters.Add(New OleDbParameter("@user", TextLogin2.Text))
    Cmd.Parameters.Add(New OleDbParameter("@password, TextSenha2.Text))

    Cmd.ExecuteNonQuery()

    myConn.Close()

    Then I get an error message: 

    Operation must use an updateable query.

    It's a strange thing, because I can edit or delete data when I have a Gridview showing the data from the respective database but I can't insert new data.

    Did I forgot something?

    Thanks for those who can help me!

    Thursday, December 19, 2013 12:05 PM

Answers

  • User-718146471 posted

    Hey, does your database file have a password? If so, you will need to provide the username and password in your connection string to get exclusive access to the DB.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 20, 2013 12:53 PM
  • User-1199946673 posted

    Dim MySQL As String = "Insert Into Users(User,Password) VALUES(@user,@password)"

    Regarding this query: Password is a Reserved Word in Jet 4.0.You need to surround this with square brackets when you use reserved words as field (or table) names, especially in Action query's (for some reason this isn't neccessary in Select queries)

    Dim MySQL As String = "Insert Into Users(User,[Password]) VALUES(@user,@password)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 20, 2013 3:57 PM

All replies

  • User-718146471 posted


    What I would do if it were me is place a breakpoint at Dim MySQL as String and step through to see if something is wrong that way. Also, placing your database operations into try/catch blocks helps narrow down what is wrong.  So re-write your block like this:

     

    Dim MySQL As String = "Insert Into Users(User,Password) VALUES(@user,@password)"
    
    Dim myConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=e:\home\bellsistemas\Dados\Test.mdb;Persist Security Info=False")
         Try
              myConn.Open()
              Dim Cmd As New OleDbCommand(MySQL, myConn)
              Cmd.Parameters.Add(New OleDbParameter("@user", TextLogin2.Text))
              Cmd.Parameters.Add(New OleDbParameter("@password, TextSenha2.Text))
              Cmd.ExecuteNonQuery()
         Catch ex as Exception
              Response.Write (ex.ToString())
         End Try
    myConn.Close()
    Thursday, December 19, 2013 12:12 PM
  • User2103319870 posted

    Hi,

    You can also try the solution suggested in the below link

    Thursday, December 19, 2013 5:13 PM
  • User-1716253493 posted

    ghlevin

    Operation must use an updateable query.

    It's a strange thing, because I can edit or delete data when I have a Gridview showing the data from the respective database but I can't insert new data.

    Did I forgot something?

    Thanks for those who can help me!

    Usualy, the problem above about security issue. You need full access (read and write).

    Right click the folder/file => properties => security ... give full access to appropriate user

    If you are not sure, give full permission to everyone.

    Thursday, December 19, 2013 8:03 PM
  • User-1199946673 posted

    oned_gk

    Usualy, the problem above about security issue.

    Not usually, it is always a security issue

    oned_gk

    You need full access (read and write).

    No, you need only modify rights

    oned_gk

    Right click the folder/file => properties => security ... give full access

    You need to set modify rights on the folder as well as the file where the mdb file is located, You need Modify ricght becaise an ldb (lock database) fille needs to be created and deleted.

    oned_gk

    to appropriate user

    And to find out the appropriate user, use Environment.Username

    oned_gk

    If you are not sure, give full permission to everyone.

    There's no need for that! All you need to know about this error can be found here:

    http://www.mikesdotnetting.com/Article/74/Solving-the-Operation-Must-Use-An-Updateable-Query-error

    Friday, December 20, 2013 6:52 AM
  • User-780133558 posted

    I tried using the Try statement, but it still comes the same error message.

    Is there any other ways to make a SQL Insert statement??? Maybe they can work!

    Friday, December 20, 2013 12:05 PM
  • User-718146471 posted

    Just for a test, please try it this way:

     

    Dim MySQL As String = "Insert Into Users(User,Password) VALUES('TestUser1','TestPassword')"
    
    Dim myConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=e:\home\bellsistemas\Dados\Test.mdb;Persist Security Info=False")
         Try
              myConn.Open()
              Dim Cmd As New OleDbCommand(MySQL, myConn)
              Cmd.Parameters.Add(New OleDbParameter("@user", TextLogin2.Text))
              Cmd.Parameters.Add(New OleDbParameter("@password, TextSenha2.Text))
              Cmd.ExecuteNonQuery()
         Catch ex as Exception
              Response.Write (ex.ToString())
         End Try
    myConn.Close()

    If this test works, then chances are we have to append your parameters of .Text with .ToString() so your final code should look like this:

     

    Dim MySQL As String = "Insert Into Users(User, Password) VALUES(@user, @password)"
    
    Dim myConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=e:\home\bellsistemas\Dados\Test.mdb;Persist Security Info=False")
         Try
              myConn.Open()
              Dim Cmd As New OleDbCommand(MySQL, myConn)
              Cmd.Parameters.Add(New OleDbParameter("@user", TextLogin2.Text.ToString()))
              Cmd.Parameters.Add(New OleDbParameter("@password, TextSenha2.Text.ToString()))
              Cmd.ExecuteNonQuery()
         Catch ex as Exception
              Response.Write (ex.ToString())
         End Try
    myConn.Close()

    Friday, December 20, 2013 12:40 PM
  • User-718146471 posted

    Hey, does your database file have a password? If so, you will need to provide the username and password in your connection string to get exclusive access to the DB.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 20, 2013 12:53 PM
  • User-1199946673 posted

    I tried using the Try statement, but it still comes the same error message.

    Please read the link in my previious post. The problem is that you don't have (sufficient) permissions!

    Friday, December 20, 2013 3:52 PM
  • User-1199946673 posted

    Dim MySQL As String = "Insert Into Users(User,Password) VALUES(@user,@password)"

    Regarding this query: Password is a Reserved Word in Jet 4.0.You need to surround this with square brackets when you use reserved words as field (or table) names, especially in Action query's (for some reason this isn't neccessary in Select queries)

    Dim MySQL As String = "Insert Into Users(User,[Password]) VALUES(@user,@password)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 20, 2013 3:57 PM