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.
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()
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.
Programming to simplify, don't look for difficult way
Suwandi - Non Graduate Programmer
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:
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()
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.
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)
Member
1 Points
6 Posts
Problem Inserting new data on Database
Dec 19, 2013 12:05 PM|ghlevin|LINK
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!
All-Star
35169 Points
9928 Posts
Moderator
Re: Problem Inserting new data on Database
Dec 19, 2013 12:12 PM|bbcompent1|LINK
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()All-Star
50601 Points
9820 Posts
Re: Problem Inserting new data on Database
Dec 19, 2013 05:13 PM|A2H|LINK
Hi,
You can also try the solution suggested in the below link
Aje
My Blog | Dotnet Funda
All-Star
52001 Points
15414 Posts
Re: Problem Inserting new data on Database
Dec 19, 2013 08:03 PM|oned_gk|LINK
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.
Suwandi - Non Graduate Programmer
All-Star
25756 Points
7019 Posts
Re: Problem Inserting new data on Database
Dec 20, 2013 06:52 AM|hans_v|LINK
Not usually, it is always a security issue
No, you need only modify rights
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.
And to find out the appropriate user, use Environment.Username
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
Member
1 Points
6 Posts
Re: Problem Inserting new data on Database
Dec 20, 2013 12:05 PM|ghlevin|LINK
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!
All-Star
35169 Points
9928 Posts
Moderator
Re: Problem Inserting new data on Database
Dec 20, 2013 12:40 PM|bbcompent1|LINK
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()All-Star
35169 Points
9928 Posts
Moderator
Re: Problem Inserting new data on Database
Dec 20, 2013 12:53 PM|bbcompent1|LINK
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.
All-Star
25756 Points
7019 Posts
Re: Problem Inserting new data on Database
Dec 20, 2013 03:52 PM|hans_v|LINK
Please read the link in my previious post. The problem is that you don't have (sufficient) permissions!
All-Star
25756 Points
7019 Posts
Re: Problem Inserting new data on Database
Dec 20, 2013 03:57 PM|hans_v|LINK
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)