11 Replies - 21770 Views - Last Post: 02 October 2009 - 04:12 AM Rate Topic: -----

#1 MentalDeveloper  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 25-January 09

Programatically SQL query using Like Operator

Posted 13 May 2009 - 09:49 AM

I am attempting to retrieve records from a table of insurance companies via code in the application I am writing. tblInsuranceCompanies is a table in an Access 2007 database.
In the application the SQL query retrieves no records and does not produce an error. The code is in a Try Catch code block.
When I walkthrough the code, I can view the content of the Sql statement. I can copy this and paste it into Access directly-- the result giving me two records.

SELECT * FROM tblInsuranceCompanies WHERE ((fInsurCompanyName Like "*banker*) OR (fInsurCompanyName Like "*BANKER*) OR (fInsurCompanyName Like "*Banker*) ) AND fInsurActive


My connection works as othe SQL queries work with it.
I am retrieving the records into a dataset. The code for this is used in multiple other code blocks successfully. In those I am not using the "Like" operator.

Any suggestions will be helpful. Thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: Programatically SQL query using Like Operator

#2 mark.bottomley  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 176
  • View blog
  • Posts: 990
  • Joined: 22-April 09

Re: Programatically SQL query using Like Operator

Posted 13 May 2009 - 10:19 AM

I'm not an SQL expert, but don't you need to assign the output of the SELECT statement to something...?? I assume you know what you are doing.
Was This Post Helpful? 0
  • +
  • -

#3 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1658
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Programatically SQL query using Like Operator

Posted 13 May 2009 - 10:31 AM

I see you've shown the SELECT statement, now show us the code you're using to execute it. Without seeing that we have no idea of how to help, or even where to start :)
Was This Post Helpful? 0
  • +
  • -

#4 MentalDeveloper  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 25-January 09

Re: Programatically SQL query using Like Operator

Posted 14 May 2009 - 07:07 AM

Here is the relevant code.
 'In a module:
	Public strPathOfDatabases As String = "C:\X\Databases\"
	Public gStrDBDataName As String = "dbMSData.mdb"
'In sub Main of same module:	
		gStrDataDBLocation1 = strPathOfDatabases & gStrDBDataName
		gCnDataString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
				"Data Source=" & gStrDataDBLocation1 & _
				";User ID=Admin;"
'In a Windows Application Form:
   Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
		Try
					 Dim strSearchFor1 As String
			Dim strSearchFor2 As String
			Dim strSearchFor3 As String
			Dim strCondition As String = ""
			strSearchFor1 = "*" & Trim(LCase(txtSearchFor.Text)) & "*"
			strSearchFor2 = "*" & Trim(UCase(txtSearchFor.Text)) & "*"
			strSearchFor3 = "*" & gFnChangeToTitleCase(txtSearchFor.Text) & "*"
			 dgvInsuranceCompanies.ClearSelection()
			Select Case lstSearchBy.SelectedIndex
				Case Is <= 0 
					strCondition = "((fInsurNEICPayerID Like " & Chr(34) & strSearchFor1 & Chr(34) & ") OR " _
					 & "(fInsurNEICPayerID Like " & Chr(34) & strSearchFor2 & Chr(34) & ") OR " _
					& "(fInsurNEICPayerID Like " & Chr(34) & strSearchFor3 & Chr(34) & ")) AND fInsurActive"
				Case 1 ' name 'THIS IS THE CONDITION THAT PRESENTS CHALLENGE
					strCondition = "((fInsurCompanyName Like " & Chr(34) & strSearchFor1 & Chr(34) & ") OR " _
					 & "(fInsurCompanyName Like " & Chr(34) & strSearchFor2 & Chr(34) & ") OR " _
					& "(fInsurCompanyName Like " & Chr(34) & strSearchFor3 & Chr(34) & ")) AND fInsurActive"
				Case 2 'city
					strCondition = "((fInsurCity Like " & Chr(34) & strSearchFor1 & Chr(34) & ") OR " _
					& "(fInsurCity Like " & Chr(34) & strSearchFor2 & Chr(34) & ") OR " _
				   & "(fInsurCity Like " & Chr(34) & strSearchFor3 & Chr(34) & ")) AND fInsurActive"
				Case 3 'state
					strCondition = "(fInsurState = '" & Trim(txtSearchFor.Text) & "') AND fInsurActive"
				Case Else
					strCondition = "fInsurActive"
					Call gAddToErrorLogFile("err in case else in btnSearch.click")
			End Select
			Call getInsuranceCompanies(strCondition)
			dgvInsuranceCompanies.ClearSelection()
		Catch ex As Exception
			Call gAddToErrorLogFile(ex.ToString)
		End Try
	End Sub

'ACTUAL TEST VALUE OF strCondition   
'---that works in Access to retrieve two records and does not work in my application not producing errors, but obtaining zero records"
' SELECT * FROM tblInsuranceCompanies WHERE ((fInsurCompanyName Like "*banker*") OR (fInsurCompanyName Like "*BANKER*") OR (fInsurCompanyName Like "*Banker*")) AND fInsurActive

  Private Sub getInsuranceCompanies(ByVal strCondition As String)
		Dim da As New OleDb.OleDbDataAdapter()
		Dim cn As New OleDb.OleDbConnection(gCnDataString)
		Dim ds As New DataSet()
		Dim SQLString As String
		Dim intLastIndex As Integer
		Dim intCount As Integer
		Dim strTemp As String
		Try
			SQLString = "SELECT * FROM tblInsuranceCompanies" & _
						" WHERE " & strCondition
			da.SelectCommand = New OleDb.OleDbCommand(SQLString, cn)
			cn.Open()
			da.Fill(ds)
			intCount = ds.Tables(0).Rows.Count
			intLastIndex = intCount - 1	' When debugging/stepping through, intCount is zero; intCount being the number of records retrieved into the DataSet ds
			If intCount > 30 Then
				With pBrCompanies '(progressbar)
					.Minimum = 1
					.Maximum = intCount
					.Step = 30
					.Visible = True
				End With
			End If
			ReDim intInsuranceID(intLastIndex)
			For intIndex As Integer = 0 To intLastIndex
				dgvInsuranceCompanies.Rows.Add()   ' dataGridView
				With dgvInsuranceCompanies.Rows(intIndex)
					.Cells("colName").Value = ds.Tables(0).Rows(intIndex).Item(1).ToString
					.Cells("colAddressL1").Value = ds.Tables(0).Rows(intIndex).Item(2).ToString
					.Cells("colAddressL2").Value = ds.Tables(0).Rows(intIndex).Item(3).ToString
					.Cells("colCity").Value = ds.Tables(0).Rows(intIndex).Item(4).ToString
					.Cells("colState").Value = ds.Tables(0).Rows(intIndex).Item(5).ToString
					strTemp = ds.Tables(0).Rows(intIndex).Item(6).ToString
					If strTemp.Length > 5 Then
						strTemp = Mid(strTemp, 1, 5) & "-" & Mid(strTemp, 6, 4)
					End If
					.Cells("colZip").Value = strTemp
					.Cells("colNEIC").Value = ds.Tables(0).Rows(intIndex).Item(8).ToString
				End With
				intInsuranceID(intIndex) = CInt(ds.Tables(0).Rows(intIndex).Item(0))
				If intIndex Mod 30 = 0 Then
					pBrCompanies.PerformStep()
				End If
			Next
			pBrCompanies.Visible = False
			dgvInsuranceCompanies.ClearSelection()
		Catch ex As Exception
			Call gAddToErrorLogFile(ex.ToString)
		Finally
			If cn.State = ConnectionState.Open Then
				cn.Close()
			End If
		End Try
	End Sub


Attached is a screen capture of the Access table.
Thanks!

Attached File(s)

  • Attached File  table.rtf (1.71MB)
    Number of downloads: 134

Was This Post Helpful? 0
  • +
  • -

#5 [email protected]  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 14-September 09

Re: Programatically SQL query using Like Operator

Posted 14 September 2009 - 08:12 AM

View PostMentalDeveloper, on 14 May, 2009 - 06:07 AM, said:

Here is the relevant code.
 'In a module:
	Public strPathOfDatabases As String = "C:\X\Databases\"
	Public gStrDBDataName As String = "dbMSData.mdb"
'In sub Main of same module:	
		gStrDataDBLocation1 = strPathOfDatabases & gStrDBDataName
		gCnDataString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
				"Data Source=" & gStrDataDBLocation1 & _
				";User ID=Admin;"
'In a Windows Application Form:
   Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
		Try
					 Dim strSearchFor1 As String
			Dim strSearchFor2 As String
			Dim strSearchFor3 As String
			Dim strCondition As String = ""
			strSearchFor1 = "*" & Trim(LCase(txtSearchFor.Text)) & "*"
			strSearchFor2 = "*" & Trim(UCase(txtSearchFor.Text)) & "*"
			strSearchFor3 = "*" & gFnChangeToTitleCase(txtSearchFor.Text) & "*"
			 dgvInsuranceCompanies.ClearSelection()
			Select Case lstSearchBy.SelectedIndex
				Case Is <= 0 
					strCondition = "((fInsurNEICPayerID Like " & Chr(34) & strSearchFor1 & Chr(34) & ") OR " _
					 & "(fInsurNEICPayerID Like " & Chr(34) & strSearchFor2 & Chr(34) & ") OR " _
					& "(fInsurNEICPayerID Like " & Chr(34) & strSearchFor3 & Chr(34) & ")) AND fInsurActive"
				Case 1 ' name 'THIS IS THE CONDITION THAT PRESENTS CHALLENGE
					strCondition = "((fInsurCompanyName Like " & Chr(34) & strSearchFor1 & Chr(34) & ") OR " _
					 & "(fInsurCompanyName Like " & Chr(34) & strSearchFor2 & Chr(34) & ") OR " _
					& "(fInsurCompanyName Like " & Chr(34) & strSearchFor3 & Chr(34) & ")) AND fInsurActive"
				Case 2 'city
					strCondition = "((fInsurCity Like " & Chr(34) & strSearchFor1 & Chr(34) & ") OR " _
					& "(fInsurCity Like " & Chr(34) & strSearchFor2 & Chr(34) & ") OR " _
				   & "(fInsurCity Like " & Chr(34) & strSearchFor3 & Chr(34) & ")) AND fInsurActive"
				Case 3 'state
					strCondition = "(fInsurState = '" & Trim(txtSearchFor.Text) & "') AND fInsurActive"
				Case Else
					strCondition = "fInsurActive"
					Call gAddToErrorLogFile("err in case else in btnSearch.click")
			End Select
			Call getInsuranceCompanies(strCondition)
			dgvInsuranceCompanies.ClearSelection()
		Catch ex As Exception
			Call gAddToErrorLogFile(ex.ToString)
		End Try
	End Sub

'ACTUAL TEST VALUE OF strCondition   
'---that works in Access to retrieve two records and does not work in my application not producing errors, but obtaining zero records"
' SELECT * FROM tblInsuranceCompanies WHERE ((fInsurCompanyName Like "*banker*") OR (fInsurCompanyName Like "*BANKER*") OR (fInsurCompanyName Like "*Banker*")) AND fInsurActive

  Private Sub getInsuranceCompanies(ByVal strCondition As String)
		Dim da As New OleDb.OleDbDataAdapter()
		Dim cn As New OleDb.OleDbConnection(gCnDataString)
		Dim ds As New DataSet()
		Dim SQLString As String
		Dim intLastIndex As Integer
		Dim intCount As Integer
		Dim strTemp As String
		Try
			SQLString = "SELECT * FROM tblInsuranceCompanies" & _
						" WHERE " & strCondition
			da.SelectCommand = New OleDb.OleDbCommand(SQLString, cn)
			cn.Open()
			da.Fill(ds)
			intCount = ds.Tables(0).Rows.Count
			intLastIndex = intCount - 1	' When debugging/stepping through, intCount is zero; intCount being the number of records retrieved into the DataSet ds
			If intCount > 30 Then
				With pBrCompanies '(progressbar)
					.Minimum = 1
					.Maximum = intCount
					.Step = 30
					.Visible = True
				End With
			End If
			ReDim intInsuranceID(intLastIndex)
			For intIndex As Integer = 0 To intLastIndex
				dgvInsuranceCompanies.Rows.Add()   ' dataGridView
				With dgvInsuranceCompanies.Rows(intIndex)
					.Cells("colName").Value = ds.Tables(0).Rows(intIndex).Item(1).ToString
					.Cells("colAddressL1").Value = ds.Tables(0).Rows(intIndex).Item(2).ToString
					.Cells("colAddressL2").Value = ds.Tables(0).Rows(intIndex).Item(3).ToString
					.Cells("colCity").Value = ds.Tables(0).Rows(intIndex).Item(4).ToString
					.Cells("colState").Value = ds.Tables(0).Rows(intIndex).Item(5).ToString
					strTemp = ds.Tables(0).Rows(intIndex).Item(6).ToString
					If strTemp.Length > 5 Then
						strTemp = Mid(strTemp, 1, 5) & "-" & Mid(strTemp, 6, 4)
					End If
					.Cells("colZip").Value = strTemp
					.Cells("colNEIC").Value = ds.Tables(0).Rows(intIndex).Item(8).ToString
				End With
				intInsuranceID(intIndex) = CInt(ds.Tables(0).Rows(intIndex).Item(0))
				If intIndex Mod 30 = 0 Then
					pBrCompanies.PerformStep()
				End If
			Next
			pBrCompanies.Visible = False
			dgvInsuranceCompanies.ClearSelection()
		Catch ex As Exception
			Call gAddToErrorLogFile(ex.ToString)
		Finally
			If cn.State = ConnectionState.Open Then
				cn.Close()
			End If
		End Try
	End Sub


Attached is a screen capture of the Access table.
Thanks!

Was This Post Helpful? 0
  • +
  • -

#6 [email protected]  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 14-September 09

Re: Programatically SQL query using Like Operator

Posted 14 September 2009 - 08:20 AM

Did anyone get an answer to this problem? I would like to know too because I have the same problem in C#.

Here is my code in the access query called pr_GetCrimesByNumber:

SELECT tblCrime.ID, tblCrime.CrimeNumber, tblCrime.SuspectSurname, tblCrime.SuspectForename, tblCrime.EnteredDate, tblCrime.IsSuspect
FROM tblCrime
WHERE (((tblCrime.CrimeNumber) Like "*" & [crime number] & "*") AND ((tblCrime.IsSuspect)=0) AND ((tblCrime.OrganizationID)=3))
ORDER BY tblCrime.EnteredDate DESC;


in the code below partNumber is a string variable passed to the method
Here is the code calling it in C#:
				OleDbCommand cmd = conn.CreateCommand();
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "pr_GetCrimesByNumber";

				cmd.Parameters.Add("crime number", OleDbType.VarChar).Value = partNumber;

				Crime thisCrime;
				OleDbDataReader reader = cmd.ExecuteReader();

				while (reader.Read())
				{

etc

Running the qery inside access gets the expected results. Running the query from C# as above gets nothing.

Any help would be appreciated - Thanks

Mentor Edit: :code:

This post has been edited by AdamSpeight2008: 14 September 2009 - 08:44 AM

Was This Post Helpful? 0
  • +
  • -

#7 MentalDeveloper  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 25-January 09

Re: Programatically SQL query using Like Operator

Posted 01 October 2009 - 12:02 PM

Hi Chris! When you posted I did not yet have a solution. Last time I retreived the entire table and searched it programmatically within my application. However, I have come upon the need for this code again. The table is too large.

I may have found a solution, but have not worked it out yet.

Refer to http://support.microsoft.com/kb/142938
with the key ideas related to our problem being
defining a query definition and setting the value of the parameter.
[quote]Dim Qd as QueryDef
Set Qd = DB.QueryDefs("By Date")
Qd!dp = 1991[code]
in either the stored query or the one coded in the application.

Good luck! Let me know if you find a resolution to the problem. :-) MentalDeveloper
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1726
  • View blog
  • Posts: 5,704
  • Joined: 25-September 09

Re: Programatically SQL query using Like Operator

Posted 01 October 2009 - 01:15 PM

Not sure if you've solved your problem but the original SQL statement above is showing LIKE ''*BANKER* instead of LIKE '*BANKER*' and it appears to be doing it before each like.

I assume those are 2 apostrophes and not a quote. Or if it is a quote, you need a closing quote for each LIKE string


Also, I don't think the data in Access is case sensitive. So SELECT * FROM Table WHERE Name LIKE 'CHARLIE*'

Would find

CharlieMay
CHARLIEMAY
charlieMay
charliemay
ChArLiEmAy

and so on...

So your statement could just read
SELECT * FROM tblInsuranceCompanies WHERE ((fInsurCompanyName Like '*banker*') AND fInsurActive)

This post has been edited by CharlieMay: 01 October 2009 - 01:36 PM

Was This Post Helpful? 0
  • +
  • -

#9 MentalDeveloper  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 25-January 09

Re: Programatically SQL query using Like Operator

Posted 01 October 2009 - 02:20 PM

Thanks, Charlie. I haven't heard from Chris yet.
In my code (see above) I use Chr(34) for the required quotation marks.

Chris,
I did not find QueryDef available, but I found another solution.
Refer to http://msdn.microsof...bparameter.aspx

which gives examples like what we are attempting to do. :-)
Was This Post Helpful? 0
  • +
  • -

#11 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1726
  • View blog
  • Posts: 5,704
  • Joined: 25-September 09

Re: Programatically SQL query using Like Operator

Posted 01 October 2009 - 02:45 PM

Try changing all occurrences of the "*" to "%"

I don't think you can use the * inside vb.net I think it needs to be % I used to run into this problem testing SQL statements inside access.

I think Access 2007 accepts either one now in the Like argument but VB.NET does not... I think ;)

This post has been edited by CharlieMay: 01 October 2009 - 02:47 PM

Was This Post Helpful? 1

#12 MentalDeveloper  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 25-January 09

Re: Programatically SQL query using Like Operator

Posted 02 October 2009 - 03:56 AM

Thanks Charlie!

Dim SQLString As String = "SELECT fProcCode, fProcDescription FROM tblProcedures " _
& "WHERE fProcActive AND " _
& "fProcDescription Like (" _
& Chr(34) & "%" _
& txtSearchFor.Text _
& "%" & Chr(34) & ")"

da.SelectCommand = New OleDb.OleDbCommand(SQLString, cn)

incision is entered by the user and the query becomes:

SELECT fProcCode, fProcDescription FROM tblProcedures WHERE fProcActive AND fProcDescription Like ("%incision%")

It is so wonderful when friends like the delimiters of % from the years around 1988 become useful. Remember the old method of adding to the DOS search command with %PATH% using the % delimiter to enclose an environment variable.
I still use the cut and paste of those years: Shift + <delete> and shift + <insert>.
I marvel at new ideas and implementations, but appreciate that the old ones are still supported.
Thanks again, Charlie!
Was This Post Helpful? 0
  • +
  • -

#13 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1726
  • View blog
  • Posts: 5,704
  • Joined: 25-September 09

Re: Programatically SQL query using Like Operator

Posted 02 October 2009 - 04:12 AM

Quote

I still use the cut and paste of those years: Shift + <delete> and shift + <insert>.

Same here, trouble is, more and more software that I use on a daily basis is taking over those keys. Not to mention the changing of the insert - delete key layouts on these new-fangled keyboards - It's a conspiracy I tell ya :crazy:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1