locked
Combobox with ' in name causes error on save RRS feed

  • Question

  • I have created a combobox named Dispatcher and linked it to a sql database.  One of the names in the sql database that is linked to the combobox named Dispatcher is "D'Ambrosio, James S".  When I select that name from the combobox dropdown list and hit the save button I get the error " System.DataSql.Client.SqlException: 'Incorrect syntax near 'Ambrosio'.

    Below is my code for the save button.  How can I make it so that I can use the name as it appears in the combobox dropdown?

    private void Savebtn_Click(object sender, EventArgs e)
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("Insert into Overnight_Parking(Callers_Name,Address,Start_Date,End_Date,Callers_Phone,City,Reason,Description,Dispatcher) values ('" + Callers_Name.Text + "','" + Address.Text + "','" + Start_Date.Value.ToString("yyyy-MM-dd") + "', '" + End_Date.Value.ToString("yyyy-MM-dd") + "','" + Callers_Phone.Text + "','" + City.Text +"','" + Reason.Text + "','" + Description.Text + "','" + Dispatcher.Text + "')",con);
                cmd.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("Data Saved Successfully");
    
                //textbox clear code
                Callers_Name.Text = "";
                Callers_Phone.Text = "";
                City.Text = "";
                Address.Text = "";
                Reason.Text = "";
                Description.Text = "";
            }



    • Edited by wgraulich Thursday, July 2, 2020 12:19 PM
    Thursday, July 2, 2020 12:09 PM

Answers

  • The first two are now the password can't be hidden with ***************.  I have to type in the password to get a conntections.

    Second I get error "System.Argument Exception: "Format of the initialization string does not conform to specification starting at index 0."  And is highlighting the below line on the error:

    (var cn = new SqlConnection(@"Data Source=UCPDAPPS2;Initial Catalog=OnCallWeb;Persist Security Info=True;User ID=sa;Password=Propaganda9") { ConnectionString = "TODO" })

    To start with, I already told you to remove the following which is invalid e.g. TODO means to place your connection string there.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by wgraulich Monday, July 6, 2020 6:00 PM
    Monday, July 6, 2020 4:56 PM
  • You forgot to do 

    cmd.Parameters.AddWithValue("@ID", TODO);

    Where TODO is the value for @ID


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by wgraulich Monday, July 6, 2020 8:19 PM
    Monday, July 6, 2020 7:10 PM
  • Hi, 

    First of all, I don't like the AddWithValue method, I always prefer to use more verbose Add method where you can specify type of the parameter.

    In your case if you want to find dates where either start date is equal the date you types or end date is equal that date you would use condition

    where .. and (Start_Date = @Date or End_Date = @Date)

    and your 

    cmd.Parameters.Add("@Date", SqlDbType.Date).Value  = StartDate.Text;

    If this would not work, you may need to try

    DateTime.Parse(StartDate.Text); -- I'm writing from the top of my head, so you may need to make sure to use the correct syntax, but that's the main idea.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Monday, July 6, 2020 10:02 PM
    • Marked as answer by wgraulich Monday, July 6, 2020 10:12 PM
    Monday, July 6, 2020 10:02 PM
  • If you would not be able to figure this out by yourself, just tell, I'll fix the posted code, but I do hope you can fix it yourself if you read it carefully and also try to apply all the suggestions from this thread.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by wgraulich Wednesday, July 8, 2020 6:15 PM
    Wednesday, July 8, 2020 4:43 PM
  • I have tried everything everyone has posted.  My script I am showing is the only thing I can get close to working.  As mentioned before I have stopped trying to fix the issue and am working on coming up with another option.  Again, thank you all for trying!
    • Marked as answer by wgraulich Wednesday, July 8, 2020 6:15 PM
    Wednesday, July 8, 2020 6:15 PM

All replies

  • Hello,

    You need to use parameters as they will handle apostrophes for you. Here is enough to get you going that if you have a auto incrementing primary key the new key is returned

    using (var cn = new SqlConnection() {ConnectionString = "TODO"})
    {
        using (var cmd = new SqlCommand() {Connection = cn})
        {
    
            cmd.CommandText = "Insert into Overnight_Parking(Callers_Name) values (@Callers_Name);" + 
                              "SELECT CAST(scope_identity() AS int);";
    
            cmd.Parameters.AddWithValue("@Callers_Name", Callers_Name.Text);
            cn.Open();
    
            var newIdentifier = (int)cmd.ExecuteScalar();
        }
    }

    If you don't have a auto incrementing primary key or don't care about the new key use this instead.

    using (var cn = new SqlConnection() {ConnectionString = "TODO"})
    {
        using (var cmd = new SqlCommand() {Connection = cn})
        {
    
            cmd.CommandText = "Insert into Overnight_Parking(Callers_Name) values (@Callers_Name)";
    
            cmd.Parameters.AddWithValue("@Callers_Name", Callers_Name.Text);
            cn.Open();
    
            cmd.ExecuteNonQuery();
        }
    }


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Proposed as answer by CoolDadTx Thursday, July 2, 2020 3:20 PM
    Thursday, July 2, 2020 12:25 PM
  • Thanks for fast reply!

    So I am a newbie so I apologize if this is a dump question, but am I adding this to the combox or the save button?  

    Thursday, July 2, 2020 12:32 PM
  • Thanks for fast reply!

    So I am a newbie so I apologize if this is a dump question, but am I adding this to the combox or the save button?  

    Seems that you would want to place it exactly where your existing code is, Savebtn_Click event.

    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Proposed as answer by Naomi N Monday, July 6, 2020 5:57 PM
    Thursday, July 2, 2020 12:53 PM
  • I made the change you suggested but get a new error "System.ArgumentException:"Format of the initialization string does not conform to specifications starting at index 0"

    Could it be because I have the SqlConnection setup before the start button.

    Public partial class Form1 : Form

    SqlConnection con = new SqlConnection(@"Data Source=UCPDAPPS2;Initial Catalog=OnCallWeb;Persist Security Info=True;User ID=sa;Password=*********");

    private void Savebtn_Click(object sender, EventArgs e)
    
            {
                using (var cn = new SqlConnection(@"Data Source=UCPDAPPS2;Initial Catalog=OnCallWeb;Persist Security Info=True;User ID=sa;Password=*************") { ConnectionString = "TODO" })
                {
                    using (var cmd = new SqlCommand() { Connection = cn })
                    {
    
                        cmd.CommandText = "Insert into Overnight_Parking(Dispatcher) values (@Dispatcher)";
    
                        cmd.Parameters.AddWithValue("@Dispatcher", Dispatcher.Text);
                        cn.Open();
    
                        cmd.ExecuteNonQuery();
                    }
                }
    
                //textbox clear code
                Callers_Name.Text = "";
                Callers_Phone.Text = "";
                City.Text = "";
                Address.Text = "";
                Reason.Text = "";
                Description.Text = "";
            }



    • Edited by wgraulich Thursday, July 2, 2020 3:25 PM
    Thursday, July 2, 2020 3:14 PM
  • Hello,

    It appears you didn't review the code so instead you defined the connection string twice, while constructing it that's fine but the other part with TODO screwed it up.

    This is correct

    using (var cn = new SqlConnection() { ConnectionString = @"Data Source=UCPDAPPS2;Initial Catalog=OnCallWeb;Persist Security Info=True;User ID=sa;Password=*************" })


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, July 2, 2020 4:10 PM
  • That didn't work, I now get 14 new errors when I apply those changes.  Thanks for trying though!
    Monday, July 6, 2020 2:37 PM
  • That didn't work, I now get 14 new errors when I apply those changes.  Thanks for trying though!
    14 new errors ???

    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, July 6, 2020 4:23 PM
  • The first two are now the password can't be hidden with ***************.  I have to type in the password to get a conntections.

    Second I get error "System.Argument Exception: "Format of the initialization string does not conform to specification starting at index 0."  And is highlighting the below line on the error:

    (var cn = new SqlConnection(@"Data Source=UCPDAPPS2;Initial Catalog=OnCallWeb;Persist Security Info=True;User ID=sa;Password=Propaganda9") { ConnectionString = "TODO" })

    Monday, July 6, 2020 4:40 PM
  • The first two are now the password can't be hidden with ***************.  I have to type in the password to get a conntections.

    Second I get error "System.Argument Exception: "Format of the initialization string does not conform to specification starting at index 0."  And is highlighting the below line on the error:

    (var cn = new SqlConnection(@"Data Source=UCPDAPPS2;Initial Catalog=OnCallWeb;Persist Security Info=True;User ID=sa;Password=Propaganda9") { ConnectionString = "TODO" })

    To start with, I already told you to remove the following which is invalid e.g. TODO means to place your connection string there.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by wgraulich Monday, July 6, 2020 6:00 PM
    Monday, July 6, 2020 4:56 PM
  • By ConnectionString = 'TODO' Karen meant you need to specify that string. In your case it's going to be what you put starting with @"Data Source ..

    In other words, just put your string into 

    ConnectionString = @"Data Source=..

    and then use

    var cn = new SqlConnection(ConnectionString);

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, July 6, 2020 4:59 PM
  • ok, so now I understand and it is working perfectly.  Sorry about the confusion, like I said this is my first build so......

    Thanks for your help and patients!

    • Marked as answer by wgraulich Monday, July 6, 2020 5:51 PM
    • Unmarked as answer by wgraulich Monday, July 6, 2020 6:00 PM
    Monday, July 6, 2020 5:51 PM
  • Hi wgraulich,

    I think you should mark Karen's reply as an answer, not your own. 

    That's how it normally works here, you mark as answer the reply that helped you to solve the issue.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, July 6, 2020 5:57 PM
  • So when I made the change, I have to manually type in the password to get connection to sql database.  Shouldn't I be able to hide the password in the code?
    Monday, July 6, 2020 6:00 PM
  • What is the type of your project? Normally you put the connection string  (with user id and password) into one config file and then get it encrypted.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, July 6, 2020 6:07 PM
  • I am using Windows Form Application.  Also I ran into an error for my update data button after making the above changes.  Error "System.Data.SqlClient.SqlExeption: 'Must delcare the scalar variable "@ID"."

    private void Updatebtn_Click(object sender, EventArgs e)
            {
                using (var cn = new SqlConnection(@"Data Source=UCPDAPPS2;Initial Catalog=OnCallWeb;Persist Security Info=True;User ID=sa;Password=Propaganda9") { ConnectionString = @"Data Source=UCPDAPPS2;Initial Catalog=OnCallWeb;Persist Security Info=True;User ID=sa;Password=Propaganda9" })
                {
                    using (var cmd = new SqlCommand() { Connection = cn })
                    {
    
                        cmd.CommandText = "UPDATE Overnight_Parking SET Dispatcher=@Dispatcher, Start_Date=@Start_Date, End_Date=@End_Date, Callers_Name=@Callers_Name, Callers_Phone=@Callers_Phone, City=@City, Address=@Address, Reason=@Reason, Description=@Description where ID=@ID";
    
                        cmd.Parameters.AddWithValue("@Dispatcher", Dispatcher.Text);
                        cmd.Parameters.AddWithValue("@Start_Date", Start_Date.Text);
                        cmd.Parameters.AddWithValue("@End_Date", End_Date.Text);
                        cmd.Parameters.AddWithValue("@Callers_Name", Callers_Name.Text);
                        cmd.Parameters.AddWithValue("@Callers_Phone", Callers_Phone.Text);
                        cmd.Parameters.AddWithValue("@City", City.Text);
                        cmd.Parameters.AddWithValue("@Address", Address.Text);
                        cmd.Parameters.AddWithValue("@Reason", Reason.Text);
                        cmd.Parameters.AddWithValue("@Description", Description.Text);
                        cn.Open();
    
                        cmd.ExecuteNonQuery();
                        cn.Close();
                        MessageBox.Show("Data Saved Successfully");
                    }
                }
    
                //textbox clear code
                Callers_Name.Text = "";
                Callers_Phone.Text = "";
                City.Text = "";
                Address.Text = "";
                Reason.Text = "";
                Description.Text = "";
            }


    • Edited by wgraulich Monday, July 6, 2020 6:49 PM
    Monday, July 6, 2020 6:36 PM
  • You forgot to do 

    cmd.Parameters.AddWithValue("@ID", TODO);

    Where TODO is the value for @ID


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by wgraulich Monday, July 6, 2020 8:19 PM
    Monday, July 6, 2020 7:10 PM
  • In the future even when you are better versed at writing SQL, use SSMS (SQL-Server Manager Studio) to write SQL.

    Select the table to work on, right click, 

    You get something like this

    USE [ForumExample]
    GO
    
    UPDATE [dbo].[Person2]
       SET [FirstName] = <FirstName, nvarchar(max),>
          ,[LastName] = <LastName, nvarchar(max),>
     WHERE <Search Conditions,,>
    GO
    
    
    

    Create parameters, setup with mocked values, finish up the SQL, clean up and run. Does it work? Create in code a parameter for each declare and set values as you are now.

    DECLARE @FirstName NVARCHAR(20)= 'Karen';
    DECLARE @LastName NVARCHAR(20)= 'Payne';
    DECLARE @Id INT= 2;
    
    UPDATE dbo.Person2 SET FirstName = @FirstName, LastName = @LastName WHERE dbo.Person2.Id = @Id;


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, July 6, 2020 7:18 PM
  • Thank you for that.  Once last thing.  I now have to adjust my search function to include ' symbols.  I wrote the following but once again I screwed something up.
     private void Searchbtn_Click(object sender, EventArgs e)
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * from Overnight_Parking Where Dispatcher like @Dispatcher or Start_Date like @Start_Date or End_Date like @End_Date or Callers_Name lile @Callers_Name or Callers_Phone like @Callers_Phone or City like @City or Address like @Address or Reason like @Reason or Description like @Description";
    
                cmd.Parameters.AddWithValue("@Dispatcher", Dispatcher.Text);
                cmd.Parameters.AddWithValue("@Start_Date", Start_Date.Text);
                cmd.Parameters.AddWithValue("@End_Date", End_Date.Text);
                cmd.Parameters.AddWithValue("@Callers_Name", Callers_Name.Text);
                cmd.Parameters.AddWithValue("@Callers_Phone", Callers_Phone.Text);
                cmd.Parameters.AddWithValue("@City", City.Text);
                cmd.Parameters.AddWithValue("@Address", Address.Text);
                cmd.Parameters.AddWithValue("@Reason", Reason.Text);
                cmd.Parameters.AddWithValue("@Description", Description.Text);
                cmd.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                dataGridView1.DataSource = dt;
    
                con.Close();
            }
    

    Monday, July 6, 2020 8:18 PM
  • At least one misspelling which is why using SSMS will help.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, July 6, 2020 9:02 PM
  • yeh I saw that and forgot to change it in the reply.  I still get a blank return in the datagridview even with that change.
    Monday, July 6, 2020 9:05 PM
  • yeh I saw that and forgot to change it in the reply.  I still get a blank return in the datagridview even with that change.
    I highly recommend first writing your SQL in SSMS, pass in what you think should work via parameters rather than trying this in code. 

    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Proposed as answer by Naomi N Monday, July 6, 2020 9:37 PM
    Monday, July 6, 2020 9:13 PM
  • I figured it out.  The only thing I can't search on is the dates.  I am capturing the date using date time picker box using custom format of MM/dd/yyyy.  I entered data using a date of 7/7/2020, but when I do a search for 7/7 I get no return.  If I leave out the / character in the search I do get returns with every date with a 7 in it.  Any thoughts on how I can search with the / character in the search?
    private void Searchbtn_Click(object sender, EventArgs e)
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * from Overnight_Parking Where (Dispatcher like @Search or Start_Date like @Search or End_Date like @Search or Callers_Name like @Search) or Callers_Phone like @Search or City like @Search or Address like @Search or Reason like @Search or Description like @Search ";
    
                cmd.Parameters.AddWithValue("@Search", "%" + Search.Text + "%");
    
                cmd.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                dataGridView1.DataSource = dt;
    
                con.Close();
                //textbox clear code
                Search.Text = "";
    
            }

    Monday, July 6, 2020 9:35 PM
  • You don't use LIKE with the dates. With dates you normally use BETWEEN condition or equal condition. What are the Start_Date and End_Date types in the database and what exactly do you want to achieve if you type, say, 7 in the search box?

    Normally, for the dates you need to use separate controls and specify the range, e.g. something like and

    select * from myTable where (myCharacterColumn1 like @Search or myCharacterColumn2 like @Search) and ( 

    (Start_Date between @StartDate and @EndDate) and End_Date between @StartDate and @EndDate))

    -------------------------------------

    This query will select all rows from the table where your character columns like the text you specified + your dates are within the date range you specified (separately).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, July 6, 2020 9:43 PM
  • The goal is to search first by single date only, say 7/7/2020.  But also to search from Start_Date to End_Date.

    For single date only I used cmd.Parameters.AddWithValue("@Date" , "%" + Start_Date.Text + "%"); attaching the @Date to the Start_Date column in the table but that didn't help.  I haven't tried the from to function, never thought of doing that until now so thanks for that!

    cmd.CommandText = "SELECT * from Overnight_Parking Where (Dispatcher like @Search or Start_Date like @Date or End_Date like @Search or Callers_Name like @Search) or Callers_Phone like @Search or City like @Search or Address like @Search or Reason like @Search or Description like @Search ";
    
                cmd.Parameters.AddWithValue("@Search", "%" + Search.Text + "%");
                cmd.Parameters.AddWithValue("@Date" , "%" + Start_Date.Text + "%");

    Monday, July 6, 2020 9:51 PM
  • Hi, 

    First of all, I don't like the AddWithValue method, I always prefer to use more verbose Add method where you can specify type of the parameter.

    In your case if you want to find dates where either start date is equal the date you types or end date is equal that date you would use condition

    where .. and (Start_Date = @Date or End_Date = @Date)

    and your 

    cmd.Parameters.Add("@Date", SqlDbType.Date).Value  = StartDate.Text;

    If this would not work, you may need to try

    DateTime.Parse(StartDate.Text); -- I'm writing from the top of my head, so you may need to make sure to use the correct syntax, but that's the main idea.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Monday, July 6, 2020 10:02 PM
    • Marked as answer by wgraulich Monday, July 6, 2020 10:12 PM
    Monday, July 6, 2020 10:02 PM
  • I was able to figure it out.  I used the below.  But if I replace the "or" with "and" so it searches all three fields I get no return.  But if I keep the "or" I get a return.  Any thoughts on why the "and" is not working in the sql script?
    con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * from Overnight_Parking Where (City like @Search or Start_Date like @Start_Date or End_Date like @End_Date)";
    
                cmd.Parameters.AddWithValue("@Search", "%" + City_Search_bx.Text + "%");
                cmd.Parameters.Add("@Start_Date", SqlDbType.DateTime).Value = DateTime.ParseExact(Start_Date_Sx.Text, "MM/dd/yyyy", null);
                cmd.Parameters.Add("@End_Date", SqlDbType.DateTime).Value = DateTime.ParseExact(End_Date_Sx.Text, "MM/dd/yyyy", null);
    
                cmd.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                dataGridView1.DataSource = dt;
    
                con.Close();
                //textbox clear code
                City_Search_bx.Text = "";


    • Edited by wgraulich Tuesday, July 7, 2020 6:37 PM
    Tuesday, July 7, 2020 6:37 PM
  • I was able to figure it out.  I used the below.  But if I replace the "or" with "and" so it searches all three fields I get no return.  But if I keep the "or" I get a return.  Any thoughts on why the "and" is not working in the sql script?
    con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * from Overnight_Parking Where (City like @Search or Start_Date like @Start_Date or End_Date like @End_Date)";
    
                cmd.Parameters.AddWithValue("@Search", "%" + City_Search_bx.Text + "%");
                cmd.Parameters.Add("@Start_Date", SqlDbType.DateTime).Value = DateTime.ParseExact(Start_Date_Sx.Text, "MM/dd/yyyy", null);
                cmd.Parameters.Add("@End_Date", SqlDbType.DateTime).Value = DateTime.ParseExact(End_Date_Sx.Text, "MM/dd/yyyy", null);
    
                cmd.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                dataGridView1.DataSource = dt;
    
                con.Close();
                //textbox clear code
                City_Search_bx.Text = "";


    Did you test the SQL in SSMS ?

    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, July 7, 2020 6:57 PM
  • First of all, instead of LIKE use

    cmd.CommandText = "SELECT * from Overnight_Parking Where (City like @Search and (Start_Date = @Start_Date and End_Date = @End_Date))";
    

    Secondly, did you verify that you're actually getting values for your dates parameters (e.g. step through the code in debugger and look at the values - do you get them or get NULLs)? What are the types of your dates columns in the database and do you have them saved with the time portion or without it assuming they are datetime and not date types?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 7, 2020 6:58 PM
  • Like you suggested in an earlier reply I did.  I used SELECT * from Overnight_Parking Where city like 'Winfield' and Start_Date like '2020-07-30' and End_Date like '2020-07-31'  and I get the return.
    Tuesday, July 7, 2020 6:59 PM
  • What are the types of your start_date and end_date columns in the database? You do not use LIKE with date / datetime types, you need to use either = or between conditions or best using greater than or equal and less than.

    Take a look at this page https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries

    This is unfortunately not the way I saw that page many years ago, so the style is different and the code also is messed up in that blog post, but hopefully that article still be useful to introduce common mistakes, etc.



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 7, 2020 7:31 PM
  • Like you suggested in an earlier reply I did.  I used SELECT * from Overnight_Parking Where city like 'Winfield' and Start_Date like '2020-07-30' and End_Date like '2020-07-31'  and I get the return.

    Since most developers don't know how to show command text with parameters, here is how and spit out the SQL in the output window which can then be dumped into SSMS or create a .txt file in Visual Studio with an extension of .sql which allows you to run the results.

    Add my peeker found here to your project.

    Here I want to get values for parameters for a SELECT statement with three parameters.

    using System;
    using System.Data;
    // next using is for the peeker
    using DataProviderCommandHelpers;
    
    namespace WindowsFormsApp1
    {
        using System.Data.SqlClient;
    
        public class DataOperationsSqlServer
        {
            private static string ConnectionString = 
                "Data Source=.\\SQLEXPRESS;Initial Catalog=NorthWindAzureForInserts;" + 
                "Integrated Security=True";
    
          
            public static DataTable Demo(DateTime startDate, DateTime endDate, string countryStartsWith)
            {
                var dt = new DataTable();
    
                using (var cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    using (var cmd = new SqlCommand { Connection = cn })
                    {
                        cmd.CommandText = @"
    SELECT        OrderID, CustomerIdentifier, EmployeeID, RequiredDate, ShippedDate, ShipVia
    FROM          Orders
    WHERE        (OrderDate BETWEEN @StartDate AND @EndDate) AND ShipCountry LIKE @ShipCountryStartsWith
    ORDER BY dbo.Orders.CustomerIdentifier";
    
                        cmd.Parameters.AddWithValue("@StartDate", startDate);
                        cmd.Parameters.AddWithValue("@EndDate", endDate);
                        cmd.Parameters.AddWithValue("@ShipCountryStartsWith", countryStartsWith);
    
                        try
                        {
                            cn.Open();
                            Console.WriteLine(cmd.CommandText);
                            Console.WriteLine();
                            Console.WriteLine(cmd.RevealCommandQuery());
                            dt.Load(cmd.ExecuteReader());
    
                        }
                        catch (Exception ex)
                        {
                            // TODO
                        }
                    }
                }
    
                return dt;
    
            }
        }
    
    }
    

    Run the code above

    private void Demobutton_Click(object sender, EventArgs e)
    {
        /*
         * Imagine the next three variables are from controls, you can't see the values
         */
        var startDate = new DateTime(2014,7,5,0,0,0);
        var endDate = new DateTime(2014,8,7,0,0,0);
        var startsWith = "B%";
    
        DataOperationsSqlServer.Demo(startDate,endDate, startsWith);
    }
    

    Output window shows

    SELECT        OrderID, CustomerIdentifier, EmployeeID, RequiredDate, ShippedDate, ShipVia
    FROM          Orders
    WHERE        (OrderDate BETWEEN @StartDate AND @EndDate) AND ShipCountry LIKE @ShipCountryStartsWith
    ORDER BY dbo.Orders.CustomerIdentifier
    
    
    SELECT        OrderID, CustomerIdentifier, EmployeeID, RequiredDate, ShippedDate, ShipVia
    FROM          Orders
    WHERE        (OrderDate BETWEEN '7/5/2014 12:00:00 AM' AND '8/7/2014 12:00:00 AM') AND ShipCountry LIKE 'B%'
    ORDER BY dbo.Orders.CustomerIdentifier
    The program '[13692] WindowsFormsApp1.exe' has exited with code 0 (0x0).
    

    In the above case everything is as expected but suppose it was not? Grab the last statement and place it into the .sql file or into a new query window in SSMS.

    Does this make sense?

    Results in SSMS


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, July 7, 2020 8:30 PM
  • I tried your suggestion and I get no return.  When I do a search in sql using my original script I get the return I am looking for, just not in the form.  Regarding Debugger I not sure what your asking.  I ran in debug mode and I am not getting any errors,  just no return when I do the search.  Regarding the date type.  In the form I am using custom format of MM/dd/yyyy.  In the sql table itself the Start_Date and End_Date columns are data type "date" with no time.  Like stated eariler if I use the or instead or and or =, the or function works.  It's when I use the and clause or = clause when it fails.
    Tuesday, July 7, 2020 9:33 PM
  • One more time

    1. In the debugger put a break on the line that sets the value for your date parameters to make sure you're getting actual date values and not the NULL

    2. As I said, you should not be using LIKE expression with date or datetime columns. The correct query for your case is supposed to be

    select columns from myTable where City like @search and 

    Start_Date = @StartDate and End_Date = @EndDate;

    ------------------------------

    To test this query try in SSMS first

    declare @Search varchar(50) = 'B%';

    declare @StartDate datetime = '20100101'; -- use date from the known row you want to get

    declare @EndDate datetime = '20200101'; -- same comment as above

    -- Try the query I suggested

    --------------------

    If that query returns the row(s) you're looking for correctly in SSMS, you need to make sure your datetime parameters do indeed get values 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 7, 2020 9:49 PM
  • And as stated earlier I removed the like some time ago.  I have tried the following:

     cmd.CommandText = "SELECT * from Overnight_Parking Where (City = @Search and Start_Date = @Start_Date AND End_Date = @End_Date)";
    
    cmd.CommandText = "SELECT * from Overnight_Parking Where (City = @Search and (Start_Date = @Start_Date AND End_Date = @End_Date))";


    I have also tested the script in SSMS and the search works.

    and the bugger is getting a null return.

    I have also tried this and it works in SSMS but not in the form:

    If I use the or statement (below):

    cmd.CommandText = "SELECT * from Overnight_Parking Where (City = @Search or (Start_Date = @Start_Date or End_Date = @End_Date))";
    It works as long as the user selects the correct dates.  If the user selects the wrong dates the return shows every entry associated with the City name.  I need it to only return what dates are picked and if the dates don't match then it should show no return.


    • Edited by wgraulich Wednesday, July 8, 2020 11:57 AM
    Wednesday, July 8, 2020 11:46 AM
  • Don't

    • Use 'like' for dates
    • When using 'like' for strings indicate starts with, ends with or contains via %.

    For instance this returns 0 records

    SELECT        OrderID, CustomerIdentifier, EmployeeID, RequiredDate, ShippedDate, ShipVia
    FROM          Orders
    WHERE        OrderDate LIKE '7/5/2014 00:00:00 AM'  AND ShipCountry LIKE 'Germany'
    ORDER BY dbo.Orders.CustomerIdentifier

    This returns (as expected) one record

    SELECT        OrderID, CustomerIdentifier, EmployeeID, RequiredDate, ShippedDate, ShipVia, ShipCountry
    FROM          Orders
    WHERE        OrderDate = '7/5/2014 00:00:00 AM' AND ShipCountry = 'Germany'
    ORDER BY dbo.Orders.CustomerIdentifier


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, July 8, 2020 12:08 PM
  • I'm NOT using the LIKE command anymore.  I my last reply it shows the like command is NOT part of the script in the form and I show I am not using the LIKE in the 2nd SSMS code.   LIKE is no long part of anything associated with this project.  AND command does not come back with a return in the form, but it does in SSMS.  OR works in both the form and SSMS, but if the user puts in the wrong dates the return shows every entry associated with the City Name.  If the user puts in the correct dates, using the OR command works in the form.

    Sorry if I am not making this clear!

    Wednesday, July 8, 2020 12:14 PM
  • 1. Are you using DATE type or DATETIME type in your SQL database for both of your start and end dates?

    2. Do you want the user to provide correctly both start and end date and not just the range?

    If both dates must match and you're using the DATE type (not the datetime or datetime2 type in your database) and you want the CITY to be also spelled correctly (e.g. you're no longer using match and you removed adding % at the end), then your query should be

    select * from myTable where City = @City 

    AND StartDate = @StartDate and EndDate = @EndDate;

    This is the query you need for the above conditions.

    Now we need to make sure we're providing the correct values for the parameters.

    For now, let's forget the form and try

    cmd.Parameters.Add('@city', SqlDbType.VarChar, 30).Value = "Wirefield";

    cmd.Parameters.Add(@StartDate, SqlDbType.Date).Value =

    DateTime.Parse("2000-07-30");

    Same for your other date.

    One important change I made - I declared your type to be Date and not DateTime. Make sure all SqlDbType match exactly the type you're using in your table and for varchar (or nvarchar) the type must match as well and you need to provide correct value for the length in that case. Once you get your form working with hard-coded values, go back and change to the form's controls but make sure you're stepping the assignment of parameter values in debugger and verify you're getting correct values.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Wednesday, July 8, 2020 12:49 PM
    Wednesday, July 8, 2020 12:48 PM
  • I am using Date type, I am using a combobox for City so it will be an exact match, I made the changes you suggested and it fails to run.  At this point I want to thank everyone for there help but I just guess this just isn't going to work.

    Thanks again!

    Wednesday, July 8, 2020 1:11 PM
  • I made suggestions directly from the top of my head, you should be able to figure out if there are still some issues, etc.

    It is a very simple problem, so I'm wondering what are you missing? Just post the code as you have it right now, we will be able to spot the problem.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 8, 2020 1:53 PM
  • So to refresh the goal is search by City, Start_Date and End_Date.  Using the below gets a return if I put in the Start_Date and End_Date as it appears in the sql table.  If I put in the wrong dates I still get a return, but that return shows every entry associated with the City.  What I am looking for is If I put in the wrong dates I do not want to get any return.  

    Replacing the OR with AND, I get no returns regardless if the Start_Date and End_Date match what is in the sql table.

    private void Search_Date_btn_Click(object sender, EventArgs e)
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * from Overnight_Parking Where City = @Search or Start_Date = @Start_Date or End_Date = @End_Date";
    
                cmd.Parameters.AddWithValue("@Search", "%" + City_Search_bx.Text + "%");
                cmd.Parameters.Add("@Start_Date", SqlDbType.Date).Value = DateTime.ParseExact(Start_Date_Sx.Text, "MM/dd/yyyy", null);
                cmd.Parameters.Add("@End_Date", SqlDbType.Date).Value = DateTime.ParseExact(End_Date_Sx.Text, "MM/dd/yyyy", null);
    
                cmd.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                dataGridView1.DataSource = dt;
    
                con.Close();
                //textbox clear code
                City_Search_bx.Text = "";
    
            }

    Wednesday, July 8, 2020 4:10 PM
  • Ok, I see your problem right away, but please read carefully what you posted and try to figure this out yourself.

    Also, I tried to give you correct query about 5 or more times but you're still using it wrongly (like you're not reading our responses at all).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 8, 2020 4:23 PM
  • BTW, do you know auntie Emma joke? 

    http://ndragan.com/pv/emma.html

    I hope it will work for you - it often works for me.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 8, 2020 4:30 PM
  • If you would not be able to figure this out by yourself, just tell, I'll fix the posted code, but I do hope you can fix it yourself if you read it carefully and also try to apply all the suggestions from this thread.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by wgraulich Wednesday, July 8, 2020 6:15 PM
    Wednesday, July 8, 2020 4:43 PM
  • I have tried everything everyone has posted.  My script I am showing is the only thing I can get close to working.  As mentioned before I have stopped trying to fix the issue and am working on coming up with another option.  Again, thank you all for trying!
    • Marked as answer by wgraulich Wednesday, July 8, 2020 6:15 PM
    Wednesday, July 8, 2020 6:15 PM
  • Ok, I'll fix your code for you just so you see the problem:

    private void Search_Date_btn_Click(object sender, EventArgs e)
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * from Overnight_Parking Where City = @Search AND Start_Date = @Start_Date AND End_Date = @End_Date";
    
                cmd.Parameters.Add("@Search",SqlDbType.VarChar, 30).Value  = City_Search_bx.Text;
    
                cmd.Parameters.Add("@Start_Date", SqlDbType.Date).Value = DateTime.ParseExact(Start_Date_Sx.Text, "MM/dd/yyyy", null);
                cmd.Parameters.Add("@End_Date", SqlDbType.Date).Value = DateTime.ParseExact(End_Date_Sx.Text, "MM/dd/yyyy", null);
    
               
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                dataGridView1.DataSource = dt;
    
                con.Close();
                //textbox clear code
                City_Search_bx.Text = "";
    
            }

    Can you find the difference between what you had and what I've changed?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 8, 2020 6:27 PM
  • Again, thanks but I tried this very code as you requested earlier and it didn't work.  But thanks!
    Wednesday, July 8, 2020 6:30 PM
  • Are you sure you tried it exactly as I posted? Please take a look at what you had posted and what I posted. Just go ahead, copy what I just posted and try as is and see if you can get it working.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 8, 2020 6:32 PM
  • Again, it is exactly the way I had it and it fails.  No need to continue with this thread I am just going to not search by date, I can search another way and it works.
    Wednesday, July 8, 2020 6:36 PM
  • Ok, I just wanted to help you as you kept posting wrong code.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 8, 2020 6:39 PM