Database not storing information properly, stores info on different rows

Question

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

Quick check - do you want to create a record for a user on the first page, and update the row for that user on the second page? — shree.pat18, Apr 26 '14 at 19:29
@shree.pat18 yes, exactly, but this information updates on the row underneath and generates a new id, i don't know how to fix this — crsMC, Apr 26 '14 at 19:30
In that case, you need to use an `UPDATE` query on your second page. You need to see which of the columns can be used to identify the record to be updated, pass that value between the pages, and add a `WHERE` clause in your update query to update only that record. — shree.pat18, Apr 26 '14 at 19:32
I understand this but what I dont understand is the WHERE clause because it should know where it's been updated because i'm coding it on the page where its been inputted? — crsMC, Apr 26 '14 at 19:36
That's noT how SQL statements work. They're all independent of each other. They have no memory, each SQL statement should have its own "where" statement. They don't get reused. — mason, Apr 26 '14 at 20:32

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

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);

edited May 23 '17 at 12:28

Community

1
1

answered Apr 26 '14 at 19:31

NDJ

5,189
1
18
27

`c.CommandText = "INSERT INTO [User] (Ans1) OUTPUT INSERTED.ID VALUES(@Ans1)";` This is what I have now? – crsMC Apr 26 '14 at 19:40
Still not working, I don't think i'm doing it right. COuld you so me? – crsMC Apr 26 '14 at 19:42
I have this now c.CommandText = "UPDATE [User] SET [Ans1] = @Ans1"; and it updates the whole column to the selected ans1 – crsMC Apr 26 '14 at 19:48
that's because you need the where clause - to say only update where the ID = xx – NDJ Apr 26 '14 at 19:49
Ok, I get what your saying, but I want this to update automatically as the user selects it, i can't just let the Id = 1 and then the next peson could be 2 and this wont update? – crsMC Apr 26 '14 at 19:53
cmd.CommandText = "UPDATE Region SET [RegionDescription] = @RegionDescription WHERE [RegionID] = @RegionID"; cmd.Parameters.AddWithValue("@RegionDescription", "East"); cmd.Parameters.AddWithValue("@RegionID", "1"); – crsMC Apr 26 '14 at 19:56
I understand this, but i don't always want the id to be 1, like i want to update automatically – crsMC Apr 26 '14 at 19:57
it won't always be 1 - it will be whichever ID you pass through from the first insert - you insert the row (say id 10) on the first page, and each subsequent page updates that row with the new infomrmation like Ans1 – NDJ Apr 26 '14 at 20:01
Thanks for the help man, I understand what your saying now, but still don't know how to bring this onto the next page and use it, thanks though. Appreciate it! – crsMC Apr 26 '14 at 20:04
Thanks for the answer @NGJ . I'm getting the error int userID = c.ExecuteScalar(); "Cannot convert from object to int? – crsMC Apr 26 '14 at 20:15
I keep getting userID does not exist in the current context – crsMC Apr 26 '14 at 20:30
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/51510/discussion-between-lastkingiskingin-and-ndj) – crsMC Apr 26 '14 at 20:37

Database not storing information properly, stores info on different rows

1 Answers1