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

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. — Tim Schmelter
– Tim Schmelter, Commented Oct 28, 2010 at 10:47

Tim Schmelter · Accepted Answer · 2010-11-01 19:36:13Z

3

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"

edited Nov 1, 2010 at 19:36

answered Oct 28, 2010 at 10:54

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

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

25 Comments

Jamie Taylor Over a year ago

I don't actually know how to debug it - i literally know nothing about ASP.Net i've done some queries and that's about it i've been thrown in the deep end with this stuff after a guy recently left

Tim Schmelter Over a year ago

Set a breakpoint in Visual Studio by clicking leftmost in the source-code window at the line you want to stop(Dim drCode2a As DataSet = LogData2.StermQ3(query) ). Then start the application and wait till it stops there. Now you can check variables etc. by hovering them, open the quick watch window(right-click on a variable) or add watches to see directly when the values are changing. odetocode.com/Articles/425.aspx

Jamie Taylor Over a year ago

I don't seem to have Visual Studio on my PC i'm guessing this is a necessity if i'm going to be able to debug?

Tim Schmelter Over a year ago

What IDE do you have? VS 2010 Express is for free: microsoft.com/express/downloads

Jamie Taylor Over a year ago

All I know is that I do everything in Dreamweaver no other program

|

Andrew Bezzub · Accepted Answer · 2010-10-28 09:31:52Z

0

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.

answered Oct 28, 2010 at 9:31

Andrew Bezzub

16.1k7 gold badges54 silver badges73 bronze badges

2 Comments

Jamie Taylor Over a year ago

To be honest I have no idea what it does - i've taken over someone else's work so doing it as they did - how can i save my query in a different variable and pass it? Thanks

Andrew Bezzub Over a year ago

I think the best way to make something working right is to understand what it is doing. So you should try to find out...

Maksim Vi. · Accepted Answer · 2010-10-28 16:40:15Z

0

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.

answered Oct 28, 2010 at 16:40

Maksim Vi.

9,24512 gold badges62 silver badges85 bronze badges

2 Comments

Jamie Taylor Over a year ago

By doing this it gives me a syntax error - its converting the ' before SELECT to a '' and erroring

Maksim Vi. Over a year ago

Completely forgot about that, just ignore the last part of my answer then =) Otherwise rewrite StermQ3 function and make it take sqlcommand instead of string.

John Hartsock · Accepted Answer · 2010-11-02 16:30:39Z

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

Collectives™ on Stack Overflow

SQL Command cannot be converted to string

4 Answers 4

25 Comments

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

25 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related