Query problem

I have a count code that works just fine...to get the total..

I need to modify it to select the total where zip = Session("zip")

can anyone help me?

Dim cmd3AsNew Data.SqlClient.SqlCommand("Select count (*) from CustomerInfo", MyConn)

cmd.Connection.Open()

Dim count3AsInteger = cmd3.ExecuteScalar()'this contains the number of records

Label_registeredusers.Text = count3

cmd.Connection.Close()

you can use a parameter in your query to limit the results The parameterized query will be much safer than simply concatenating in the zip code as text in your sql statement as that would expose you to the possibility of a sql injection attack.

Dim paramZipAs New System.Data.SqlClient.SqlParameterparamZip.ParameterName ="@.zip"param.Value = Session("zip")Dim cmd3As New Data.SqlClient.SqlCommand("Select count (*) from CustomerInfo WHERE zip=@.zip", MyConn) cmd3.Parameters.Add(paramZip)cmd.Connection.Open()Dim count3As Integer = cmd3.ExecuteScalar()'this contains the number of recordsLabel_registeredusers.Text = count3cmd.Connection.Close()

|||

You could do this:

Dim cmd3AsNew Data.SqlClient.SqlCommand("Select count (*) from CustomerInfo WHERE zip = '" & Session("zip") &"'", MyConn)

But it would be better to use command Parameters like :|||

A lot easier than I thought... Thank you very much!

sql

Query plan re-use on views?

Here's the setup:

Client database has a complex view with eight nested subqueries used to return "dashboard" information. The application code uses NHibernate to call and filter the view with three parameters, one of which is the CustomerID.

A certain customer, (the biggest client), has more than ten times the number of records of the next largest customer.

Occasionally, the database reaches a state where when this particular customer tries to run the dashboard view, the application times out.

If I open up the view and re-save it, all is well again for a few days.

What gives?

Views are supposedly not pre-compiled, though I know that 2000 stores bits and pieces of query plans.

Any ideas on what causes this and what to do about it?are you experiencing large amounts of new data on a regular basis throwing off statistics and fragmenting your indices. just cuz they ain't precompiled don;t mean that they do not use that stuff right?|||No. Loaded a ton of historical data when the app was first installed, but inserts have been slow and steady since then.

Query of counts

I have a table with three fields: AcctNo INT, Code CHAR(1), Amount MONEY

The Code has three values: 'A', 'B', or 'C'.

Each AcctNo has 1, 2, or all 3 of the Codes assigned to it (ie. AcctNo is not unique)

I need to know how many AcctNos have one value assigned to the Code field, how many have two, and how many have three and I need to know the sum of the Amount for each group.

Can this be done in one statement or do I need three statements?

Fred

The query below returns the results as requested. Is this what you're looking for?

Chris

DECLARE @.Values TABLE (AcctNo INT, Code CHAR(1), Amount MONEY)

INSERT INTO @.Values(AcctNo, Code, Amount)

SELECT 1, 'A', 1.00 UNION

SELECT 1, 'B', 2.50 UNION

SELECT 2, 'C', 1.25 UNION

SELECT 3, 'C', 1.43 UNION

SELECT 3, 'A', 1.96 UNION

SELECT 3, 'B', 2.00 UNION

SELECT 4, 'C', 1.43 UNION

SELECT 4, 'A', 1.96 UNION

SELECT 4, 'B', 2.10 UNION

SELECT 5, 'B', 0.92 UNION

SELECT 5, 'A', 1.24 UNION

SELECT 6, 'C', 0.02 UNION

SELECT 7, 'B', 0.11

SELECT SUM([AccountNoCount]), SUM(TotalAmount), [CodeCount]

FROM (SELECT COUNT(DISTINCT AcctNo) AS [AccountNoCount], SUM(Amount) AS TotalAmount, COUNT(Code) AS [CodeCount]

FROM @.Values

GROUP BY AcctNo) t

GROUP BY [CodeCount]

|||

Yes, that is what I wanted first.

But I need to add another level of complexity. There are duplicates of the AccountNo and Code, only the Amount is different. I need to count duplicates as one. (or anything more than one; there were some with three times and four)

Thanks,

Fred

|||

All you should need is an extra DISTINCT, see below.

Chris

SELECT SUM([AccountNoCount]), SUM(TotalAmount), [CodeCount]

FROM (SELECT COUNT(DISTINCT AcctNo) AS [AccountNoCount], SUM(Amount) AS TotalAmount, COUNT(DISTINCT Code) AS [CodeCount]

FROM @.Values

GROUP BY AcctNo) t

GROUP BY [CodeCount]

Query Notification & Windows Service

I could get Query Notification working for a windows forms client using the
SQL Dependency object but the same code doesn't work from a simple windows
service.
The OnChange event doesn't seem to be raised up to the windows service.
Looking at the SQL trace, it doesn't look like the a notification is sent to
the windows service from the SQL server.
Appreciate your response.
RamaThe notifications are sent using Service Broker, try following the steps in
this post http://blogs.msdn.com/remusrusanu/a.../20/506221.aspx
to figure out the cause.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Rama" <rama.bhandaru@.eclipsys.com> wrote in message
news:en6X%23S$XGHA.1192@.TK2MSFTNGP03.phx.gbl...
>I could get Query Notification working for a windows forms client using the
> SQL Dependency object but the same code doesn't work from a simple windows
> service.
> The OnChange event doesn't seem to be raised up to the windows service.
> Looking at the SQL trace, it doesn't look like the a notification is sent
> to
> the windows service from the SQL server.
> Appreciate your response.
> Rama
>

Query name change

sp_rename doesn't change the name inside the source code for an object. You
can try this yourself,
create a view, look in syscomments, rename it and then look again. You will
have the old object name
in the stored source code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JerryWendell" <JerryWendell@.discussions.microsoft.com> wrote in message
news:0DBB3987-AABB-4B13-A3BF-A357B09A3EB2@.microsoft.com...
>I am using SQLServer 2000 with an Access 2003 .adp front end.
> I created a view. And then I changed the name of the view.
> In another SQLServer database (in the same server) using a different .adp
> front-end, I imported the view (using File->Get External Data->Import).
> After it was imported into the second database, it had the original name.
> Any ideas on why this happened? Or how I can keep it from happening?
> Thanks!
> JerryI am using SQLServer 2000 with an Access 2003 .adp front end.
I created a view. And then I changed the name of the view.
In another SQLServer database (in the same server) using a different .adp
front-end, I imported the view (using File->Get External Data->Import).
After it was imported into the second database, it had the original name.
Any ideas on why this happened? Or how I can keep it from happening?
Thanks!
Jerry|||sp_rename doesn't change the name inside the source code for an object. You
can try this yourself,
create a view, look in syscomments, rename it and then look again. You will
have the old object name
in the stored source code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JerryWendell" <JerryWendell@.discussions.microsoft.com> wrote in message
news:0DBB3987-AABB-4B13-A3BF-A357B09A3EB2@.microsoft.com...
>I am using SQLServer 2000 with an Access 2003 .adp front end.
> I created a view. And then I changed the name of the view.
> In another SQLServer database (in the same server) using a different .adp
> front-end, I imported the view (using File->Get External Data->Import).
> After it was imported into the second database, it had the original name.
> Any ideas on why this happened? Or how I can keep it from happening?
> Thanks!
> Jerry