Parameterized query - error when executing a query

Question

I've recently been trying to figure out a way to get parameterized queries to work and i think i'm almost there but seem to be getting an error when executing my query

Here's the code

Dim LogData2 As sterm.MarkData = New sterm.MarkData()

Dim query As String = ("Select * from openquery (db, 'SELECT * FROM table WHERE investor=@investor')")

Dim cmd As New SqlCommand(query)
cmd.Parameters.AddWithValue("@investor", 34)

Dim drCode2a As DataSet = LogData2.StermQ3(query)

I've debugged it and it doesn't seem to be putting the parameter into the query.

"Select * from openquery (db, 'SELECT * FROM table WHERE investor=@investor')"

That's what i get when i debug the line Dim drCode2a As DataSet = LogData2.StermQ3(query)

Any ideas what i'm doing wrong?

SOLUTION

Here's how I solved my problem

Dim conn As SqlConnection = New SqlConnection("server='h'; user id='w'; password='w'; database='w'; pooling='false'")

conn.Open()

Dim query As New SqlCommand("DECLARE @investor varchar(10), @sql varchar(1000) Select @investor = 69836 select @sql = 'SELECT * FROM OPENQUERY(db,''SELECT * FROM table WHERE investor = ''''' + @investor + ''''''')' EXEC(@sql)", conn)

dgBookings.DataSource = query.ExecuteReader
dgBookings.DataBind()

Thanks for the help

John Hartsock · Accepted Answer · 2010-11-02 16:29:13Z

1

Ok Jamie taylor I will try to answer your question again.

You are using OpenQuery becuase you are probably using a linked DB

Basically the problem is the OpenQuery Method takes a string you cannot pass a variable as part of the string you sent to OpenQuery.

You can format your query like this instead. The notation follows servername.databasename.schemaname.tablename. If you are using a linked server via odbc then omit databasename and schemaname, as illustrated below

    Dim conn As SqlConnection = New SqlConnection("your SQL Connection String")
    Dim cmd As SqlCommand = conn.CreateCommand()
    cmd.CommandText = "Select * db...table where investor = @investor"
    Dim parameter As SqlParameter = cmd.CreateParameter()
    parameter.DbType = SqlDbType.Int
    parameter.ParameterName = "@investor"
    parameter.Direction = ParameterDirection.Input
    parameter.Value = 34

edited Nov 2, 2010 at 16:29

answered Nov 2, 2010 at 15:52

John Hartsock

87.1k23 gold badges135 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Jamie Taylor Over a year ago

I've done that but without the ... and I get must declare @investor and if i do it with the ... then I get invlaid column prefix

Jamie Taylor Over a year ago

I'm now getting Property access must assign to the property or use its value.

John Hartsock Over a year ago

@Jamie Taylor ... Set the parameter direction

Jamie Taylor Over a year ago

I'm now getting 'SqlDataParameter' is not defined.

John Hartsock Over a year ago

@Jamie Taylor ... I found some typos in the code I wrote but what is there now works, as I just tested it.

|

Collectives™ on Stack Overflow

Parameterized query - error when executing a query

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related