Solved

reuse CommandText

Posted on 2006-07-07
3
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

718 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