how to do dynamic sql query in vb.net?

Question

i'm trying to do this dynamic sql query in vb.net where i declare two variable and pass a SELECT on each variable

 Dim cmd As New SqlCommand("Declare @val1 int " & _
                           "Declare @val2 int " & _
                           "SET @val1 = 'SELECT Count(*) from table1tbl'" & _
                           "SET @val2 = 'SELECT Count(*) from table2tbl'", conn)
    Dim rdr As SqlDataReader = cmd.ExecuteReader
    rdr.Read()
    Dim str1 = rdr("@val1")
    Dim str2 = rdr("@val2")
    rdr.Close()

i know this code is not right..so how to makes this right ? tnx in advance

Liam · Accepted Answer · 2014-06-26 05:13:59Z

2

You can't do that from a sqlcommand, so to execute your code you'd have to create a stored procedure and add to the end:

Select @val1, @val2

To return the values as a table that you are creating.

I assume that you are doing something more creative than your actual example, but is there anyway of including everything in your select statement? A correct version should go something like this:

Select (select count(*) from table1tbl), (select count(*) from table2tbl)

That will return the data in the format you want so that you can read it with the code you have posted.

answered Jun 26, 2014 at 5:13

Liam

5,5463 gold badges33 silver badges42 bronze badges

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

Comments

Ibra · Accepted Answer · 2014-06-26 05:24:12Z

1

Dim sQuery as String
Dim val1 
Squery = <a> SELECT Count(*) from table1tbl </a>.Value
Using cmd As New SqlCommand(sQuery, con)
  If cmd.Connection.State <> ConnectionState.Open Then
    cmd.Connection.Open()
  End If
val1 = cmd.ExecuteScalar()
End Using

answered Jun 26, 2014 at 5:24

Ibra

19011 bronze badges

2 Comments

user3211476 Over a year ago

what's with that <a> tag thing ?

Ibra Over a year ago

'its a Multy line string ex: Dim var1 As String var1 = "ABC" Dim str = <a> Hello it's the first line And you can do this <%= var1 %> </a>

Collectives™ on Stack Overflow

how to do dynamic sql query in vb.net?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related