CODEWITHSUNDEEP
asp.netsqlvb.net
Jamie Taylor
Jamie Taylor

Reputation: 3530

Parameterized query - error when executing a query

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

Upvotes: 0

Views: 293

Answers (1)

John Hartsock
John Hartsock

Reputation: 86872

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

Upvotes: 1

Related Questions