reuse CommandText

I have the following code.  I try to reset CommandText SQL based on a condition.   If ct = 1 Then reset CommandText SQL .  But, I don't think it works.  Can I change the SQL inside CommandText like that?  Thanks.
 
     CMD.Connection = conn
        conn.Open()
        CMD.CommandText = "SELECT  count(*) ct FROM  XX where LEASE='" & Request.QueryString("LEASE") & "'"

        da.SelectCommand = CMD
        da.Fill(ds, "ContactRequests")

        Dim ct As Integer = ds.Tables(0).Rows(0)("ct")

        If ct = 1 Then
            CMD.CommandText = "SELECT objID FROM  XX where LEASE='" & Request.QueryString("LEASE") & "'"
            da.SelectCommand = CMD
            da.Fill(ds, "ContactRequests")
            Dim objID As String = ds.Tables(0).Rows(0)("objID").ToString()
end if
   
ewang1205Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
gregg1ep00Connect With a Mentor Commented:
There is a MUCH easier, much more efficient way to do this.  There is no need to use DataAdapters or DataSets if you're retrieving this tiny amount of data.

------------------------------
CMD.Connection = conn
CMD.CommandText = "SELECT count(*) ct FROM XX where LEASE='" & Request.QueryString("LEASE") & "'"

conn.Open()
Dim ct As Integer = CInt( CMD.ExecuteScalar() )

If ct = 1 Then
    CMD.CommandText = "SELECT objID FROM XX where LEASE='" & Request.QueryString("LEASE") & "'"
    Dim objID As String = CStr( CMD.ExecuteScalar() )
End If
------------------------------

Hope that helps!
Greg
0
 
gregg1ep00Commented:
Oops, forgot to close the connection.  After the code above, add this line:

conn.Close()
0
 
ewang1205Author Commented:
gregg1ep00:  That is great.  Faster and simpler and working great!  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.