locked
How to insert data from a form into my database? RRS feed

  • Question

  • User2118991054 posted

    I can't seem to insert new data  into my database it gives an error when i try to update

    Incorrect syntax near the keyword 'WHERE'.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

    Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'.


    My Source code:

     <div ID="form">
                    <table style="width: 68%; margin-left: 156px; margin-top: 20px;">
                        <tr>
                            <td ID="rowStyle" style="border-style: groove; border-color: #000000">
                                Permit-Type:</td>
                            <td class="style20" style="border-style: groove; border-color: #000000">
                                <asp:DropDownList ID="DropDownPermit" runat="server" 
                                    onselectedindexchanged="DropDownPermit_SelectedIndexChanged" 
                                    AutoPostBack="True">
                                    <asp:ListItem Value="--Choose Permit--">--Choose Permit--</asp:ListItem>
                                    <asp:ListItem Value="2">Blue</asp:ListItem>
                                    <asp:ListItem>Red</asp:ListItem>
                                    <asp:ListItem Value="1">Yellow</asp:ListItem>
                                </asp:DropDownList>
                            </td>
                            <td>
                                <asp:RequiredFieldValidator ID="RequiredFieldPermit" runat="server" 
                                    ControlToValidate="DropDownPermit" Display="Dynamic" 
                                    ErrorMessage="Choose Permit Type!!" ForeColor="Blue" 
                                    InitialValue="--Choose Permit--"></asp:RequiredFieldValidator>
                            </td>
                        </tr>
                        <tr>
                            <td ID="rowStyle" style="border-style: groove; border-color: #000000">
                                Validity:</td>
                            <td class="style20" style="border-style: groove; border-color: #000000">
                                <asp:DropDownList ID="DropDownValidity" runat="server" 
                                    onselectedindexchanged="DropDownPermit_SelectedIndexChanged" 
                                    AutoPostBack="True">
                                    <asp:ListItem>--Select Validity--</asp:ListItem>
                                    <asp:ListItem>Day</asp:ListItem>
                                    <asp:ListItem>Week</asp:ListItem>
                                    <asp:ListItem>Month</asp:ListItem>
                                    <asp:ListItem>Semester</asp:ListItem>
                                    <asp:ListItem>Year</asp:ListItem>
                                </asp:DropDownList>
                            </td>
                            <td>
                                <asp:RequiredFieldValidator ID="RequiredFieldValidity" runat="server" 
                                    ControlToValidate="DropDownValidity" Display="Dynamic" 
                                    ErrorMessage="Pick Validity!!" ForeColor="Blue" 
                                    InitialValue="--Select Validity--"></asp:RequiredFieldValidator>
                            </td>
                        </tr>
                        <tr>
                            <td ID="rowStyle" style="border-style: groove; border-color: #000000">
                                Duration-Days</td>
                            <td class="style20" style="border-style: groove; border-color: #000000">
                                <asp:TextBox ID="duration" runat="server" Width="170px" AutoPostBack="True" style="text-align: center"
                                    Enabled="False" ReadOnly="True" ></asp:TextBox>
                            </td>
                            <td>
                                <asp:RequiredFieldValidator ID="RequiredFieldduration" runat="server" 
                                    ControlToValidate="duration" Display="Dynamic" 
                                    ErrorMessage="Enter Duration Days!!" ForeColor="Blue"></asp:RequiredFieldValidator>
                            </td>
                        </tr>
                        <tr>
                            <td ID="rowStyle" style="border-style: groove; border-color: #000000">
                                Total:</td>
                            <td class="style20" style="border-style: groove; border-color: #000000">
                                <asp:Label ID="total" runat="server" Text="$"></asp:Label>
                            </td>
                            <td>
                                &nbsp;</td>
                        </tr>
                    </table>
                </div>
                <asp:Button ID="back2" runat="server" CausesValidation="False" 
                    onclick="back2_Click" style="margin-left: 555px" Text="Back" Width="100px" />
                <asp:Button ID="pay" runat="server" onclick="pay_Click" 
                    style="margin-left: 286px" Text="Proceed To Pay" />
                <br />
            </asp:View>
            <asp:View ID="View3" runat="server">
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <br />
                <br />
                <div ID="form2">
                    <h3 ID="heading">
                        Payment Details</h3>
                    <br />
                    <br />
                    <table style="width: 72%; margin-left: 148px;">
                        <tr>
                            <td ID="rowStyle" style="border-style: groove; border-color: #000000">
                                Card-Type:</td>
                            <td class="style22" style="border-style: groove; border-color: #000000">
                                <asp:RadioButtonList ID="RadioButtonPermit" runat="server" 
                                    RepeatDirection="Horizontal" RepeatLayout="Flow" style="color: #000000">
                                    <asp:ListItem>Visa</asp:ListItem>
                                    <asp:ListItem>Master Card</asp:ListItem>
                                    <asp:ListItem>American Express</asp:ListItem>
                                </asp:RadioButtonList>
                            </td>
                            <td>
                                <asp:RequiredFieldValidator ID="RequiredFieldCard" runat="server" 
                                    ControlToValidate="RadioButtonPermit" Display="Dynamic" 
                                    ErrorMessage="Select Card Type!!" ForeColor="Blue"></asp:RequiredFieldValidator>
                            </td>
                        </tr>
                        <tr>
                            <td ID="rowStyle" style="border-style: groove; border-color: #000000">
                                Card-Number:</td>
                            <td class="style22" style="border-style: groove; border-color: #000000">
                                <asp:TextBox ID="cardNumber" runat="server" Width="198px"></asp:TextBox>
                            </td>
                            <td>
                                <asp:RequiredFieldValidator ID="RequiredFieldCardNumber" runat="server" 
                                    ControlToValidate="cardNumber" Display="Dynamic" 
                                    ErrorMessage="Enter Card Number!!" ForeColor="Blue"></asp:RequiredFieldValidator>
                                <br />
                                <asp:RegularExpressionValidator ID="RegularExpressionCardNumber" runat="server" 
                                    ControlToValidate="cardNumber" Display="Dynamic" 
                                    ErrorMessage="Invalid Card Number!!" ForeColor="Blue" 
                                    ValidationExpression="^((4\d{3})|(5[1-5]\d{2}))(-?|\040?)(\d{4}(-?|\040?)){3}|^(3[4,7]\d{2})(-?|\040?)\d{6}(-?|\040?)\d{5}"></asp:RegularExpressionValidator>
                            </td>
                        </tr>
                        <tr>
                            <td ID="rowStyle" style="border-style: groove; border-color: #000000">
                                Card-Expires:</td>
                            <td class="style22" style="border-style: groove; border-color: #000000">
                                <asp:DropDownList ID="DropDownMonth" runat="server">
                                    <asp:ListItem>--Choose Month--</asp:ListItem>
                                    <asp:ListItem>01</asp:ListItem>
                                    <asp:ListItem>02</asp:ListItem>
                                    <asp:ListItem>03</asp:ListItem>
                                    <asp:ListItem>04</asp:ListItem>
                                    <asp:ListItem>05</asp:ListItem>
                                    <asp:ListItem>06</asp:ListItem>
                                    <asp:ListItem>07</asp:ListItem>
                                    <asp:ListItem>08</asp:ListItem>
                                    <asp:ListItem>09</asp:ListItem>
                                    <asp:ListItem>10</asp:ListItem>
                                    <asp:ListItem>11</asp:ListItem>
                                    <asp:ListItem>12</asp:ListItem>
                                </asp:DropDownList>
                                &nbsp;/
                                <asp:DropDownList ID="DropDownYear" runat="server">
                                    <asp:ListItem>--Choose Year--</asp:ListItem>
                                    <asp:ListItem>2013</asp:ListItem>
                                    <asp:ListItem>2014</asp:ListItem>
                                    <asp:ListItem>2015</asp:ListItem>
                                    <asp:ListItem>2016</asp:ListItem>
                                    <asp:ListItem>2017</asp:ListItem>
                                    <asp:ListItem>2018</asp:ListItem>
                                    <asp:ListItem>2019</asp:ListItem>
                                    <asp:ListItem>2020</asp:ListItem>
                                </asp:DropDownList>
                            </td>
                            <td>
                                <asp:RequiredFieldValidator ID="RequiredFieldMonth" runat="server" 
                                    ControlToValidate="DropDownMonth" Display="Dynamic" 
                                    ErrorMessage="Select Month!!" ForeColor="Blue" InitialValue="--Choose Month--"></asp:RequiredFieldValidator>
                                <br />
                                <asp:RequiredFieldValidator ID="RequiredFieldYear" runat="server" 
                                    ControlToValidate="DropDownYear" Display="Dynamic" ErrorMessage="Select Year!!" 
                                    ForeColor="Blue" InitialValue="--Choose Year--"></asp:RequiredFieldValidator>
                            </td>
                        </tr>
                        <tr>
                            <td ID="rowStyle" style="border-style: groove; border-color: #000000">
                                Total:</td>
                            <td class="style22" style="border-style: groove; border-color: #000000">
                                <asp:Label ID="totalValue" runat="server" Text="$0.00"></asp:Label>
                            </td>
                            <td>
                                &nbsp;</td>
                        </tr>
                        <tr>
                            <td ID="rowStyle" style="border-style: groove; border-color: #000000">
                                Permit-Start Date:</td>
                            <td class="style22" style="border-style: groove; border-color: #000000">
                                <asp:TextBox ID="date" runat="server" Height="22px" ReadOnly="True" 
                                    style="text-align: center" Width="155px" AutoPostBack="True"></asp:TextBox>
                            </td>
                            <td>
                                &nbsp;</td>
                        </tr>
                        <tr>
                            <td ID="rowStyle" style="border-style: groove; border-color: #000000">
                                Permit-Expiry date:</td>
                            <td class="style22" style="border-style: groove; border-color: #000000">
                                <asp:TextBox ID="expiry" runat="server" ReadOnly="True" 
                                    Width="152px" AutoPostBack="True" style="text-align: center"></asp:TextBox>
                            </td>
                            <td>
                                &nbsp;</td>
                        </tr>
                    </table>
                    <asp:Button ID="back3" runat="server" style="margin-left: 212px" Text="Back" 
                        Width="100px" CausesValidation="False" onclick="back3_Click1" />
                    <asp:Button ID="confirm" runat="server" style="margin-left: 221px" 
                        Text="Confirm" Width="100px" onclick="confirm_Click" />

    and code behind:

    protected void  confirm_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ParkingConnectionString"].ConnectionString);
        con.Open();
        SqlCommand cmd = new SqlCommand("insert into [Durations]  (ColumnName) values('" + Label1.Text + "','" + DropDownPermit.SelectedValue + "','" + duration.Text + "', '" + date.Text + "', '" + expiry.Text + "'  WHERE ([username] = @username)", con);
        cmd.ExecuteNonQuery();
        con.Close();
    
    
    }





     

    Tuesday, September 24, 2013 11:43 PM

Answers

  • User-1716253493 posted

    so what if i want insert a new data based on the user login how would i go on doing that 

    INSERT INTO TBL (col1,col2,col3,userid) Values (@val1,@val2,@val3,@userid)



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 25, 2013 12:24 AM
  • User-1716253493 posted

    Set it as int, then in column properties, go to identity specification set isidentity=yes increament=1 seed=1

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 25, 2013 2:08 AM

All replies

  • User-1716253493 posted

    if you want insert data dont use WHERE clause

    INSERT INTO tbl (Col1,col2,col3) VALUES (@val1,@val2,@val3)

    WHERE clause usualy for upadating data, like

    UPDATE tbl SET col1=@val1,col2=@val2,col3=@val3 WHERE userid=@userid




    Wednesday, September 25, 2013 12:00 AM
  • User1067395925 posted

    so what if i want insert a new data based on the user login how would i go on doing that 

    Wednesday, September 25, 2013 12:05 AM
  • User177399542 posted

    Hi it is because you are writing wrong query. Instead of using update query you are writing Invalid Insert query.

    write your update query like : 

    UPDATE yourtablename SET yourCol1=@yourcol1Value,yourCol2=@yourcol2Value,yourcol3=@yourcol3Value WHERE yourprimarykey=@PrimaryKeyValue



    Wednesday, September 25, 2013 12:06 AM
  • User-1716253493 posted

    so what if i want insert a new data based on the user login how would i go on doing that 

    INSERT INTO TBL (col1,col2,col3,userid) Values (@val1,@val2,@val3,@userid)



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 25, 2013 12:24 AM
  • User2118991054 posted

    so okay i i fixed that but when i click on confirm it gives me an error saying cannot insert NULL into column "durID"

    and one more thing when i do succesfully get it inserted into the table how do i update this in my gridview

    Wednesday, September 25, 2013 1:09 AM
  • User-1716253493 posted
    Set durid as identify (autoincreament)
    Wednesday, September 25, 2013 1:13 AM
  • User2118991054 posted

    what do u mean "identify" there is no identify in data type

    Wednesday, September 25, 2013 1:15 AM
  • User-1716253493 posted

    Set it as int, then in column properties, go to identity specification set isidentity=yes increament=1 seed=1

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 25, 2013 2:08 AM