Update parameters with Access

Question

User1347756920 posted

Hi. I'm new to asp.net using VB. I have a detailsview of that record that I’m trying to update/edit. There are no errors but the problem is that it only will update the record if I am on the very first record of the gridview. I would be grateful for any help! The problem is with the update command:

Code:

UpdateCommand="UPDATE [renewals] SET [Phone] = ?, [Address] = ?, [City] = ?, [State] = ?, [Zip] = ? WHERE [id]=@idNum" 

@idNum parameter is not recognized. It will work if I hard code a value but that is pointless of course. I tried adding

Code:

 <asp:ControlParameter Name="idNum" ControlID="idNum" PropertyName="Value" Type="String" />

to the <UpdateParameters> but then not even the first record is updated.

Here is the code:

Code:

Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
       
        Dim ln As String = Session("last_name")
        Dim id As String = Session("ID")
        lastName.Value = ln
        idNum.Value = id
        
    End Sub

Protected Sub DetailsView_ItemUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdatedEventA  rgs)
        idNum.DataBind()
        
        With GridView1
            .DataBind()
        End With

AND THE FORM:

 <form runat="server">
  <asp:HiddenField ID="lastName" runat="server"></asp:HiddenField> 
  <asp:HiddenField ID="idNum" runat="server"></asp:HiddenField>
  <asp:Label ID="welcome" runat="server" ForeColor="#000000"></asp:Label><br />
  <asp:Label ID="editMessage" runat="server" ForeColor="#CC0000"></asp:Label>
  

         <asp:AccessDataSource ID="AccessDataSource1" Runat="server" DataFile="F:\MyData_Files\nd_renew.mdb"
           SelectCommand="select * FROM [renewals] WHERE [id]=@idNum And [Last Name]=@lastName" >   
           <FilterParameters>
                 <asp:ControlParameter Name="idNum" ControlID="GridView1" PropertyName="SelectedValue"></asp:ControlParameter>
            </FilterParameters>
      
            <SelectParameters>
                 <asp:ControlParameter Name="idNum" ControlID="idNum" PropertyName="Value" Type="String" />
                 <asp:ControlParameter Name="lastName" ControlID="lastName" PropertyName="Value" Type="String"/>
            </SelectParameters>

              
             </asp:AccessDataSource>
        
        <asp:GridView ID="GridView1" Runat="server" DataSourceID="AccessDataSource1"
            DataKeyNames="id" AutoGenerateSelectButton="False" 
            EnableModelValidation="True" Visible="true">
        </asp:GridView>

        <asp:AccessDataSource id="AccessDataSource2" runat="server"
                DataFile="F:\MyData_Files\nd_renew.mdb"
                SelectCommand="select * FROM [renewals] WHERE [id]=@idNum And [Last Name]=@lastName"
                DeleteCommand="DELETE FROM [renewals] WHERE [id] = ?"
                UpdateCommand="UPDATE [renewals] SET [Phone] = ?, [Address] = ?, [City] = ?, [State] = ?, [Zip] = ? WHERE [id]=@idNum"
                InsertCommand="INSERT INTO [renewals] ([idNum], [FirstName], [LastName], [Phone], [Address], [City], [State], [Zip]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)" FilterExpression="id='@idNum'">
                
                <FilterParameters>
                    <asp:ControlParameter Name="idNum" ControlID="GridView1" PropertyName="SelectedValue" Type="String"/>
                   </FilterParameters>
      
                <SelectParameters>
                    <asp:ControlParameter Name="idNum" ControlID="idNum" PropertyName="Value" Type="String" />
                    <asp:ControlParameter Name="lastName" ControlID="lastName" PropertyName="Value" Type="String"/>
                </SelectParameters>

             <UpdateParameters>
           
            <asp:Parameter Name="Phone" Type="string" />
            <asp:Parameter Name="Address" Type="string"  />
            <asp:Parameter Name="City" Type="string" />
            <asp:Parameter Name="State"  Type="string"  />
            <asp:Parameter Name="Zip" Type="string" />
            
            </UpdateParameters>
</asp:AccessDataSource>


  <asp:DetailsView ID="DetailsView" Runat="server" 
 AutoGenerateRows="false"
 AutoGenerateDeleteButton="False"
 AutoGenerateEditButton="False" 
 DataSourceID="AccessDataSource2" 
 DataKeyNames="id"
 AutoGenerateInsertButton="False" 
 Visible="True" 
 EnableModelValidation="True" 
 Width="400px"
 onitemupdated="DetailsView_ItemUpdated" oniteminserted="DetailsView_ItemInserted" onitemdeleted="DetailsView_ItemDeleted" >
 
  <Fields>
  
   <asp:CommandField ButtonType="Button" HeaderStyle-BackColor="#E0E0E0" ItemStyle-BackColor="#E0E0E0" ShowHeader="True" ShowInsertButton="False" ShowEditButton="True"  ShowDeleteButton="False"
    ControlStyle-Font-Size="9pt"
    ControlStyle-Width="50px"
    />
    <asp:TemplateField HeaderText="ID">
                <ItemTemplate>
                    <%# Eval("id")%>
                </ItemTemplate>
            </asp:TemplateField>



  <asp:TemplateField HeaderText="Phone:" HeaderStyle-BackColor="#E0E0E0" HeaderStyle-Font-Bold="True">
  <ItemTemplate>
  <asp:Label ID="lblPhone" Text='<%# Eval("Phone") %>' Runat="Server"/></ItemTemplate>
  <EditItemTemplate>
  <asp:TextBox ID="editPhone" Runat="Server" Text='<%# Bind("Phone") %>'/>
  <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="editPhone"  ErrorMessage="Phone is required." ValidationGroup=""></asp:RequiredFieldValidator>
  </EditItemTemplate>
  </asp:TemplateField>

    <asp:BoundField DataField="First Name" HeaderText="First Name:" HeaderStyle-BackColor="#E0E0E0" HeaderStyle-Font-Bold="True" sortexpression="First Name"/>
    <asp:BoundField DataField="Last Name" HeaderText="Last Name:" HeaderStyle-BackColor="#E0E0E0" HeaderStyle-Font-Bold="True" sortexpression="Last Name"/>
    <asp:BoundField DataField="Address" HeaderText="Address:" HeaderStyle-BackColor="#E0E0E0" HeaderStyle-Font-Bold="True" sortexpression="Address"/>
    <asp:BoundField DataField="City" HeaderText="City:" HeaderStyle-BackColor="#E0E0E0" HeaderStyle-Font-Bold="True" sortexpression="City"/>
    <asp:BoundField DataField="State" HeaderText="State:" HeaderStyle-BackColor="#E0E0E0" HeaderStyle-Font-Bold="True" sortexpression="State"/>
    <asp:BoundField DataField="Zip" HeaderText="Zip:" HeaderStyle-BackColor="#E0E0E0"	HeaderStyle-Font-Bold="True" sortexpression="Zip"/>
   
  </Fields>
  
 </asp:DetailsView>
 
                 
  </form>

---

Answer 1

User1983249378 posted

why you put @ at start of each parameter in query while in fileter and select parameter you didn't write any thing , Remove @ and make parameter names a bit change like

for idNum use id_Num in query as well as in 

<FilterParameters>
                 <asp:ControlParameter Name="id_Num" ControlID="GridView1" PropertyName="SelectedValue"></asp:ControlParameter>
            </FilterParameters>

 
Mark as answer if the post help you.

Answer 2

User1347756920 posted

Thanks for your suggestion. Unfortunately, that doesn't seem to change anything. The SELECT parameters seem to work fine but when I try to UPDATE a record it will only update if it is the first record in the table.