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

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

Re use the WHERE statement for another parameter

Posted By:      Posted Date: October 18, 2010    Points: 0   Category :ASP.Net

This is my stored procedure:

SELECT artItems.userID, itemID, surname, lastname, gender
FROM artItems INNER JOIN artUser ON artItems.userID = artUser.userID WHERE (@surname IS NULL OR surname LIKE '%' + @surname + '%') AND (@lastname IS NULL OR lastname LIKE '%' + @lastname + '%') ORDER BY lastname SELECT @numTot = COUNT(gender) FROM artUser WHERE "Same where as above"

I want the @numMale to return the number of female users from the above set by counting in the gender column. I don't wanna have to write the same WHERE clause again, but reuse the previous.
But how?

Thanks in advance!


View Complete Post

More Related Resource Links

Parameter For Select Statement

I Am Working On A Project,I am using MVC, VS 2010, SQL 2005, Model Is Created Using LINQ TO SQL (.dbml)Table Joining Code in My Controller Looks Like Thispublic ActionResult Index() { var DataContext = new SMSAPPDataContext(); var result = from t in DataContext.tbl_teachers from e in DataContext.tbl_teacherenrollments from b in DataContext.tbl_batches from c in DataContext.tbl_classes from s in DataContext.tbl_subjects where t.Teacher_ID == e.Teacher_ID && e.Batch_ID == b.Batch_ID && b.Class_ID == c.Class_ID && e.Sub_ID == s.Sub_ID && c.Class_Name == "TEN" && c.Class_Section == "C" && t.Teacher_Name == " ABC " select c; return View(result.ToList()); } This Table Join QUery Gives Me Data From On Table with Select Statement.And My Inherit Page In View Looks Like This<%@ Page Title="" Language=&quo

using IF statement with parameter query


I need to do an IF THEN depending on the query result.  If I use the SQLDataSource control...How do I declare and pass a variable for my IF THEN?  Or should I use ADO?  If so, how does it declare and pass variable in ADO? 


Enterprize library 4.1 getting output parameter after adding record in db, plz guide



I need to get output parameter (flag) from db after saving record in database. I am using Microsoft Enterprise Library 4.1 for DB. I am inserting record using I Data reader.

Please guide


Passing Linq Statement between master and content page


I usually work with windows forms, and am just starting to work with ASP.Net, so appologies if this is a dumb question!

I have a master page which has a search button on it. The results of this are built up using a Linq statement and if there are any results, I want to display the results in a contents page.

On the masterpage, I have the following code:

Private _res

    Public Overridable ReadOnly Property SearchRes()
            Return _res
        End Get
    End Property

 Private Sub DoSearch()
   'some code to get a linq result

    If res.Any Then
            _res = res
            Server.Transfer("SearchResults.aspx", True)
            'no results
            LabelNoRes.Visible = True
        End If
 End Sub

On the SearchResults.aspx page I have this code.

Dim m As pxSite = CType(Page.Master, MasterPage)
        If m.SearchRes IsNot Nothing Then
            Label1.Text = "Results found = " & m.SearchRes.Count
        End If

m.searchRes is always nothing. 

What am I doing wrong here?


Use a column as parameter for a sds in another column; gridview


Hola, I have a gridview which is bound to a sds which returns 7 columns and I added two columns from the GUI.  What I want is to fill up the 9th column with DDLs and these  DDLS will get their data from a different sds which is using a stored procedure with a single parameter.  I created the 9th row, used template field, made the sds for 9th row, the stored_procedure, threw a DDL in Item template and all  I need now is to pass a parameter in the first column to the sds in the 9th column.  I can code this but i'm getting tired of aspx.cs :) and would like to do it from the GUI.

Sort by gridview SortExpression parameter via Stored Procedure


I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />

type object parameter



I have a problem I can't figure out.

I've been trying to add a listbox with multi select enabled as a parameter to my select statement in sqldatasource.

So i created a function like this :

string strItemTypes = "";
foreach (ListItem li in lbItemType.Items)
   if (li.Selected)
      strItemTypes += "," + li.Value;
strItemTypes = strItemTypes.Substring(1);

which gives me the selected items like this for example : "1,2,3"...
Then I've been trying to add it to my sql select statement :

SELECT tblOfficesItems.idOfficesItems, tblStockItems.Name, tblStockItems.Description, tblStockItemTypes.TypeItem, tblStockItems.Notes FROM tblOfficesItems INNER JOIN tblStockItems ON tblOfficesItems.fkItem = tblStockItems.idItem INNER JOIN tblStockItemTypes ON tblStockItems.fkTypeItem = tblStockItemTypes.idTypeItem WHERE tblStockItems.fkTypeItem IN ( @fkTypeItems )

using a hiddenfield with a controlparameter of type=object
but it just don't seem to work, seems like the data just won't bind or something and I don't get any errors so I'm confused!!

then i tried this :

SELECT tblOfficesItems.idOfficesItems, tblStockItems.Name

MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



ReportViewer :: Failed to enable constraints. But my statement is valid!!

  • A
  • n error has occurred during report processing.
      • Exception has been thrown by the target of an invocation.
        • Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

    Why do I keep getting this error, even when not using any data on the report. When I run the query in SQL server I get a resultset. However when I try to render the report I get this error.


    Get ServerReport selected Parameter value to use in vb query


    I'm using ReportViewer in Asp.net 2.0 to view a SSRS report. I need the value of the selected parameter to use in a vb query. The parameters are populated on the server and its a single selection. Doing searches I've come across  ReportViewer1.ServerReport.GetParameters() but I can't figure out if I can use this to determine which value the user has selected in the parameter dropdownlist. Any help would be appreciated.

    Crystal Report parameter value window.


     Hi friends,

       I am using Crystal report which displays the result of stroed procedure which have 2 paramater.

      When i run report it shows two window for getting each parameter.

      My need is, can i have one window for getting all the paramter value for stored procedure.


      With Advanced thanks,






    Crystal Report vs 2003 convert to vs 2008 parameter problem


    Hi friend,

    I have a project develop with Visual Studio 2003, when i convert the project to Visual Studio 2005 is work well. But when i convert to Visual Studio 2008, the crystal report when have pass parameter will prompt the parameter field to re-type then show the report.

    But in this converted project i create a new report and pass the parameter is ok. That means i wan re-do all report @.@??

    Does someone can help me solve this problem??

    Thank you

    Whats wrong with this statement


    Im trying to updated some old ASP pages with some new .net pages and cant seem to get this update statement to work. I need to update the current ASP code below and then once thats working im going to use that same statement in a procedure for all our new .Net pages..

    So can anyone see what im doing wrong by inserting the 2nd column to the update statement?

    strSqual = "update tas set date_completed='" & TransDate & ", trans_id=" & InsertedTransID & "' where date_completed is null and sercontract_id="& strContractID & " and DATEDIFF(dd,task_due_date,'" & strProcDate & "')<=14"


    I am trying to add an extra column to the update, but not sure if the syntax is correct.

    If Statement Issue



    This is very strange to me. I have spent 2hrs looking into this inside out , but dont seem to get it

    I have an if statement that inserts data into the database. But i check to make sure the name is not entered twice. Now when the name is already in the db, then it should insert else put out a message saying its already in the db

    My code works fine except that  when its not in the db, its inserts but still puts out the message that says "Name is already in use" Not sure if am not seeing something. But thought i did bring it to the forum. Here is my code...Pretty simple

    Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click

            Dim objConnection As SqlConnection
            Dim objCommand As SqlCommand
            Dim strSQLQuery1 As String


            ' If there's nothing to Add for then don't search
            ' o/w build our SQL Query and execute it.
            If String.IsNullOrEmpty(txtAdd.Text) Or Leadbank.SelectedValue = "000" Then


    statement cannot appear outside of a method body


    hi all,

      i am posting my vb.net problem .plz tell me how to resolve this :-

    Dim conn As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
        'Dim mysqlconnection As New SqlConnection(conn)
        'If mysqlconnection.State=ConnectionState.closed Then mysqlconnection.Open();
        'Dim mySqlcommand As New SqlCommand("select CategoryId,CategoryName from Categories", mysqlconnection)
        'Dim mySqlDataAdapter As New SqlDataAdapter(mySqlcommand)
        'Dim mydataSet As New DataSet()

        'if mysqlconnection.State=ConnectionState.closed Then mysqlconnection.Open();

    i m getting 5 errors in this code

    declaration expected in mySqlDataAdapter.Fill method; Gridview1.DataSource=ds; Gridview1.DataBind();

    how to multiple condition in if statement in asp.net C#



    i am using if condition to find out a time

    if(time=="08 AM)


    response.write"shift A";


    but problem is how can i put multiple condition in if like

    if(time=="06 AM" and "08 AM" and "07 AM")



    Hello, i need help changing the following code into a switch...case statementUndecided

    <script runat="server">
    void Page_Load()
        if (Page.IsPostBack)
        public String toSring()
        int intcomputerChoice=1;
           if (Page.IsPostBack)

    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