Classic ASP ADODB with SQL Server Compact Edition 3.5 is not working in Command Parameters

Question

Hi All,

I'm new to SQL CE. I'm programming in Classic ASP, connecting the SQL CE using ADODB.Connection. I have created the table and trying to insert data from the ASP. I tried in 3 ways.

1. The inline insert statement [e.g. INSERT INTO tblName(col1, col2) VALUES(1,2)] (WORKED)
2. The parameterized insert statement [e.g. INSERT INTO tblName(Col1) VALUES(?)] (WORKED). I added the Command Parameter and supplied the value.
3. The parameterized insert statement with more than one param ( FAILED)

I dont know what wrong with multiple parameters. It throwing me the unhandled error when the Cmd.Execute statement runs.

"The remote procedure call failed and did not execute."

I did lots of Google to find out the issue. But no use. I didn't get any clues.

Please help me to solve this issue

-Ganesh

Answer 1

have you set the CommandType property of the Command object to adCmdText?

cmd.CommandType = adCmdText

thats how i do this:

dim sql : sql = "insert into tbl(fld1, fld2) values(?, ?)"
dim cmd : set cmd = server.createObject("ADODB.Command")
cmd.ActiveConnection = adodbConnection
cmd.CommandType = adCmdText

set param = cmd.CreateParameter("fld1", adVarWChar, , 20, "value1")
cmd.Parameters.Append param
set param = cmd.CreateParameter("fld2", adVarWChar, , 20, "value2")
cmd.Parameters.Append param

cmd.CommandText = sql
cmd.Execute