Update of Access Database Not Working

Arov · November 12th, 2004, 02:56 PM

I have an access DB, I am trying to write an asp.net page that allows me to update records.
The page loads, and i see the datagrid, I can click edit, and the fields that i want to edit are available for editing, and i can click cancel, and that works fine. When i click ok, get an error message: "No value given for one or more required parameters."

the code for the page is below, or it can be seen in action at http://whyarentumarried.com/whyarent...estupdate.aspx

I am sure that I am doing something wrong, I just can't figure out what it is. Any help would be greatly appreciated.
Thanks in advance.

Code:

 <%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<script runat="server" enableviewstate="true">

    Function MyQueryMethod() As System.Data.DataSet
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=c:\sites\FrontPage1\mainasset\database\AccessforWebMatrix.mdb"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

        Dim queryString As String = "SELECT [BuyersTable1].[Column0], [BuyersTable1].[First Name], [BuyersTable1].[Last N" & _
            "ame], [BuyersTable1].[Shipped], [BuyersTable1].[TrackNum] FROM [BuyersTable1]"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
        dataAdapter.SelectCommand = dbCommand
        Dim dataSet As System.Data.DataSet = New System.Data.DataSet
        dataAdapter.Fill(dataSet)

        Return dataSet
    End Function


    Sub page_load(ByVal sender As Object, ByVal e As EventArgs)
        If Not page.ispostback Then
            DG1.DataSource = MyQueryMethod()
            DG1.DataBind()
        End If
    End Sub

    Sub editProduct(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)

        DG1.editItemIndex = e.item.itemIndex
        DG1.DataSource = MyQueryMethod()
        DG1.DataBind()

    End Sub

    Sub updateProduct(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)

        'Determine the value of the ProductID column

        Dim Column0 As Integer = e.Item.Cells(1).Text

        'reference each textbox

        Dim Shipped As TextBox = e.Item.Cells(4).Controls(0)
        Dim TrackNum As TextBox = e.Item.Cells(5).Controls(0)



        if shipped.Text ="true" or shipped.Text="True" then
            MyUpdateMethod(Column0, True, TrackNum.Text)
        Else
            MyUpdateMethod(Column0, False, TrackNum.Text)
        End If


        DG1.EditItemIndex = -1

        DG1.DataSource = MyQueryMethod()
        DG1.DataBind()

    End Sub


    Function MyUpdateMethod(ByVal column0 As Integer, ByVal shipped As Boolean, ByVal trackNum As String) As Integer
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=c:\sites\FrontPage1\mainasset\database\AccessforWebMatrix.mdb"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

        Dim queryString As String = "UPDATE [BuyersTable1] SET [Shipped]=@Shipped, [TrackNum]=@TrackNum WHERE ([BuyersT" & _
            "able].[Column0] = @Column0)"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        Dim dbParam_column0 As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_column0.ParameterName = "@Column0"
        dbParam_column0.Value = column0
        dbParam_column0.DbType = System.Data.DbType.Int32
        dbCommand.Parameters.Add(dbParam_column0)
        Dim dbParam_shipped As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_shipped.ParameterName = "@Shipped"
        dbParam_shipped.Value = shipped
        dbParam_shipped.DbType = System.Data.DbType.[Boolean]
        dbCommand.Parameters.Add(dbParam_shipped)
        Dim dbParam_trackNum As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_trackNum.ParameterName = "@TrackNum"
        dbParam_trackNum.Value = trackNum
        dbParam_trackNum.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_trackNum)

        Dim rowsAffected As Integer = 0
        dbConnection.Open()
        Try
            rowsAffected = dbCommand.ExecuteNonQuery
        Finally
            dbConnection.Close()
        End Try

        Return rowsAffected
    End Function



    Sub cancelProduct(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)

        DG1.editItemIndex = -1
        DG1.DataSource = MyQueryMethod()
        DG1.DataBind()

    End Sub

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''



</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">

        <asp:Datagrid ID="DG1" Runat="server" AutoGenerateColumns="False" onUpdateCommand="updateProduct" onCancelCommand="cancelProduct" onEditCommand="editProduct" DataSourceID="AccessDataSource1">
            <Columns>
                <asp:EditCommandColumn ButtonType="PushButton" UpdateText="OK" HeaderText="Update" CancelText="Cancel" EditText="Change"></asp:EditCommandColumn>
                <asp:BoundColumn HeaderText="ID" DataField="Column0" ReadOnly="True"></asp:BoundColumn>
                <asp:BoundColumn HeaderText="First Name" DataField="First Name" ReadOnly="True"></asp:BoundColumn>
                <asp:BoundColumn HeaderText="Last Name" DataField="Last Name" ReadOnly="True"></asp:BoundColumn>
                <asp:BoundColumn HeaderText="Shipped?" DataField="Shipped"></asp:BoundColumn>
                <asp:BoundColumn HeaderText="Tracking Number" DataField="TrackNum"></asp:BoundColumn>
            </Columns>
        </asp:Datagrid>&nbsp;&nbsp;&nbsp;
    </form>
</body>
</html>