.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

sql count record problem

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :Sql Server

if i have a prodcut with product no. and each product is made by many parts with parts no.
now i would like to retrieve data to count how many parts to make a product.
in sql, can i count parts no. by each product no. like belows:

select product_no,parts_name,manufacture_date,(select count(*) from item where product_no=p.product_no) as [No. of items] from prodcut p left outer join item i on p.product_no=i.product_no

but i found that the execution time is too long, is it the right way to count no. of item in each product??

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?  

record count in sqldatasource

Hi,   I would like to know how many records are selected in sqldatasource after select command   Thanks

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

DataBase maximum table record count


Hello Friends,

My requirement is, we have more than 5 database servers, each server lot of databases, each database lot of tables.

 I want show the details


ServerName: abcServer

DatabaseName: xyzDatabase

MaximumRecordsTableName: empTable


In asp or Classic asp.

please anybody help for code and query.


how to split the connection string in asp ?

record count for subquery which is used for INSERT

My current code is like this

Insert into ReportDetail( Partcipantid, Reportid)

      select distinct ParticipantID , 3 from abc   where

        closed = 0 and ( isnull ( primaryphone, '' )

Problem getting same record when trying to populate data into texboxes when selecting a row/record f


I can't seem to get the select record from the datagrid to populate in the textboxes. I get the same record no matter what I select in the datagrid. Hopefully someone can help with this. I tried using a where clause for POPULATION_LIST_ID = '" & Me.lblPopulationID.Text & "' and I get a ORA-01722 error - invalid number. Here is the code I have so far for the grid:

  Protected Sub MHGrid_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles MHGrid.SelectedIndexChanged
Me.Table1.Visible = True
Me.MHGrid.Visible = True

Dim dvSql As DataView = DirectCast(MHFoundation.Select(DataSourceSelectArguments.Empty), DataView)

For Each drvSql As DataRowView In dvSql
Me.lblPopulationID.Text = drvSql("POPULATION_LIST_ID").ToString
Me.txtMRN.Text = drvSql("MRN1").ToString()

How to get total record count in an OUTPUT Parameter


I couldn't get the total record count in a, in my stored proc, in a given scenario. Please help


My SP looks like this:

create proc RecordCount

       @CustomerID varchar(10),

       @RecCnt int OUTPUT


begin tran

if(CustomerID = 'ABC')

@RecCnt = select count(*) from Customers where CustomerID='ABC'



begin tran


@RecCnt = select count(*) from Customers where CustomerID='XYZ' and TranDate > '05/01/2009'




-- This is how I called my SP

declare @rc int

exec RecordCount 'XYZ', @RecCnt = @rc OUTPUT

select @rc

help on triggers inserting record problem?


I have created a trigger and i want to insert a record in emp table whenever i insert i must get the confirmation that record is added i want to do this

using triggers.

USE [mine]
/****** Object:  Trigger [dbo].[insert2]    Script Date: 10/03/2010 09:06:59 ******/
ALTER trigger [dbo].[insert2]  
   on [dbo].[emp]  
    for insert  
    declare @sno int,@result varchar(50),@empid int,@name varchar(50)  
    select @empid=empid,@name=@name from emp where @empid=empid 
    insert into triglog values (@empid,@name)  

above is my code emp is my primary table and triglog is the table where i want to get the confirmation that data is aded when a new record is inserted into emp,actually i want to know when a new record is added? is there anyway to know this when i execute the code im getting error like inserting null values into sno and result but how to pull the values from emp and enter it in triglog im confused help with this??thanks.

Problem when creating a Record Center in SharePoint 2010


Hi Guys

I have a problem when creating a Record Center in SharePoint 2010 on a publishing Portal. The Record center creates succesfully, but when I try to upload a record sharepoint says that the list does not exist, also when I try to go into the site settings of the record center I get the same issue. When trying to specify the libraries in the record centre management page....same thing! What could this be? 

Problem inserting a record into database

I created a table with a foreign key:
Create table Products
    Pro_ID int Primary key not null, 
    Client_ID int Foreign key (Client_ID) references Client_Details not null,
    [Prod_Name] [varchar](50) NULL,
    Prod_Description varchar(50) null,
    Purchase_Date DateTime Not null,
    Price Money null,
    Delivered int null
I need to populate that table with some data:
insert into Products (Pro_ID,Client_ID,Prod_Name,Prod_Description,Purchase_Date,Price)
values(2024,102,'Valuation Roll 2001','Property Values for 2001',Getdate(),15000)
When I run the query I get an error:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Products__Client__03317E3D". 
The conflict occurred in database "valrollclients", table "dbo.client_details", column 'client_id'.
The st

SSAS - MDX - need to generate a count for the life of a record



I'm struggling trying to create a calculation which will show what I want.  My requirement is to show a count of 1 while a record is active, and 0 when it is not.  For example, in my data warehouse I have an enrollment record active from 1/1/2010 through 12/31/2010 (12 months).  If the user runs an excel cube report for 2010 by months, I want to show a 1 for all 12 months (if the report were run for quarters, then a 1 for each quarter etc.).  If the user runs a report from 2008 through 2010, the record would show 0 for 2008 and 2009.


Now I could haul off and create a daily record for that enrollment, and have 365 daily records in my DB.  I really don't want to do that. Conceptually here's what I want to accomplish:


IIF([Coverage Start Date].[Date].CurrentMember.FirstChild>=[Coverage Start Date].[Calendar Date] AND [Coverage Start Date].[Date].CurrentMember.FirstChild <= [Coverage End Date].[Calendar Date],1,0)


So if the current member in the example above is Q3 2010, then [Coverage Start Date].[Date].CurrentMember.FirstChild would be '7/1/2010' (first day of Q3).  Since 7/1 is >= 1/1 [Coverage Start Date].[Calendar Date] and <= 12/31 [Coverage End Date].[Calendar Date] this should evaluate to 1.


it doesn't. I ge

Unable to Export to Spreadsheet in SharePoint if record Count increase more than 1100


Hi All,

When I am trying to export the SharePoint View from SharePoint site to Spreadsheet, sometimes I am able to export the the whole data and sometimes I only get Column headers but no data or records

I have Infopath form (2007) published on SharePoint Site and I am using MOSS 2007 and Excel 2003

I am not able reach the cause of this behaivour of Export to Spreadsheet functionality

Is their a Buffer size Issue with IIS or Session time out

I am not sure because when I export to Spreadsheet and if the record goes to Spreadsheet the file size is just 1.72 MB

Please suggest

Hitesh Duggal

display a record count from sql database connection


sometimes learning asp.net feels so heavy handed compared to some of the general purpose scripting languages like php.  for examples, all i want to do is create a sql connection that pulls a row count and displays that number.  I don't need a grid, list, detail page, etc., nothing this heavy... a simply query that will display a simple bit of text.  for the life of me I can't figure out how to do it.  Using these controls, I'm not in the code behind with the query, so I wouldn't attach this to a label (or would i), and there's nothing i see in the toolbox that does what i want.  Perhaps i'm too accustomed to php but it feels like with asp.net, major things are simple, and simple things are hard.  any help would be appreciated.  i'm trying to convert my career to c#.net

Workflow. Copy item to new list - problem updating additional fields in the new list record




I'm having a couple of problems


I have a list, List A. On a couple of triggers I want list A to copy fields to a new record in List B.

Many of the columns are the same Say, column1, column 2, colmn 3. The Copy Item is working fine.


I then want to amend several fields in the new record in List B to reflect the new info from list A, and create info in a fields (say ListBcolum4 type choice and ListBcolum5 single line).

For the life of me I cant get this to work... ive tried updating fields, creating items...

A pointer on how to do this would be greatfully received.

Record Count on MERGE Statement on Insert,Update,Delete


HI All,

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE  separately and store it in a variable so I can get it in the application side? 



khrizz tell

Problem to write a query..using COUNT()...


I am facing problem to get desired output..

i m using query as written below.........

SELECT  studentprofile.RollNo, AttendanceMaster.ScholarID, studentprofile.StudentName 

COUNT(AttendanceMaster.AttendanceStatus) AS Expr1

FROM         AttendanceMaster AS AttendanceMaster INNER JOIN
                  studentprofile AS studentprofile ON                    AttendanceMaster.ScholarID=studentprofile.ScholarID


(studentprofile.Section = 'A')

AND (studentprofile.Class = 'Nursery')

AND (studentprofile.SchoolCategory = 'PRE SCHOOL')

AND (studentprofile.SchoolType = 'XYZ School')

AND (AttendanceMaster.AttendanceStatus = 'Present')

AND (AttendanceMaster.Date BETWEEN '11/1/2010 12:00:00 AM' AND '11/30/2010 12:00:00 AM')
GROUP BY studentprofile.RollNo,AttendanceMaster.ScholarID, studentprofile.StudentName

this query gives output propery for those students who comes(AttendanceStatus='Present') atleast ne day........but unable to show the the re

Regular Expression Word Count :: Problem when word wrapped in "", need "eg" to be counted as 1 word


Hi very new to regular expression, i have the following js function to return the number of words displayed in a text area.


 var matches = textarea.replace(/<[^<|>]+?>|&nbsp;/gi,' ').match(/\b/g);
 var count = 0;
 if(matches) {
  count = matches.length/2;


This function works perfectly except when a word is contained in double quotes: eg "word"

This will come back as 3 words, and I need it to be 1

Any help would be much appreciated.


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