ADO Command Parameter example

[aco636]

Hi there
As always any help appreciated. Have been using vbs to manage database for sometime but only recently realised its possible to use parameter queries with vbs. Something I thought was the luxury of vb.NET C#, etc.

However looking for a breif example of how I might use this in an insert statement

Example follows.
Set cn = ADODB Connection 'short for example
Set cmd = ADODB Command
set parameter = cmd.CreateParameter
'This is where I get confused

'FixedValueField is alays the same text
INSERT INTO MyTable1 (VariableStringField, FixedStringFiled, MemoField)
VALUES(?, 'TestString', ?)

How to Build Parameter and then how to populate it and finally how to use it.

If I am inserting, doo I have to create a parameter even for known values?

Regards ACO

 

[fstrahberger]

Hi,
maybe this code will help you (access database)

'------------------------------------------------------------
' create connection object -> here we use access
'------------------------------------------------------------
Dim oCn: Set oCn = Wscript.CreateObject("ADODB.Connection")
oCn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb")

'-----------------------------------------------------------
' create sql statement and prepare (compile) it
'-----------------------------------------------------------
Dim strSql: strSql = "INSERT INTO tblCustomer (ID, Name, Notes) VALUES ('1', ?, 'xxx');"
Dim oCmd: Set oCmd = WScript.CreateObject("ADODB.Command")
oCmd.ActiveConnection = oCn
oCmd.CommandText = strSql
oCmd.Prepared = True

'----------------------------------------------------------
' create parameter object and assign to command object
'----------------------------------------------------------
Dim oParam: Set oParam = oCmd.CreateParameter("p_name", 8, 1) ' 8 = adBSTR, 1 = adInput
oCmd.Parameters.Append oParam
oCmd("p_name") = "Hello Parameter"
oCmd.Execute

'---------------------------------------------------------
' cleanup
'---------------------------------------------------------
oCn.Close()
Set oParam = Nothing
Set oCmd = Nothing
Set oCn = Nothing

the problem with vbs is, you don't have the constants defined by ADODB (such as adVarChar). You have to use numbers instead. I suggest you use the Object-Catalogue in Excel to get the values.

 

[aco636]

Hi there
Thanks very much for explaining this.
One other question, what does oCmd.Prepared = True do?
I know it might be ovious from the reading the command but I have not seen/used this argument before?

REgards ACO

 

[fstrahberger]

Hi,

"oCmd.Prepared = True" compiles the statement against the database. This means the statement is only parsed once even if you insert more than one row.

If you insert a second row, the insert will be faster because the statement is already "prepared / compiled". If you only insert one row, you should not use "Prepared = True