[RESOLVED] Learn about parameterized queries...

[DubweiserTM]

This morning Techgnome suggest me to learn about "Parameterizez queries"... http://www.vbforums.com/showpost.php...64&postcount=4

Are there suggestions where I can find a newbie tutorial for this...

Thanks in advance !

[Hack]

Just be patient. Tg has said that he will be putting one together and when it is finished it will either be posted in our CodeBank or Tutorial section, whichever would be the most appropriate.

[techgnome]

Search MSDN for Command.Parameters.... you may need to adjust the search so that you get back info on ADO (and not ADO.NET)... but it's out there...

-tg

[DubweiserTM]

Search MSDN for Command.Parameters.... you may need to adjust the search so that you get back info on ADO (and not ADO.NET)... but it's out there...

-tg

I found this link but not really simple for someone who begin with SQL queries...

http://msdn.microsoft.com/library/de...tml/vb00j1.asp

Is it the link you talked about ???

[techgnome]

Just be patient. Tg has said that he will be putting one together and when it is finished it will either be posted in our CodeBank or Tutorial section, whichever would be the most appropriate.

Oooh, owch... that was another dig wasn't it?

I just need to have one day to myself to write it out properly. I've got a bunch of notes and an outline (I don't want to jsut throw it together like I did the other ones) I just need the time to actualy put it down.

Dub - no, that's not quite the one I was talking about.... hmmm.....

Go to MSDN, and put this into the search:
Command.Parameters AND ADO AND VB

just like that, the entire line "AND"s and all.

-tg

PS: it's going to be a tutorial.... won't be in the codebank.

[DubweiserTM]

Dub - no, that's not quite the one I was talking about.... hmmm.....

Go to MSDN, and put this into the search:
Command.Parameters AND ADO AND VB

just like that, the entire line "AND"s and all.

-tg

PS: it's going to be a tutorial.... won't be in the codebank.

Im asking me if Im stupid ??? I found nothing clear, maybe because Im a french person...

http://search.msdn.microsoft.com/sea...AND+ADO+AND+VB

Could you give me a little example...Sorry if I disturb you with this...

[techgnome]

no, it's not you.... it's MS.... they've totaly dumped all the VB6 stuff.... which means all that comes back is irrelevant .NET stuff. I think it's time I finaly write that tutorial. Stay tuned, watch this space and the Tutorials section too.... I'll be posting something tonight or tomorrow.

Using the SQL sample in from the other post.

Create a Command object, set the commandText to the SQL statement,
set the CommandType to adCommandText. Then:
dbCommand.Parameters.Add dbCommand.CreateParameter("@FldParam",adVarChar, adParameterInput, 255, adoCombo.Text)
Lastly, set the Command object's .ActiveConnection to your connection object, then finaly:
Set rstResults = dbCommand.Execute

And you should have your results in the recordset.

-tg

[DubweiserTM]

no, it's not you.... it's MS.... they've totaly dumped all the VB6 stuff.... which means all that comes back is irrelevant .NET stuff. I think it's time I finaly write that tutorial. Stay tuned, watch this space and the Tutorials section too.... I'll be posting something tonight or tomorrow.
-tg

Cool, thanks !

[DubweiserTM]

Create a Command object, set the commandText to the SQL statement,
set the CommandType to adCommandText. Then:
dbCommand.Parameters.Add dbCommand.CreateParameter("@FldParam",adVarChar, adParameterInput, 255, adoCombo.Text)
Lastly, set the Command object's .ActiveConnection to your connection object, then finaly:
Set rstResults = dbCommand.Execute

And you should have your results in the recordset.

-tg

Here it's my code, sorry some words are in french...

I got an error on this line (bold and underlined) ???

Public Sub SearchTable(adoSource As String, adoTable As String, adoField As String, adoCombo As ComboBox, intNbrField As Integer)
'****************************
'TITRE: Search Table
'****************************
'DESCRIPTION:
'Recherche la table cible à l'intérieur d'une base de données
'La base de données servant à la recherche, correspond à celle lié au control
'****************************
'ARGUMENTS:
'adoSource ---> Source de la base de données concernée
'adoTable ---> Table concernée dans la base de données
'adoField ---> Field concerné dans la base de données
'adoCombo ---> Rechercher l'item sélectionné dans ce ComboBox
'intNbrField ---> Nombre de field recherché
'****************************
    'Déclarations des variables
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cmdSQL As ADODB.Command
    Dim prmSQL As ADODB.Parameter
    
    Set cmdSQL = New ADODB.Command
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set prmSQL = New ADODB.Parameter
    
    'Connection Database
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & adoSource
    cn.Open
    
    cmdSQL.CommandText = "SELECT * FROM adoTable WHERE adoField = ?"
    cmdSQL.CommandType = adCmdText
    cmdSQL.Parameters.Add cmdSQL.CreateParameter(adoField, adVarChar, adParamInput, 255, adoCombo.Text)
    cmdSQL.ActiveConnection
    
    Set rs = cmdSQL.Execute
    
    'Rechercher la ou les tables cibles (1,2,3)
    rs.MoveFirst
    Select Case intNbrField
        Case 1
            strTableCible1 = rs.Fields("RefTable").Value
        Case 2
            strTableCible1 = rs.Fields("RefTable").Value
            strTableCible2 = rs.Fields("RefTable1").Value
        Case 3
            strTableCible1 = rs.Fields("RefTable").Value
            strTableCible2 = rs.Fields("RefTable1").Value
            strTableCible3 = rs.Fields("RefTable2").Value
    End Select
    
End Sub

Thanks again !

[DubweiserTM]

Now it's okay I made some change...

Old version:
cmdSQL.CommandText = "SELECT * FROM adoTable WHERE adoField = ?"
cmdSQL.CommandType = adCmdText
cmdSQL.Parameters.Add cmdSQL.CreateParameter(adoField, adVarChar, adParamInput, 255, adoCombo.Text)
cmdSQL.ActiveConnection

New version:
Set cmdSQL.ActiveConnection = cn
cmdSQL.CommandText = "SELECT * FROM " & adoTable & " WHERE " & adoField & " = prmSQL"
cmdSQL.CommandType = adCmdText
Set prmSQL = cmdSQL.CreateParameter(adoField, adVarChar, adParamInput, 255, adoCombo.Text)
cmdSQL.Parameters.Append prmSQL
Set rs = cmdSQL.Execute

Now the big question ! What is the difference between that and a simple query ???

I know what there's in this link:

http://www.vbforums.com/showpost.php...64&postcount=4

Thanks !

[techgnome]

Ok... first you can't pass in the field name as a parameter.... you have to have that already inthere.... otherwise it gets treated as a value, and that's not going to return the results you want.
The problem you are experienceing with this is because you've over generalized the process. There's nothing wrong with that.... just need to make sure that it is set up right.

Asuming adoTable and adoField are strings passed in, this is what your SQL string should look like

strSQL = "SELECT * FROM " & adoTable & " WHERE " & adoField & " = ?"

Then set the command settings

cmdSQL.CommandText = strSQL
cmdSQL.CommandType = adCmdText

Now create your parameter and append it

Set prmSQL = cmdSQL.CreateParameter("SearchCrit", adVarChar, adParamInput, 255, adoCombo.Text)
cmdSQL.Parameters.Append prmSQL

You are probably now asking where did "SearchCrit" come from? I made it up. When dealing with parameterized queries in Access, it doesn't care what the name is. That's why we simply hold its place with a "?" .... since it uses ordinal positioning where the parameter holder and the appened parameter are matched up 1, 2, 3, it doesn't matter what we set as the parameter name. But with SQL Server, which uses named parameters, the names do matter. Again, since we are using Access, it doesn't matter at this point. You could call it coconuts, or CocoPuffs, or dipstick, or anything you like (I suggest something meaningful), just as long as you give it a name.

Ok, now call it:

Set rs = cmdSQL.Execute

-tg

[DubweiserTM]

Now my problem in this link is really resolved: http://www.vbforums.com/showpost.php...07&postcount=1

Regarding the point #3 on this Reply, am I okay ? http://www.vbforums.com/showpost.php...64&postcount=4

Thanks for all...

[techgnome]

Yes, you should be fine..... Use a drop down for the list of tables and fields.... and you;ll be OK... there's no way to parameterize tables or their fields, so to minimize injection attacks (which the problem I was explaining in the other thread), minimize how the user enters the data. By using a combo, it severly limits the data that the user has to select from and they can't just type anything in for the table/field names. The value being searched for then becomes the only problem, which is taken care of by using the parameterized query.

-tg