Problem with query string and extract values

Question

I can't extract the values through a query and insert them into textboxes

Where am I going wrong?

 Request.QueryString.Get("ID_Persona");

 string query = "SELECT ID,Nome,Cognome,Email,CodiceFiscale FROM Persona WHERE ID = @id";

 using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString))
       {         
           SqlCommand cmd = new SqlCommand(query, con);

           cmd.Parameters.AddWithValue("@ID","");
           cmd.Parameters.AddWithValue("@Nome", TextBox1.Text);
           cmd.Parameters.AddWithValue("@Cognome", TextBox15.Text);
           cmd.Parameters.AddWithValue("@Email", TextBox20.Text);
           cmd.Parameters.AddWithValue("@CodiceFiscale", TextBox22.Text);

           con.Open();

           cmd.ExecuteNonQuery();
       }

what is the behavior you are experiencing now? Does it retrieve the the columns you want? you just need to assign the values to the textboxes now? — JohnChris, Jun 14 '19 at 07:15
I need to enter the database values in these texboxes. I identify the line with the id — Elmachico, Jun 14 '19 at 07:16
You used `ExecuteNonQuery`. That doesn't return any data. Use ExecuteReader instead, or a microORM like Dapper to make the code simpler — Panagiotis Kanavos, Jun 14 '19 at 07:18
There is no point passing a `@Nome` parameter that isn't being used. — mjwills, Jun 14 '19 at 07:33
`cmd.Parameters.AddWithValue("@ID","");` I doubt if query return anything for **empty** `ID` — Dmitry Bychenko, Jun 14 '19 at 07:34
@Elmachico please try the updated answer, was probably an error that I was using while for reading multiple records — JohnChris, Jun 14 '19 at 07:38

JohnChris · Accepted Answer · 2019-06-14T07:43:39.193

3

You need to use ExecuteReader to read values, something like this:

var connectionString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
string query = "SELECT ID,Nome,Cognome,Email,CodiceFiscale FROM Persona WHERE ID = @id";
using (SqlConnection con = new SqlConnection(connectionString))
{
    using (var cmd = new SqlCommand(query, con))
    {
      cmd.Parameters.AddWithValue("@ID", Request.QueryString.Get("ID_Persona"));
      con.Open();
      using (var rdr = cmd.ExecuteReader())
      {
          if (rdr.Read())
          {
              //IDTextBox? = rdr["Id"].ToString(),
              TextBox1.Text = rdr["Nome"].ToString(),
              TextBox15.Text = rdr["Cognome"].ToString(),
              TextBox20.Text= rdr["Email"].ToString(),
              TextBox22.Text= rdr["CodiceFiscale"].ToString(),    
          }
      }
   }
}

edited Jun 14 '19 at 07:43

answered Jun 14 '19 at 07:21

JohnChris

1,360
15
29

can you `debug` and see what part is not working? is an exception being thrown? – JohnChris Jun 14 '19 at 07:30
No it does not throw any exception, now I tell you in the debug what I see – Elmachico Jun 14 '19 at 07:32
`if (rdr.Read())` instead of `while (rdr.Read())` since you want to read only one record – Dmitry Bychenko Jun 14 '19 at 07:34
the id is taken from a listview where there is the change key and I pass that in a quey string – Elmachico Jun 14 '19 at 07:39
yep as others have said, make sure you are passing in an ID as the question has an empty string, I thought you did that on purpose, but maybe not – JohnChris Jun 14 '19 at 07:41
JohnChris Thank you so much your method works, you gave me a big hand, I also thank all the others – Elmachico Jun 14 '19 at 07:51
@Elmachico was a team effort indeed, no problem – JohnChris Jun 14 '19 at 07:53
1

@JohnChris to the next friend, I will not easily forget you! – Elmachico Jun 14 '19 at 07:57

score 2 · Answer 2 · answered Jun 14 '19 at 07:21

You should use a ExecuteReader() instead of ExecuteNonQuery() since ExecuteNonQuery is meant for DML operations. Again, you need only the ID value to be passed then why you are passing unnecessary parameters to your query. Remove them all. An example below

    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine(String.Format("{0}", reader["Email"]));
    }

Dmitry Bychenko · Answer 3 · 2019-06-14T08:00:49.727

I can see several issues:

You should use ExecuteReader() instead of ExecuteNonQuery()
You should provide just 1 parameter - @ID; I doubt if it should have an empty value.
You should wrap IDisposable into using

Code:

   string query = 
     @"SELECT ID,
              Nome,
              Cognome,
              Email,
              CodiceFiscale 
         FROM Persona 
        WHERE ID = @id";

   using (SqlConnection con = new SqlConnection(...))
   {         
       con.Open();

       using SqlCommand cmd = new SqlCommand(query, con) 
       {
           // I doubt if you want empty Id here. 
           // I've assumed you want to pass ID_Persona
           cmd.Parameters.AddWithValue("@ID", Request.QueryString.Get("ID_Persona")); 

           using (var reader = cmd.ExecuteReader())
           {
               if (reader.Read()) 
               {
                   TextBox1.Text  = Convert.ToString(reader["Nome"]); 
                   TextBox15.Text = Convert.ToString(reader["Cognome"]); 
                   TextBox20.Text = Convert.ToString(reader["Email"]); 
                   TextBox22.Text = Convert.ToString(reader["CodiceFiscale"]);   
               }
           }
       }
  }

Problem with query string and extract values

3 Answers3