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 !
Printable View
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 !
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.
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...Quote:
Originally Posted by techgnome
http://msdn.microsoft.com/library/de...tml/vb00j1.asp
Is it the link you talked about ???
Oooh, owch... that was another dig wasn't it? ;)Quote:
Originally Posted by Hack
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.
Im asking me if Im stupid ??? I found nothing clear, maybe because Im a french person...Quote:
Originally Posted by techgnome
http://search.msdn.microsoft.com/sea...AND+ADO+AND+VB
Could you give me a little example...Sorry if I disturb you with this...
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
Cool, thanks !Quote:
Originally Posted by techgnome
Here it's my code, sorry some words are in french...Quote:
Originally Posted by techgnome
I got an error on this line (bold and underlined) ???
Thanks again !Code: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
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 !
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
VB Code:
Then set the command settings
VB Code:
Now create your parameter and append it
VB Code:
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:
VB Code:
-tg
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...
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