ASP Classic, Passing Null to a stored Procedure

[DEPearson]

I am having a problem passing NULL to a stored procedure on a MS SQL server. The code is ASP classic.

I builded the sql Command first then remoteDB.Connection.Execute(sqlCommand, ,adCmdText)

My asp classic code to build the sqlCommand
sqlCommand = "procWebAddProspectOwner " & "1,25," & "'" & "HHTitle" & "','" & "HHFirstName" &"NULL"& "," &"NULL"

The response write to the web browser for debuging shows this
procWebAddProspectOwner 1,25,'HHTitle','HHFirstName',NULL,NULL

ADODB.Connection error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.


To trouble shoot I cut and paste this from the asp page into the SQL Query Analyer and it works great.
procWebAddProspectOwner 1,25,'HHTitle','HHFirstName',NULL,NULL

any ideas:
David

[Newsgroup_User · Accepted solution]

"DEPearson" <webforumsuser@macromedia.com> wrote in message
news:f2hspv$8h1$1@forums.macromedia.com...
> ADODB.Connection error '800a0bb9'
> Arguments are of the wrong type, are out of acceptable range, or are in
> conflict with one another.
>

That's generally a missing connection string. Make sure the connection
string is there, that you've spelled it right, and that your Connections
folder is on the server.

[DEPearson]

The connection string is there, and looks good.
I ran a response.Write("<br/> connection is " & remoteDB.State & "<br>")
The results was 1 for good connection.

[DEPearson]

Here is the connection with command

'Create the object
Set remoteDB = Server.CreateObject("ADODB.Connection")

'Open the connection
strConnection = "Provider=SQLOLEDB.1;Server=XXXXXX.XXXXX.XXX,1435;Uid=XXXXXX;Pwd=XXXXXXX;Database=XXXXXXXXXX"
response.Write(strConnection)
remoteDB.Open strConnection
response.Write("<br/> connection is " & remoteDB.State & "<br>")
'remoteDB.State is returning 1 for good connection
'Insert the record here
sqlCommand = "procWebAddProspectOwner " & "1,25," & "HHTitle" & "','" & "HHFirstName" & "','" & "HHLastName" & "," &"NULL"& "," &"NULL"& "," &"NULL"& "," &"NULL"
response.Write(sqlCommand)
Set rs = remoteDB.Connection.Execute(sqlCommand, ,adCmdText)
'
The error is generated here. I take and copy the response.write output to SQL Query Analyer and the command works good.


'Close the connection
remoteDB.Close
response.Write("<br/> connection is " & remoteDB.State)
'If I comment out the sql command execute this State returns 0 indicating the connection is closed.

'Destroy the connection
set remoteDB = Nothing

[DEPearson]

I just found the answer, the code above is for retrieving a record set where I have Set rs = etc.... Replace that line with remoteDB.Execute(sqlCommand).

Appears to work now, have to check distance end.

Thanks Lionstone, you got me thinking in the right direction.

[Newsgroup_User]

Glad you got it working. :)

"DEPearson" <webforumsuser@macromedia.com> wrote in message
news:f2i2qn$fsp$1@forums.macromedia.com...
>I just found the answer, the code above is for retrieving a record set
>where I
> have Set rs = etc.... Replace that line with
> remoteDB.Execute(sqlCommand).
>
> Appears to work now, have to check distance end.
>
> Thanks Lionstone, you got me thinking in the right direction.
>