Order BY

[mentalhard]

Order BY [RESOLVED]

Hi,
Marry Chrismas everyone!!!

Currently i have a query like following:

Dim strSelect As String = "SELECT DISTINCT [title] FROM [books] WHERE [title] LIKE '%" & Me.txtSearch.Text & "%'"

and it returns all the names that contains the searching term. What i really want is to make it that it first returns "Titles" that starts with the search term and then the other Titles that possible contains the search term inside (middle or end).

Thanks

 

[jmcilhinney]

Please make the effort to post in the most appropriate forum. This is quite obviously a data access question so it quite obviously belongs in the Data Access forum. Moved.

First of all, don't use string concatenation to insert values into an SQL statement. Always use parameters where you can.

Secondly, you won't be able to do what you ask for with one query. You'll need to use two, in which case using parameters is advantageous again.

Dim connection As New SqlConnection("connection string here")
Dim adapter As New SqlDataAdapter("SELECT DISTINCT title FROM books WHERE title LIKE @title", connection)
Dim table As New DataTable

adapter.SelectCommand.Parameters.Add("@title", SqlDbType.VarChar)

'Get records where title starts with the specified substring.
adapter.SelectCommand.Parameters("@title").Value = Me.txtSearch.Text & "%"
adapter.Fill(table)

'Get records where title contains the specified substring after at least one character.
adapter.SelectCommand.Parameters("@title").Value = "%_" & Me.txtSearch.Text & "%"
adapter.Fill(table)

 

[InertiaM]

You should be able to do it as one query. I dont have a database here, so this code may not be perfectly correct, but you should get the idea :-

SELECT SORTORDER = 1, DISTINCT [title] FROM [books] WHERE [title] LIKE @titlestartswith

UNION ALL

SELECT SORTORDER = 2, DISTINCT [title] FROM [books] WHERE [title] LIKE @titleincludes AND [title] NOT LIKE @titlestartswith

ORDER BY

SORTORDER, [title]

 

[mentalhard]

You should be able to do it as one query. I dont have a database here, so this code may not be perfectly correct, but you should get the idea :-

SELECT SORTORDER = 1, DISTINCT [title] FROM [books] WHERE [title] LIKE @titlestartswith

UNION ALL

SELECT SORTORDER = 2, DISTINCT [title] FROM [books] WHERE [title] LIKE @titleincludes AND [title] NOT LIKE @titlestartswith

ORDER BY

SORTORDER, [title]

Will this work with MS Access because i just tried and it returns nothing.
Could you please check the query when you will have more time and MS Access near you to test it. Thanks in advance

P.S. my bad jmcilhinney. I thought that i am posting in Data Access forum actually. Thanks for moving it in more appropriate forum.
Btw, your solution is not very helpful as i am using datareader in the project. not dataadapter and stuff.

 

[mentalhard]

Well there is one very simple solution. cjard will know it indeed as it works perfect under ORACLE as well. Namely it's INSTR function:

Dim strSelect As String = "SELECT [title] FROM [books] WHERE [title] LIKE '%" & Me.txtSearch.Text & "%' ORDER BY INSTR([title],'" & Me.txtSearch.Text & "')"

Thanks for the other suggestions anyway

 

[JohnH]

Btw, your solution is not very helpful as i am using datareader in the project. not dataadapter and stuff.

You still use a (OleDB)Command and Parameters, so the setup is exactly the same until you get the reader (instead of DA.Fill).

 

[jmcilhinney]

You should be able to do it as one query. I dont have a database here, so this code may not be perfectly correct, but you should get the idea :-

SELECT SORTORDER = 1, DISTINCT [title] FROM [books] WHERE [title] LIKE @titlestartswith

UNION ALL

SELECT SORTORDER = 2, DISTINCT [title] FROM [books] WHERE [title] LIKE @titleincludes AND [title] NOT LIKE @titlestartswith

ORDER BY

SORTORDER, [title]

There is that. I always forget about UNION.