Update from datagrid

Discussion in 'ASP .Net' started by Russ Green, Jul 20, 2003.

  1. Russ Green

    Russ Green Guest

    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
    Russ Green, Jul 20, 2003
    #1
    1. Advertising

  2. Russ Green

    John Toop Guest

    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.

    "Russ Green" <> wrote in message
    news:...
    > 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
    >
    >
    John Toop, Jul 21, 2003
    #2
    1. Advertising

  3. Russ Green

    Russ Green Guest

    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

    "John Toop" <> wrote in message
    news:e$...
    > 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.
    >
    > "Russ Green" <> wrote in message
    > news:...
    > > 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
    > >
    > >

    >
    >



    ---
    Outgoing mail is certified Virus Free.
    Checked by AVG anti-virus system (http://www.grisoft.com).
    Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003
    Russ Green, Jul 22, 2003
    #3
  4. Russ Green

    Rajeev Soni Guest

    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

    "Russ Green" <> wrote in message news:...
    > 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
    >
    > "John Toop" <> wrote in message
    > news:e$...
    > > 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.
    > >
    > > "Russ Green" <> wrote in message
    > > news:...
    > > > 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
    > > >
    > > >

    > >
    > >

    >
    >
    > ---
    > Outgoing mail is certified Virus Free.
    > Checked by AVG anti-virus system (http://www.grisoft.com).
    > Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003
    >
    >
    >
    Rajeev Soni, Jul 23, 2003
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. matthew schouppe
    Replies:
    3
    Views:
    2,359
    Scott Allen
    Apr 30, 2004
  2. =?Utf-8?B?TWljaGFlbA==?=

    How to update datagrid after update

    =?Utf-8?B?TWljaGFlbA==?=, Mar 17, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    499
    Alex D.
    Mar 17, 2006
  3. Andreas Klemt
    Replies:
    0
    Views:
    293
    Andreas Klemt
    Oct 8, 2003
  4. Kostia
    Replies:
    1
    Views:
    263
    Scott Mitchell [MVP]
    Feb 26, 2004
  5. matthew schouppe

    DataGrid ItemStyle is a textbox and doesn't update the datagrid datasource

    matthew schouppe, Apr 29, 2004, in forum: ASP .Net Datagrid Control
    Replies:
    3
    Views:
    200
    Scott Allen
    Apr 30, 2004
Loading...

Share This Page