.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

Problem with select

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

I need help with an sql query.

ALTER PROC ProductGetProductInDepartment
@departmentId	INT
SELECT tp.[name],
CASE WHEN LEN(tp.[Description]) <= 60 THEN tp.[Description] ELSE
SUBSTRING(tp.[Description], 1, 60) + '...' END AS [Description],
p.Thumbnail, p.UnitePrice 
FROM TypeOfProduct tp
INNER JOIN Product p ON tp.TypeOfProductId = p.TypeOfProductId
INNER JOIN ProductCategory pc ON pc.ProductId = p.ProductId
INNER JOIN Category c ON c.CategoryId = pc.CategoryId
INNER JOIN Department d ON d.DepartmentID = c.DepartmentId
WHERE d.DepartmentID = @departmentId

The problem is that I get all products in return, I want have only one of each product. Now I get one in every size and every color.


View Complete Post

More Related Resource Links

Problem Select ancestors a Record

Hi; I have a table by three columns ("ID","Title","ParentID"),me want to select ancestors a Record by "ID" identifier. example: ID      Title       ParentID 1         Library       null 2         Section1      1 3        Section2       1 4        1.1              2   for example if record 4 selected return records by "ID" identifier 2,1 How can me do it?  

Problem Add Category Item by select department

Add Category Item by select department Firstly database has two tables Department and Category Department Table Id -------int  (PK) dep_name--------nvarchar(50) dep_description---------nvarchar(100) -------------------------------------------------------- Category Table Id------int cat_name------- nvarchar(50) cat_description----------- nvarchar(100) IsActive--------bit DepartmentId-----int   (FK) ---------------------------------------------------------- I have form contain DetailsView control under name CategoryDetailsView. My problem: this mechanism not duo correctly I don't know why,  only show to me when I run page" Procedure or function InsertCategory has too many arguments specified. " My idea when a user need to create category must on him write category details ( category name, Description, IsActive) and select this category where to insert by select department name from drop downlist This control connecting with table under name Category, insert process by stored procedure under name sp_InsertCategoryALTER Procedure [dbo].[InsertCategory] (@cat_name nvarchar(50) ,@cat_description nvarchar(100),@IsActive bit , @Result int output)AsBegin Insert Into Category (cat_name , cat_description, IsActive ) Values (@cat_name , @cat_description, @IsActive ) Select @Result = @@IdentityEnd <asp:DetailsView ID="CategoryDetailsView

people picker problem, with multiple select lose the list.

<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} p {mso-margin-top-alt:auto; margin-right:0cm; mso-margin-bottom-alt:auto; margin-left:0cm; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> Hi guys I have a master table with configuration for reports. when I load a InfoPath form (2010) for new report I have a approvers that set ok  , and I have  Notifies (multiple people) when I load a value form master they left me in the field only the first value(people) and lose the others. Do you know if there any way to load a multiple people value in people picker control?   Thank in advance Ramiro

solve VS2008 Problem how to select multiples

HiI am working on visual studio 2008 professional. In web application. I have placed few labels, textboxes and buttons and I want to align them all. I am not able to select multiple controls..Why whats the problem

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

Select Distinct Problem.



I have a user defined data table like this.





/****** Object:  UserDefinedTableType [dbo].[udtt_AssemblyItem]    Script Date: 10/05/2010 11:47:57 ******/

CREATE TYPE [dbo].[udtt_AssemblyItem] AS TABLE(

[Id] [int] NOT NULL,

[SystemTypeDescription] [varchar](100) NOT NULL,

[Type] [varchar](100) NOT NULL,

[SubType] [varchar](100) NOT NULL,

[Depth] [decimal](18, 3) NOT NULL,

[Height] [decimal](18, 3) NOT NULL,

[Width] [decimal](18, 3) NOT NULL,

[MaterialCost] [decimal](18, 3) NOT NULL



Now I add some records to it like this.
declare @p1 dbo.udtt_AssemblyItem
insert into @p1 values(0,'SL 1000','Partition','',1.800,1.800,0.300,402.560)
insert into @p1 values(0,'SL 1000','Nib','150mm',0,1.800,0.150,70.840)
insert into @p1 values(0,'SL1010','Partition','',1.500,1.800,0.300,197.810)
insert into @p1 values(0,'SL1010','Nib','150mm',0,1.800,0.150,55.180)
insert into @p1 values(0,'CL 1000','Partition','',1.500,1.800,0.300,368.090)
insert into @p1 values(0,'CL 1000','Nib','150mm',0,1.800,0.150,70.840)
insert into @p1 values(0,'CB 2000','Partition','',1.500,1.800,0.

Apply a Filter in a Select statement Problem


Hello all

I need to select some rows from a table and apply a distinct on two of the columns (keeping the most recent one) and get all the attributes of the selected rows.

for example

if I have a table with the following rows and coloumns

1 1 1 1/1/2010
2 1 1 2/1/2010
3 1 2 1/1/2010

I need to do a select distinct on columns A and B keeping the most recent row and return all the coloumns of the selected rows

so i need the result to be rows 2 and 3 and discard row 1 because there is another row (2) having the same data in columns A and B and the other row (2) is more recent.

I tryed creating a veiw, use a common table expression and create a stored procedure...

but my knowledge with sql is limited and cannot get the result i want...

Is there a way of getting that result without having to change the database structure?

Any suggestions are welcome


SQL Select problem with bit data type


I have an odd situation.  I wish to select the value of a bit field in my MS SQL query.  I want to know if it is true or false (1 or 0), but my query seems to somehow converting all the responses (true or false) to the letter 'A'.  I want to know what the bit value is: true or false.

Here is the query:

SELECT dbo.roles.pk_role_id, 
FROM   dbo.members 
       INNER JOIN dbo.roles 
         ON dbo.members.pk_id = dbo.roles.fk_member_id 
       INNER JOIN dbo.teams 
         ON dbo.roles.fk_teambar_number = dbo.teams.pk_id 
WHERE  ( dbo.roles.fk_teambar_number = @FK_TEAMBAR_NUMBER ) 
       AND ( dbo.roles.TYPE = 'Player' )  

Why is it converting all TEAMS.ACTIVE date to the letter 'A' for both true and false ???  I bind the results to a gridview and both true and false are listed as an 'A', and I get the same thing where I run the query in my GoDaddy query analyzer.  Does anyone have an idea how to fix this?

select top 3 order by problem


Hi all


I want get the last 3 tags that added to the db.


My Tag table schema is:



CREATE TABLE [dbo].[tblTag](

      [TagId] [int] IDENTITY(1,1) NOT NULL,

      [Header] [nvarchar](200) NULL,

      [IsActive] [tinyint] NULL DEFAULT ((0)),

      [Ordered] [int] NOT NULL DEFAULT ((1)),

      [Language] [nvarchar](5) NULL,

      [DateTime] [smalldatetime] NULL,

      [Official] [nvarchar](max) NULL,

      [LastUpdate] [datetime] NULL,

      [Locked] [tinyint] NULL,

      [LastUpdateUser] [nvarchar](256) NULL,

      [NumViews] [bigint] NULL,

      [AllUserModified] [nvarchar](max) NULL,



      [TagId] AS

problem with date range between select Query in data adapter


hi all,

can any one help me out...why am getting "data type mis match error"

 DateTime dFromDate = dt_From.Value;
            dt_From.Value = DateTime.Parse(dFromDate.ToShortDateString());
            string From = dFromDate.ToShortDateString();

            DateTime dToDate = dt_To.Value;
            dt_To.Value = DateTime.Parse(dToDate.ToShortDateString());
            string To = dToDate.ToShortDateString();
            OleDbConnection cn = new OleDbConnection(ConfigurationSettings.AppSettings["connec"]);
            OleDbDataAdapter da = new OleDbDataAdapter("select * from Route_Expenses a where Entry_Date between '" +From+"' and '"+To+"'",cn);
            DataSet ds = new DataSet();
            dataGridView1.DataSource = ds.Tables[0];

 Please..its Urgent!

Jquery select problem in Tree view


i am create a tree view in asp.net

All Department




   When i select     staff  than i require output is

           var treeSelect= All Department->office->Admin->Staff


Radio Button Select problem in a table


a Table contain

<table id="Grid_Event1" cellspacing="0" rules="all" border="1" style="font-family:verdana;font-size:11px;border-collapse:collapse;">
<th scope="col">FORMCODE</th><th scope="col">ID</th><th scope="col">COMPANY</th><th scope="col">OWNER</th

problem with SQL select statement when variable are NULL


Hello all,

I'm struggling with a strange problem.

Let me first start with giving you my SQLdatasource:


      <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        SelectCommand="SELECT ID_PLS, Checked, Userid, DONE, Entity, Environment, TO_DO, Description, HOW_TO, LINK_TO_PROC, Overtime, Processing_date, Driver FROM PLS_DAILY WHERE (Entity = @Entity) AND (Environment = @Environment) OR (Entity = @Entity2) AND (Environment = @Environment2)">
               <asp:SessionParameter DefaultValue="" Name="Entity" SessionField="eses_entity" 
                   Type="String" />
               <asp:SessionParameter DefaultValue="" Name="Entity2" SessionField="eb_entity" 
                   Type="String" />
               <asp:SessionParameter DefaultValue="" Name="Environment2" SessionField="eb_environment" 
                   Type="String" />
               <asp:SessionParameter DefaultValue="" Name="Environment" SessionField="eses_environment" 
                   Type="String" />

Then let me give you the sessionfield parameters:

    Protected Sub Button1_Click(ByVa

AJAX GridView Checkbox Select All in ASP.Net

In the previous article GridView Select Multiple Rows you learnt the multiple row selection in GridView control. Here you will get the C# code sample to select all the gridview rows using single checkbox server control. You can highlight the selected rows with different color and Font state Bold. If you want to use the selection values on different ASP.Net

Problem to group radio button across gridview rows

In general no one can easily group or make a single selection from radio button list inside gridview rows. There are a lot of way to make a single or unique selection from list. Here i want to share how i can address this problem in the most easiest way. Googling the problem most of the cases i saw the grouping in horizontal way. Thats why i tried to group the radio buttons in vertical manner. Let i have a requirement like below:

Regrading Sharpoint 2010 problem and solutions

We can write your question and problem on this blogger.you get ans.use.

This sharpoint 2010 problem solving threads

Hi friend,

you can use this thread to solving problem and solutions.
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