12 Replies - 1811 Views - Last Post: 08 November 2011 - 09:14 AM

#1 timmack  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 31-January 11

How to use the Like operator of T-SQL command query?

Posted 07 November 2011 - 11:06 PM

Hello!

I've been using LINQ for database query operations but I want to try also the T-SQL command query using the 'Like' operator which I believe same as the combination of startswith() and contains() method in linq. I tried to use the format I found online but nothing happens, it won't retrieve the data which I supposed to display on the drop-down list when the user types in the keywords on the combobox which would the same keywords stored in my records. I need some assistance if I missed some valid syntax for my select querycommand. Here is my code below for your reference. I greatly appreciate any informative response that could be shared. Thank you.


  private void Combobox4_TextChanged(object sender, EventArgs e)
               {
                SqlConnection myConnection = new SqlConnection();

                SqlCommand myCommand = new SqlCommand();

                SqlDataReader dr;

                string inputval = comboBox4.Text;

 

                string connect = @"Data Source =.\SQLExpress; Integrated Security =true; AttachDbFilename =|DataDirectory|\POSdb.mdf;User Instance =true;";

 

                myConnection = new SqlConnection(connect);

                myConnection.Open();

                myCommand = myConnection.CreateCommand();

                myCommand.CommandText = "Select ProductName From AdminTab Where ProductName Like 'inputval%' ";

                dr = myCommand.ExecuteReader();

                while (dr.Read())

                {

                    comboBox4.Items.Add(dr.GetString(2));

                }
               }


Is This A Good Question/Topic? 0
  • +

Replies To: How to use the Like operator of T-SQL command query?

#2 Dogstopper  Icon User is offline

  • The Ninjaducky
  • member icon



Reputation: 2876
  • View blog
  • Posts: 11,051
  • Joined: 15-July 08

Re: How to use the Like operator of T-SQL command query?

Posted 08 November 2011 - 02:24 AM

Is this more along the lines of a C# question or an MS SQL question? If C#, you might have better help if I move it to the C# forum.
Was This Post Helpful? 0
  • +
  • -

#3 timmack  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 31-January 11

Re: How to use the Like operator of T-SQL command query?

Posted 08 November 2011 - 02:32 AM

Hey!

What do you think about the question? C# is the language used but the issue is all about the sql query.
Was This Post Helpful? 0
  • +
  • -

#4 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: How to use the Like operator of T-SQL command query?

Posted 08 November 2011 - 02:40 AM

dr.GetString(2)


Why 2? The query only returns one column. It starts counting at 0, so that's the index you should be using.

The query looks fine though. Except, if you are trying to put the value of the inputval variable in there, what you have won't do that. It'll just use the "inputval" string for every query. Try something like:
myCommand.CommandText = "Select ... Like @likeStr";
myCommand.Parameters.AddWithValue("@likeStr", inputval + "%");


Was This Post Helpful? 0
  • +
  • -

#5 timmack  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 31-January 11

Re: How to use the Like operator of T-SQL command query?

Posted 08 November 2011 - 05:53 AM

The index is 2 which is why I used dr.Getstring(2) since the column I'm querying is the third column which is the ProductName. I already tried to use the stored procedure and it works, however, it isn't like what I expected when I used LINQ which when the user enters the keyword which starts same letter and same keywords it will automatically displays in the drop-down list and will filter the final string when you stop typing in. In T-SQL command using 'like' operator with stored procedure, it has a delayed which means when you type the same keyword it will not browse on the list but it will only retrieve if you type it again so you need to type again in order to display the data in the drop-down list. Now, I realized that LINQ really beats T-SQL command since SQL is already an old lanquage then it is acceptable.
Was This Post Helpful? 0
  • +
  • -

#6 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: How to use the Like operator of T-SQL command query?

Posted 08 November 2011 - 06:12 AM

View Posttimmack, on 08 November 2011 - 12:53 PM, said:

The index is 2 which is why I used dr.Getstring(2) since the column I'm querying is the third column which is the ProductName.

That does not match the query you posted. The index the GetString function takes is the index of the column in the result set, not in the table it is querying.

Like I say, the query should work fine. You're just not reading the data set correctly in C#.
Was This Post Helpful? 0
  • +
  • -

#7 timmack  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 31-January 11

Re: How to use the Like operator of T-SQL command query?

Posted 08 November 2011 - 06:24 AM

Alright, now if not 2 what should be the index should I used in the getstring() method if the column is in the third?

Quote

That does not match the query you posted. The index the GetString function takes is the index of the column in the result set, not in the table it is querying.


Are you sure about that?
Was This Post Helpful? 0
  • +
  • -

#8 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: How to use the Like operator of T-SQL command query?

Posted 08 November 2011 - 06:37 AM

View Posttimmack, on 08 November 2011 - 01:24 PM, said:

Are you sure about that?

Yes, absolutely. Just think about it. What index would you put in the GetString function if you were executing a query like this:
SELECT
	f.some_column,
	s.another_column
FROM first_table AS f
LEFT JOIN second_table AS s
	ON f.pk = s.f_pk;


Assuming the tables are:
CREATE TABLE first_table (
	pk INTEGER PRIMARY KEY,
	some_column VARCHAR(255)
);
CREATE TABLE second_table (
	pk INTEGER PRIMARY KEY,
	another_column VARCHAR(255),
	f_pk INTEGER NOT NULL REFERENCES first_table(pk)
);


Both columns are at index 1 in their respective tables. If the GetString were referencing the table index, there would be no way to get both values from this query.

It doesn't matter where in the tables the columns are, only where you list them in the SELECT query; where they will be in the result set returned to C#.

In your case, to get the first and only column in the set, use 0.
Was This Post Helpful? 0
  • +
  • -

#9 timmack  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 31-January 11

Re: How to use the Like operator of T-SQL command query?

Posted 08 November 2011 - 07:14 AM

Hi Alti!

Thank you so much for you responses. I appreciate your effort but I got it on my own using stored procedure. I just missed the syntax '%' in my stored procedure. Here's your reference:

ALTER PROCEDURE CheckExistingProdName
(
	@Prodname varchar(100)
)
AS
Begin

SELECT * FROM AdminTab WHERE ProductName Like @Prodname + '%' Order by CompanyBrandName DESC 

End
	/* SET NOCOUNT ON */
	RETURN


However, your suggestion about the Getstring() method is absurd to me. You said that the index the GetString function takes is the index of the column in the result set, not in the table it is querying. You can never determine the index of the result set in a column. Using that stored procedure would return all productnames that has the same keyword that has been typed in and will be displayed on the drop-down list just like when you research in google. That's what I've been looking for. Thanks anyway.
Was This Post Helpful? 0
  • +
  • -

#10 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: How to use the Like operator of T-SQL command query?

Posted 08 November 2011 - 07:47 AM

I'm glad you got it working :)

View Posttimmack, on 08 November 2011 - 02:14 PM, said:

You can never determine the index of the result set in a column.

Sure you can. The index of the columns in the result set are determined either by:
1) The order you define in the SELECT clause, or
2) In case you just use *, the order of the columns as listed in the tables.

As a general rule you should avoid using * in SELECT queries. It's rare that you actually need all the columns, so there is little point wasting resources returning them all. For example, in your procedure you use *, thus returning all the fields for the selected rows. But if I am understanding you correctly you only use the one field: ProductName. All the others (I'm assuming there are others) would just be thrown away unused.

I'm not sure exactly how you are executing your procedure, but if dr.GetString(2) is involved at some point, try replacing it with dr.GetString(0) and rewriting the procedure so that SELECT * FROM ... becomes SELECT ProductName FROM .... See if anything changes. (It shouldn't. Except perhaps a minor resource/performance gain.)

Just for the record, the procedure is unnecessary. You could just as easily execute the query directly from C#. Based on what you posted previous, I'd imagine this would work as well:
private void Combobox4_TextChanged(object sender, EventArgs e)
{
	string connect = @"Data Source =.\SQLExpress; Integrated Security =true; AttachDbFilename =|DataDirectory|\POSdb.mdf;User Instance =true;";
	using(SqlConnection myConnection = new SqlConnection(connect))
	{
		myConnection.Open();
	
		SqlCommand myCommand = myConnection.CreateCommand();
		myCommand.CommandText = "SELECT ProductName FROM AdminTab WHERE ProductName Like @Prodname Order by CompanyBrandName DESC";
		myCommand.Parameters.AddWithValue("@Prodname", comboBox4.Text + "%");

		SqlDataReader dr = myCommand.ExecuteReader();
		while (dr.Read())
		{
		  comboBox4.Items.Add(dr.GetString(0));
		} 
	}
}


Was This Post Helpful? 0
  • +
  • -

#11 timmack  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 31-January 11

Re: How to use the Like operator of T-SQL command query?

Posted 08 November 2011 - 07:58 AM

Yeah, I tried to use that code using
comboBox4.Items.Add(dr.GetString(0));
but that will return the BarCodes of the respective product names in the drop-down list which is the first column in my table. I'm just confused why dr.GetString(0) is your choice.
Was This Post Helpful? 0
  • +
  • -

#12 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: How to use the Like operator of T-SQL command query?

Posted 08 November 2011 - 08:49 AM

View Posttimmack, on 08 November 2011 - 02:58 PM, said:

I'm just confused why dr.GetString(0) is your choice.

It's because if you use the query I used, starting with SELECT ProductName FROM, there will be no other column in the result table other than ProductName. The only index available will be 0. Trying to use 2 should result in an error..

If using 0 is returning bar codes, the bar codes must have been included in the SELECT query. Most likely as a result of using the wild-card char *, which would return the entire table. (Which would make it seem like you should be using the table column index.)

Perhaps this will make more sense. I don't seem to be doing a very good job of explaining this. :)

Lest say we have this table:
+----+----------+--------+
| pk | bar_code | name   |
+----+----------+--------+
|  1 | 01010101 | John   |
|  2 | 10101010 | Joe    |
|  3 | 11110000 | Jimmy  |
+----+----------+--------+


If I execute this code: (basically the same as before, just with a new SQL query.)

private void Combobox4_TextChanged(object sender, EventArgs e)
{
	string connect = @"Data Source =.\SQLExpress; Integrated Security =true; AttachDbFilename =|DataDirectory|\POSdb.mdf;User Instance =true;";
	using(SqlConnection myConnection = new SqlConnection(connect))
	{
		myConnection.Open();
	
		SqlCommand myCommand = myConnection.CreateCommand();
		myCommand.CommandText = "SELECT name FROM the_table WHERE name LIKE 'Jo%';";
		
		SqlDataReader dr = myCommand.ExecuteReader();
		while (dr.Read())
		{
		  comboBox4.Items.Add(dr.GetString(0));
		} 
	}
}


What the dr DataReader contains before the while loop is a result table; a new table i no way related to the original the_table table. It would look like this:
+------+
| name |
+------+
| John |
| Joe  |
+------+


It only contains data for the single row I chose in the SELECT statement and completely ignores the others. So even though "name" is the third column (second index) of the original the_table table, it is now the first and only column in the result table C# is reading from.

That's why I use 0 as the index for GetString. I'm getting a string from the result table, not the original table.
Was This Post Helpful? 0
  • +
  • -

#13 timmack  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 31-January 11

Re: How to use the Like operator of T-SQL command query?

Posted 08 November 2011 - 09:14 AM

Hello Alti!

Thank you so much for your help. I realize now what your talking about.I'm sorry,if I didn't get it in the first place but now I got it and I greatly appreciated your explanation. I figured these out now. They have the same result based on your explanation. Thank you. You did an excellent job!

Code 1:

myCommand.CommandText = "SELECT name FROM the_table WHERE name LIKE 'Jo%';";
         

        SqlDataReader dr = myCommand.ExecuteReader();

        while (dr.Read())

        {

          comboBox4.Items.Add(dr.GetString(0));

        }


Code 2:

myCommand.CommandText = "SELECT * FROM the_table WHERE name LIKE 'Jo%';";
         

        SqlDataReader dr = myCommand.ExecuteReader();

        while (dr.Read())

        {

          comboBox4.Items.Add(dr.GetString(2));

        }

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1