Solved

reuse CommandText

Posted on 2006-07-07
3
323 Views
Last Modified: 2012-06-21
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
   
0
Comment
Question by:ewang1205
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
gregg1ep00 earned 500 total points
ID: 17061062
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
 
LVL 4

Expert Comment

by:gregg1ep00
ID: 17061085
Oops, forgot to close the connection.  After the code above, add this line:

conn.Close()
0
 

Author Comment

by:ewang1205
ID: 17061157
gregg1ep00:  That is great.  Faster and simpler and working great!  Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question