SQL error "Arguments are of the wrong type ..."

Hi

I have a web page which receives information from a form (using
request.form) and also attempts to look at an Access query to read in
recoeds to a variable named rsGroup.

When I have the following line commented, so it does not activate, I
can successfully print all of the response.write's below:

rsGroup.Open strSQL, adoCon

response.write(strSQL)
response.write("

")
response.write(GroupSend)
response.write("

")
response.write(request.form("GroupName"))
response.write("

")
response.write(" " & request.form("Subject"))
response.write("

")
response.write(" " & request.form("Content"))

response.write(strSQL) prints as:
SELECT qrySendemail*GroupName FROM qrySendemail WHERE GroupName =
'FOOTBALL'
and works fine if I use it directly in an access query

However when I uncomment

rsGroup.Open strSQL, adoCon

I get the following error message

"Arguments are of the wrong type, are out of acceptable range, or are
in conflict with one another"

You will see I have several alternative SQL's (currently
commented)which I have tried, all work OK when rsGroup.Open strSQL,
adoCon is commented.

I have several other pages using similar ado connections and generally
similar code, which work OK.

Any advice would be appreciated

Note - I do realise I am querying an access query not a table; I will
not be updating or deleting records in the query.

Thanks ColinK

Code:

<%@ Language=VBScript %>

<%
'Dimension variables
Dim objDC 'Holds the Database Connection Object
Dim strSQL 'Holds the SQL query to query the database
Dim rsGroup 'Holds the details of group members
Dim GroupSend 'holds the group name to send to

'insert the Group name to send to into the variable GroupSend
GroupSend = (Request.Form("GroupName"))

'Create an ADO connection object on the server using the ADO Database
connection object
Set objDC = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a ****
DSN-less connection ****
objDC.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("../databases/GC1.mdb")

'Create an ADO recordset object
Set rsGroup = Server.CreateObject("ADODB.Recordset")

' request record for requested Groupid

'NOTE first 2 sql's are currently commented
'strSQL = "SELECT * FROM qrySendemail WHERE GroupName = '" &
Request.Form("GroupName") &"' "
'strSQL = "SELECT * FROM qrySendemail WHERE GroupName = 'FOOTBALL' "
strSQL = "SELECT qrySendemail*GroupName FROM qrySendemail WHERE
GroupName = 'FOOTBALL' "


'Set the cursor type we are using so we can navigate through the
recordset
rsGroup.CursorType = 2

'Open the recordset with the SQL query
'rsGroup.Open strSQL, adoCon
'rsGroup.Close

response.write(strSQL)
response.write("

")
response.write(GroupSend)
response.write("

")
response.write(request.form("GroupName"))
response.write("

")
response.write(" " & request.form("Subject"))
response.write("

")
response.write(" " & request.form("Content"))
%>

Re: SQL error "Arguments are of the wrong type ..."

Among the myriad other problems, adoCon is not defined / instantiated
anywhere. Maybe you should try objDC instead.

BTW - What's the difference between an "ado" and an "obj" prefix?

Bob Lehmann

wrote in message
news:1122850363.408184.323800@g43g2000cwa.googlegroups.com.. .
> Hi
>
> I have a web page which receives information from a form (using
> request.form) and also attempts to look at an Access query to read in
> recoeds to a variable named rsGroup.
>
> When I have the following line commented, so it does not activate, I
> can successfully print all of the response.write's below:
>
> rsGroup.Open strSQL, adoCon
>
> response.write(strSQL)
> response.write("

")
> response.write(GroupSend)
> response.write("

")
> response.write(request.form("GroupName"))
> response.write("

")
> response.write(" " & request.form("Subject"))
> response.write("

")
> response.write(" " & request.form("Content"))
>
> response.write(strSQL) prints as:
> SELECT qrySendemail*GroupName FROM qrySendemail WHERE GroupName =
> 'FOOTBALL'
> and works fine if I use it directly in an access query
>
> However when I uncomment
>
> rsGroup.Open strSQL, adoCon
>
> I get the following error message
>
> "Arguments are of the wrong type, are out of acceptable range, or are
> in conflict with one another"
>
> You will see I have several alternative SQL's (currently
> commented)which I have tried, all work OK when rsGroup.Open strSQL,
> adoCon is commented.
>
> I have several other pages using similar ado connections and generally
> similar code, which work OK.
>
> Any advice would be appreciated
>
> Note - I do realise I am querying an access query not a table; I will
> not be updating or deleting records in the query.
>
> Thanks ColinK
>
> Code:
>
> <%@ Language=VBScript %>
>
> <%
> 'Dimension variables
> Dim objDC 'Holds the Database Connection Object
> Dim strSQL 'Holds the SQL query to query the database
> Dim rsGroup 'Holds the details of group members
> Dim GroupSend 'holds the group name to send to
>
> 'insert the Group name to send to into the variable GroupSend
> GroupSend = (Request.Form("GroupName"))
>
> 'Create an ADO connection object on the server using the ADO Database
> connection object
> Set objDC = Server.CreateObject("ADODB.Connection")
>
> 'Set an active connection to the Connection object using a ****
> DSN-less connection ****
> objDC.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("../databases/GC1.mdb")
>
> 'Create an ADO recordset object
> Set rsGroup = Server.CreateObject("ADODB.Recordset")
>
> ' request record for requested Groupid
>
> 'NOTE first 2 sql's are currently commented
> 'strSQL = "SELECT * FROM qrySendemail WHERE GroupName = '" &
> Request.Form("GroupName") &"' "
> 'strSQL = "SELECT * FROM qrySendemail WHERE GroupName = 'FOOTBALL' "
> strSQL = "SELECT qrySendemail*GroupName FROM qrySendemail WHERE
> GroupName = 'FOOTBALL' "
>
>
> 'Set the cursor type we are using so we can navigate through the
> recordset
> rsGroup.CursorType = 2
>
> 'Open the recordset with the SQL query
> 'rsGroup.Open strSQL, adoCon
> 'rsGroup.Close
>
> response.write(strSQL)
> response.write("

")
> response.write(GroupSend)
> response.write("

")
> response.write(request.form("GroupName"))
> response.write("

")
> response.write(" " & request.form("Subject"))
> response.write("

")
> response.write(" " & request.form("Content"))
> %>
>

Re: SQL error "Arguments are of the wrong type ..."

iam247@gmail.com wrote:
> Hi
>
> I have a web page which receives information from a form (using
> request.form) and also attempts to look at an Access query to read in
> recoeds to a variable named rsGroup.
>
> When I have the following line commented, so it does not activate, I
> can successfully print all of the response.write's below:
>

> However when I uncomment
>
> rsGroup.Open strSQL, adoCon
>
> I get the following error message
>
> "Arguments are of the wrong type, are out of acceptable range, or are
> in conflict with one another"
>


This error has nothing to do with the SQL you are sending. It's got
everything to do with the argument you are using for your database
connection. In this section, you create a connection named "objDC":

> 'Create an ADO connection object on the server using the ADO Database
> connection object
> Set objDC = Server.CreateObject("ADODB.Connection")
>
> 'Set an active connection to the Connection object using a ****
> DSN-less connection ****
> objDC.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("../databases/GC1.mdb")
>


And here, you try to use an undefined variable named "adoCon":
> 'Open the recordset with the SQL query
> 'rsGroup.Open strSQL, adoCon

which results in the expected "arguments of the wrong type ..." error.


A better connection string would be:

objDC.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Server.MapPath("../databases/GC1.mdb")

Avoid using the obsolete ODBC driver

I'm sorry to see you are still using the insecure and inefficient dynamic
sql technique. My preference would be to parameterize the qrySendemail query
and call it like:

Set rsGroup = CreateObject("ADODB.Recordset")
objDC.qrySendemail Request.Form("GroupName"),rsGroup

HTH,
Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"