HELP: stored procedure (query) from out vb6 does not work?!

G

G.Esmeijer

Friends,

I would like to access a a query from out a vb6 funtion.
The query in Acees delivers the right result.
however I do not get it working fromout vb6
Who can help?
What is wrong in the code?

The follwoing contains the code and the query


functie findLocations(ByVal locatie as string)

Dim locatieCode As String

Dim prm As New ADODB.Parameter
Dim m_rs As New ADODB.Recordset
Dim cmd As New ADODB.Command

locatieCode = UCase(locatie)

m_rs.close
m_rs.CursorLocation = adUseClient

prm.Direction = adParamInput
prm.Type = adChar
prm.Size = 7
prm.Value = locatieCode
cmd.Parameters.Append prm

cmd.ActiveConnection = cnnLOC '// active connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "ZoekLocatie"

Set m_rs = cmd.Execute

If m_rs.RecordCount > 0 Then
' do something with it
' ....

End If

m_rs.close

End Function


'// this is the query in Access
'***********************************
SELECT Id, Locatie, PinCode, Soort
FROM LOCATIE
WHERE LOCATIE Like [@locatie]
ORDER BY Locatie;
 
V

Van T. Dinh

Do you include the wild-cards in the value of the parameter [@locatie]?

Notes:
1. Since you use ADO code, the wild cards in ADO is % and _ rather than *
and ?.
2. If you use Like, you should use wild-cards as otherwise Like is the same
with = but less efficient.

HTH
Van T. Dinh
MVP (Access)
 
G

G.Esmeijer

I a using % as wildcard.

I'm looking for a way to find records like

4A3%5%%

The folling are found:

4A35111

4A31511

So it responds only on the first % wildcard: whever their is a 5 it's OK.

So my question would be: how do I find all the records like 4A3%5%%

Regards

Gerrit Esmeijer

Van T. Dinh said:
Do you include the wild-cards in the value of the parameter [@locatie]?

Notes:
1. Since you use ADO code, the wild cards in ADO is % and _ rather than *
and ?.
2. If you use Like, you should use wild-cards as otherwise Like is the
same
with = but less efficient.

HTH
Van T. Dinh
MVP (Access)



G.Esmeijer said:
Friends,

I would like to access a a query from out a vb6 funtion.
The query in Acees delivers the right result.
however I do not get it working fromout vb6
Who can help?
What is wrong in the code?

The follwoing contains the code and the query


functie findLocations(ByVal locatie as string)

Dim locatieCode As String

Dim prm As New ADODB.Parameter
Dim m_rs As New ADODB.Recordset
Dim cmd As New ADODB.Command

locatieCode = UCase(locatie)

m_rs.close
m_rs.CursorLocation = adUseClient

prm.Direction = adParamInput
prm.Type = adChar
prm.Size = 7
prm.Value = locatieCode
cmd.Parameters.Append prm

cmd.ActiveConnection = cnnLOC '// active connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "ZoekLocatie"

Set m_rs = cmd.Execute

If m_rs.RecordCount > 0 Then
' do something with it
' ....

End If

m_rs.close

End Function


'// this is the query in Access
'***********************************
SELECT Id, Locatie, PinCode, Soort
FROM LOCATIE
WHERE LOCATIE Like [@locatie]
ORDER BY Locatie;
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Since % means any number of any characters, you will need to change the criteria
to something like the following

Like "4A3_5__"

That is 4 A 3 _ 5 _ _ (with spaces inserted for visual clarity (remove them)


G.Esmeijer said:
I a using % as wildcard.

I'm looking for a way to find records like

4A3%5%%

The folling are found:

4A35111

4A31511

So it responds only on the first % wildcard: whever their is a 5 it's OK.

So my question would be: how do I find all the records like 4A3%5%%

Regards

Gerrit Esmeijer

Van T. Dinh said:
Do you include the wild-cards in the value of the parameter [@locatie]?

Notes:
1. Since you use ADO code, the wild cards in ADO is % and _ rather than *
and ?.
2. If you use Like, you should use wild-cards as otherwise Like is the
same
with = but less efficient.

HTH
Van T. Dinh
MVP (Access)



G.Esmeijer said:
Friends,

I would like to access a a query from out a vb6 funtion.
The query in Acees delivers the right result.
however I do not get it working fromout vb6
Who can help?
What is wrong in the code?

The follwoing contains the code and the query


functie findLocations(ByVal locatie as string)

Dim locatieCode As String

Dim prm As New ADODB.Parameter
Dim m_rs As New ADODB.Recordset
Dim cmd As New ADODB.Command

locatieCode = UCase(locatie)

m_rs.close
m_rs.CursorLocation = adUseClient

prm.Direction = adParamInput
prm.Type = adChar
prm.Size = 7
prm.Value = locatieCode
cmd.Parameters.Append prm

cmd.ActiveConnection = cnnLOC '// active connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "ZoekLocatie"

Set m_rs = cmd.Execute

If m_rs.RecordCount > 0 Then
' do something with it
' ....

End If

m_rs.close

End Function


'// this is the query in Access
'***********************************
SELECT Id, Locatie, PinCode, Soort
FROM LOCATIE
WHERE LOCATIE Like [@locatie]
ORDER BY Locatie;
Click to expand...
Click to expand...
Click to expand...
 
V

Van T. Dinh

Your Search-For String requires a digit "5" (after "4A3") in the Search-In
Strings. There can be any String after "5" including empty String.

The search correctly found the Strings that match the Search-For Strings.
If this is not as you required, you specified the incorrect Search-For
String.

BTW, you "%" is the same as "%%" since either will match any (sub)String.

If you need help with the Search-For String, describle what you want to
select and post somewhat larger sample of selection.

--
HTH
Van T. Dinh
MVP (Access)


G.Esmeijer said:
I a using % as wildcard.

I'm looking for a way to find records like

4A3%5%%

The folling are found:

4A35111

4A31511

So it responds only on the first % wildcard: whever their is a 5 it's OK.

So my question would be: how do I find all the records like 4A3%5%%

Regards

Gerrit Esmeijer

Van T. Dinh said:
Do you include the wild-cards in the value of the parameter [@locatie]?

Notes:
1. Since you use ADO code, the wild cards in ADO is % and _ rather than *
and ?.
2. If you use Like, you should use wild-cards as otherwise Like is the
same
with = but less efficient.

HTH
Van T. Dinh
MVP (Access)



G.Esmeijer said:
Friends,

I would like to access a a query from out a vb6 funtion.
The query in Acees delivers the right result.
however I do not get it working fromout vb6
Who can help?
What is wrong in the code?

The follwoing contains the code and the query


functie findLocations(ByVal locatie as string)

Dim locatieCode As String

Dim prm As New ADODB.Parameter
Dim m_rs As New ADODB.Recordset
Dim cmd As New ADODB.Command

locatieCode = UCase(locatie)

m_rs.close
m_rs.CursorLocation = adUseClient

prm.Direction = adParamInput
prm.Type = adChar
prm.Size = 7
prm.Value = locatieCode
cmd.Parameters.Append prm

cmd.ActiveConnection = cnnLOC '// active connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "ZoekLocatie"

Set m_rs = cmd.Execute

If m_rs.RecordCount > 0 Then
' do something with it
' ....

End If

m_rs.close

End Function


'// this is the query in Access
'***********************************
SELECT Id, Locatie, PinCode, Soort
FROM LOCATIE
WHERE LOCATIE Like [@locatie]
ORDER BY Locatie;
Click to expand...
Click to expand...
Click to expand...
 
Top