.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Using query results as parameters

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server
Hello all, i was wondering if there is a way of, only using tsql, solve this: i have a query that returns me a name of a column and i want to use that result like this: "Select (the_name_of_the_returned_column) from mytable"

View Complete Post

More Related Resource Links

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

SqlDataSource and Parameters query


Hi everyone,

I'm on the edge wondering is it possible ? I have got 2 questions. Please help me out.

1. I have a GridView on my page and it uses sqldatasource with parameterized query. What I want to do is, on page load (where nothing has been selected so no parameter supplied), I want it to query everything (something like SELECT * FROM [this_table]) but since my SelectCommand is something like

SELECT * FROM [this_table] WHERE [this_column] = @someParameters AND [that_column] = @someParameters.

Can I play around with default value to achieve something like that but how ? Now, when the page loads, it doesn't show anything (No Gridview).

2. On my page, I made something like (username, gender, address, and more) and one single search button. That means, no single control enable auto postback. What I am trying to accomplish is building dynamic query

(if username specifed -> SELECT * FROM [this_table] WHERE [username] LIKE @username).

If both username and gender are specified (SELECT * FROM [this_table] WHERE [username] LIKE @username AND [gender] = @gender) and you know the rest. How can I do this using GridView and SqlDataSource ? To my knowledge, I can only specify one SELECT statement in a sqldatasource.  I am stucked and desparately looking for help. Thanks all.

Linq query results to List collection



I have a GridView in my page. I want to use below code in code beind. query nothwing DB using linq . add linq query result to new list and then bind list as gridview Datasource

I use below code . But I don't know how to add results to List? Could any one tell me how !

Public Class OrderCity
        Private _name As String
        Private _city As String
        Public Property name As String
                Return _name
            End Get
            Set(ByVal value As String)
                _name = value

            End Set
        End Property
        Public Property city As String
                Return _city
            End Get
            Set(ByVal value As String)
                _city = value

            End Set
        End Property
    End Class
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim dbe As New SFFAV1DataContext
        Dim latestadsDs = From p In dbe.Products _
                            Select New With {p.Title, p.ProducedCity} _
                             Take (2)

        Dim FilteredList As New List(Of OrderCity)()
        For Each result In latestadsDs
            Dim Menedfcou = Nothing
            FilteredList.Insert(0, Menedfcou)

E-mail query results

I have the following piece of code that returns nothing when there is only one row that fits the criteria. If there are two or more rows that match the criteria it works. Any idea what is wrong?? Declare @drugID varchar(4000),@lineresult varchar(4000), @SEND VARCHAR(256), @RECP VARCHAR(256), @TITLE VARCHAR(256), @BODY VARCHAR(4000), @SERVERNAME VARCHAR(256) set @drugID = '' declare r_cursor INSENSITIVE CURSOR FOR SELECT AHI_DRUG_DOSE.Drug_Display_Name+ ' ' +convert(varchar(10),AHI_CAB_EVENT.Station_Id)+ ' ' +convert(varchar(25),AHI_CAB_EVENT.Event_Dttm)+ ' ' + AHI_CAB_EVENT.Event_Type+ ' ' + convert(varchar(10),AHI_CAB_EVENT.Trans_Qty)+ ' ' +convert(varchar(25),AHI_CAB_EVENT.Site_Patient_Id)+ ' ' +AHI_CAB_EVENT.Pat_Name+ ' ' +AHI_CAB_EVENT.Misc1 FROM AHI_DRUG_DOSE AHI_DRUG_DOSE INNER JOIN CRX_DATA.dbo.AHI_CAB_EVENT AHI_CAB_EVENT ON AHI_DRUG_DOSE.Drug_Dose_Id = AHI_CAB_EVENT.Drug_Dose_Id WHERE (AHI_CAB_EVENT."Station_Id" = 'OPS' OR AHI_CAB_EVENT."Station_Id" = 'OR1' OR AHI_CAB_EVENT."Station_Id" = 'OR2' OR AHI_CAB_EVENT."Station_Id" = 'PACU') AND AHI_CAB_EVENT."Event_Type" = 'WASTE' AND AHI_CAB_EVENT."Misc1" <> 'Partial Dose' AND AHI_CAB_EVENT."Event_Dttm" >= get

Search query returning 0 results

I'm using a query which returns 0 results but was working some weeks ago.  I checked the code and nothing has changed since then.  I created a Search Center Site on another port and using the same terms I get many results there.  I checked my scopes and they are all right. Nothing changed there either. Could someone help me figure out what may be the problem in my query.  Or maybe it's my Scopes/Content Sources configuration. SELECT URL, FileName, Title, Description, Rank, Size, HitHighlightedSummary, Path FROM SCOPE() WHERE freetext(defaultproperties, ' +légumes')  AND ( "scope" = 'Scope - My Portal - All Site - fr'  )    ORDER BY Rank DESCDaniel Siconnelli There is neither good nor evil... only mogwais and gremlins

how can I detect query results

Im trying to get the number of result in a select query in a database...how can I get the number(row) of result from a ExecuteReader() function?

Query Designer - Pane - Results grayed-out?

In Query Designer, right click, select pane -- the option for Results is unavailable (grayed-out)?

How to extend this query to write the results back to a database?

FOr this query I have four tables I would like to access. SELECT StockA, SUM(Score) AS Total FROM dbo.ScoresNew_LongPrice WHERE [When] >= @StartTime AND [When] < @EndTime GROUP BY StockA ORDER BY SUM(Score) DESC _LongPrice, _ShortPrice, _LongVolume, _ShortVolume I would like to improve the above query to return not just the results from LongPrice, but four columns, of sum(score), from each table, if possible. And, if that is possible, I would also like to have the query then update another table with its results.  Not a new table, but just updated to an existing table. Anyway, I can do some stuff in T-SQL but this seems over my head.  Any help would be appreciated. (The four tables all have identical structures)

How to hide "No results are available. Either no query is specified..." in Core Search Results webpa

Hi All, by default core search results webpart displays this message "No results are available. Either no query is specified, or the query came from advanced search (Federated Webparts do not support Advanced Search queries)." till you perform your first search. So, how can i hide this message when page loaded ? Thanks,

show a "no results" message if a db query returns nothing

I inherited some C# code behind that queries a database table and need to add something that displays a "No results" message (using a Label web control, perhaps) if the query doesn't return any records. I'm just learning C# but am coming up short on how to do this. Would anyone be kind enough to point me to an example, or demonstrate? Thanks a lot. Here's the code I inherited (the programmer who wrote this is no longer available). This is the code behind from the search results page:using System; using System.Text; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Collections; using System.Collections.Generic; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace Maine_Innkeeper_Site_2010 { public partial class lodging_search_output : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string sql = Session["logdingSearchSql"].ToString(); SqlCommand cmd = common.openDatabase(); cmd.CommandText = sql; string PreviousTown = ""; try { StringBuilder sb = new StringBuilder(); SqlDataReader rdr = cmd.ExecuteReader(); sb.Append("<ul class=lodging-search-initial-results>"); while (rdr.Read()) {

Problem with SELECT COUNT query and parameters

Hello!I have a problem with SELECT COUNT query in ASP.net. I want to create CMS with articles which have categories (which have the option to be deleted). The problem is that I want to get the number of articles within the specified category so if there aren't any articles with the specified category I can proceed with the category deletion.I have the following code:protected void Page_Load(object sender, EventArgs e) { } protected void GridViewKategorije_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "Uredi") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow odabraniRed = GridViewKategorije.Rows[index]; TableCell ClanakID = odabraniRed.Cells[2]; string ID = ClanakID.Text; Response.Redirect("/Portal/Administracija/Kategorija.aspx?idKategorija=" + ID); } else if (e.CommandName == "Obrisi") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow odabraniRed = GridViewKategorije.Rows[index]; TableCell KategorijaID = odabraniRed.Cells[2]; String connString = WebConfigurationManager.ConnectionStrings["CMS"].ToString(); SqlConnection conn = new SqlConnection(connString); conn.Open(); using (SqlC

Dynamically fill query parameters in a table adapter ?

SO I have created an object data source on a page from a table adapter using the various Wizards in Visual Studio.Its meant to query a diary Database by a DateTime parameter called diaryDate. I want to query from a date Now to the next 14 days ahead.I want to display the entries in a Grid View when the page loads.So I'm a bit stuck now as to how to set the two query parameters date1 and date2. I want to set date1 = now() and date2 = now() + 14 days.How do I set those parameters when the page loads ?Should I have used the adapter Wizard approach of do I have to write all the code in the code behind page ??This is my datasource code'<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" InsertMethod="Insert" OldValuesParameterFormatString="original_{0}" SelectMethod="GET_nextTwoWeeks" TypeName="query_getNextTwoWeeksTableAdapters.diaryDatesTableAdapter"> <SelectParameters> <asp:FormParameter DefaultValue="" FormField="date1" Name="date1" Type="DateTime" /> <asp:FormParameter DefaultValue="" FormField="date2" Name="date2" Type="DateTime" /> </SelectParameters> <InsertParameters>

How to do a <> Select Query, and assign results to a Group 'Other'


How can I use this in a Select Query?
<> "*" & "Internet" & "*" Or <> "*" & "Old Customer" & "*" Or <> "*" & "Reference" & "*" Or <> "*" & "Saw Trucks" & "*" Or <> "*" & "Y/P" & "*" I want to group all the results (named count) and call the result 'Other'

Here’s my SQL now:

SELECT DATABASE.[LEAD FROM], Count(DATABASE.[LEAD FROM]) AS [Count of Leads], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-7,Date()) & "#") AS [Last 7-Days], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-30,Date()) & "#") AS [Last 30-Days], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-365,Date()) & "#") AS [Last 365-Days]



Paging Content Query Web Part Results


I am running SharePoint 2010 Enterprise in a Standalone installation on a development computer attempting to figure out a way to display press releases 10 per page with as many pages as needed to cover the results.  Considering I'm dealing with at least 2000 press releases, I can't have the web part load all the press releases and use javascript to display 10 results at a time.  As such I've attempted to use this: http://blog.mastykarz.nl/sharepoint-2010-content-query-web-part-paging/ however I've been unsuccessful. 

While I successfully added and deployed the solution to my development environment, enabled the feature on the site collection and added the web part, I cannot style the end results to actually show the paging.  I receive a "The web part references an untrusted XSL file. Only XSL files contained in this site's Style Library may be referenced. " error whenever I attempt to define the ItemStyle XSL Style Sheet (/Style Library/XSL Style Sheets/ImtechContentQueryMain.xsl) within the area the webpart takes up (i.e. it doesn't completely break the page, I get the same error if I define any xsl style sheet there so the .xsl isn't broken, maybe the webpart? maybe my link is wrong?  I used this page:

Query with parameters in data flow



How can I run a query with parameters in a data flow task? I know I can use a script component and write code that executes the query, but I'm looking for a simpler solution, if there's such one.



Sort query results by the number of characters matched



I have a query that takes a multi-word search string and parses it to spit out a LIKE (with AND or OR) condition that I then insert into my dynamic query.

Example of my parsing:

    'cheese chocolate cake'))),
                                ' ', ' þ')
                                , 'þ ',
                                    ''), ' þ'
                    , 'þ'),
                    '%'' OR CakeName LIKE ''%') + '%'')' 

Say for example that a user searches for the string "cheese chocolate cake". My parsing would result in the following string:

(c.CakeName LIKE '%CHEESE%' 
OR CakeName LIKE '%CAKE%')

What I wan to do is sort the results based on the number of characters that were actually matched. So, if c.CakeName contained the following:


  • Cheese Cake
  • Chocolate Cake
  • Chocolate Cheese Cake
  • Lemon Cake

The results would be sorted thusly:

  1. Chocolate Cheese Cake
  2. Chocolate Cake
  3. Cheese Cake
  4. Lemon Cake

SQL query gives results, but Visual Studio doesn't



I have problem accessing data from SQL query in ASP.net. I checked the query and it returns two fields, both with data, but when I run the same query in Visual Studio I get the following error: "Invalid attempt to read data when no data is present."

The problem is that data does exist...

This is what SQL query returns when executed on SQL Server Management Studio (I bolded the different header so you can see the difference):

url idClanak
Upload/Default2.png 8
Upload/Default3.png 8
Upload/Catrinel Menghia 7.jpg 8

The following is the ASP.net code (I bolded the piece of the code that apparently has some problems):

    private void Upisivanje(int idClanak, string naslov, string datumString, string kategorija, string autor, string slika, string opisSlike, string sadrzaj)
        string facebook = "<a href='http://www.facebook.com/sharer.php?u=" + Request.RawUrl + "&t=" + naslov + "'><img src='Images/Facebook.png'/></a>";
        string twitter = "<a href='http://twitter.com/home?status=" + naslov + " na: " + Request.RawUrl + "'><img src='Images/Twitter.png'/></a>
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend