ADO parameter query 1

[Niebotel]

I have teh following code and keep getting an error which I cannot find:

Cannot find item in collection....

Here is my code:
Public Function BepaalLidnummer()
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset




cat.ActiveConnection = CurrentProject.Connection

cmd.CommandText = "Parameters [Postkod] string; SELECT * FROM Tbl_leden WHERE (((Tbl_leden.DD) Like [Postkod])) ORDER BY Tbl_leden.DD DESC"
cat.Procedures.Append "LedenHoogsteLidnummer", cmd
Set cmd = cat.Procedures("LedenHoogsteLidnummer").Command


cmd.Parameters("Postkod") = "9999" <<<<<<<< here is the error line

rs.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

 

[SkipVought]

Hi,

Don't think it will work with the LIKE operator. Try this...

MyVar = "9999"

cmd.CommandText = "SELECT * FROM Tbl_leden WHERE (((Tbl_leden.DD) Like '*" & MyVar & "*')) ORDER BY Tbl_leden.DD DESC"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Niebotel]

Hi Skip,
Does not work; sais parameter missing??

 

[SkipVought]

did you remove ALL the parameter parts???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Niebotel]

Yes I removed all.
If I replace MyVar by a string ir works fine, so it has to do with the parameter

 

[SkipVought]

please post ALL your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Niebotel]

Public Function BepaalLidnummer()
Dim cat As New ADOX.Catalog
Dim cmd As New adodb.Command
Dim rs As New adodb.Recordset

Dim PostkodStr As String

Dim LidGevonden As Boolean, WachterGevonden As Boolean, strLidnum As String

LidGevonden = True
WachterGevonden = True

If IsNumeric(Left(Forms![Wachters]![Postcode], 4)) = True Then
PostkodStr = Left(Forms![Wachters]![Postcode], 4) & "*"
Else
PostkodStr = "9999*"
End If

cat.ActiveConnection = CurrentProject.Connection

cmd.CommandText = "Parameters [Postkod] string; SELECT TOP 1 Tbl_leden.Debiteuren_nr_Gert FROM Tbl_leden WHERE (((Tbl_leden.Debiteuren_nr_Gert) like [Postkod])) ORDER BY Tbl_leden.Debiteuren_nr_Gert DESC"
cat.Procedures.Append "LedenHoogstePostkode1", cmd
Set cmd = cat.Procedures("LedenHoogstePostkode1").Command

cmd.Parameters("Postkod") = PostkodStr <<<<<<<<<ERROR LINE

rs.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

 

[SkipVought]

This is like pulling teeth!!!

Where is the code that I gave you to replace the parameter stuff???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Niebotel]

Sorry, I went back to the original code, because I think I cannot solve it without parameter.
I go to wath the TV now. I will come back tomorrow; anyhow thanks.

Willem

 

[SkipVought]

If you're going to watch TV, then have fun, and you're on your own, if TV is more important than this.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RoyVidar]

The percent sign (%) is the wildcard character in ADO, not *.

But are you creating a stored query through ADOX just to execute this? This is not the way it should be done. It crates way to much unnecessary overhead, and also causes bloat.

You should either have a stored query to utilize with parameters, or just run the dynamic SQL (see sample below).

DAO is most often preferred when working againts Jet data, but you can also use ADO.

I am assuming the Debiteuren_nr_Gert field is text.

[tt] ' typed - not tested
If IsNumeric(Left(Forms![Wachters]![Postcode], 4)) Then
PostkodStr = Left(Forms![Wachters]![Postcode], 4) & "%"
Else
PostkodStr = "9999%"
End If
SQL = "SELECT TOP 1 Debiteuren_nr_Gert " & _
"FROM Tbl_leden " & _
"WHERE Debiteuren_nr_Gert like '" & PostkodStr & _
"' ORDER BY Debiteuren_nr_Gert DESC"
Set rs = currentproject.connection.execute(SQL,,adcmdtext)[/tt]

Chiph's faq faq709-1526 might also give some hints on usage of parameters on dynamic SQL, though I'm not 100% sure the sample work directly on Jet.

Roy-Vidar

 

[Niebotel]

Hi Roy, thanks a lot. I replaced the * and it works now with the following code (I created the query on forehand)
Set rs = New Recordset
Set cmd = New Command

cmd.ActiveConnection = CurrentProject.Connection

cmd.CommandText = "HoogsteLidnummerLeden"
Set Prm = cmd.CreateParameter
Prm.Type = adChar
Prm.Size = 25
cmd.Parameters.Append Prm


cmd.Parameters(0).Value = Postkod

rs.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
Debug.Print rs!Debiteuren_nr_Gert

I have one question left:
When do you use SQL and when ADO?
Is that somewhere explained?
Thanks again

Willem

 

[RoyVidar]

> When do you use SQL and when ADO?

I'm not sure what you're asking, but here are some opinions and thoughts...

Around here, and in Access/Jet communities in general, the answer to when to use ADO, would probably be "Never". One would recommend DAO over ADO, since it is native to Access/Jet, and ADO is a more general library, designed to be able to connect to all kind of data sources.

The answer to when to use SQL, would probably be "Always", cause the comparison, would be SQL vs recordsets. One should probably avoid recordset approaches as much as possible, and use SQL.

But, perhaps the question you're asking is more when to use Dynamic SQL over stored queries?

If so, the usual "mantra" is to always use stored queries, and pass parameters, as this will "always" outperform dynamic SQL, which, even though many will claim it to be the truth, the whole truth and nothing but..., isn't necessarily so.

With proper indexing, the Jet engine will be able to optimize also dynamic SQL. AND - due to how stored queries are compiled, you may even experience dynamic SQL to be faster, because it will be optimized based on the current state and fill degree if the tables, vs the stored queris optimalization plan, which is stored at the time they were compiled.

Bottom line, some of the guys working with large datasets, find little or no performance penalty with dynamic SQL vs stored queries (some to the contrary), and find more convenience using dynamic SQL in development (also, not cluttering the database window with hunnerd and umpteen querydefs.

When working with other platforms than Jet, SQL injection attacks might be an issue, and working with querydefs (dynamic, as in chiph's faq, or stored) and parameters, is probably more secure, and/or easier to achieve vs using than "pure" dynamic SQL.

Roy-Vidar