SQL insert error

[Guest]

Dear All

I am trying to execute following cod

Sub SubmitBtn_Click(ByVal Sender As Object, ByVal E As EventArgs
Dim DBConn As OleDbConnectio
Dim DBInsert As New OleDbComman
DBConn = New OleDbConnection("PROVIDER="
& "Microsoft.Jet.OLEDB.4.0;"
& "DATA SOURCE="
& Server.MapPath("Emps.mdb;")
DBInsert.CommandText = "Insert Into Employees "
& "(LastName, FirstName, PhoneNumber, EmailAddress, "
& "Notes, DepartmentID) values ("
& "'" & Replace(txtLastName.Text, "'", "''") & "', "
& "'" & Replace(txtFirstName.Text, "'", "''") & "', "
& "'" & Replace(txtPhoneNumber.Text, "'", "''") & "', "
& "'" & Replace(txtEmailAddress.Text, "'", "''")
& "', "
& "'" & Replace(txtNotes.Text, "'", "''") & "', "
& ddlDepartments.SelectedItem.Value & ")
DBInsert.Connection = DBCon
DBInsert.Connection.Open(
DBInsert.ExecuteNonQuery(
Response.Redirect("./index.aspx"
End Su

while executing following
DBInsert.ExecuteNonQuery(

query i
Insert Into Employees (LastName, FirstName, PhoneNumber, EmailAddress, Notes, DepartmentID) values ('asdfas', 'asdf', 'asdfasfdasdf', 'asf', 'asdfasdf', 3

I get an error saying as follows, Whats happening... it's new to me.. more to that its an example striaght copy paste from one book


Operation must use an updateable query.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Operation must use an updateable query

Source Error:

Line 58: DBInsert.Connection = DBCon
Line 59: DBInsert.Connection.Open(
Line 60: DBInsert.ExecuteNonQuery(
Line 61: Response.Redirect("./index.aspx"
Line 62: End Su


Source File: c:\inetpub\wwwroot\WebApplication1\add1.aspx.vb Line: 60

Stack Trace:

[OleDbException (0x80004005): Operation must use an updateable query.
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method
System.Data.OleDb.OleDbCommand.ExecuteNonQuery(
WebApplication1.add1.SubmitBtn_Click(Object Sender, EventArgs E) in c:\inetpub\wwwroot\WebApplication1\add1.aspx.vb:6
System.Web.UI.WebControls.Button.OnClick(EventArgs e
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData
System.Web.UI.Page.ProcessRequestMain(

 

[Martin Feuersteiner]

Hi Pravin

I've recreated the table in SQL Server 2000 using varchar(50) for all fields
but DepartmentID for which I used int.
I had no problem running the Query in SQL Analyzer. However, you might want
to check your table keys. I had a similar odd problem before and it turned
out to be caused by an misdefined primary key.

Martin

Dear All,

I am trying to execute following code

Sub SubmitBtn_Click(ByVal Sender As Object, ByVal E As EventArgs)
Dim DBConn As OleDbConnection
Dim DBInsert As New OleDbCommand
DBConn = New OleDbConnection("PROVIDER=" _
& "Microsoft.Jet.OLEDB.4.0;" _
& "DATA SOURCE=" _
& Server.MapPath("Emps.mdb;"))
DBInsert.CommandText = "Insert Into Employees " _
& "(LastName, FirstName, PhoneNumber, EmailAddress, " _
& "Notes, DepartmentID) values (" _
& "'" & Replace(txtLastName.Text, "'", "''") & "', " _
& "'" & Replace(txtFirstName.Text, "'", "''") & "', " _
& "'" & Replace(txtPhoneNumber.Text, "'", "''") & "', " _
& "'" & Replace(txtEmailAddress.Text, "'", "''") _
& "', " _
& "'" & Replace(txtNotes.Text, "'", "''") & "', " _
& ddlDepartments.SelectedItem.Value & ")"
DBInsert.Connection = DBConn
DBInsert.Connection.Open()
DBInsert.ExecuteNonQuery()
Response.Redirect("./index.aspx")
End Sub

while executing following
DBInsert.ExecuteNonQuery()

query is
Insert Into Employees (LastName, FirstName, PhoneNumber, EmailAddress,

Notes, DepartmentID) values ('asdfas', 'asdf', 'asdfasfdasdf', 'asf',
'asdfasdf', 3)

I get an error saying as follows, Whats happening... it's new to me.. more

to that its an example striaght copy paste from one book.

Operation must use an updateable query.
Description: An unhandled exception occurred during the execution of the

current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Operation must use an updateable query.

Source Error:


Line 58: DBInsert.Connection = DBConn
Line 59: DBInsert.Connection.Open()
Line 60: DBInsert.ExecuteNonQuery()
Line 61: Response.Redirect("./index.aspx")
Line 62: End Sub


Source File: c:\inetpub\wwwroot\WebApplication1\add1.aspx.vb Line: 60

Stack Trace:


[OleDbException (0x80004005): Operation must use an updateable query.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)

System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)

System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
WebApplication1.add1.SubmitBtn_Click(Object Sender, EventArgs E) in c:\inetpub\wwwroot\WebApplication1\add1.aspx.vb:60
System.Web.UI.WebControls.Button.OnClick(EventArgs e)

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePo
stBackEvent(String eventArgument)

System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler

sourceControl, String eventArgument)

 

[WJ]

Trap the Exception.Message (try{}catch{Exception: e}) or turn on Debug/Trace
to find the exact line that is broken. Normally, a table must also be
existed and opened successfully before any maintenance operations
(add/chg/del) can be performed. The exception message would give you
specific error.

John

 

[Pravin A. Sable]

Hi Martin,

Sorry for not specifying problem completely, I am getting careless after
coming to academics again.

The database is simple access xp database. I did some search on google
and found that, this error is due to privileges of machinename/ASP.net
user. I gave ASP.net user administrative privileges but yet no change.
In IIS I gave read, write, permission for this database file, but no
change. I am kind of stuck. Definitely this error has to do something
with Access and file permission but I am not sure how to solve them.

Best Regards,
Pravin

Hi Pravin

I've recreated the table in SQL Server 2000 using varchar(50) for all fields
but DepartmentID for which I used int.
I had no problem running the Query in SQL Analyzer. However, you might want
to check your table keys. I had a similar odd problem before and it turned
out to be caused by an misdefined primary key.

Martin

Dear All,

I am trying to execute following code

Sub SubmitBtn_Click(ByVal Sender As Object, ByVal E As EventArgs)
Dim DBConn As OleDbConnection
Dim DBInsert As New OleDbCommand
DBConn = New OleDbConnection("PROVIDER=" _
& "Microsoft.Jet.OLEDB.4.0;" _
& "DATA SOURCE=" _
& Server.MapPath("Emps.mdb;"))
DBInsert.CommandText = "Insert Into Employees " _
& "(LastName, FirstName, PhoneNumber, EmailAddress, " _
& "Notes, DepartmentID) values (" _
& "'" & Replace(txtLastName.Text, "'", "''") & "', " _
& "'" & Replace(txtFirstName.Text, "'", "''") & "', " _
& "'" & Replace(txtPhoneNumber.Text, "'", "''") & "', " _
& "'" & Replace(txtEmailAddress.Text, "'", "''") _
& "', " _
& "'" & Replace(txtNotes.Text, "'", "''") & "', " _
& ddlDepartments.SelectedItem.Value & ")"
DBInsert.Connection = DBConn
DBInsert.Connection.Open()
DBInsert.ExecuteNonQuery()
Response.Redirect("./index.aspx")
End Sub

while executing following
DBInsert.ExecuteNonQuery()

query is
Insert Into Employees (LastName, FirstName, PhoneNumber, EmailAddress,

Notes, DepartmentID) values ('asdfas', 'asdf', 'asdfasfdasdf', 'asf',
'asdfasdf', 3)

I get an error saying as follows, Whats happening... it's new to me.. more

to that its an example striaght copy paste from one book.

Operation must use an updateable query.
Description: An unhandled exception occurred during the execution of the

current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Operation must use an

updateable query.

Source Error:


Line 58: DBInsert.Connection = DBConn
Line 59: DBInsert.Connection.Open()
Line 60: DBInsert.ExecuteNonQuery()
Line 61: Response.Redirect("./index.aspx")
Line 62: End Sub


Source File: c:\inetpub\wwwroot\WebApplication1\add1.aspx.vb Line: 60

Stack Trace:


[OleDbException (0x80004005): Operation must use an updateable query.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32

hr)

System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)

System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult)

System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,

Object& executeResult)

System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior

behavior, String method)

System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
WebApplication1.add1.SubmitBtn_Click(Object Sender, EventArgs E) in
c:\inetpub\wwwroot\WebApplication1\add1.aspx.vb:60

System.Web.UI.WebControls.Button.OnClick(EventArgs e)

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePo
stBackEvent(String eventArgument)

System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler

sourceControl, String eventArgument)

System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()