CODEWITHSUNDEEP
mysqlasp-classicparameter-passing
user3614788
user3614788

Reputation: 1

classic asp parameter query to MYSQL

Hi I am fairly new to MySQL and have tried to follow examples to use parameter queries.

if I simply put a ? in the sql statement the code works, but I want to know how to pass multiple parms so am trying to use a named parameter.

however I get an error

[MySQL][ODBC 5.1 Driver][mysqld-5.1.73-log]Unknown column '56case_id' in 'where clause'

/t3.asp, line 32

you will see commented out several other methods I have tried without success

and help would be great

my code is...

Set connContent = Server.CreateObject("ADODB.Connection") 
connContent.ConnectionString=.....
connContent.Open

Set cmdContent = Server.CreateObject("ADODB.Command")
Set cmdContent.ActiveConnection = connContent
Set rs = Server.CreateObject("ADODB.Recordset")
cmdContent.Prepared = True

Const ad_varChar = 200
Const ad_ParamInput = 1
Const ad_Integer = 3
Const ad_DBDate = 133 
Const ad_DBTimeStamp = 135

'theNumber = 23
'theText = "Hello there!"
'theDate = "2011-10-15"
case_id=56

SQL = " select * from tbl_cases where case_id > ?case_id; "

Set newParameter = cmdContent.CreateParameter("?case_id", ad_Integer, ad_ParamInput, 50, case_id)
cmdContent.Parameters.Append newParameter

'cmdContent.Parameters.Add(new MySqlParameter("case_id",case_id));
'cmdContent.Parameters.AddWithValue ("@Case_id", 3);

cmdContent.CommandText = SQL
set rs=cmdContent.Execute

do until rs.eof
response.write rs.fields("case_id")
rs.movenext
loop


%>

Upvotes: 0

Views: 1190

Answers (2)

Mark F.
Mark F.

Reputation: 21

Named parameters aren't possible but multiple ? parameters work fine. The ?s are used in the order they are created so it really pays to keep the code well organized. Here is a no frills example... you'll see that I put the cmd statement first, immediately followed by the parameters in the order they are needed -- each of which is condensed into one line of code.

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open(connectionString)
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn

cmd.CommandText = "UPDATE metadata SET meta_key=?, meta_value=? WHERE meta_id=?;"
cmd.Parameters.Append cmd.CreateParameter("@meta_key", adVarChar, adParamInput, 255, meta_key)
cmd.Parameters.Append cmd.CreateParameter("@meta_value", adLongVarChar, adParamInput, len(meta_value), meta_value)
cmd.Parameters.Append cmd.CreateParameter("@meta_id", adInteger, adParamInput, 11, meta_id)

cmd.Execute rCount
response.write(rCount & " records affected")
conn.Close

Upvotes: 1

ulluoink
ulluoink

Reputation: 2785

it is not posible to use named Parameters with classic asp/adodb

Upvotes: 0

Related Questions