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 hardway ...
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)
ghlevin
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!
bbcompent1
All-Star
35080 Points
9827 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()A2H
All-Star
47080 Points
8921 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
A2H
My Blog | Linkedin | Dotnet Funda
oned_gk
All-Star
50490 Points
14820 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
hans_v
All-Star
25756 Points
7025 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
ghlevin
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!
bbcompent1
All-Star
35080 Points
9827 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()bbcompent1
All-Star
35080 Points
9827 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.
hans_v
All-Star
25756 Points
7025 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!
hans_v
All-Star
25756 Points
7025 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)