How to insert data from a form into my database?

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();


}

 

Answer 1

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

Answer 2

User1067395925 posted

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

Answer 3

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

Answer 4

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)

Answer 5

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

Answer 6

User-1716253493 posted
Set durid as identify (autoincreament)

Answer 7

User2118991054 posted

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

Answer 8

User-1716253493 posted

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