StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Command cannot be converted to string
text
Body
copied!<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><asp:DataGrid id="dgBookings" runat="server" AutoGenerateColumns="true" ShowHeader="true"> </asp:DataGrid> </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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload