none
C# window form app and SQL RRS feed

  • Question

  • I am having an issue with a sql query when using C# Windows Form App

    string CB = "Car193";

    CB = textBox1.Text;

    using (SqlCommand cmd = new SqlCommand("select RaceNumber FROM [blue].[yellow].[green] where [RacetNumber] in ('CB')", con))

    This will just bring up the Race Number but not the (CAR193)

    What am I doing wrong?

    Tuesday, February 5, 2019 2:29 PM

All replies

  • In your SELECT statement you're just selecting RaceNumber from the list of columns in your table. If you want to select other columns then add them to the list.

    SELECT RaceNumber, Column2, Column3 FROM table WHERE condition

    Test your queries in SSMS before trying to use them in code to ensure you're getting the data you want. 

    Also your WHERE condition doesn't look right to me. You're looking for RacetNumber that is equal to CB. You're actually doing nothing with the CB variable you defined. Use parameterized queries to insert data into a query.

    using (var cmd = new SqlCommand("SELECT RaceNumber FROM table WHERE RaceNumber = @number", con))
    {
       cmd.Parameters.AddWithValue("@number", CB);
    };
    This will select rows where RaceNumber is equal to whatever value is in your CB variable.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, February 5, 2019 2:57 PM
    Moderator
  • Hello,

    When first starting out with SQL, as CoolDadTx mentioned, write your statements in SSMS

    Example table

    I want all customers in Germany

    SELECT CustomerIdentifier , CompanyName , ContactName , ContactTitle , City , PostalCode , Country 
    FROM   dbo.Customer
    WHERE  Country = 'Germany'

    We find it works so next step is to create a parameter.

    DECLARE @Country AS NVARCHAR(15) = 'Germany'
    SELECT CustomerIdentifier , CompanyName , ContactName , ContactTitle , City , PostalCode , Country 
    FROM   dbo.Customer
    WHERE  Country = @Country

    The parameter then is used as in CoolDadTx example AddWithValue.

    If we want to in this case exclude Germany

    DECLARE @Country AS NVARCHAR(15) = 'Germany'
    SELECT CustomerIdentifier , CompanyName , ContactName , ContactTitle , City , PostalCode , Country 
    FROM   dbo.Customer
    WHERE  Country <> @Country

    It can get a little interesting going from WHERE IN from SQL to C#.

    SELECT CustomerIdentifier , CompanyName , ContactName , ContactTitle , City , PostalCode 
    FROM   dbo.Customer
    WHERE  Country IN ( 'Germany', 'Mexico' );
    

    I have a method for this in the following TechNet Wiki.

    In closing best to start off very simple, run through the lessons here.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, February 5, 2019 4:02 PM
    Moderator
  • Hi,

    We could use the following to query the result with when in clause with parameters.

    using (SqlConnection con = new SqlConnection(connstr))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        string CB = "a,b";
                        cmd.Connection = con;
                        List<string> list = new List<string>();
                        int i = 1;
                        foreach (var item in CB.Split(','))
                        {
                            // IN clause
                            list.Add("@RacetNumber" + i.ToString());
    
                            // parameter
                            cmd.Parameters.AddWithValue("@RacetNumber" + i.ToString(), item);
    
                            i++;
                        }
                        string sqlString = string.Format("select RaceNumber FROM [blue].[yellow].[green] where [RacetNumber] in ({0})",string.Join(",", list));
                        cmd.CommandText = sqlString;
                        con.Open();
                        SqlDataReader sdr = cmd.ExecuteReader();
                        if (sdr.HasRows)
                        {
                            while (sdr.Read())
                            {
                                Console.WriteLine(sdr.GetString(0));
                            }
                        }
                    }


    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Stanly Fan Wednesday, February 13, 2019 2:37 AM
    Wednesday, February 6, 2019 1:54 AM
    Moderator