How to Store data from database table in multiple variables?

Question

What would be the correct syntax for that? My trial:

public void CalculateFRDBToks(TestBLL testToks)
{
    try
    {
        con.Open();
        cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = ("Select @Freq12, @Freq20, @Freq9, @Freq11 FROM TOKS_Test");
        cmd.Parameters.AddWithValue("@Freq12", testToks.freq12);//<--variable
        cmd.Parameters.AddWithValue("@Freq20", testToks.freq20);<--variable
        cmd.Parameters.AddWithValue("@Freq9", testToks.freq11);<--variable
        cmd.Parameters.AddWithValue("@Freq11", testToks.freq9);<--variable
        SqlDataReader dr = cmd.ExecuteReader();
        cmd.ExecuteNonQuery();
        con.Close();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Gives Object reference not set to an instance of an object.

possible duplicate of [What is a NullReferenceException and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Iain Galloway, Feb 27 '15 at 15:17
Although it is a NRE, but the issue is wrong usage of SQL and parameters. — Habib, Feb 27 '15 at 15:19
What line is actually throwing the error? Where is con instantiated? You are putting parameters in the select portion of the sql statement, so at best you will simply get the values back that you put in. It will not store it in the db. I'm sure there are other issues... — Jeremy, Feb 27 '15 at 15:22

score 0 · Answer 1 · edited May 23 '17 at 11:52

Although it is a NRE, but the issue is wrong usage of SQL and parameters.

Parameters are used when you have to pass something to the query, for example if you want to search a specific column against a value from your code then pass parameters and use it in WHERE clause.

In your code it appears you are only selecting certain columns from your table without any condition. You have to specify column name.

Like:

cmd.CommandText = ("Select ID, Name, Col3, Col4 FROM TOKS_Test");

Few other things in your code:

You haven't attached a connection object with your command object
Enclose your SqlConnection and SqlCommand object in using statement
Use ExecuteReader as you are going to get/expect multiple values from your query.

Something like:

public void CalculateFRDBToks(TestBLL testToks)
{
    try
    {
        using (SqlConnection con = new SqlConnection("Connection String"))
        using (SqlCommand cmd = new SqlCommand("SELECT col1, col2, col3 FROM TOKS_TEST", con))
        {
            con.Open();
            cmd.CommandType = CommandType.Text;
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                Console.WriteLine("Col1 {0}, Col2 {1}, Col3 {2}", dr["Col1"], dr["Col2"], dr["Col3"]);
                //or populate a List of your object based on values
            }
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

See this question for SqlParameter: Why do we always prefer using parameters in SQL statements?

Thank you for your reply, but it still doesn't explain how to store retrieved data in a variable. — Ženia Bogdasic, Feb 27 '15 at 15:33
@CookieDemon, Create a class with properties you expect to have based on your query. Create a `List` and add items to it. — Habib, Feb 27 '15 at 15:34
@CookieDemon, sure you can use *variables*, but if you are expecting multiple values /rows, you can't keep all the values in a single set of variables. — Habib, Feb 27 '15 at 15:38

How to Store data from database table in multiple variables?

1 Answers1