Update Access DB from DataGrid

[rangerstud620]

I'm trying to udpate an Access DB from a dropdownlist in a datagrid. When I click the Update button, I get the following error at the cmd.ExecuteNonQuery() line: "System.Data.OleDb.OleDbException: Expected query name after EXECUTE." I'm new to ASP.NET so maybe I'm way off course, but how do I fix this?

 

Private Sub UpdateRecord(ByVal source As Object, ByVal E As DataGridCommandEventArgs)

 Dim di As DataGridItem
 Dim cmd As New OleDbCommand("UPDATE NewReport SET HEADING = @HeadingID, HEADNAME = @HeadingName WHERE ID = @Id", objConnection)
 Dim param As OleDbParameter

 di = E.Item
 cmd.CommandType = CommandType.StoredProcedure

 param = cmd.Parameters.Add("@HeadingID", OleDbType.Char)
 param.Value = DirectCast(di.FindControl("lstHeadings"), DropDownList).SelectedItem.Value

 param = cmd.Parameters.Add("@HeadingName", OleDbType.Char)
 param.Value = DirectCast(di.FindControl("lstHeadings"), DropDownList).SelectedItem.Text
   
 param = cmd.Parameters.Add("@Id", OleDbType.Integer)
 param.value = dgCompany.DataKeys(CInt(E.Item.ItemIndex))
       
 cmd.Connection.Open()
 cmd.ExecuteNonQuery() 'Errors here
 dgCompany.EditItemIndex = -1
 cmd.Connection.Close()
     
 LoadGrid()
End Sub

[bri189a]

I'm trying to udpate an Access DB from a dropdownlist in a datagrid. When I click the Update button, I get the following error at the cmd.ExecuteNonQuery() line: "System.Data.OleDb.OleDbException: Expected query name after EXECUTE." I'm new to ASP.NET so maybe I'm way off course, but how do I fix this?

 

 'cmd.CommandType = CommandType.StoredProcedure
 'Use this instead:
 cmd.CommandType = CommandType.Text

 

Your not using a stored procedure, your using inline SQL (text).

[rangerstud620]

Your not using a stored procedure' date=' your using inline SQL (text).[/quote']

 

I changed that line of code and now I get the following error: "System.Data.OleDb.OleDbException: Operation must use an updateable query." Any ideas?

[bri189a]

Step through the code with the debugger and see if the values are what you expect them to be, it may be that the id isn't there or something like that. HEADER may also be a key word, don't think so, but something to check. Try running the exact query in Access, (set it up under queries), then run it, Access will prompt you for the values, if it works there you at least know your SQL is good.

[PlausiblyDamp]

IIRC OleDbCommand doesn't support named parameters - just use ? in place of the names.

clicky for another thread on the topic

[rangerstud620]

IIRC OleDbCommand doesn't support named parameters - just use ? in place of the names.

clicky for another thread on the topic

 

I checked my SQL query in Access and it seems to work fine. I have checked the values of each of the parameters and they are correct. I changed the parameter names to ? but I get the same "Expected query name after EXECUTE" error. Any other ideas? Is there a different way I could go about udpating the database? Thanks!

[rangerstud620]

I got it figured out. My code was ok, the problem was in the permissions of the root folder for my site. The user accounts did not have read/write permissions. Everything is working good...for now :) Thanks for the help!