Count Error
Mar 13, 2008
I have table #allcall
contains two columns
clid and date
For every record I want to find how many time clid appears in previous 30 days.
I use following code
select a.clid, a.date, (select sum(case when datediff(d, x.date, a.date)<=30
then 1 else 0 end) from #allcall as x
where x.clid=a.clid ) as nbr
from #allcall as a
but get error msg:
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.
How to fix this problem?
Thanks
View 8 Replies
ADVERTISEMENT
Jan 31, 2008
I would like to fail a package depending on the error. The package extracts data from Excel files. I would like to continue processing if an Excel file is badly formatted, but stop processing if there is a serious issue. like the file server hosting the Excel files crashed.
I was thinking about dynamically changing the MaxeErrorCount property based on the Error ID or description.
Any ideas on an intelligent/simple way to do this
View 1 Replies
View Related
Aug 6, 2006
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
View 5 Replies
View Related
Jan 25, 2007
I try to get a Row Count from data flow - and get Error:
[Row Count [724]] Error: The variable "User::COUNT_NEWIMGS" specified by VariableName property is not a valid variable. Need a valid variable name to write to.
I tried setting COUNT_NEWIMGS on Package Level, on Data Flow level (Int32 datatype)
i tried specifying variable as "COUNT_NEWIMGS", "User::COUNT_NEWIMGS", "@[COUNT_NEWIMGS]" - still the same error
And then when I'm ready to crash my keyboard (again) - I found on another forum that Row Count doesnt even update that variable until the dft is complete. WTF!!?!!
View 6 Replies
View Related
Mar 19, 2004
I need your help:
I want to get the total number of expired messages of two tables.
But if gives me this error:
Line 17: Syntax error near '@Count'
What did I do wrong? Am I not allowed to add two Integers??
CREATE PROCEDURE Portal_GetExpired
(
@Count int OUTPUT
)
AS
SELECT Count(*) as x
FROM Portal_Announcements
WHERE ExpireDate < GetDate()
SELECT Count(*) as y
FROM Portal_Events
WHERE ExpireDate < GetDate()
@Count=x+y
GO
View 3 Replies
View Related
Oct 14, 2007
hello,
i have a working stored procedure SELECT CommentID, UserName, PictureID, DateCommented, COUNT(CommentID) OVER (PARTITION BY PictureID) AS 'NrOfComments'
FROM Comments WHERE PictureID = @PictureID
witch returns among others the number of comments for a picture
i need to select the number of distinct users who commented that user too, so i added this at SELECT statement, COUNT(DISTINCT UserName) AS 'Expr1'
i get that error:
"Colum 'Comments.CommentID' is invalig in the select list beacuse it is not contained in either an aggregate function or the GROUP BY clause."
what should i do, to select the number of distinct users who commented on a specific picture?
here are the table rows, if you need that
CommentID --- UserName --- PictureID --- DateCommented
please help me, thank you
View 5 Replies
View Related
Jun 28, 2007
I have a Data Flow Task with an OLE DB Source containing this query:
SELECT Batch_Idx, Package, FailureSource
FROM Audit.ProcessPackage
WHERE (Status = 2) AND (Batch_Idx > ?) and FailureSource IS NOT NULL
At this point, before sending the data to the Recordset Destination, I would like to do a Row Count, and do something different depending on if there are any rows or not. When I try to put in the Row Count after the OLE DB Source, I get this error:
"The component has forbidden the requested use of the input column with lineage id xxx"
My variable for the RowCount results is RowCount and it is declared at package level. I have tried to use all each of the columns as input columns, and I get the error for every one. Do you know how I can fix this error? Thank you!
View 5 Replies
View Related
Nov 30, 2007
We have a table of students with fields for the ID of the school district and their school name. For example:
DistrictID
StudentName
SchoolName
10001
John Smith
Washington Elementary
10001
Jane Smith
Lincoln Middle
10002
David White
Hill High
...
...
...
I want is a listing with the school district ID, the student name, and the number of schools in the district.
I believe the following statement should give me what I want:
SELECT DistrictID, StudentName, COUNT(DISTINCT SchoolName) OVER (PARTITION BY DistrictID)
FROM StudentData
However, I get the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'distinct'.
If I take out the DISTINCT it runs fine, however it counts all the rows as expected, which is not what I want.
If I remove the OVER clause it runs fine, however, it counds the schools across all the districts, which is not what I want.
I can remove the OVER clause and use GROUP BY, but not while also retrieving StudentName.
The SQL 2005 Online Books don't seem to indicate that my statement is invalid. Here are the links just for reference:
Count: http://msdn2.microsoft.com/en-us/library/ms175997.aspx
Over: http://msdn2.microsoft.com/en-us/library/ms189461.aspx
Anyone have an idea one what I'm doing wrong or how to go about getting this information?
Thanks!
Matt Penner
View 3 Replies
View Related
Sep 14, 2000
I am trying to run the plan analyser on a SQL query that I cut and pasted from a Siebel trace. I get the following error
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect
There is no count in the SQL query, and there doesn;t seen to be a message number to look up, anyone got any ideas?
I have done the obvious stuff like changing the DB name in the query, but to no avail....
Cheers
Mike
View 1 Replies
View Related
Sep 21, 2004
I'm stuck. This is in C#.
I am making the following query:
string query = INSERT INTO region_info(prefix, region, last_update) VALUES ('" + RegionInfo.Prefix + "', '" + region + "', ???TIMESTAMP???);
and then executing
query = query.Replace("???TIMESTAMP???", "'" + DateTime.Now.ToString("yyyyMMdd") + "'");
Thus, an example query is:
INSERT INTO region_info(prefix, region, last_update) VALUES ('907209', 'Alaska-Juneau', '20040921');
When I execute this query through my program(uses ADO.net), I get a "COUNT field incorrect or syntax error" exception, but if I run this same query through the query analyzer, it works fine.
Any ideas on what'z going wrong?
Thanks
View 5 Replies
View Related
Aug 29, 2007
I am trying to pass back the number of errors encountered by a child package to the Parent package. I have a script within the child package, which will set the value of the Parent package's variable (ChildErrCount). However, I have no idea how to access the Child package's Errors collection to get a count.
Any ideas? Has someone figured out a way to reference the current package's properties (besides what's available from Dts.* ?
Thanks!
View 11 Replies
View Related
Nov 25, 2007
I have created the following temp table.
--DROP TABLE #temp_Table
--select 0 as Populated, c.Table_name, Column_name, Ordinal_position, Value, Data_type, Character_maximum_length
-- INTO #temp_Table
-- from
--(SELECT 'lists' AS 'TABLE_NAME', *
-- FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'Lists', 'column', default))c
--INNER JOIN
-- (SELECT * FROM INFORMATION_SCHEMA.columns)d
-- ON c.objname = d.column_name COLLATE Latin1_General_CI_AI
-- AND c.TABLE_NAME = d.TABLE_NAME
--ORDER BY d.Ordinal_Position
--select * from #Temp_Table
The temp table above #Temp_Table has these 2 columns among others.
Table_Name, Column_name
Lists.......ListID
Lists.......ItemName
Lists.......ItemAbbreviation
Lists.......ItemDescription
From the table named in the Table_Name column I want to populate a new column named "Populated" with the count of rows based on this select:
SELECT COUNT(*) FROM Lists WHERE Len(Notes)<> 0
So that I will end up with:
Table_Name ...Column_name ........Populated
Lists...............ListID......................22
Lists...............ItemName...............10
Lists...............ItemAbbreviation......20
Lists...............ItemDescription.......5
My attempt at writing this select is as follows.
select *, populated from #Temp_Table #t
--loop through table for getting count of all columns.
WHILE #t.Ordinal_position IS NOT NULL
begin
Update #t
set populated = (SELECT COUNT(*) FROM Lists WHERE Len(Notes)<> 0)
end
And get this error
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "#t.Ordinal_position" could not be bound.
How do i correct this?
View 1 Replies
View Related
Aug 30, 2007
I am getting this error:
The variable "MyVariable" specified by VariableName property is not a valid variable. Need a valid variable name to write to.
This is my scenario:
I had a Row Count task on a data flow that was writing to a locally scoped variable (called MyVariable for this example.)
I needed to access the value of this variable at the Control Flow (global scope), so I deleted it and recreated it at that level.
My new variable has the same name as the old variable, just different scope.
Now I get this error every time I run the package.
BUT WAIT THERE'S MORE!
I have another Row Count step in the same data flow that is presently writing to a globally scoped variable called "ErrorRows."
If I change this step to write to MyVariable it works fine. If I change my other step to use ErrorRows, it works fine. If I change them back I get the error again.
I have tried deleting and recreating the step, and the variable, and using different names for them. Something is very jiggy with this variable!!
View 6 Replies
View Related
Aug 28, 2006
i'm getting following exception when i try to execute stored procedure.
{"Ambiguous column name 'MemberID'.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }
I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting
"Transaction count after EXECUTE indicates that......"
In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN
/* Its failing at MemberID = tTempResult.MemberID bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID */
here is my stored proc
CREATE PROCEDURE dbo.ExportFile
@intMonth INT,
@intYear INT,
@dtFirstDayOfMonth DATETIME
AS
BEGIN
BEGIN TRANSACTION
/*
I have some logic here that will select rows into temporary table
#TEMPRESULT
*/
UPDATE
dbo.RebateInstanceItem
SET
ResubmitCreated = @dtmNewCreated
FROM #TEMPRESULT tTempResult
WHERE
MemberID = tTempResult.MemberID
AND RebateInstanceItem.IsResubmit = 'Y'
AND (RebateInstanceItem.ResubmitCreated = @dtmLastCreated
OR RebateInstanceItem.ResubmitCreated IS NULL)
IF @@ERROR<>0
GOTO ERR_HANDLER // when error it will goto error_handler that will rollback
DROP TABLE #TEMPRESULT
IF @@ERROR<>0
GOTO ERR_HANDLER
COMMIT TRANSACTION
RETURN 0
END
ERR_HANDLER:
ROLLBACK TRANSACTION
RETURN 1
GO
View 8 Replies
View Related
Aug 28, 2006
i'm getting following exception when i try to execute stored procedure.{"Ambiguous column name 'MemberID'.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting"Transaction count after EXECUTE indicates that......" In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN/* Its failing at MemberID = tTempResult.MemberID bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID */ here is my stored procCREATE PROCEDURE dbo.ExportFile @intMonth INT, @intYear INT, @dtFirstDayOfMonth DATETIMEAS BEGINBEGIN TRANSACTION /* I have some logic here that will select rows into temporary table #TEMPRESULT */ UPDATE dbo.RebateInstanceItem SET ResubmitCreated = @dtmNewCreated FROM #TEMPRESULT tTempResult WHERE MemberID = tTempResult.MemberID AND RebateInstanceItem.IsResubmit = 'Y' AND (RebateInstanceItem.ResubmitCreated = @dtmLastCreated OR RebateInstanceItem.ResubmitCreated IS NULL) IF @@ERROR<>0 GOTO ERR_HANDLER // when error it will goto error_handler that will rollback DROP TABLE #TEMPRESULT IF @@ERROR<>0 GOTO ERR_HANDLERCOMMIT TRANSACTIONRETURN 0ENDERR_HANDLER: ROLLBACK TRANSACTION RETURN 1GO
View 5 Replies
View Related
Oct 7, 2015
Naming convention and what am I doing wrong here:
,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where Channels.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and c2.DateContractFunded < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedLastYear
FROM Channels AS C
INNER JOIN ChannelContacts AS CC ON C.ChannelID = CC.ChannelID
INNER JOIN ChannelProductPlan AS CPP ON C.ChannelID = CPP.ChannelID
INNER JOIN tblLuMktReps AS MR ON C.MarketRepID = MR.MarketRepID
INNER JOIN tblLuHoldingCo AS HC ON C.HoldingCoID = HC.HoldingCoIDError message:
Msg 107, Level 16, State 3, Line 1
The column prefix 'Channels' does not match with a table name or alias name used in the query.
View 9 Replies
View Related
Aug 10, 2015
I am doing a distinct count on a related table's column, but get an out of memory error if I run it for the entire table (works great for just a few rows when filtered down).The error I get is: "We couldn't get data from the external source.The operation has been cancelled because there is not enough memory available for the application. If using 32-bit version of the product consider upgrading.
I know I can add a related column and that works fine...but that seems to me like I've defeated the purpose, I have a good and proper lookup table, and should be able to run my query against its relationship.Here is the query and details below *Note I supplied a scaled down sample, on my actual model I receive these errors, not in the sample as it has only a few rows
List Workers Distinct Project Customers:=CALCULATE(DISTINCTCOUNT(Projects[CustomerID]),'WorkersToProjects')
Other measure which returns no errors, but included for completeness:
List Workers Projects:=CALCULATE(DISTINCTCOUNT([ProjectID]),ISBLANK(WorkersToProjects[ProjectID])=FALSE())
My goal here is to allow the user to view the workers assigned to a project, but also get counts of the workers assigned to the CUSTOMER of a project. For example, suppose we lose a customer, we want to see how many workers would be impacted by that, so a count of projects per worker is not useful there, we need to see a count of workers per project's customer (owner of project whom project work is being done for)The question being: How can I accomplish this:
1. WITHOUT adding a calculated column to WorkersToProjects (of Projects.CustomerID)
2. WITH better performance?
There must be a better way to write this DAX to still get the correct answer?*Pic of pivot table, again, the numbers are accurate but the formula used to List Workers Distinct Project Customers measure does NOT scale :( 3 count for red , the number of Projects John has and 2 count for blue, the unique customers/owners of those projects "Veridian Dynamics" and "Massive Dynamic". URL....
View 3 Replies
View Related
May 25, 2015
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Â
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
it will take 12 to 15 min to execute.
View 3 Replies
View Related
Jul 3, 2013
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
View 9 Replies
View Related
Nov 26, 2007
I use SQL 2000
I have a Column named Bool , the value in this Column is 0�0�1�1�1
I no I can use Count() to count this column ,the result would be "5"
but what I need is "2" and "3" and then I will show "2" and "3" in my DataGrid
as the True is 2 and False is 3
the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count
could it be done by Count()? please help.
thank you very much
View 5 Replies
View Related
Jul 23, 2005
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
View 5 Replies
View Related
Aug 21, 2007
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
group by substring(m.PTNumber, 3,3)
order by AUPct Desc
Thanks. Dan
View 1 Replies
View Related
Dec 20, 2007
Hi,
I'm trying to get a record count out of a databse using OLE DB Source and row count tasks but keep getting an error. I set up a variable as int32 and select the variable name in the row count task and when I go to the Input Columns tab to select a field to count, it gives me this error:
Error at Data Flow Task[Row Count[505]]: The component "Row Count" (505) has forbidden the requested use of the input column with lineage ID 32.
I don't even know what this means?
thanks,
View 4 Replies
View Related
Jun 25, 2007
Hi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
View 6 Replies
View Related
Aug 28, 2007
Is there a difference in performance when using count(*) or count(columnname)?
View 10 Replies
View Related
Mar 20, 2004
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
Thank you for your assistance in advance.
Joel
View 1 Replies
View Related
Feb 26, 2007
I have an sql command for when you add a new name to the database it counts to see how many of the name entered to the textbox exist in the database. If the count is 0 then it will add the name to the table. Else it displays an error message.
This works fine for inserting a new name but on my update page where you may update the name I have the same code which on button click counts to see how many exist. But if you leave the textbox the same value and click the button the count obviously results in 1 and brings up an error message.
Is there a way I can do a count but not including the name that is currently the value of the textbox?protected void UpdateSharedArea(object sender, EventArgs e)
{
SqlConnection connection = new SqlConnection(docShare_ConString);
//Count the amount of area names that are the same as typed by user
SqlCommand existCheck = new SqlCommand("SELECT COUNT(doc_area_name) FROM document_area WHERE doc_area_name = @doc_area_name", connection);
SqlParameter areaname = new SqlParameter("@doc_area_name", SqlDbType.VarChar);
areaname.Value = AreaText.Text;
existCheck.Parameters.Add(areaname);
connection.Open();
int count = (int)existCheck.ExecuteScalar();
connection.Close();
//If the area name does not exist within the table
if (count == 0)
{
//Update name Cheers, Mark
View 2 Replies
View Related
Jun 12, 2007
Hi,
This is my table structure ,
Name
John
John
Raj
John
Raj
From the above table i want to count the repeated name and my output should be
Name Count
John 3
Raj 2
since no of jone in my table is 3 and number of Raj is 2 .
how to write the query for this ?
View 1 Replies
View Related
Jun 17, 2007
I want to do something like this:
SELECT COUNT (SELECT TOP(10) * FROM MyTable order by Date Desc) FROM MyTable where User = 'Scott'
What I want is to return the number of affected rows where the column 'User' equals 'Scott'....But is should only check in the 10 latest inserted rows.....
Hope you understand what I mean...
View 4 Replies
View Related
Sep 26, 2007
'<%# Eval("Username") %>'
Hi: Everybody
Today is not my day, I am trying to get a Count(*) from a table (name: Photos), I can get the total number if like this: WHERE (u_username = 'jamest85' )
Now, I want replace the 'jamest85' to a value from a Label, like: Label1.text or '<%# Eval("Username") %>', but always has error, so can you please check for me, how to make it right?
Thanks.
Below is the code:
<asp:Label ID="Label1" runat="server" Text='<%# Eval("Username") %>'></asp:Label> <asp:SqlDataSource ID="SqlDataSource2"runat="server" ConnectionString="<%$ ConnectionStrings:myConString %>" SelectCommand="SELECT COUNT(*) AS TotalPhotos FROM Photos WHERE (u_username = 'jamest85' ) GROUP BY u_username"></asp:SqlDataSource>
Thank you very much
jamest85
View 2 Replies
View Related
Sep 26, 2007
Hello, I need to retrieve all records from a table named Blogs and the number of Posts associated with which Blog giving the name NumberOfPosts to that extra column. I have the following: SELECT b.*, p.COUNT(*) AS NumberOfPosts FROM dbo.Blogs b LEFT JOIN dbo.Posts p ON b.BlogId = p.BlogId I get the error: Incorrect syntax near '*'. Could someone, please, help me out? Thanks, Miguel
View 3 Replies
View Related
May 17, 2008
Hi all, Im using ASP.Net.In that i have an sql count statement which returns the count of the total messages in a particular date. Dim dt As Date
smsuser = uname.SelectedItem.Text
d = day.SelectedItem.Value
m = mon.SelectedItem.Value
y = year.SelectedItem.Value
dt = dt.ToShortDateString.Concat(d, "/", m, "/", y)
Dim StrSql As String = "select Count(Message) from Message where UserName='" & smsuser & "' AND Date='" & dt & "'"
Dim cmd As New SqlCommand(StrSql, con)
Dim reader As SqlDataReader
Dim no As String
Try
con.Open()
reader = cmd.ExecuteReader
If reader.Read = True Then
count.Text = reader.GetValue(0)
End If The count is displayed in a text box. But, the sql statement is not giving the correct count.Wat is wrong? Pls help...
View 10 Replies
View Related
Dec 24, 2003
Dear SQL,
I want to count the number of records, so I tried this:
SELECT COUNT(*) AS RecordCount
FROM Categories
WHERE Active = 1
ORDER BY Show_Order ASC
But it gives me an error:
error 8126: Column name 'Categories.Show_Order' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.
How can I make it work ? (I must ORDER it...)
View 6 Replies
View Related
