title: SQL Command cannot be converted to string
body: <p>Here's my code</p>
  
  <p>I seem to be getting an error <code>SQL command cannot be converted to string</code> with this code </p>
  
  <pre><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()
  </code></pre>
  
  <p>I tried adding in a <code>Convert.ToString</code> like this</p>
  
  <pre><code>Dim drCode2a As DataSet = LogData2.StermQ3(Convert.ToString(query))
  </code></pre>
  
  <p>And now I get this error</p>
  
  <pre><code>Could not find server 'System' in sysservers. Execute sp_addlinkedserver to add the server to sysservers
  </code></pre>
  
  <p>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.</p>
  
  <p>Source code of StermQ3</p>
  
  <pre><code>StermQ3(String) As System.Data.DataSet
  
  Public Function StermQ3(ByVal strSQL6 As String) As System.Data.DataSet
  </code></pre>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Source code as it is after some changes have been made</p>
  
  <pre><code>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
  </code></pre>
  
  <p>HTML is </p>
  
  <pre><code>&lt;asp:DataGrid id="dgBookings" runat="server" AutoGenerateColumns="true" ShowHeader="true"&gt;
  
  &lt;/asp:DataGrid&gt;
  </code></pre>
  
  <p>Old way I used to do my query</p>
  
  <pre><code>Dim query As String = "Select * from openquery (db, 'SELECT * FROM table WHERE investor=''" + Session("number") + "'' ')"
  </code></pre>
  
  <p>That works if i replace my SQL Command but it is open to SQL Injection</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>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?</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>It works like this</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>Any ideas?</p>
  
  <p><strong>SOLUTION</strong></p>
  
  <p>Here's how i solved my issue</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>Thanks for all the help</p>