none
C# 3.0 - Retrieve data from MS SQL Server using parameterized values

    Question

  • Hi guys,

     

    I am using the following block of code to retrieve some data from MS SQL Server.

    I am having problems with the highlighted (yellow) part of the code.

    Thanks in advance for any help.

    Aldo.

     

    The original SQL string (from resources file) is:

    mySqlCommand= SELECT {0} FROM {1} WHERE {2}

     

    I need to change it to:

    SELECT colName FROM ACCOUNTS WHERE ACCOUNTS.ACCOUNTKEY Between X AND Y

    Notes: colName equals to the string "FULLNAME".

           I am gonna read X and Y from textBoxes, so I need to parameterized them.

     

    Code Snippet

    public static IList<string> QueryColumnDataToList(ResourceManager rm, string colName)

            {

                IList<string> myList = new List<string>();

                string sqlString = string.Format(CultureInfo.CurrentCulture, rm.GetString("mySqlCommand"), colName, "ACCOUNTS", "ACCOUNTS.ACCOUNTKEY Between ? AND ?");

     

                try

                {

                    using (SqlConnection myConnection = new SqlConnection(rm.GetString("mySqlConnection")))

                    {

                        using (SqlCommand sqlComm = new SqlCommand(sqlString, myConnection))

                        {

                            sqlComm.CommandType = CommandType.Text;

                            sqlComm.Parameters.AddWithValue("X", "'3020'");

                            sqlComm.Parameters.AddWithValue("Y", "'3020'");

     

                            // Checking here I see the sql string still has X and Y instead of the new values

     

                            myConnection.Open();

                            SqlDataReader myDataReader = sqlComm.ExecuteReader();

                            while (myDataReader.Read())

                            {

                                myList.Add(myDataReader[1].ToString());

                            }

                            myConnection.Close();

                        }

                    }

                }

                catch (Exception ex)

                {

                    MessageBox.Show("Error: " + ex.Message, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    Application.Exit(); // finish the program

                }

                return myList;

            }

    Monday, May 19, 2008 1:48 PM

Answers

  • This is more of a SQL Question than a C# question, but what you really want to do is change this:

     

    SELECT colName FROM ACCOUNTS WHERE ACCOUNTS.ACCOUNTKEY Between X AND Y

     

    To this:

     

    SELECT colName FROM ACCOUNTS WHERE ACCOUNTS.ACCOUNTKEY Between @X AND @Y

     

    (not the @ sign)

     

    And then when you add the parameters, there is no need to use the quotes at all, as I'm assuming AccountKey is some kind of numeric value (int, bigint, tinyint, etc).

     

    sqlComm.Parameters.AddWithValue("@X", 3020);

    sqlComm.Parameters.AddWithValue("@Y", 3020);

     

    That should do it for you, and the selection should work properly.

    Monday, May 19, 2008 1:54 PM

All replies

  • This is more of a SQL Question than a C# question, but what you really want to do is change this:

     

    SELECT colName FROM ACCOUNTS WHERE ACCOUNTS.ACCOUNTKEY Between X AND Y

     

    To this:

     

    SELECT colName FROM ACCOUNTS WHERE ACCOUNTS.ACCOUNTKEY Between @X AND @Y

     

    (not the @ sign)

     

    And then when you add the parameters, there is no need to use the quotes at all, as I'm assuming AccountKey is some kind of numeric value (int, bigint, tinyint, etc).

     

    sqlComm.Parameters.AddWithValue("@X", 3020);

    sqlComm.Parameters.AddWithValue("@Y", 3020);

     

    That should do it for you, and the selection should work properly.

    Monday, May 19, 2008 1:54 PM
  • Hi David,

     

    I made the changes, but it still doesn't work.

     

    Code Snippet

    public static IList<string> QueryColumnDataToList(ResourceManager rm, string colName)

            {

                IList<string> myList = new List<string>();

                string sqlString = string.Format(CultureInfo.CurrentCulture, rm.GetString("mySqlCommand"), colName, "ACCOUNTS", "ACCOUNTS.ACCOUNTKEY Between @X AND @Y");

     

                try

                {

                    using (SqlConnection myConnection = new SqlConnection(rm.GetString("mySqlConnection")))

                    {

                        using (SqlCommand sqlComm = new SqlCommand(sqlString, myConnection))

                        {

                            sqlComm.CommandType = CommandType.Text;

                            sqlComm.Parameters.AddWithValue("@X", "'3020'");

                            sqlComm.Parameters.AddWithValue("@Y", "'3020'");

     

                            myConnection.Open();

                            SqlDataReader myDataReader = sqlComm.ExecuteReader();

                            while (myDataReader.Read())

                            {

                                myList.Add(myDataReader[1].ToString());

                            }

                            myConnection.Close();

                        }

                    }

                }

                catch (Exception ex)

                {

                    MessageBox.Show("Error: " + ex.Message, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    Application.Exit(); // finish the program

                }

                return myList;

            }

     

     

     

    Monday, May 19, 2008 3:49 PM
  • You didn't make all the changes, take all quotes off 3020.

     

    Monday, May 19, 2008 4:00 PM
  • Actually I also tried that unsuccesfully

     

    Monday, May 19, 2008 4:18 PM
  • I am having problems with the highlighted (yellow) part of the code.

     

    A reasonable question is: what is the problem? Is it that the values are not assigned?

     

    You should change your catch block. You should Console ex.Message & ex.StackTrace. It is all you need.

     

    Just looking at your code I see a problem. You do not tell Sql Server what parameter type you are passing. I've never used AddWithValue but I assume it cannot figure out the default. It just cannot be the case.

     

    Also you do not define the length of the parameter. It is important in string values.

     

    I am giving you a live sample of OleDb code that should fit your needs. All you need is to change things syntactically. I use it daily. If it is still not enough, I will pull a Sql Server code for that.

     

    public void insertIntoSearchGroupsTable ( string named )

    {

    using ( OleDbConnection conn = new OleDbConnection ( Globals.connStr ) )

    {

    conn.Open ( );

    OleDbCommand cmdm = new OleDbCommand ( );

    cmdm.Connection = conn;

    cmdm.CommandType = CommandType.Text;

    cmdm.CommandText = "INSERT INTO searchGroups (named, memo1) VALUES ( ?, ? ) ";

    cmdm.Parameters.Clear ( );

    cmdm = SetControls.createOleDbParameter ( cmdm, "named", named );

    cmdm = SetControls.createOleDbParameter ( cmdm, "memo1", "" );

    try

    {

    cmdm.ExecuteNonQuery ( );

    }

    catch (Exception ex)

    {

    Console.WriteLine ( " {0} \r\n {1}", ex.Message, ex.StackTrace );

    }

    }

    } // insertIntoSearchGroupsTable

     

    // this is a separate routine that completely assures that the parameters are set properly. It defines the type, length and values based on input call parameter string. If you need to pass a different parameter type cast, you should write an oveload

     

    public static OleDbCommand createOleDbParameter ( OleDbCommand cmdm, string parName, string parValue )

    { // overload: type string

    OleDbParameter par2 = cmdm.CreateParameter ( );

    par2.SourceVersion = DataRowVersion.Proposed;

    par2.ParameterName = parName;

    par2.DbType = DbType.String;

    par2.Direction = ParameterDirection.InputOutput;

    par2.SourceColumn = parName;

    par2.Value = parValue;

    par2.Size = parValue.Length;

    cmdm.Parameters.Add ( par2 );

    return cmdm;

    } // createOleDbParameter

     

    Also in your code I do not see the Sql Query forming. What do you want to do? SELECT, INSERT, or something else?

     

    Just on the side:

     

    using (SqlCommand sqlComm = is not really necessery. Once you closed the connection your sqlCommand is gone too.

    Monday, May 19, 2008 4:58 PM
  • Hi guys,

     

    The block below works nice.

    I only made the changes as you suggested.

    It didn't work the first time because of remote connection problems with SQL Server.

    Thanks a lot!

    Aldo.

     

    public static IList<string> QueryColumnDataToList(ResourceManager rm, string colName)

            {

                IList<string> myList = new List<string>();

                string sqlString = string.Format(CultureInfo.CurrentCulture, rm.GetString("mySqlCommand"), colName, "ACCOUNTS", "ACCOUNTS.SORTGROUP Between @X AND @Y");

     

                try

                {

                    using (SqlConnection myConnection = new SqlConnection(rm.GetString("mySqlConnection")))

                    {

                        using (SqlCommand sqlComm = new SqlCommand(sqlString, myConnection))

                        {

                            sqlComm.CommandType = CommandType.Text;

                            sqlComm.Parameters.AddWithValue("@X", 3020);

                            sqlComm.Parameters.AddWithValue("@Y", 3999);

     

                            myConnection.Open();

                            SqlDataReader myDataReader = sqlComm.ExecuteReader();

                            while (myDataReader.Read())

                            {

                                // 0 is the index for the first column, (in this case the only one).

                                // Another possibility is:

                                // myList.Add(myDataReader[colName].ToString());

                                myList.Add(myDataReader[0].ToString());

                            }

                            myConnection.Close();

                        }

                    }

                }

                catch (Exception ex)

                {

                    MessageBox.Show("Error: " + ex.Message, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    Application.Exit(); // finish the program

                }

                return myList;

            }

     

    Tuesday, May 20, 2008 4:37 AM