Update Access DB from DataGrid

R

rangerstud620

Freshman
Joined
Jun 14, 2005
Messages
27
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?

Visual Basic: 
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
 
rangerstud620 said:
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?

Visual Basic: 
  'cmd.CommandType = CommandType.StoredProcedure
  'Use this instead:
  cmd.CommandType = CommandType.Text
Click to expand...

Your not using a stored procedure, your using inline SQL (text).
 
bri189a said:
Your not using a stored procedure, your using inline SQL (text).
Click to expand...

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?
 
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 said:
IIRC OleDbCommand doesn't support named parameters - just use ? in place of the names.
clicky for another thread on the topic
Click to expand...

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!
 
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!
 
Back
Top