CODEWITHSUNDEEP
c#asp.netsqlsql-servergridview
user3267755
user3267755

Reputation: 1070

I'm having a SQL Insert issue in my gridview

I have a gridview that I have set up to allow an insert into the next row of my table, I have an issue though... I have 2 insert statements and a select statement that updates the tables view after the insert is done. I am inserting values into two tables...the first one updates a persons information, and the second one updates the trip schedule to include them on a trip.

The second statement is where I have run into an issue...when a new record is inserted into the database, a new PersonID is created automatically for a person because I set that as the PK and identifier for the Person table, but I'm not sure how to take that newly created PersonID and insert it into the TripSchedule table.

Here is my code for inserting a new user:

protected void AddNewCustomer(object sender, EventArgs e)
{
    string nFirstName = ((TextBox)GridView1.FooterRow.FindControl("txtFirstName")).Text;
    string nLastName = ((TextBox)GridView1.FooterRow.FindControl("txtLastName")).Text;
    string nEmergency = ((TextBox)GridView1.FooterRow.FindControl("txtEmergency")).Text;
    string nCell = ((TextBox)GridView1.FooterRow.FindControl("txtCell")).Text;
    string nAge = ((TextBox)GridView1.FooterRow.FindControl("txtAge")).Text;
    string nActivityCard = ((TextBox)GridView1.FooterRow.FindControl("txtActivityCard")).Text;
    string nInitials = ((TextBox)GridView1.FooterRow.FindControl("txtInitials")).Text;
    string nBoat = ((TextBox)GridView1.FooterRow.FindControl("txtBoat")).Text;
    string nGroup = ((TextBox)GridView1.FooterRow.FindControl("txtGroup")).Text;

    SqlConnection con = new SqlConnection(strConnString);
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "insert into Person(FirstName, LastName, Emergency#, Cell#, Age, ActivityCard, CraftType, Initials, Group#) " +
        "values(@FirstName, @LastName, @Emergency, @Cell, @Age, @ActivityCard, @Boat, @Initials, @Group);" +
        "insert into TripSchedule(TripType, PersonID, Time, Date) values ('" + ddlTripType.SelectedItem + "', WHAT GOES HERE?, '" + ddlTripTime.SelectedItem + "', '" + TextBox1.Text + "');" + 
        "SELECT Person.PersonID, Person.FirstName AS FirstName, Person.LastName AS LastName, Person.Emergency# AS Emergency#, Person.Cell# AS Cell#, Person.Age AS Age, " +
        "Person.ActivityCard AS ActivityCard, Person.CraftType AS CraftType, Person.Initials AS Initials, Person.Group# AS Group# " +
        "FROM Person INNER JOIN " +
        "TripSchedule ON Person.PersonID = TripSchedule.PersonID where TripSchedule.Date = '" + TextBox1.Text + "' and " +
        "TripSchedule.Time = '" + ddlTripTime.SelectedItem + "' and TripSchedule.TripType = '" + ddlTripType.SelectedItem + "';";

    cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = nFirstName;
    cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = nLastName;
    cmd.Parameters.Add("@Emergency", SqlDbType.NChar).Value = nEmergency;
    cmd.Parameters.Add("@Cell", SqlDbType.NChar).Value = nCell;
    cmd.Parameters.Add("@Age", SqlDbType.NChar).Value = nAge;
    cmd.Parameters.Add("@ActivityCard", SqlDbType.NChar).Value = nActivityCard;
    cmd.Parameters.Add("@Initials", SqlDbType.NChar).Value = nInitials;
    cmd.Parameters.Add("@Boat", SqlDbType.VarChar).Value = nBoat;
    cmd.Parameters.Add("@Group", SqlDbType.VarChar).Value = nGroup;

    GridView1.DataSource = GetData(cmd);
    GridView1.DataBind();
}

Upvotes: 2

Views: 91

Answers (1)

marc_s
marc_s

Reputation: 754518

I would personally split this up into at least two SqlCommand calls - one to insert the Person, another to insert the TripSchedule. Also: you should use parametrized queries all over - not just some places (and not in others....).

To get the newly inserted PersonID, use the SCOPE_IDENTITY() call - something like this:

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO dbo.Person(FirstName, LastName, Emergency#, Cell#, Age, ActivityCard, CraftType, Initials, Group#) " +
                  "VALUES(@FirstName, @LastName, @Emergency, @Cell, @Age, @ActivityCard, @Boat, @Initials, @Group);" +
                  "SELECT SCOPE_IDENTITY();";

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = nFirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = nLastName;
cmd.Parameters.Add("@Emergency", SqlDbType.NChar).Value = nEmergency;
cmd.Parameters.Add("@Cell", SqlDbType.NChar).Value = nCell;
cmd.Parameters.Add("@Age", SqlDbType.NChar).Value = nAge;
cmd.Parameters.Add("@ActivityCard", SqlDbType.NChar).Value = nActivityCard;
cmd.Parameters.Add("@Initials", SqlDbType.NChar).Value = nInitials;
cmd.Parameters.Add("@Boat", SqlDbType.VarChar).Value = nBoat;
cmd.Parameters.Add("@Group", SqlDbType.VarChar).Value = nGroup;

conn.Open();
int newPersonID = (int)cmd.ExecuteScalar();
conn.Close();

Since this SqlCommand call returns exactly one row, one column (the newly inserted PersonID), you can easily use the .ExecuteScalar() method - and you get back the newly inserted PersonID as the result of this call. Now you can insert the data into the TripSchedule table (and again: please use all parameters for this call, too!).

Or alternatively: wrap this all up into a single stored procedure that handles all this T-SQL kung-fu internally.

Or better yet: in these days and times, I'd personally switch to an ORM like Entity Framework to make this all much easier

Upvotes: 2

Related Questions