SQL Command cannot be converted to string

Question

Here's my code

I seem to be getting an error SQL command cannot be converted to string with this code

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

Dim query As New SqlCommand("Select * from openquery (db, 'SELECT * FROM table WHERE person=@person')")
query.Parameters.AddWithValue("@person", Session("number"))

Dim drCode2a As DataSet = LogData2.StermQ3(query)

dgBookings.DataSource = drCode2a.Tables(0).DefaultView
dgBookings.DataBind()

I tried adding in a Convert.ToString like this

Dim drCode2a As DataSet = LogData2.StermQ3(Convert.ToString(query))

And now I get this error

Could not find server 'System' in sysservers. Execute sp_addlinkedserver to add the server to sysservers

I'm obviously doing something wrong but not sure what - ASP.Net and SQL is all new to me so any help would be much appreciated.

Source code of StermQ3

StermQ3(String) As System.Data.DataSet

Public Function StermQ3(ByVal strSQL6 As String) As System.Data.DataSet

UPDATE

Source code as it is after some changes have been made

Sub Page_Load(ByVal Sender as Object, ByVal e as EventArgs)

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

Dim query As New SqlCommand("Select * from openquery (db, 'SELECT * FROM table WHERE person=@person')") 
query.Parameters.AddWithValue("@person", Session("number")) 
query.CommandType = CommandType.StoredProcedure
query.CommandText = "openquery"

Dim drCode2a As DataSet = LogData2.StermQ3(Convert.ToString(query)) 

dgBookings.DataSource = drCode2a.Tables(0).DefaultView 
dgBookings.DataBind()

End Sub

HTML is

<asp:DataGrid id="dgBookings" runat="server" AutoGenerateColumns="true" ShowHeader="true">

</asp:DataGrid>

Old way I used to do my query

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

That works if i replace my SQL Command but it is open to SQL Injection

UPDATE

I now have it so it work without the parameters bit here's my updated souce code any idea why it won't add the parameter value?

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


Dim query As New SqlCommand("Select * from openquery (db, 'Select * from table where investor = @investor ') ", conn)
query.Parameters.AddWithValue("@investor", 69836)

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

It works like this

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


Dim query As New SqlCommand("Select * from openquery (db, 'Select * from table where investor = 69836') ", conn)

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

Any ideas?

SOLUTION

Here's how i solved my issue

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 all the help

Answer 1

What LogData2.StermQ3 method does? My guess that it logs query your are going to execute. If it is so, than you should save your query in different variable and pass it to the method.

Answer 2

Have you debugged to find out what StermQ3 does? You are passing the string reference from an SqlCommand-Object to it what might be nonsense. I think you need its CommandText Property, but you have a problem because you don't get the actually SQL Text what is executed on the database. The parameters are evaluated in the Database and not here.

What you can try is following:

 Dim commandText As String = query.CommandText
 For Each parameter As System.Data.SqlClient.SqlParameter In query.Parameters
     commandText=commandText.Replace(parameter.ParameterName, parameter.Value.ToString)
 Next
 Dim drCode2a As DataSet = LogData2.StermQ3(commandText)

But i don't think that you want that, because you are again open for SQL-Injection(StermQ3 returns a Dataset). I think StermQ3 needs to get an overloaded version that takes a SQLCommand as parameter.

EDIT: I think the SqlCommand does not know that it should execute a StoredProcedure. Set its CommandType and as CommandText the name of the SP:

query.CommandType = CommandType.StoredProcedure
query.CommandText = "openquery"

Answer 3

It looks that the function takes a query, not SQL command. Try the following:

Dim query As String = "Select * from openquery (db, 'SELECT * FROM table WHERE person=" & Session("number") & "')"
Dim drCode2a As DataSet = LogData2.StermQ3(query)

dgBookings.DataSource = drCode2a.Tables(0).DefaultView
dgBookings.DataBind()

Yes, it may not prevent you from SQL injections, so the best you can do in this case is to use

query = query.Replace("'", "''")

before you pass it to that function. In all other cases use parametrized queries.