WPF insert to database executes but no data added-CodePudding

I have a small table (tbl_user_favs) that is meant to store user favorites. Column 1 is the user ID, and then every column after that is a slot for a selected favorite PC to be stored, denoted Slot1, Slot2.

                using (SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\UserFavorites.mdf;Initial Catalog=tbl_user_favs;Integrated Security=True;Connect Timeout=30"))
                {
                    string cmdString = ("SELECT * FROM tbl_user_favs WHERE UserID = '"   Globals.emailUID   "'");
                    SqlCommand cmd = new SqlCommand(cmdString, con);

                    cmd.Parameters.Add("@Slot1", SqlDbType.VarChar).Value = PCnum;
                    

                    DataSet loginCredentials = new DataSet();
                    SqlDataAdapter dataAdapter;

                    con.Open();

                    //dataAdapter = new SqlDataAdapter(cmdString, con);
                    dataAdapter = new SqlDataAdapter(cmd);
                    dataAdapter.Fill(loginCredentials);
                    //cmd.ExecuteNonQuery();

                    con.Close();
                }

Code executes, but it does not add the PCnum to the database next to the UserID. It should lok through the rows, find the UserID that matches the logged in user, Globals.emailUId, and add the PCnum to an open slot. Without worrying yet how to dynamically increment the Slots, why isn't this insert adding the PCnum to Slot 1? I've seen some tutorials use ExecuteNonQuery, and some use the dataAdapter, but both have the same result. I suspect there is something off with my SQL? Thank you

CodePudding user response：

There are a couple things going on here.

First is that you are using "Parameters" incorrectly. It's supposed to add data to your query, not data to the database/row/column after a query has been made.

sql parameterized query in C# with string

Second, you are doing a select query, so you are only getting data from the db, not putting data into it.

To do what you want, you'd need to do this instead: (I don't have a good way to test this, so it may need tweaks, but it should be close.)

using (SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\UserFavorites.mdf;Initial Catalog=tbl_user_favs;Integrated Security=True;Connect Timeout=30"))
{
  string cmdString = ("UPDATE tbl_user_favs SET Slot1 = @Slot1 WHERE UserID = @EmailUID");
  SqlCommand cmd = new SqlCommand(cmdString, con);

  cmd.Parameters.AddWithValue("@Slot1", PCnum);
  cmd.Parameters.AddWithValue("@EmailUID", Globals.emailUID);
  con.Open();
  cmd.ExecuteNonQuery();
  con.Close();
}

You no longer need the DataSet or the SqlDataAdapter.

Since you seem to be confused on what parameterization is and why to use it, check out this question, too.

And here's just some more reading on the topic in general. I used these articles as resources for this answer:
https://visualstudiomagazine.com/articles/2017/07/01/parameterized-queries.aspx

https://www.c-sharpcorner.com/UploadFile/a20beb/why-should-always-use-the-parameterized-query-to-avoid-sql-i/