locked
How to update the data in database by textbox RRS feed

  • Question

  • User-1762805634 posted

    Hi,I tried to update the data in database by some textbox

    When I click the button,there exist error that there doesn't exist value for the database

    There is my code:

    ASP:

            <asp:SqlDataSource ID="CreditCardSqlDataSource" runat="server"
                
                ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
                DeleteCommand="DELETE FROM [Credit_Card] WHERE (([User_ID] = ?) OR ([User_ID] IS NULL AND ? IS NULL))"
                InsertCommand="INSERT INTO [Credit_Card] ([User_ID], [card_no], [card_type], [security_no], [Holder_Name], [Expirty_Year], [Expiry_Month]) VALUES (?, ?, ?, ?, ?, ?, ?)"
                ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
                SelectCommand="SELECT [User_ID], [card_no], [card_type], [security_no], [Holder_Name], [Expirty_Year], [Expiry_Month] FROM [Credit_Card]"
                
                UpdateCommand="UPDATE [Credit_Card] SET [card_no] = @card_no, [card_type] = @card_type, [security_no] = @security_no, [Holder_Name] = @Holder_Name, [Expirty_Year] = @Expirty_Year, [Expiry_Month] = @Expiry_Month WHERE (([User_ID] = @User_ID) OR ([User_ID] IS NULL AND ? IS NULL))" >
                <DeleteParameters>
                    <asp:Parameter Name="User_ID" Type="String" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="User_ID" Type="String" />
                    <asp:Parameter Name="card_no" Type="String" />
                    <asp:Parameter Name="card_type" Type="String" />
                    <asp:Parameter Name="security_no" Type="Int32" />
                    <asp:Parameter Name="Holder_Name" Type="String" />
                    <asp:Parameter Name="Expirty_Year" Type="Int32" />
                    <asp:Parameter Name="Expiry_Month" Type="Int32" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="User_ID" Type="String" />
                    <asp:Parameter Name="card_no" Type="String" />
                    <asp:Parameter Name="card_type" Type="String" />
                    <asp:Parameter Name="security_no" Type="String" />
                    <asp:Parameter Name="Holder_Name" Type="String" />
                    <asp:Parameter Name="Expirty_Year" Type="String" />
                    <asp:Parameter Name="Expiry_Month" Type="String" />
                </UpdateParameters>
            </asp:SqlDataSource>

    C#

    protected void Submit_Click(object sender, EventArgs e)
    {
                int length = UserGridView.Rows.Count;
                int length1 = CreditCardGridView.Rows.Count;
                CreditCardSqlDataSource.UpdateParameters[0].DefaultValue = UserNameTextBox.Text;
                CreditCardSqlDataSource.UpdateParameters[1].DefaultValue = CardNumberTextBox.Text;
                CreditCardSqlDataSource.UpdateParameters[2].DefaultValue = CardTypeDropDownList.Text;
                CreditCardSqlDataSource.UpdateParameters[3].DefaultValue = SecurityNumberTextBox.Text;
                CreditCardSqlDataSource.UpdateParameters[4].DefaultValue = CardHolderNameTextBox.Text;
                CreditCardSqlDataSource.UpdateParameters[5].DefaultValue = ExpiryMonthTextBox.Text;
                CreditCardSqlDataSource.UpdateParameters[6].DefaultValue = ExpiryYearTextBox.Text;
               // try
                //{
                    CreditCardSqlDataSource.Update();
               // }
               // catch
               // {
               // }

    }

    Tuesday, February 28, 2012 5:19 AM

Answers

  • User-1199946673 posted

    First of all, you need to realize that OleDb parameters are not recognized by their name, but by their position, meaning that they must be added in the order they (first) appear in the command. So the first parameter in your updatecommand is @card_no, so you need to specify this parameter first in the parameter collection. Also, at the end, you're using ? as a parameter. This is fine because the parameters in Jet are recognized by position, and each ? will be treated as a new parameter.

    But your intention is to use the same parameter (@User_ID) multiple times. In that case, you need to use named parameters (although MSDN says this is NOT supported, but this is wrong!), or you need to specify the same parameter multiple times, but this is really not good practice:

    UpdateCommand="UPDATE [Credit_Card] SET [card_no] = @card_no, [card_type] = @card_type, [security_no] = @security_no, [Holder_Name] = @Holder_Name, [Expirty_Year] = @Expirty_Year, [Expiry_Month] = @Expiry_Month WHERE [User_ID] = @User_ID OR ([User_ID] IS NULL AND @User_ID IS NULL)"

    <UpdateParameters>
        <asp:Parameter Name="card_no" Type="String" />
        <asp:Parameter Name="card_type" Type="String" />
        <asp:Parameter Name="security_no" Type="String" />
        <asp:Parameter Name="Holder_Name" Type="String" />
        <asp:Parameter Name="Expirty_Year" Type="Int32" />
        <asp:Parameter Name="Expiry_Month" Type="Int32" />
        <asp:Parameter Name="User_ID" Type="String" />
    </UpdateParameters>

     By the way, instead of using normal Parameters and setting the default value in code behing, you can also use ControlParameters

    <UpdateParameters>
        <asp:ControlParameter Name="card_no" Type="String" ControlId="CardNumberTextBox" PropertyName="Text" />
        <asp:ControlParameter Name="card_type" Type="String" ControlId="CardTypeDropDownList" PropertyName="Text" />
        <asp:ControlParameter Name="security_no" Type="String" ControlId="SecurityNumberTextBox" PropertyName="Text" />
        <asp:ControlParameter Name="Holder_Name" Type="String" ControlId="CardHolderNameTextBox" PropertyName="Text" />
        <asp:ControlParameter Name="Expirty_Year" Type="Int32" ControlId="ExpiryMonthTextBox" PropertyName="Text" />
        <asp:ControlParameter Name="Expiry_Month" Type="Int32" ControlId="ExpiryYearTextBox" PropertyName="Text" />
        <asp:ControlParameter Name="User_ID" Type="String" ControlId="UserNameTextBox" PropertyName="Text" />
    </UpdateParameters>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 28, 2012 6:07 AM

All replies

  • User-1199946673 posted

    First of all, you need to realize that OleDb parameters are not recognized by their name, but by their position, meaning that they must be added in the order they (first) appear in the command. So the first parameter in your updatecommand is @card_no, so you need to specify this parameter first in the parameter collection. Also, at the end, you're using ? as a parameter. This is fine because the parameters in Jet are recognized by position, and each ? will be treated as a new parameter.

    But your intention is to use the same parameter (@User_ID) multiple times. In that case, you need to use named parameters (although MSDN says this is NOT supported, but this is wrong!), or you need to specify the same parameter multiple times, but this is really not good practice:

    UpdateCommand="UPDATE [Credit_Card] SET [card_no] = @card_no, [card_type] = @card_type, [security_no] = @security_no, [Holder_Name] = @Holder_Name, [Expirty_Year] = @Expirty_Year, [Expiry_Month] = @Expiry_Month WHERE [User_ID] = @User_ID OR ([User_ID] IS NULL AND @User_ID IS NULL)"

    <UpdateParameters>
        <asp:Parameter Name="card_no" Type="String" />
        <asp:Parameter Name="card_type" Type="String" />
        <asp:Parameter Name="security_no" Type="String" />
        <asp:Parameter Name="Holder_Name" Type="String" />
        <asp:Parameter Name="Expirty_Year" Type="Int32" />
        <asp:Parameter Name="Expiry_Month" Type="Int32" />
        <asp:Parameter Name="User_ID" Type="String" />
    </UpdateParameters>

     By the way, instead of using normal Parameters and setting the default value in code behing, you can also use ControlParameters

    <UpdateParameters>
        <asp:ControlParameter Name="card_no" Type="String" ControlId="CardNumberTextBox" PropertyName="Text" />
        <asp:ControlParameter Name="card_type" Type="String" ControlId="CardTypeDropDownList" PropertyName="Text" />
        <asp:ControlParameter Name="security_no" Type="String" ControlId="SecurityNumberTextBox" PropertyName="Text" />
        <asp:ControlParameter Name="Holder_Name" Type="String" ControlId="CardHolderNameTextBox" PropertyName="Text" />
        <asp:ControlParameter Name="Expirty_Year" Type="Int32" ControlId="ExpiryMonthTextBox" PropertyName="Text" />
        <asp:ControlParameter Name="Expiry_Month" Type="Int32" ControlId="ExpiryYearTextBox" PropertyName="Text" />
        <asp:ControlParameter Name="User_ID" Type="String" ControlId="UserNameTextBox" PropertyName="Text" />
    </UpdateParameters>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 28, 2012 6:07 AM
  • User-1762805634 posted

    Thx,Its work

    But why is it important to update in ordering?

    Tuesday, February 28, 2012 8:11 PM
  • User-1199946673 posted

    Because like I said, jet parameters are recognized by their position, an not by their name. So let's say you have a command

    SELECT field1 FROM table1 WHERE (b = @b OR (b IS NULL AND @b IS NULL)) AND a = @a

    Jet sees 2 parameters, @b and @a

    But the name is only important in recognizing the amount of parameters, so lets assum you enter the parameters like this:

    <asp:Parameter Name="a" Type="String" />
    <asp:Parameter Name="b" Type="String" />

    the first parameter, although you called it a,  will be assigned to parameter @b, because that parameter appears first in the command, and the second will be assigned to parameter @a

    This might be very confusing, but this is how it works. When you use question marks, it becomes even more confusing

    SELECT field1 FROM table1 WHERE (b = ? OR (b IS NULL AND ? IS NULL)) AND a = ?

    In this case, Je will see 3 parameters, although you intention is to have only 2 parameters, but how could Jet possibly know that? In this case, you need to assign 3 parameters:

    <asp:Parameter Name="b1" Type="String" />
    <asp:Parameter Name="b2" Type="String" />
    <asp:Parameter Name="a" Type="String" />

    again, how you name the parameters is not so important, the only thing to remember is that the first parameter (b1) is assigned to the first questionmark in the command, etc...

    Wednesday, February 29, 2012 4:06 AM
  • User-1752700239 posted

    Thank you.. It helped me lot

    Wednesday, February 29, 2012 4:07 AM