Can't use Variables in SQL Datasource when using Union?

[Bishop]

Is this true or am I missing something?

I use variables and unions all the time, maybe this is the first time I've
tried them together in a VB SQL datasource.

 

[Rich P]

Depends on what you are doing:

Imports System
Imports System.Data.SqlClient

Private Sub GetUnionData()

Dim da As New SqlDataAdapter, conn As New SqlConnection
Dim strSql As String, ds As New Dataset

conn.ConnectionString = "Data Source=yourSvr;" _
& "DATABASE=yourDB;Integrated Security=True"

da.SelectCommand = New SqlCommand
da.SelectCommand.Connection = conn
strSql = "Select * From tbl1 Where fldx = @P1 "
strSql &= "Union All Select * From tbl2 where fldx = @P2"

da.SelectCommand.Parameters.AddNew SqlParameter("@P1",
SqlDbType.VarChar, 2, "fldx"))

da.SelectCommand.Parameters.AddNew SqlParameter("@P2",
SqlDbType.VarChar, 2, "fldx"))

da.Fill(ds, "localTbl")
Datagridview1.DataSource = ds.Tables("localTbl")

End Sub

In this sample I have to vars aiming at the same columnname. haven't
tried it before so don't know if it would work. Might be able to leave
out the ColumnName reference in the Param statement.


Rich