Parameters via the Command Object

G

Guest

Ladies and Gentleman,

I am attempting to execute an append query with paramaters. In the criteria
section of my query I have a parameter called strSQL. In the Query Parameters
Box I used the data type "Text". I placed [strSQL] in the criteria box.

All of my parameters where I use the datatypeenum "integer" work well. I
continuously get an error when I try to set a parameter using a string. I am
using the datatypeenum adChar and I have already tried using all of the
datatypeenums that can be used with Text. They all produce an error. I also
have the size declared as Long and am using a variable for the string length.
I have tried setting the size to 4 times the length of the string and I still
get an error.

I would appreciate any feedback I can get. I have pasted the code below.

Regards,

Pete

Private Sub Report_Open(Cancel As Integer)

On Error GoTo Err_Report_Open

'Upon opening, this form executes UpdateTable which updates tblSECTIONLIST.
tblSectionList determines the data
'order for rstAgendaData which is the record source for this report.

Dim cmdReportPrint As New ADODB.Command, prmReportPrint As New
ADODB.Parameter, cnn As New ADODB.Connection, _
prmGroupValue As New ADODB.Parameter, frm As Form, intGroupValue As Integer,
intDate As Integer, _
strSQL As String, intNumColumns As Integer, cat As New ADOX.Catalog, col As
ADOX.Column, lngSeed As Long, _
ChangeSeed As Boolean, intI As Integer

Set frm = Forms!frmReportPrint
Let intDate = frm.cboMeetingDate
Let intGroupValue = frm.grpPrintReport.Value
Call UpdateTable

DoCmd.SetWarnings False
Set cnn = CurrentProject.Connection
cnn.Execute "qdfClearReportTable"
DoCmd.SetWarnings True
Let lngSeed = 1
cat.ActiveConnection = cnn
Set col = cat.Tables("tblReportRecSource").Columns("PrimaryKey")
col.Properties("Seed") = lngSeed
cat.Tables("tblReportRecSource").Columns.Refresh
If col.Properties("Seed") = lngSeed Then
ChangeSeed = True
Else: ChangeSeed = False
End If

cnn.Close
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing

Let strSQL = "(((qdfReportRecSource.SA_AlertNumber) Is Null)) OR "
intNumColumns = frm![lstSelectedFlds].ListCount
For intI = 0 To intNumColumns
If frm![lstSelectedFlds].Column(0, intI) > "" Then
If intI = 0 Then
Let strSQL = strSQL & "(((qdfReportRecSource.SA_AlertNumber)=" &
Chr(34) & frm![lstSelectedFlds].Column(0, intI) & Chr(34) & "))"
Else
Let strSQL = strSQL & " OR
(((qdfReportRecSource.SA_AlertNumber)=" & Chr(34) &
frm![lstSelectedFlds].Column(0, intI) & Chr(34) & "))"
End If
End If
Next intI

Let strSQL = strSQL & Chr(59)

Set cmdReportPrint.ActiveConnection = CurrentProject.Connection
cmdReportPrint.CommandType = adCmdText
cmdReportPrint.CommandText = strSQL
Set prmReportPrint = cmdReportPrint.CreateParameter("intDate", adInteger,
adParamInput, , intDate)
cmdReportPrint.Parameters.Append prmReportPrint
Set prmGroupValue = cmdReportPrint.CreateParameter("intGroupValue",
adInteger, adParamInput, , intGroupValue)
cmdReportPrint.Parameters.Append prmGroupValue
Set prmGroupValue = cmdReportPrint.CreateParameter("intSelection",
adInteger, adParamInput, , intMainSelection)
cmdReportPrint.Parameters.Append prmGroupValue
Set prmGroupValue = cmdReportPrint.CreateParameter("strSQL", adChar,
adParamInput, lngPrmSize, strSQL)
cmdReportPrint.Parameters.Append prmGroupValue
cmdReportPrint.Execute

Set prmReportPrint = Nothing
Set prmGroupValue = Nothing
Set cmdReportPrint = Nothing
Let strSQL = ""
Exit_Report_Open:
Exit Sub

Err_Report_Open:
msgbox "Error Number " & Err.Number & " " & Err.Description
Resume Exit_Report_Open

End Sub
 
B

Brendan Reynolds

You appear to be using the variable strSQL for two different purposes here.
You're assigning it to the CommandText property as well as to the parameter.
As far as I can tell, it does not appear that this variable will ever
contain a complete and valid SQL statement suitable for use as the
CommandText property. To verify this (or indeed disprove it if I am
mistaken) try printing the value of the variable to the Immediate Window ...

Debug.Print strSQL '<- add this line just before the existing line below
cmdReportPrint.CommandText = strSQL

What is the result in the Immediate Window?

--
Brendan Reynolds
Access MVP

Pete B. said:
Ladies and Gentleman,

I am attempting to execute an append query with paramaters. In the
criteria
section of my query I have a parameter called strSQL. In the Query
Parameters
Box I used the data type "Text". I placed [strSQL] in the criteria box.

All of my parameters where I use the datatypeenum "integer" work well. I
continuously get an error when I try to set a parameter using a string. I
am
using the datatypeenum adChar and I have already tried using all of the
datatypeenums that can be used with Text. They all produce an error. I
also
have the size declared as Long and am using a variable for the string
length.
I have tried setting the size to 4 times the length of the string and I
still
get an error.

I would appreciate any feedback I can get. I have pasted the code below.

Regards,

Pete

Private Sub Report_Open(Cancel As Integer)

On Error GoTo Err_Report_Open

'Upon opening, this form executes UpdateTable which updates
tblSECTIONLIST.
tblSectionList determines the data
'order for rstAgendaData which is the record source for this report.

Dim cmdReportPrint As New ADODB.Command, prmReportPrint As New
ADODB.Parameter, cnn As New ADODB.Connection, _
prmGroupValue As New ADODB.Parameter, frm As Form, intGroupValue As
Integer,
intDate As Integer, _
strSQL As String, intNumColumns As Integer, cat As New ADOX.Catalog, col
As
ADOX.Column, lngSeed As Long, _
ChangeSeed As Boolean, intI As Integer

Set frm = Forms!frmReportPrint
Let intDate = frm.cboMeetingDate
Let intGroupValue = frm.grpPrintReport.Value
Call UpdateTable

DoCmd.SetWarnings False
Set cnn = CurrentProject.Connection
cnn.Execute "qdfClearReportTable"
DoCmd.SetWarnings True
Let lngSeed = 1
cat.ActiveConnection = cnn
Set col = cat.Tables("tblReportRecSource").Columns("PrimaryKey")
col.Properties("Seed") = lngSeed
cat.Tables("tblReportRecSource").Columns.Refresh
If col.Properties("Seed") = lngSeed Then
ChangeSeed = True
Else: ChangeSeed = False
End If

cnn.Close
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing

Let strSQL = "(((qdfReportRecSource.SA_AlertNumber) Is Null)) OR "
intNumColumns = frm![lstSelectedFlds].ListCount
For intI = 0 To intNumColumns
If frm![lstSelectedFlds].Column(0, intI) > "" Then
If intI = 0 Then
Let strSQL = strSQL & "(((qdfReportRecSource.SA_AlertNumber)="
&
Chr(34) & frm![lstSelectedFlds].Column(0, intI) & Chr(34) & "))"
Else
Let strSQL = strSQL & " OR
(((qdfReportRecSource.SA_AlertNumber)=" & Chr(34) &
frm![lstSelectedFlds].Column(0, intI) & Chr(34) & "))"
End If
End If
Next intI

Let strSQL = strSQL & Chr(59)

Set cmdReportPrint.ActiveConnection = CurrentProject.Connection
cmdReportPrint.CommandType = adCmdText
cmdReportPrint.CommandText = strSQL
Set prmReportPrint = cmdReportPrint.CreateParameter("intDate", adInteger,
adParamInput, , intDate)
cmdReportPrint.Parameters.Append prmReportPrint
Set prmGroupValue = cmdReportPrint.CreateParameter("intGroupValue",
adInteger, adParamInput, , intGroupValue)
cmdReportPrint.Parameters.Append prmGroupValue
Set prmGroupValue = cmdReportPrint.CreateParameter("intSelection",
adInteger, adParamInput, , intMainSelection)
cmdReportPrint.Parameters.Append prmGroupValue
Set prmGroupValue = cmdReportPrint.CreateParameter("strSQL", adChar,
adParamInput, lngPrmSize, strSQL)
cmdReportPrint.Parameters.Append prmGroupValue
cmdReportPrint.Execute

Set prmReportPrint = Nothing
Set prmGroupValue = Nothing
Set cmdReportPrint = Nothing
Let strSQL = ""
Exit_Report_Open:
Exit Sub

Err_Report_Open:
msgbox "Error Number " & Err.Number & " " & Err.Description
Resume Exit_Report_Open

End Sub
Click to expand...
 
Top