siddhuoops
asked on
Insert data into SQL database
Hi everyone,
I have a web entry form which I have made using DetailsView in C#. I don't know how would I insert the data filled in by the data entry person to the Sql Database. Do I need to create an empty table with just the column names? How would I actually insert the entries into the respective columns in the database? There is a submit button that would enter the values in the database. Could somebody help me on this? This is very urgent.
Thank you all in advance.
I have a web entry form which I have made using DetailsView in C#. I don't know how would I insert the data filled in by the data entry person to the Sql Database. Do I need to create an empty table with just the column names? How would I actually insert the entries into the respective columns in the database? There is a submit button that would enter the values in the database. Could somebody help me on this? This is very urgent.
Thank you all in advance.
Are you binding your DetailsView to any kind of DataSource ?
ASKER
The DetailsView is binded to the SqlDataSource..
Here is an example of my project:
Date TxtDate
Vendor txtVendor
Client ddrClient
Submit
Here is an example of my project:
Date TxtDate
Vendor txtVendor
Client ddrClient
Submit
ASKER
The submit button is not inside the detailsview. Its outside and all the entries are made inside the detailsview.
ASKER
Here is what I have done so far but I am not getting it to work.
protected void DetailsView1_PageIndexChan ging(objec t sender, DetailsViewPageEventArgs e)
{
string fetch;
fetch = "INSERT INTO Discrepancy(";
fetch += "Date_To_Media, Vendor, Vendor_Name, ";
fetch += "Client, Dealer_Agent, Invoice_Num, Invoice_Date, Invoice_Amt, Date_Given_to_Buyer, Buyer_Name, Date_Given_to_Acctg)";
fetch += "VALUES ('";
fetch += txtDate.Text + "','";
fetch += txtVcode.Text + "','";
fetch += txtVname.Text + "','";
fetch += txtDealer.Text + "',";
fetch += txtInvNum.Text + "','";
fetch += txtInvDate.Text + "','";
fetch += txtInvAmt.Text + "','";
fetch += txtDatetoBuyer.Text + "','";
fetch += txtDatetoAcctg.Text + "')";
string con = "Data Source=sasql01;Initial Catalog=Projections; User ID=sa_sql;Password=str@teg 1K@m3r1ka" ;
SqlConnection conn = new SqlConnection(con);
SqlCommand cmd = new SqlCommand(fetch, conn);
int added = 0;
try
{
conn.Open();
added = cmd.ExecuteNonQuery();
Label1.Text = added.ToString() + "records inserted.";
}
catch (Exception err)
{
Label1.Text = "Error inserting record.";
Label1.Text += err.Message;
}
finally
{
conn.Close();
}
}
protected void btnSubmit_Click(object sender, DetailsViewPageEventArgs e)
{
}
Error: txtDate is not defined, txtVcode is not defined.................an d so on.
Am I using the right function to insert my values. On the other hand, my source code looks something like this.
<%@ Page Language="C#" MasterPageFile="~/Test.mas ter" AutoEventWireup="true" CodeFile="Default3.aspx.cs " Inherits="Default3" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="Cont entPlaceHo lder1" Runat="Server">
<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px" AutoGenerateRows="False" DataKeyNames="Client" AllowPaging="True" BackColor="White" BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" CellPadding="3" CellSpacing="1" GridLines="None"
DataSourceID="SqlDataSourc e1" EnableViewState="False" OnPageIndexChanging="Detai lsView1_Pa geIndexCha nging">
<Fields>
<asp:TemplateField HeaderText="Medium">
<ItemTemplate><asp:RadioBu tton ID="radPrint" runat="server" Text="Print" Width="66px" OnCheckedChanged="radPrint _CheckedCh anged" GroupName="Medium" /> <asp:RadioButton ID="radBroadCast" runat="server" Text="BroadCast" GroupName="Medium" /><br />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date To Media" InsertVisible="False"
SortExpression="Date To Media">
<ItemTemplate>
<asp:TextBox ID="txtDate" runat="server" Width="100px">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Vendor Code" SortExpression="Vendor Code">
<ItemTemplate>
<asp:TextBox ID="txtVcode" runat="server" Width="60px">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Vendor" SortExpression="Vendor">
<ItemTemplate>
<asp:TextBox ID="txtVname" runat="server">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Client">
<ItemTemplate>
<asp:DropDownList ID="ddrClient" DataSourceID="SqlDataSourc e1" DataTextField="Client"
DataValueField="Client" AutoPostBack="True" Width="200px" runat="server">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource1" runat="Server" ConnectionString="<%$ ConnectionStrings:Projecti onsConnect ionString %>"
SelectCommand="SELECT [Client] FROM [tbl_Current_Year_Projecti ons]"></as p:SqlDataS ource>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Dealer/Agent">
<ItemTemplate>
<asp:TextBox ID="txtDealer" runat="server">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Invoice#">
<ItemTemplate>
<asp:TextBox ID="txtInvNum" runat="server" Width="100px">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Invoice Date">
<ItemTemplate>
<asp:TextBox ID="txtInvDate" runat="server" Width="100px">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Invoice Amt">
<ItemTemplate>
<asp:TextBox ID="txtInvAmt" runat="Server" Width="100px">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date Given to Buyer">
<ItemTemplate>
<asp:TextBox ID="txtDatetoBuyer" runat="server" Width="100px">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Buyer Name">
<ItemTemplate>
<asp:DropDownList ID="ddrBuyer" AutoPostBack="true" runat="server" Width="200px">
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date Given to acctg">
<ItemTemplate>
<asp:TextBox ID="txtDatetoAcctg" runat="server" Width="100px">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Completed">
<ItemTemplate>
<asp:CheckBox ID="chkCompleted" runat="Server" />
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
</Fields>
<FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
<EditRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#DEDFDE" ForeColor="Black" />
<PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />
</asp:DetailsView>
<asp:Label ID="Label1" runat="server" Width="278px"></asp:Label> <br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Projecti onsConnect ionString %>"
SelectCommand="SELECT [Client] FROM [tbl_Current_Year_Projecti ons]"></as p:SqlDataS ource>
<br />
<asp:Button ID="btnSubmit" runat="server" OnClick="btnSubmit_Click" Text="Submit"
Width="86px" />
</asp:Content>
protected void DetailsView1_PageIndexChan
{
string fetch;
fetch = "INSERT INTO Discrepancy(";
fetch += "Date_To_Media, Vendor, Vendor_Name, ";
fetch += "Client, Dealer_Agent, Invoice_Num, Invoice_Date, Invoice_Amt, Date_Given_to_Buyer, Buyer_Name, Date_Given_to_Acctg)";
fetch += "VALUES ('";
fetch += txtDate.Text + "','";
fetch += txtVcode.Text + "','";
fetch += txtVname.Text + "','";
fetch += txtDealer.Text + "',";
fetch += txtInvNum.Text + "','";
fetch += txtInvDate.Text + "','";
fetch += txtInvAmt.Text + "','";
fetch += txtDatetoBuyer.Text + "','";
fetch += txtDatetoAcctg.Text + "')";
string con = "Data Source=sasql01;Initial Catalog=Projections; User ID=sa_sql;Password=str@teg
SqlConnection conn = new SqlConnection(con);
SqlCommand cmd = new SqlCommand(fetch, conn);
int added = 0;
try
{
conn.Open();
added = cmd.ExecuteNonQuery();
Label1.Text = added.ToString() + "records inserted.";
}
catch (Exception err)
{
Label1.Text = "Error inserting record.";
Label1.Text += err.Message;
}
finally
{
conn.Close();
}
}
protected void btnSubmit_Click(object sender, DetailsViewPageEventArgs e)
{
}
Error: txtDate is not defined, txtVcode is not defined.................an
Am I using the right function to insert my values. On the other hand, my source code looks something like this.
<%@ Page Language="C#" MasterPageFile="~/Test.mas
<asp:Content ID="Content1" ContentPlaceHolderID="Cont
<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px" AutoGenerateRows="False" DataKeyNames="Client" AllowPaging="True" BackColor="White" BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" CellPadding="3" CellSpacing="1" GridLines="None"
DataSourceID="SqlDataSourc
<Fields>
<asp:TemplateField HeaderText="Medium">
<ItemTemplate><asp:RadioBu
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date To Media" InsertVisible="False"
SortExpression="Date To Media">
<ItemTemplate>
<asp:TextBox ID="txtDate" runat="server" Width="100px">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Vendor Code" SortExpression="Vendor Code">
<ItemTemplate>
<asp:TextBox ID="txtVcode" runat="server" Width="60px">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Vendor" SortExpression="Vendor">
<ItemTemplate>
<asp:TextBox ID="txtVname" runat="server">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Client">
<ItemTemplate>
<asp:DropDownList ID="ddrClient" DataSourceID="SqlDataSourc
DataValueField="Client" AutoPostBack="True" Width="200px" runat="server">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource1" runat="Server" ConnectionString="<%$ ConnectionStrings:Projecti
SelectCommand="SELECT [Client] FROM [tbl_Current_Year_Projecti
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Dealer/Agent">
<ItemTemplate>
<asp:TextBox ID="txtDealer" runat="server">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Invoice#">
<ItemTemplate>
<asp:TextBox ID="txtInvNum" runat="server" Width="100px">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Invoice Date">
<ItemTemplate>
<asp:TextBox ID="txtInvDate" runat="server" Width="100px">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Invoice Amt">
<ItemTemplate>
<asp:TextBox ID="txtInvAmt" runat="Server" Width="100px">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date Given to Buyer">
<ItemTemplate>
<asp:TextBox ID="txtDatetoBuyer" runat="server" Width="100px">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Buyer Name">
<ItemTemplate>
<asp:DropDownList ID="ddrBuyer" AutoPostBack="true" runat="server" Width="200px">
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date Given to acctg">
<ItemTemplate>
<asp:TextBox ID="txtDatetoAcctg" runat="server" Width="100px">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Completed">
<ItemTemplate>
<asp:CheckBox ID="chkCompleted" runat="Server" />
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
</Fields>
<FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
<EditRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#DEDFDE" ForeColor="Black" />
<PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />
</asp:DetailsView>
<asp:Label ID="Label1" runat="server" Width="278px"></asp:Label>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Projecti
SelectCommand="SELECT [Client] FROM [tbl_Current_Year_Projecti
<br />
<asp:Button ID="btnSubmit" runat="server" OnClick="btnSubmit_Click" Text="Submit"
Width="86px" />
</asp:Content>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help. I am off tomorrow, so I will have to wait until Thursday to run this code. I will let you know by Thursday. Thank you for your help once again.
ASKER
I used the above code and I got an error. The error is "The type or namespace name stringBuilder' could not be found(are you missing a using directive or an assembly reference?)"
ASKER
I figured this error out by having Using.System.Text. But here is the problem I am running into.
I have a try catch block to check if I have any errors and this is what I am getting after I hit the submit button.
Error inserting record.Unclosed quotation mark after the character string ')'. Incorrect syntax near ')'.
This is my code:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls. WebParts;
using System.Web.UI.HtmlControls ;
using System.Data.SqlClient;
using System.Text;
public partial class Default3 : System.Web.UI.Page
{
//private DataSet ds;
//private SubmitEntry ser = new SubmitEntry();
//private static DateTime DateToMedia=0;
//private static int Venor=0;
//private static string VendorName=0;
//private static string Client=0;
//private static string DealerAgent=0;
//private static int InvoiceNum=0;
//private static DateTime InvoiceDate = 0;
//private static Decimal InvoiceAmt = 0;
//private static DateTime DateGivenToBuyer = 0;
//private static string BuyerName = 0;
//private static DateTime DateGivenToAcctg = 0;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void radPrint_CheckedChanged(ob ject sender, EventArgs e)
{
}
protected void DetailsView1_PageIndexChan ging(objec t sender, DetailsViewPageEventArgs e)
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
StringBuilder fetch = new StringBuilder();
fetch.Append("INSERT INTO Discrepancy(");
fetch.Append("Date_To_Medi a, Vendor, Vendor_Name, ");
fetch.Append("Client, Dealer_Agent, Invoice_Num, Invoice_Date, Invoice_Amt, Date_Given_to_Buyer, Buyer_Name, Date_Given_to_Acctg)");
fetch.Append("VALUES ('");
fetch.AppendFormat("{0}',' ", ((TextBox)DetailsView1.Fin dControl(" txtDate")) .Text);
fetch.AppendFormat("{0}',' ", ((TextBox)DetailsView1.Fin dControl(" txtVcode") ).Text);
fetch.AppendFormat("{0}',' ", ((TextBox)DetailsView1.Fin dControl(" txtVname") ).Text);
fetch.AppendFormat("{0}',' ", ((TextBox)DetailsView1.Fin dControl(" txtDealer" )).Text);
fetch.AppendFormat("{0}',' ", ((TextBox)DetailsView1.Fin dControl(" txtInvNum" )).Text);
fetch.AppendFormat("{0}',' ", ((TextBox)DetailsView1.Fin dControl(" txtInvDate ")).Text);
fetch.AppendFormat("{0}',' ", ((TextBox)DetailsView1.Fin dControl(" txtInvAmt" )).Text);
fetch.AppendFormat("{0}',' ", ((TextBox)DetailsView1.Fin dControl(" txtDatetoB uyer")).Te xt);
fetch.AppendFormat("{0}',' )", ((TextBox)DetailsView1.Fin dControl(" txtDatetoA cctg")).Te xt);
string con = "Data Source=sasql01;Initial Catalog=Projections; User ID=sa_sql;Password=str@teg 1K@m3r1ka" ;
SqlConnection conn = new SqlConnection(con);
SqlCommand cmd = new SqlCommand(fetch.ToString( ), conn);
//Add the parameters
//cmd.Parameters.AddWithVa lue("@Date _To_Media" ,
int added = 0;
try
{
conn.Open();
added = cmd.ExecuteNonQuery();
Label1.Text = added.ToString() + "records inserted.";
}
catch (Exception err)
{
Label1.Text = "Error inserting record.";
Label1.Text += err.Message;
}
finally
{
conn.Close();
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
}
}
I have a try catch block to check if I have any errors and this is what I am getting after I hit the submit button.
Error inserting record.Unclosed quotation mark after the character string ')'. Incorrect syntax near ')'.
This is my code:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.
using System.Web.UI.HtmlControls
using System.Data.SqlClient;
using System.Text;
public partial class Default3 : System.Web.UI.Page
{
//private DataSet ds;
//private SubmitEntry ser = new SubmitEntry();
//private static DateTime DateToMedia=0;
//private static int Venor=0;
//private static string VendorName=0;
//private static string Client=0;
//private static string DealerAgent=0;
//private static int InvoiceNum=0;
//private static DateTime InvoiceDate = 0;
//private static Decimal InvoiceAmt = 0;
//private static DateTime DateGivenToBuyer = 0;
//private static string BuyerName = 0;
//private static DateTime DateGivenToAcctg = 0;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void radPrint_CheckedChanged(ob
{
}
protected void DetailsView1_PageIndexChan
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
StringBuilder fetch = new StringBuilder();
fetch.Append("INSERT INTO Discrepancy(");
fetch.Append("Date_To_Medi
fetch.Append("Client, Dealer_Agent, Invoice_Num, Invoice_Date, Invoice_Amt, Date_Given_to_Buyer, Buyer_Name, Date_Given_to_Acctg)");
fetch.Append("VALUES ('");
fetch.AppendFormat("{0}','
fetch.AppendFormat("{0}','
fetch.AppendFormat("{0}','
fetch.AppendFormat("{0}','
fetch.AppendFormat("{0}','
fetch.AppendFormat("{0}','
fetch.AppendFormat("{0}','
fetch.AppendFormat("{0}','
fetch.AppendFormat("{0}','
string con = "Data Source=sasql01;Initial Catalog=Projections; User ID=sa_sql;Password=str@teg
SqlConnection conn = new SqlConnection(con);
SqlCommand cmd = new SqlCommand(fetch.ToString(
//Add the parameters
//cmd.Parameters.AddWithVa
int added = 0;
try
{
conn.Open();
added = cmd.ExecuteNonQuery();
Label1.Text = added.ToString() + "records inserted.";
}
catch (Exception err)
{
Label1.Text = "Error inserting record.";
Label1.Text += err.Message;
}
finally
{
conn.Close();
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
}
}
Slight typo. The line:
fetch.AppendFormat("{0}',' )", ((TextBox)DetailsView1.Fin dControl(" txtDatetoA cctg")).Te xt);
Should say:
fetch.AppendFormat("{0}')" , ((TextBox)DetailsView1.Fin dControl(" txtDatetoA cctg")).Te xt);
fetch.AppendFormat("{0}','
Should say:
fetch.AppendFormat("{0}')"
ASKER
got it to work..great!! thanks a lot.