Update from datagrid

  • Thread starter Thread starter Russ Green
  • Start date Start date
R

Russ Green

I am currently learning asp.net (vb) with Web Matrix. I am building an
intranet page that will have an editable datagrid on it. I have got the
datagrid to populate OK but I cannot get the Update method to work
correctly.

I click on edit and and the non-readonly field become editable as in
http://www.russgreen.net/software/newsgroupimages/screenshot1.gif
The update button does nothing. See
http://www.russgreen.net/software/newsgroupimages/screenshot2.gif

Error page
http://www.russgreen.net/software/newsgroupimages/SyntaxerrorinUPDATEstateme
nt_.htm
..aspx page
http://www.russgreen.net/software/newsgroupimages/db_project_list_new.aspx

Can someone please help explain this please?

Sub DataGrid_Update(s As Object, e As DataGridCommandEventArgs )
'create edit text boxes
Dim txtName As textbox = E.Item.cells(2).Controls(0)
Dim txtStatus As textbox = E.Item.cells(3).Controls(0)
Dim txtAction As textbox = E.Item.cells(4).Controls(0)
Dim txtStaff As textbox = E.Item.cells(5).Controls(0)

'update SQL
Dim strUpdateSql As String ="UPDATE Projects SET" & _
" Name =@Name, Status =@Status, Action = @Action, Staff = @Staff" &
_
" WHERE ProjectID = @ProjectID"

'connection stuff
Dim conn As OleDbConnection = New OleDbConnection(strConn)
conn.Open()

'command Object to Execute the SQL
Dim cmd As OleDbCommand = New OleDbCommand(strUpdateSql, conn)
cmd.Parameters.Add(New OleDbParameter("@name", txtName.text))
cmd.Parameters.Add(New OleDbParameter("@Status", txtStatus.text))
cmd.Parameters.Add(New OleDbParameter("@Action", txtAction.text))
cmd.Parameters.Add(New OleDbParameter("@Staff", txtStaff.text))
cmd.Parameters.Add(New OleDbParameter("@ProjectID",
CType(e.Item.Cells(0).Text, Long)))
cmd.ExecuteNonQuery()

'do housekeeping
DataGrid1.EditItemIndex = -1
conn.close

'rebind
BindData()
End Sub
 
Hi Russ,

You need to use the last parameter @product ID

cmd.Parameters.Add(New OleDbParameter("@ProjectID",
DataGrid1.DataKeys(e.Item.ItemIndex))

It's interesting your sub is

DataGrid_Update but the ID of the object seems to be DataGrid1. I would have
expected DataGrid1_UpdateCommand but I suppose that could just be the way
that datamatrix wires it up. Then again... perhaps I'm wrong.
 
OK

I sorted it. Well almost. The attached page works on the Webmatrix testing
server but not on IIS. Under IIS the page render OK but UPDATE generate the
following exception: -

System.Data.OleDb.OleDbException: Operation must use an updateable query. at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at ASP.db_project_list_aspx.DataGrid_Update(Object s,
DataGridCommandEventArgs e) in D:\Web Design
Work\SALT_INTRANET\asp_net\db_project_list.aspx:line 68

Any Ideas?

Russ
 
Hi Russ,
you are getting this error "Operation must use an updateable query" may for two reasons...
1. Folder where you are having MDB file doesnot have permission for your asp.net applications.
2. Give the permission for MDB file.

Rajeev
 
Back
Top