locked
Syntax for conditions RRS feed

  • Question

  • using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    
    namespace Decompression
    {
        public partial class SearchWindow : Form
        {
            public SearchWindow()
            {
                InitializeComponent();
            }
    
           private bool IsAutoIdValid()
            {
                if(findCarTextBox.Text=="")
                {
                    IsGoodsValid();
                    return false;
                }
                else if((findCarTextBox.Text=="@tr_numb") && (findGoodsTextBox.Text=="@comment"))
                {
                    IsAllValid();
                    return false;
                }
    
                          
              else  if(Regex.IsMatch(findCarTextBox.Text,@"^\*$"))
                {
                    MessageBox.Show("Car number must contain only numbers and letters");
                        return false;
                }
                else
                {
                    return true;
                }
    
                
    
            }
    
            private bool IsGoodsValid()
            {
                if (Regex.IsMatch(findGoodsTextBox.Text, @"^\*$"))
                {
                    MessageBox.Show("Goods name must contain only numbers and letters");
                    return false;
                }
                else
                {
                    return true;
                }
            }
    
            private bool IsAllValid()
            {
                if (("@comment" == findGoodsTextBox.Text) && ("@tr_numb" == findCarTextBox.Text))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            
    
            private void btnFind_Click(object sender, EventArgs e)
            {
                if (IsAllValid())
                {
                    using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.connString))
                    {
                        const string sqlcom = "select* from hawk.dbo.transport where comment=@comment" +
                            "and @tr_numb=tr_numb";
                        using (SqlCommand sqm = new SqlCommand(sqlcom, cn))
                        {
                            sqm.Parameters.Add(new SqlParameter("@comment", SqlDbType.VarChar, 1000));
                            sqm.Parameters["@comment"].Value = findGoodsTextBox.Text;
                            sqm.Parameters.Add(new SqlParameter("@tr_numb", SqlDbType.VarChar, 10));
                            sqm.Parameters["@tr_numb"].Value = findCarTextBox.Text;
                            try
                            {
                                cn.Open();
                                using (SqlDataReader cr = sqm.ExecuteReader())
                                {
                                    DataTable dt = new DataTable();
                                    dt.Load(cr);
                                    this.dgvCarFind.DataSource = dt;
                                    cr.Close();
                                }
    
                            }
                            catch
                            {
                                MessageBox.Show("Information is not find");
                            }
                            finally
                            {
                                cn.Close();
                            }
                        }
    
                    }
                }
                else if (IsAutoIdValid())
                {
                    using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.connString))
                    {
    
                        const string sqlcom = "select* from hawk.dbo.transport where tr_numb=@tr_numb";
                        using (SqlCommand sqm = new SqlCommand(sqlcom, cn))
                        {
                            sqm.Parameters.Add(new SqlParameter("@tr_numb", SqlDbType.VarChar, 10));
                            sqm.Parameters["@tr_numb"].Value = findCarTextBox.Text;
                            try
                            {
                                cn.Open();
                                using (SqlDataReader cr = sqm.ExecuteReader())
                                {
                                    DataTable dt = new DataTable();
                                    dt.Load(cr);
                                    this.dgvCarFind.DataSource = dt;
                                    cr.Close();
                                }
    
                            }
                            catch
                            {
                                MessageBox.Show("Car number is not find");
                            }
                            finally
                            {
                                cn.Close();
                            }
    
                        }
    
                    }
                }
                else if (IsGoodsValid())
                {
                    using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.connString))
                    {
    
                        const string sqlcom = "select* from hawk.dbo.transport where comment=@comment";
                        using (SqlCommand sqm = new SqlCommand(sqlcom, cn))
                        {
                            sqm.Parameters.Add(new SqlParameter("@comment", SqlDbType.VarChar, 1000));
                            sqm.Parameters["@comment"].Value = findGoodsTextBox.Text;
                            try
                            {
                                cn.Open();
                                using (SqlDataReader cr = sqm.ExecuteReader())
                                {
                                    DataTable dt = new DataTable();
                                    dt.Load(cr);
                                    this.dgvCarFind.DataSource = dt;
                                    cr.Close();
                                }
    
                            }
                            catch
                            {
                                MessageBox.Show("Goods name is not find");
                            }
                            finally
                            {
                                cn.Close();
                            }
    
    
                        
    
                        }
                    }
                }
               
                
            }
        }
    }
    

    Thursday, December 5, 2019 3:45 PM

Answers

  • If it's interesting for somebody, I've made it:

    namespace Decompression
    {
        public partial class SearchWindow : Form
        {
            public SearchWindow()
            {
                InitializeComponent();
            }
    
           private bool IsGoodsValid()
            {
                if(findGoodsTextBox.Text=="")
                {
                    IsAutoIdValid();
                    return false;
                }
               
    
                      
             
                else
                {
                    return true;
                }
    
                
    
            }
    
            private bool IsAutoIdValid()
            {
                if(findCarTextBox.Text=="")
                {
                    IsGoodsValid();
                    return false;
                }
                
                return true;
            }
    
            private bool IsAllValid()
            {
                if (( findGoodsTextBox.Text!="") && ( findCarTextBox.Text!=""))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            
    
            private void btnFind_Click(object sender, EventArgs e)
            {
                if (IsAllValid())
                {
                    using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.connString))
                    {
                        const string sqlcom = "select* from hawk.dbo.transport where comment=@comment and tr_numb=@tr_numb";
                        using (SqlCommand sqm = new SqlCommand(sqlcom, cn))
                        {
                            sqm.Parameters.Add(new SqlParameter("@comment", SqlDbType.VarChar, 1000));
                            sqm.Parameters["@comment"].Value = findGoodsTextBox.Text;
                            sqm.Parameters.Add(new SqlParameter("@tr_numb", SqlDbType.VarChar, 10));
                            sqm.Parameters["@tr_numb"].Value =  findCarTextBox.Text;
                            try
                            {
                                cn.Open();
                                using (SqlDataReader cr = sqm.ExecuteReader())
                                {
                                    DataTable dt = new DataTable();
                                    dt.Load(cr);
                                    this.dgvCarFind.DataSource = dt;
                                    cr.Close();
                                }
    
                            }
                            catch
                            {
                                MessageBox.Show("Information is not find");
                            }
                            finally
                            {
                                cn.Close();
                            }
                        }
    
                    }
                }
                else if (IsGoodsValid())
                {
                    using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.connString))
                    {
    
                        const string sqlcom = "select* from hawk.dbo.transport where comment=@comment";
                        using (SqlCommand sqm = new SqlCommand(sqlcom, cn))
                        {
                            sqm.Parameters.Add(new SqlParameter("@comment", SqlDbType.VarChar, 1000));
                            sqm.Parameters["@comment"].Value = findGoodsTextBox.Text;
                            try
                            {
                                cn.Open();
                                using (SqlDataReader cr = sqm.ExecuteReader())
                                {
                                    DataTable dt = new DataTable();
                                    dt.Load(cr);
                                    this.dgvCarFind.DataSource = dt;
                                    cr.Close();
                                }
    
                            }
                            catch
                            {
                                MessageBox.Show("Goods are not find");
                            }
                            finally
                            {
                                cn.Close();
                            }
    
                        }
    
                    }
                }
                else if (IsAutoIdValid())
                {
                    using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.connString))
                    {
    
                        const string sqlcom = "select* from hawk.dbo.transport where tr_numb = @tr_numb";
                        using (SqlCommand sqm = new SqlCommand(sqlcom, cn))
                        {
                            sqm.Parameters.Add(new SqlParameter("@tr_numb", SqlDbType.VarChar, 10));
                            sqm.Parameters["@tr_numb"].Value = findCarTextBox.Text;
                            try
                            {
                                cn.Open();
                                using (SqlDataReader cr = sqm.ExecuteReader())
                                {
                                    DataTable dt = new DataTable();
                                    dt.Load(cr);
                                    this.dgvCarFind.DataSource = dt;
                                    cr.Close();
                                }
    
                            }
                            catch
                            {
                                MessageBox.Show("Car name is not find");
                            }
                            finally
                            {
                                cn.Close();
                            }
    
    
                        
    
                        }
                    }
                }
               
                
            }
        }
    }
    

    • Marked as answer by Decompressor Monday, December 16, 2019 9:20 AM
    Monday, December 16, 2019 9:19 AM

All replies

  • Thursday, December 5, 2019 3:45 PM
  • Car Number=

    findCarTextBox

    Goods=findGoodsTextBox

    I want to search by Car Number(tr_numb in sql table or by Goods (comment in sql table) or by Car Number and Goods simultaneusly. But if search by Car Number or by Goods works normally, search by Car number and Goods simultaneulsy doesn't work. What is syntax to such search?


    Please revise this reply, take text out of the code block

    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, December 5, 2019 4:55 PM
  • Car Number=findCarTextBox=tr_numb

    Goods=findGoodsTextBox=comment

    Point is that search doesn't work properly. It works, when I search by Car Number or by Goods, but I want  it can work also by Car Number and by Goods simultaneously. When I search by Car Number and Goods simultaneously, result is only by Car Number and doesn't take into account Goods. For instance:

    Friday, December 6, 2019 7:23 AM
  • Hi Decompressor,
    >>const string sqlcom = "select* from hawk.dbo.transport where comment=@comment and @tr_numb=tr_numb";
    You can try to chang the

    "select* from hawk.dbo.transport where comment=@comment and @tr_numb=tr_numb"

    to the

    "select* from hawk.dbo.transport where comment=@comment and tr_numb=@tr_numb".

    Best Regards,
    Daniel Zhang


    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.

    Friday, December 6, 2019 8:33 AM
  • Thank you, but that not helped
    Friday, December 6, 2019 10:41 AM
  • Hi Decompressor,
    You should check whether the sql statement is correct by executing the sql statement in the database.
    Best Regards,
    Daniel Zhang


    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.

    Monday, December 9, 2019 9:05 AM
  • I made what you wrote. It does not help

    Monday, December 9, 2019 12:44 PM
  • Monday, December 9, 2019 12:45 PM
  • Perhaps trying a contains e.g.

    SELECT * FROM hawk.dbo.transport WHERE CONTAINS(comment, @comment) AND tr_numb = @tr_numb


    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, December 9, 2019 1:35 PM
  • Perhaps trying a contains e.g.

    SELECT * FROM hawk.dbo.transport WHERE CONTAINS(comment, @comment) AND tr_numb = @tr_numb


    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, December 9, 2019 3:14 PM
  • Right click on the table in SSMS (SQL-Server Management Studio) and define a full-text index

    Or use LIKE

    "SELECT * FROM hawk.dbo.transport WHERE CONTAINS(comment,%" + @comment + "%") AND tr_numb = @tr_numb"


    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, December 9, 2019 3:32 PM
  • Right click on the table in SSMS (SQL-Server Management Studio) and define a full-text index

    Or use LIKE

    SELECT * FROM hawk.dbo.transport WHERE CONTAINS(comment, "%" + @comment "%") AND tr_numb = @tr_numb


    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, December 9, 2019 3:37 PM
  • It's not active
    Monday, December 9, 2019 3:37 PM
  • It's not active
    Version 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

    Monday, December 9, 2019 4:29 PM
  • It's not active

    Version 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, December 10, 2019 7:20 AM
  • Nobody can solve issue?
    Friday, December 13, 2019 2:34 PM
  • Nobody can solve issue?

    One last consideration, the spaces in the parameter

    This works in my test but one or two spaces in the like will fail.

    DECLARE @QuantityPerUnit nvarchar(20)= '%oz%';
    DECLARE @CategoryId int = 2
    
    SELECT ProductID, 
                      ProductName, 
                      SupplierID, 
                      CategoryID, 
                      QuantityPerUnit, 
                      UnitPrice, 
                      UnitsInStock, 
                      UnitsOnOrder, 
                      ReorderLevel, 
                      Discontinued, 
                      DiscontinuedDate
    FROM NorthWindAzure3a.dbo.Products
    WHERE dbo.Products.QuantityPerUnit LIKE @QuantityPerUnit
          AND dbo.Products.CategoryID = @CategoryId;


    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

    Friday, December 13, 2019 4:53 PM
  • Nobody can solve issue?

    One last consideration, the spaces in the parameter

    This works in my test but one or two spaces in the like will fail.

    DECLARE @QuantityPerUnit nvarchar(20)= '%oz%';
    DECLARE @CategoryId int = 2
    
    SELECT ProductID, 
                      ProductName, 
                      SupplierID, 
                      CategoryID, 
                      QuantityPerUnit, 
                      UnitPrice, 
                      UnitsInStock, 
                      UnitsOnOrder, 
                      ReorderLevel, 
                      Discontinued, 
                      DiscontinuedDate
    FROM NorthWindAzure3a.dbo.Products
    WHERE dbo.Products.QuantityPerUnit LIKE @QuantityPerUnit
          AND dbo.Products.CategoryID = @CategoryId;


    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

    In SSMS it works either with "=" or with "like"

    but in VS if I write "select* from hawk.dbo.transport where tr_numb like @tr_numb and comment = @comment" it doesn't work neither with "=" nor with "like"

    Monday, December 16, 2019 7:19 AM
  • If it's interesting for somebody, I've made it:

    namespace Decompression
    {
        public partial class SearchWindow : Form
        {
            public SearchWindow()
            {
                InitializeComponent();
            }
    
           private bool IsGoodsValid()
            {
                if(findGoodsTextBox.Text=="")
                {
                    IsAutoIdValid();
                    return false;
                }
               
    
                      
             
                else
                {
                    return true;
                }
    
                
    
            }
    
            private bool IsAutoIdValid()
            {
                if(findCarTextBox.Text=="")
                {
                    IsGoodsValid();
                    return false;
                }
                
                return true;
            }
    
            private bool IsAllValid()
            {
                if (( findGoodsTextBox.Text!="") && ( findCarTextBox.Text!=""))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            
    
            private void btnFind_Click(object sender, EventArgs e)
            {
                if (IsAllValid())
                {
                    using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.connString))
                    {
                        const string sqlcom = "select* from hawk.dbo.transport where comment=@comment and tr_numb=@tr_numb";
                        using (SqlCommand sqm = new SqlCommand(sqlcom, cn))
                        {
                            sqm.Parameters.Add(new SqlParameter("@comment", SqlDbType.VarChar, 1000));
                            sqm.Parameters["@comment"].Value = findGoodsTextBox.Text;
                            sqm.Parameters.Add(new SqlParameter("@tr_numb", SqlDbType.VarChar, 10));
                            sqm.Parameters["@tr_numb"].Value =  findCarTextBox.Text;
                            try
                            {
                                cn.Open();
                                using (SqlDataReader cr = sqm.ExecuteReader())
                                {
                                    DataTable dt = new DataTable();
                                    dt.Load(cr);
                                    this.dgvCarFind.DataSource = dt;
                                    cr.Close();
                                }
    
                            }
                            catch
                            {
                                MessageBox.Show("Information is not find");
                            }
                            finally
                            {
                                cn.Close();
                            }
                        }
    
                    }
                }
                else if (IsGoodsValid())
                {
                    using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.connString))
                    {
    
                        const string sqlcom = "select* from hawk.dbo.transport where comment=@comment";
                        using (SqlCommand sqm = new SqlCommand(sqlcom, cn))
                        {
                            sqm.Parameters.Add(new SqlParameter("@comment", SqlDbType.VarChar, 1000));
                            sqm.Parameters["@comment"].Value = findGoodsTextBox.Text;
                            try
                            {
                                cn.Open();
                                using (SqlDataReader cr = sqm.ExecuteReader())
                                {
                                    DataTable dt = new DataTable();
                                    dt.Load(cr);
                                    this.dgvCarFind.DataSource = dt;
                                    cr.Close();
                                }
    
                            }
                            catch
                            {
                                MessageBox.Show("Goods are not find");
                            }
                            finally
                            {
                                cn.Close();
                            }
    
                        }
    
                    }
                }
                else if (IsAutoIdValid())
                {
                    using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.connString))
                    {
    
                        const string sqlcom = "select* from hawk.dbo.transport where tr_numb = @tr_numb";
                        using (SqlCommand sqm = new SqlCommand(sqlcom, cn))
                        {
                            sqm.Parameters.Add(new SqlParameter("@tr_numb", SqlDbType.VarChar, 10));
                            sqm.Parameters["@tr_numb"].Value = findCarTextBox.Text;
                            try
                            {
                                cn.Open();
                                using (SqlDataReader cr = sqm.ExecuteReader())
                                {
                                    DataTable dt = new DataTable();
                                    dt.Load(cr);
                                    this.dgvCarFind.DataSource = dt;
                                    cr.Close();
                                }
    
                            }
                            catch
                            {
                                MessageBox.Show("Car name is not find");
                            }
                            finally
                            {
                                cn.Close();
                            }
    
    
                        
    
                        }
                    }
                }
               
                
            }
        }
    }
    

    • Marked as answer by Decompressor Monday, December 16, 2019 9:20 AM
    Monday, December 16, 2019 9:19 AM