Save CommandText in a string variable in VB.NET

Hi all.

I have the following vb.net code that populates a datagrid (I didn't include that part of the code), I would also like to save it in a string variable that will later be used to export to Excel:
Dim con As New SqlConnection
        Dim cmd As New SqlCommand

con.ConnectionString = "Data Source=myServer;Initial Catalog=myDB;Integrated Security=True"

        con.Open()

        cmd.Connection = con

        cmd.CommandText = "SELECT Division, CustomerNo, SalesOrderNumber,InvoiceNumber, InvoiceDate, CustomerPO,ShipToName, ShipToCity, ShipToState, ShipToZipCode,tblFreightFactor.Carrier,  ShipmentType FROM tblFreightFactor INNER JOIN tblFreightFactor_Carriers ON tblFreightFactor.Carrier = tblFreightFactor_Carriers.Carrier WHERE  ( CarrierName = @FreightFactorCriteria) And (InvoiceDate >= @InvoiceStartDate) And (InvoiceDate <= @InvoiceEndDate) ORDER BY InvoiceDate"
        cmd.Parameters.AddWithValue("@FreightFactorCriteria", cmbFreightFactorCriteria.Text)
        cmd.Parameters.AddWithValue("@InvoiceStartDate", txtInvoiceStartDate.Text)
        cmd.Parameters.AddWithValue("@InvoiceEndDate", txtInvoiceEndDate.Text)
        
        Dim sda As New SqlDataAdapter(cmd)
        Dim dt As New DataTable()

Open in new window


I've tried the following:
Dim sql As String
sql = cmd.CommandText.ToString

Open in new window


But that includes the @parameters instead of the actual parameter. So I don't want the results but the entire SELECT statement from the commandtext with the actual parameters. How can I get this to work?

Thank you in advance.
printmediaAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
Check the type of the parameter before deciding how to replace it with it's value in the SQL statement.
sql = cmd.CommandText.ToString

For Each p As SqlParameter In cmd.Parameters
    
        sql = sql.Replace(p.ParameterName, If(IsNumeric(p.Value),p.Value.ToString(), "'" + p.Value.ToString() + "'"))
    
Next p

Open in new window

0
 
NorieVBA ExpertCommented:
Why do you need the full statement with the parameters replaced with values to export to Excel?
0
 
printmediaAuthor Commented:
To export what appears in the datagridview control to Excel. It's a lot quicker to export especially when there are thousands of records.

Dim oXLApp As Excel.Application 'Declare the object variables
        Dim oXLBook As Excel.Workbook
        Dim oXLSheet As Excel.Worksheet

        oXLApp = New Excel.Application  'Create a new instance of Excel
        oXLBook = oXLApp.Workbooks.Add

        oXLSheet = oXLBook.Worksheets(1)
        oXLApp.Visible = True

        Dim sql As String = "" & [u]here's where I would put the string variable[/u] & ""   

        Dim cn As New ADODB.Connection()
        Dim rs As New ADODB.Recordset()
        Dim cnStr As String
        Dim cmd As New ADODB.Command()

        cnStr = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Integrated Security=SSPI;"

        cn.Open(cnStr)
        cmd.ActiveConnection = cn
        cmd.CommandText = sql
        rs = cmd.Execute
        oXLSheet.Range("A1").CopyFromRecordset(rs)
        rs.Close()
        cn.Close()

Open in new window

0
 
printmediaAuthor Commented:
I've tried the following which works but what if I also have a parameter that is a number, I can't put the single quotes:

sql = cmd.CommandText.ToString

For Each p As SqlParameter In cmd.Parameters
            sql = sql.Replace(p.ParameterName, "'" + p.Value.ToString() + "'")
        Next

Open in new window

0
 
printmediaAuthor Commented:
That did it. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.