Inserting into and then retrieving form from database

[jon4321]

I've received the requirements to make a form that inserts into a database, and then allows the user to edit the information they inserted. So what the form would do is get registration information from the user, insert it into the appropriate tables, and then redirect to a page with something like a gridview on it that would display all the form information that was submitted.

How would I be able to do this, especially since I have to work with data from multiple tables? I understand how to insert the data, but how would I get that exact data back and use it in a gridview?

[thines01]

GridView has a DataSource property that can be set to a collection or Linq result.

[jon4321]

Thanks. I will keep that in mind when I come to it, but now I'm actually having problems with the inserting part. For some reason, 2 records are being inserted when my repeater only has one. When I debug and step through the loop, it only goes through one time but two records are generated. What I'm trying to do is insert the address into the address table, and then insert the people's names, age, etc into another table (each with the last address ID since it's the foreign key). Here is my code:

addressIns = new SqlCommand("dbo.udp_OSSADDRESS_ups", thisConnection);
                    addressIns.CommandType = CommandType.StoredProcedure;

                    addressIns.Parameters.Add("ADDRESSID", SqlDbType.Int);
                    addressIns.Parameters.Add("Address", SqlDbType.VarChar, 60);
                    addressIns.Parameters.Add("City", SqlDbType.VarChar, 50);
                    addressIns.Parameters.Add("State", SqlDbType.Char, 2);
                    addressIns.Parameters.Add("Zip", SqlDbType.VarChar, 15);
                    addressIns.Parameters.Add("Email", SqlDbType.VarChar, 50);
                    addressIns.Parameters.Add("HPhone", SqlDbType.VarChar, 25);
                    addressIns.Parameters.Add("MPhone", SqlDbType.VarChar, 25);
                    addressIns.Parameters.Add("AptNum", SqlDbType.VarChar, 10);
                    

                    addressIns.Parameters["ADDRESSID"].Value = 0;
                    addressIns.Parameters["Address"].Value = txtSN1.Text;
                    addressIns.Parameters["City"].Value = txtCity1.Text;
                    addressIns.Parameters["State"].Value = ddlState1.SelectedValue;
                    addressIns.Parameters["Zip"].Value = txtZip1.Text;
                    addressIns.Parameters["Email"].Value = txtEmail1.Text;
                    addressIns.Parameters["HPhone"].Value = txtHPhone1.Text;
                    addressIns.Parameters["MPhone"].Value = txtCPhone1.Text;
                    addressIns.Parameters["AptNum"].Value = txtApt1.Text;
                    
                    

                    //addressIns.ExecuteNonQuery();
                    //gets the last AddressID
                    int lastAddressID = Convert.ToInt32(addressIns.ExecuteScalar().ToString());
                    
                    addressIns.Parameters.Clear();
                    addressIns.Dispose();

                    //insert Person
                    addressIns = new SqlCommand("dbo.udp_OSSPERSON_ups", thisConnection);
                    addressIns.CommandType = CommandType.StoredProcedure;

                    

                    List<int> personIDs = new List<int>();

                    foreach (RepeaterItem item in repeater1.Items)
                    {
                        addressIns.Parameters.Clear();
                        addressIns.Parameters.Add("PERSONID", SqlDbType.Int);
                        addressIns.Parameters.Add("firstName", SqlDbType.NVarChar, 50);
                        addressIns.Parameters.Add("lastName", SqlDbType.NVarChar, 50);
                        addressIns.Parameters.Add("AorC", SqlDbType.Char, 1);
                        addressIns.Parameters.Add("gender", SqlDbType.Char, 1);
                        addressIns.Parameters.Add("age", SqlDbType.Int);
                        addressIns.Parameters.Add("ADDRESSID", SqlDbType.Int);

                        addressIns.Parameters["PERSONID"].Value = 0;
                        addressIns.Parameters["firstName"].Value = ((TextBox)item.FindControl("txtfirstName")).Text;
                        addressIns.Parameters["lastName"].Value = ((TextBox)item.FindControl("txtlastName")).Text;
                        addressIns.Parameters["AorC"].Value = 'A';
                        addressIns.Parameters["gender"].Value = null;
                        //((RadioButtonList)item.FindControl("rdoChildGender")).SelectedItem.Value.ToString()
                        addressIns.Parameters["age"].Value = null;
                        addressIns.Parameters["ADDRESSID"].Value = lastAddressID;


                        
                        personIDs.Add(Convert.ToInt32(addressIns.ExecuteScalar().ToString()));
                    }
                    addressIns.ExecuteNonQuery();

[thines01]

You're telling me the ExecuteNonQuery() only runs once, but two records are inserted?
Do you have any insert triggers set up on the table?

[prit005]

private void showTrainGrid()
{
string scon = connect.GetConnection();
SqlConnection conn = new SqlConnection(scon);
conn.Open();
string qry = "select * from Train";
SqlCommand cmd = new SqlCommand(qry, conn);
//SqlDataReader sdr = cmd.ExecuteReader();
//if (sdr.HasRows)
//{
//    while (sdr.Read())
//    {
//        GridView1.DataSource = sdr;
//        GridView1.DataBind();
//    }
//}
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}

while redirecting the page after insertion Add the code at Page Load of the grid then the grid will display no of records as you inserted from insertion form.

[jon4321]

You're telling me the ExecuteNonQuery() only runs once, but two records are inserted?
Do you have any insert triggers set up on the table?

Thanks, I figured out that the ExecuteScalar() method was inserting in addition to ExecuteNonQuery(). I was thinking it would just return a value, not insert as well.

private void showTrainGrid()
    {
        string scon = connect.GetConnection();
        SqlConnection conn = new SqlConnection(scon);
        conn.Open();
        string qry = "select * from Train";
        SqlCommand cmd = new SqlCommand(qry, conn);
        //SqlDataReader sdr = cmd.ExecuteReader();
        //if (sdr.HasRows)
        //{
        //    while (sdr.Read())
        //    {
        //        GridView1.DataSource = sdr;
        //        GridView1.DataBind();
        //    }
        //}
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        sda.Fill(ds);
         GridView1.DataSource = ds;
        GridView1.DataBind();
}

while redirecting the page after insertion Add the code at Page Load of the grid then the grid will display no of records as you inserted from insertion form.

Thank you. I'm actually using C#.net and figured out how to get sqldatasource to accept session variables as arguments. I'll be passing in each form identifier so the user sees their specific data. If this sounds like a bad plan, please let me know.