Adding a new row to SQL table

Hi all,

I have a database setup with a few rows and i would like to allow a user to create a new row of data. After some googling, I've deduced that I need to somehow use an onclick command to perform an insert command. here is my code as of right now:

protectedvoid ItemAdd_Click(object sender,EventArgs e)

{

int newnum = 4;int POnum = newnum ++;

string Item = textbox3.Text.ToString();

string Quantity = textbox4.ToString();

string Part = textbox5.ToString();

string Description = textbox6.Text.ToString();

string Price = textbox7.Text.ToString();string UOM = textbox8.Text.ToString();

SqlDataSource2.InsertParameters.Clear();

SqlDataSource2.InsertParameters.Add("@.Item", Item);

SqlDataSource2.InsertParameters.Add("@.Quantity", Quantity);

SqlDataSource2.InsertParameters.Add("@.Part", Part);

SqlDataSource2.InsertParameters.Add("@.Description", Description);

SqlDataSource2.InsertParameters.Add("@.Price", Price);

SqlDataSource2.InsertParameters.Add("@.UOM", UOM);

SqlDataSource1.InsertCommand ="insert into ItemMaster "+

"values (12, @.Item, @.Quantity, @.Part, @.Description, @.Price, @.UOM)";

SqlDataSource2.Insert();

}

here's the aspx:

<%@.PageLanguage="C#"MasterPageFile="~/MasterPage.master"AutoEventWireup="true"CodeFile="createpo.aspx.cs"Inherits="Subpgs_Purchasing_createpo"Title="Create A PO" %>

<asp:ContentID="Content1"ContentPlaceHolderID="ContentPlaceHolder1"Runat="Server">

<tablestyle="width: 600px; position: relative; top: 30px; height: 253px"cellpadding="10px">

<tr>

<tdstyle="width: 180px; text-align: left;"rowspan="2">

Select Vendor<br/>- or -<br/><asp:LinkButtonID="Linkbutton1"runat="server"OnClick="Linkbutton1_Click">Create Vendor</asp:LinkButton></td>

<tdstyle="width: 100px"rowspan="2">

<asp:ListBoxID="ListBox2"runat="server"DataSourceID="SqlDataSource1"DataTextField="Name"

DataValueField="ID"OnSelectedIndexChanged="ListBox2_SelectedIndexChanged"></asp:ListBox><asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand="SELECT * FROM [Vendor_info]"></asp:SqlDataSource>

</td>

<tdstyle="width: 180px; text-align: left;">

Job/Req.S.O. No.</td>

<tdstyle="width: 100px">

<asp:TextBoxID="Job"runat="server"></asp:TextBox></td>

</tr>

<tr>

<tdstyle="width: 180px; text-align: left;">

Terms</td>

<tdstyle="width: 100px">

<asp:TextBoxID="Terms"runat="server"></asp:TextBox></td>

</tr>

<tr>

<tdstyle="width: 180px; text-align: left;">

F.O.B.</td>

<tdstyle="width: 100px">

<asp:TextBoxID="FOB"runat="server"></asp:TextBox></td>

<tdstyle="width: 180px; text-align: left;">

Ship To:<br/>

Address:<br/>

City / State / Zip:</td>

<tdstyle="width: 100px">

<asp:TextBoxID="Ship1"runat="server"></asp:TextBox>

<asp:TextBoxID="Ship2"runat="server"></asp:TextBox>

<asp:TextBoxID="Ship3"runat="server"></asp:TextBox></td>

</tr>

</table>

<br/>

<br/>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Purchase_Orders.mdf;Integrated Security=True;User Instance=True;Context Connection=False"

ProviderName="System.Data.SqlClient"SelectCommand="SELECT [Item], [Quantity], [Part], [Description], [Price], [UOM], [Ammount], [PONumber] FROM [ItemMaster]"DeleteCommand="DELETE FROM [ItemMaster] WHERE [PONumber] = @.PONumber"InsertCommand="INSERT INTO [ItemMaster] ([Item], [Quantity], [Part], [Description], [Price], [UOM], [Ammount], [PONumber]) VALUES (@.Item, @.Quantity, @.Part, @.Description, @.Price, @.UOM, @.Ammount, @.PONumber)"UpdateCommand="UPDATE [ItemMaster] SET [Item] = @.Item, [Quantity] = @.Quantity, [Part] = @.Part, [Description] = @.Description, [Price] = @.Price, [UOM] = @.UOM, [Ammount] = @.Ammount WHERE [PONumber] = @.PONumber">

<DeleteParameters>

<asp:ParameterName="PONumber"Type="Decimal"/>

</DeleteParameters>

<UpdateParameters>

<asp:ParameterName="Item"Type="String"/>

<asp:ParameterName="Quantity"Type="Decimal"/>

<asp:ParameterName="Part"Type="String"/>

<asp:ParameterName="Description"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="UOM"Type="String"/>

<asp:ParameterName="Ammount"Type="Decimal"/>

<asp:ParameterName="PONumber"Type="Decimal"/>

</UpdateParameters>

<InsertParameters>

<asp:ParameterName="Item"Type="String"/>

<asp:ParameterName="Quantity"Type="Decimal"/>

<asp:ParameterName="Part"Type="String"/>

<asp:ParameterName="Description"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="UOM"Type="String"/>

<asp:ParameterName="Ammount"Type="Decimal"/>

<asp:ParameterName="PONumber"Type="Decimal"/>

</InsertParameters>

</asp:SqlDataSource>

<br/>

<table>

<tr>

<td>Item #</td>

<td><asp:TextBoxID="textbox3"runat=server></asp:TextBox></td>

<td>Quantity</td>

<td><asp:TextBoxID="textbox4"runat=server></asp:TextBox></td>

<td>Part Number</td>

<td><asp:TextBoxID="textbox5"runat=server></asp:TextBox></td>

</tr>

<tr>

<td>Description</td>

<td><asp:TextBoxID="textbox6"runat=server></asp:TextBox></td>

<td>Unit Price</td>

<td><asp:TextBoxID="textbox7"runat=server></asp:TextBox></td>

<td>Unit of Measure</td>

<td><asp:TextBoxID="textbox8"runat=server></asp:TextBox></td>

</tr>

<tr>

<tdcolspan="6"align="center"><asp:ButtonID="ItemAdd"runat=servertext="Add Item"OnClick="ItemAdd_Click"/></td>

</tr>

</table>

<br/>

<br/>

<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"DataKeyNames="PONumber"

DataSourceID="SqlDataSource2">

<Columns>

<asp:CommandFieldShowEditButton="True"/>

<asp:BoundFieldDataField="Item"HeaderText="Item"SortExpression="Item"/>

<asp:BoundFieldDataField="Quantity"HeaderText="Quantity"SortExpression="Quantity"/>

<asp:BoundFieldDataField="Part"HeaderText="Part"SortExpression="Part"/>

<asp:BoundFieldDataField="Description"HeaderText="Description"SortExpression="Description"/>

<asp:BoundFieldDataField="Price"HeaderText="Price"SortExpression="Price"/>

<asp:BoundFieldDataField="UOM"HeaderText="UOM"SortExpression="UOM"/>

<asp:BoundFieldDataField="Ammount"HeaderText="Ammount"SortExpression="Ammount"/>

<asp:BoundFieldDataField="PONumber"HeaderText="PONumber"ReadOnly="True"SortExpression="PONumber"

Visible="False"/>

</Columns>

</asp:GridView>

<br/>

<br/>

<br/>

<asp:ButtonID="Button1"runat="server"Text="Submit Data"OnClick="Button1_Click"/> <br/>

<br/>

<asp:LabelID="Label1"runat="server"Text="Please Verify Information below!"Visible="False"></asp:Label><br/>

<br/>

<tablealign="left">

<tr>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label4"runat="server"Text="Job/REQ.S.O. No."Visible="false"></asp:Label></td>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label5"runat="server"></asp:Label></td>

</tr>

<tr>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label7"runat="server"Text="Terms"Visible="false"></asp:Label></td>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label8"runat="server"></asp:Label></td>

</tr>

<tr>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label10"runat="server"Text="F.O.B."Visible="false"></asp:Label></td>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label11"runat="server"></asp:Label></td>

</tr>

</table>

<asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataSourceID="SqlDataSource1"

Height="50px"Visible="False"Width="260px"BackColor="#CCCCCC"BorderColor="#999999"BorderStyle="Solid"BorderWidth="3px"CellPadding="4"CellSpacing="2"DataKeyNames="ID"ForeColor="Black">

<FooterStyleBackColor="#CCCCCC"/>

<EditRowStyleBackColor="#000099"Font-Bold="True"ForeColor="White"/>

<RowStyleBackColor="White"/>

<PagerStyleBackColor="#CCCCCC"ForeColor="Black"HorizontalAlign="Left"/>

<Fields>

<asp:BoundFieldDataField="ID"HeaderText="ID"ReadOnly="True"SortExpression="ID"/>

<asp:BoundFieldDataField="Name"HeaderText="Name"SortExpression="Name"/>

<asp:BoundFieldDataField="Street"HeaderText="Street"SortExpression="Street"/>

<asp:BoundFieldDataField="City"HeaderText="City"SortExpression="City"/>

<asp:BoundFieldDataField="State"HeaderText="State"SortExpression="State"/>

<asp:BoundFieldDataField="Zip"HeaderText="Zip"SortExpression="Zip"/>

<asp:BoundFieldDataField="Telephone Number"HeaderText="Telephone Number"SortExpression="Telephone Number"/>

<asp:BoundFieldDataField="Contact Information"HeaderText="Contact Information"

SortExpression="Contact Information"/>

<asp:BoundFieldDataField="E-mail address"HeaderText="E-mail address"SortExpression="E-mail address"/>

<asp:BoundFieldDataField="Fax Number"HeaderText="Fax Number"SortExpression="Fax Number"/>

<asp:BoundFieldDataField="Comments"HeaderText="Comments"SortExpression="Comments"/>

</Fields>

<HeaderStyleBackColor="Black"Font-Bold="True"ForeColor="White"/>

</asp:DetailsView>

<br/>

<br/>

<br/>

<asp:ButtonID="Button2"runat="server"Text="E-Mail PO"Visible="False"OnClick="Button2_Click"/>

</asp:Content>

whenever I run this, I receive the following error and am not sure how to correct it:

System.Data.SqlClient.SqlException: Must declare the scalar variable "@.Item".

If someone could please explain to me the process here of what is going ion it would be greatly appreciated. My understanding is the @.item is just a variable if you will so i'm not sure why its stating i need to declare it.

Thank you all!

Welcome to the community. I would suggest you use the InsertCode option to insert code in your posts from next time. Lets start from the starting and then we will remove errors as we go.

Gizm0:

string Item = textbox3.Text.ToString();

string Quantity = textbox4.ToString();

string Part = textbox5.ToString();

I am assuming these are your textboxes. You should use Text property of textboxes like you used for others. Also, you don't have to use ToString() method. Text property returns the textbox value as a string.

|||

Hi Bullpit.

Sorry about that, I didn't see the code Icon there. Anyhow, I made the changes and continue to receive the same error. At first glance, does it at least look like i'm on the right path? I've always been more hardware/network inclined so a lot of the google searches and forums I used to piece this together are still Greek to me.

Thanks for the response!

|||

I don't use SqlDataSource as such. Since you are writing some code, you can instead use this code to insert values to your database. Extend this example to add more parameters.

protected void ItemAdd_Click(object sender, EventArgs e) {// connection string to your database SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");// open connection mySqlConnection.Open();// create command object to execute query SqlCommand mySqlCommand = mySqlConnection.CreateCommand();// set command text to the INSERT sql query mySqlCommand.CommandText = @."INSERT INTO Employee (Item, Quantity, Part...) VALUES( @.Item, @.Quantity, @.Part...)";// Add parameters mySqlCommand.Parameters.Add("@.Item", SqlDbType.VarChar); mySqlCommand.Parameters.Add("@.Quantity", SqlDbType.VarChar); mySqlCommand.Parameters.Add("@.Part", SqlDbType.VarChar);// set parameter values mySqlCommand.Parameters["@.Item"].Value = textbox3.Text; mySqlCommand.Parameters["@.Quantity"].Value = textbox4.Text; mySqlCommand.Parameters["@.Part"].Value = textbox5.Text;// execute query mySqlCommand.ExecuteNonQuery();// close connection mySqlConnection.Close();}

You will need these namespaces.

using System.Data;
using System.Data.SqlClient;

|||

Hey BullPit,

I really appreciate the help! I was able to get this working using your code above. Now it looks like I just need to validate the input boxes to make sure the correct data is being entered in the fields.

Again, thanks!

sql