CODEWITHSUNDEEP
c#asp.netsqlsql-serverdatabase
cala
cala

Reputation: 1421

Database not storing information properly, stores info on different rows

The database is not storing information all on the same row. On the first page, when I click the button it records it and that's fine, it's stored. Then on the next page, when i click the button, it stores the information, but on a different row? Any solutions? Heres the problem, and code below.

enter image description here

PAGE 1

public void addInformationToDatabase()
    {
        string Sex = ddlGender.Text;
        string Name = tbxName.Text;
        string DOB = tbxDOB.Text;

        string connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        SqlConnection Con = new SqlConnection(connectionString);

        SqlCommand command = new SqlCommand();
        command.Connection = Con;
        command.CommandType = CommandType.Text;
        command.CommandText = "INSERT INTO [User] (GenderID,Name,DOB) VALUES(@Sex,@Name,@DOB)";

        command.Parameters.AddWithValue("@Sex", Sex);
        command.Parameters.AddWithValue("@Name", Name);
        command.Parameters.AddWithValue("@DOB", DOB);

        try
        {
            Con.Open();
            command.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            Con.Close();
        }
    }

2ND PAGE

public void save()
    {

        string checkboxSelection = CheckBoxList1.SelectedItem.ToString();

        string connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        SqlConnection Con = new SqlConnection(connectionString);

        SqlCommand c = new SqlCommand();
        c.Connection = Con;
        c.CommandType = CommandType.Text;
        c.CommandText = "INSERT INTO [User] (Ans1) VALUES(@Ans1)";

        c.Parameters.AddWithValue("@Ans1", checkboxSelection);

        try
        {
            Con.Open();
            c.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            Con.Close(); 
        }
    }

Any help appreciated

Upvotes: 1

Views: 56

Answers (1)

NDJ
NDJ

Reputation: 5194

your first page needs to get the ID back following the insert and then your second page needs to do an update based on that ID, not a subsequent insert.

There are a lot of resources about getting ids back - e.g How to get last inserted id?

(I'm assuming the id field uniquely identifies your row)

first query - 

 c.CommandText = "INSERT INTO [User] (Ans1) VALUES(@Ans1); SELECT SCOPE_IDENTITY()";
 ...
  int userID = (Int32) c.ExecuteScalar();

you'll need to pass that ID to your 2nd page and change the insert to be an update:

"UPDATE User] SET Ans1 = @Ans1 WHERE Id = @id";

you'll also need to add the id as a parameter 

c.Parameters.AddWithValue("@id", userID);

Upvotes: 1

Related Questions