Parameterized queries

[Chris]

Hi, I am using ADO against an Sql Server 2000 backend. My problem is
I'm trying to use a dynamic sql query as part of an import csv process.
The final stage after matching field names, etc is to build a
parameterized query.

Unfortunatly when it comes to executing the command all I get is the
error "Must declare the variable @name". From what I could find it
seems that ADO will not support parameterized queries.

Could anyone please advise if the know of a way to get this to work
with ADO or an alternative.

Thanks, Chris.

 

[Sylvain Lafontaine]

Maybe a little piece of code of what you have tried to do will help us in
assisting you.

 

[Chris]

Here is the code I wanted to run, to solve the problem I've had to
write a stored procedure which seems to work.
But this has the disadvantage that I need a stored procedure for every
table I want to be able to import into, instead of being able to use a
generic method that matches fields from the csv file to the sql table.

'''''''''''''''''''''''''
''' Create parameters
''' and SQL
'''''''''''''''''''''''''
For index = 0 To field_count
Set cmd_params(index) = Nothing
Next

For index = LBound(matched_field_indices) To
UBound(matched_field_indices)
Set param = insert_cmd.CreateParameter( _
name:="@" & target_fields(index), _
Direction:=adParamInput, _
Type:=adVarChar, _
Size:=8000)
insert_cmd.Parameters.Append param

Let sql_fields = sql_fields & "," & target_fields(index)
Let sql_params = sql_params & "," & param.name

Set cmd_params(matched_field_indices(index)) = param
Next
'Trim leading ,
Let sql_fields = Mid(sql_fields, 2)
Let sql_params = Mid(sql_params, 2)

Let sql = "INSERT INTO " & target_table & " (" & sql_fields & ")
VALUES (" & sql_params & ")"
Set insert_cmd.ActiveConnection = target_conn
Let insert_cmd.CommandText = sql
Let insert_cmd.CommandType = adCmdText

rs.MoveNext
While Not rs.EOF
For index = 0 To field_count
If Not cmd_params(index) Is Nothing Then
cmd_params(index) = rs(index).value
End If
Next
insert_cmd.Execute options:=adExecuteNoRecords
rs.MoveNext
Wend
rs.Close

 

[Sylvain Lafontaine]

Usually, named parameters are for stored procedures. With ODBC, it was
possible to use unnamed parameters but it's the first time that I see
someone trying to use named parameters with a SQL-String and ADO.

I will make some tests tonight but in the meantime, I would suggest that you
drop the use of parameters if you want to dynamically build your SQL Insert
Statement.

 

[Chris]

That's one option but does that not then raise the issue of sql
injection attacks? Hence why I turned it into a stored procedure.

 

[Sylvain Lafontaine]

Yes, preventing injection attacks is another reason why to replace your
INSERT INTO sql statement with a stored procedure. (I forgot to mention
that with ADO.NET, it is now possible to named parameters not only for
stored procedures but also for regular sql strings.)

However, if you want a trully secure system, don't forget then even when the
values has been stored inside the database, there is still the possibility
of having injection everywhere you will build a dynamic sql string; even if
you are using values from a table field.