LPompa LPompa - 2 months ago 16
C# Question

Insert textbox items and gridview items into a sql database

I am trying to insert a set of 4 textbox values and a dynamic grid into SQL database. Currently I setup the dynamic grid values to be added to as comma delimited and then split out and entered into SQL. That part by itself works but when I try to add the

textboxes
outside of the
gridview
I get a number of errors no matter how I try it.

The most recent try was to add the textboxes outside of the gridview into the same comma delimited format and then parse and insert into SQL. This is not working the error I get says that I have a
Null Exception
but for the life of me I can't find it. Any help would be greatly appreciated.

private void InsertRecords(StringCollection sc)
{
StringBuilder sb = new StringBuilder(string.Empty);
string[] splitItems = null;
const string sqlStatement = "INSERT INTO Traveler_Management.dbo.traveler (FirstName, LastName, Email, Phone, FlightNo, ArrivalDate, DepartureDate, Country, City) VALUES (@firstName, @lastName, @email, @phone, @TextBox4, @TextBox1, @TextBox2, @ddl1, @TextBox3)";
{

foreach (string item in sc)
{
if (item.Contains(","))
{
splitItems = item.Split(",".ToCharArray());
sb.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", sqlStatement, splitItems[0], splitItems[1], splitItems[2], splitItems[3], splitItems[4], splitItems[5], splitItems[6], splitItems[7], splitItems[8]);
}
}

using (SqlConnection connection = new SqlConnection(GetConnectionString()))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
{
cmd.CommandType = CommandType.Text;
//cmd.ExecuteNonQuery();
}
}
lblMessage.Text = "Records successfully saved!";
}
}
protected void submit_Click(object sender, EventArgs e)
{
int rowIndex = 0;
StringCollection sc = new StringCollection();
if (ViewState["CurrentTable"] != null)
{
DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
if (dtCurrentTable.Rows.Count > 0)
{
TextBox firstName = (TextBox)FindControl("firstName");
TextBox lastName = (TextBox)FindControl("lastName");
TextBox email = (TextBox)FindControl("email");
TextBox phone = (TextBox)FindControl("phone");

sc.Add(string.Format("{5},{6},{7},{8}", firstName.Text, lastName.Text, email.Text, phone.Text));

for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
{
//extract the TextBox values
TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("TextBox4");
TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("TextBox1");
TextBox box3 = (TextBox)Gridview1.Rows[rowIndex].Cells[3].FindControl("TextBox2");
TextBox box4 = (TextBox)Gridview1.Rows[rowIndex].Cells[4].FindControl("TextBox3");

DropDownList ddl1 = (DropDownList)Gridview1.Rows[rowIndex].Cells[6].FindControl("ddl1");

//Get the values from the textboxes and drop down list then add it to the collections with a comma as the delimited values
sc.Add(string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8}", box1.Text, box2.Text, box3.Text, box4.Text, ddl1.SelectedItem.Text));
rowIndex++;
}

//Call the insert method to execute inserts
InsertRecords(sc);
}
}
}
private string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
}


I apologize here is the aspx file

<%@ Page Language="C#" AutoEventWireup="true" MasterPageFile="~/Site.Master" CodeFile="Default16.aspx.cs" Inherits="Default16" %>

<asp:Content ID="intakeTripDets" ContentPlaceHolderID="MainContent" runat="server">
<div class="input">

<label>First Name</label><br />
<asp:TextBox ID="firstName" runat="server"></asp:TextBox><br /><br />
<label>Last Name</label><br />
<asp:TextBox ID="lastName" runat="server"></asp:TextBox><br /><br />
<label>Email Address</label><br />
<asp:TextBox ID="email" runat="server">email@example.com</asp:TextBox><br /><br />
<label>Phone Number</label><br />
<asp:TextBox ID="phone" MaxLength="16" runat="server">001-555-888-1234</asp:TextBox><br /><br />
</div>

<div class="input">
<p>Please enter trip details on the below form. You can add additional rows as needed for each leg of our trip.</p>
<asp:Label runat="server" ID="lblMessage" Text=""></asp:Label><br />

<asp:gridview ID="Gridview1" runat="server" ShowFooter="true" AutoGenerateColumns="false" >
<Columns>
<asp:BoundField DataField="RowNumber" HeaderText="" />

<asp:TemplateField HeaderText="Flight Number" runat="server">
<ItemTemplate>
<asp:TextBox ID="TextBox4" width="100px" runat="server"></asp:TextBox><br />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Arrival Date">
<ItemTemplate>
<asp:TextBox ID="TextBox1" width="100px" runat="server" ></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Departure Date">
<ItemTemplate>
<asp:TextBox ID="TextBox2" width="100px" runat="server" ></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<ItemTemplate>
<asp:TextBox ID="TextBox3" width="100px" runat="server" ></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<asp:DropDownList ID="ddl1" runat="server" Width="125px">
<asp:ListItem Value="-1">--SELECT COUNTRY--</asp:ListItem>
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Button ID="buttonRemove" Width="20px" Height="20px" Text="-" runat="server" CommandName="Delete" OnClick="buttonRemove_Click"/>
<asp:Button ID="ButtonAdd" Width="20px" Height="20px" runat="server" Text="+" CommandName="Insert" onclick="ButtonAdd_Click" />
</ItemTemplate>
<FooterStyle />
<FooterTemplate>
<asp:Button ID="submit" runat="server" text="Submit Trip" Height="30px" Width="100px" OnClick="submit_Click"/></div>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:gridview>
</div>
</asp:Content>


UPDATE:

I now have made some changes to my code but it appears that the textboxes are null on postback. Can someone please explain how to make the text within my textbox be committed to the database?

Answer

Without being able to see the front-end markup (the .aspx file), I can only guess at the structure of the document, but I think this is something closer to what you are trying to accomplish.

I am making the assumption that the four textboxes appear within the gridview's row. Honestly, I have no idea how ViewState is getting involved, but it probably shouldn't. Also, it would probably be easier to do this whole thing using data binding. Here is a video that does a pretty good job of explaining how to bind a GridView control: https://www.youtube.com/watch?v=-zAXVvxdt3o

    protected void submit_Click(object sender, EventArgs e)
    {

        if (ViewState["CurrentTable"] != null)
        {
            DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];


            if (dtCurrentTable.Rows.Count > 0)
            {
                foreach (GridViewRow row in Gridview1.Rows)
                {
                    StringCollection sc = new StringCollection();


                    TextBox box1 = (TextBox)row.FindControl("TextBox4");
                    TextBox box2 = (TextBox)row.FindControl("TextBox1");
                    TextBox box3 = (TextBox)row.FindControl("TextBox2");
                    TextBox box4 = (TextBox)row.FindControl("TextBox3");
                    DropDownList ddl1 = (DropDownList)row.FindControl("ddl1");


                    sc.Add(string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8}", firstName.Text, lastName.Text, email.Text, phone.Text, box1.Text, box2.Text, box3.Text, box4.Text, ddl1.SelectedItem.Text));
                    InsertRecords(sc);
                }

            }
        }
    }

    private string GetConnectionString()
    {
        return ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
    }

    private void InsertRecords(StringCollection sc)
    {

        foreach (string item in sc)
        {
            if (item.Contains(","))
            {
                string[] splitItems = item.Split(",".ToCharArray());

                string sqlStatement =
                    "INSERT INTO Traveler_Management.dbo.traveler (FirstName, LastName, Email, Phone, FlightNo, ArrivalDate, DepartureDate, Country, City) VALUES (@firstName, @lastName, @email, @phone, @TextBox4, @TextBox1, @TextBox2,  @ddl1, @TextBox3)";

                using (SqlConnection connection = new SqlConnection(GetConnectionString()))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(sqlStatement, connection))
                    {
                        command.CommandType = CommandType.Text;
                        command.Parameters.AddWithValue("@firstName", splitItems[0]);
                        command.Parameters.AddWithValue("@lastName", splitItems[1]);
                        command.Parameters.AddWithValue("@email", splitItems[2]);
                        command.Parameters.AddWithValue("@phone", splitItems[3]);
                        command.Parameters.AddWithValue("@TextBox4", splitItems[4]);
                        command.Parameters.AddWithValue("@TextBox1", splitItems[5]);
                        command.Parameters.AddWithValue("@TextBox2", splitItems[6]);
                        command.Parameters.AddWithValue("@ddl1", splitItems[7]);
                        command.Parameters.AddWithValue("@TextBox3", splitItems[8]);

                        try
                        {
                            command.ExecuteNonQuery();
                            lblMessage.Text = "Records successfully saved!";
                        }
                        catch (Exception ex)
                        {

                            lblMessage.Text = "There was an error saving the record.";
                            //Code here to handle the exception however you want to do that
                        }
                    }
                    connection.Close();
                }
            }
        }

    }