ExecuteScalar - Count(*)

Mar 25, 2007

i have Cust table with 5 columns (Name, Add, Contact, UserID, Passwd)

my sql statement is not working correctly..
"SELECT COUNT(*) FROM Cust WHERE UserID='" + textBoxEmail + "'AND Passwd='" + textBoxPW + "'"

what maybe the problem? i have 1 record and when im running it, whether the input is right or wrong, the count is always zero(0). i think the problem is in my sql statement(maybe in the where clause) because i tried counting the records by "select count(*) from cust" and it correctly says 1 record. pls help!

View 3 Replies


ADVERTISEMENT

Get Count With Executescalar()

Apr 18, 2005

Howdie y'all!
I'm trying to do an executescalar() on the next stored procudure...
SELECT COUNT(*) FROM tblUsers WHERE UserEmail = @UserEmail;
Strangely enough I get SqlServer exception that tells me there's a syntax error.
Is there something I'm overseeing?
Cheers,
Wes

View 2 Replies View Related

ExecuteScalar

Mar 23, 2007

private void buttonLogin_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirecto ry|\PEService.mdf;Integrated Security=True;User Instance=True";
conn.Open();
string strSQL = "Select Count(*) as ctr From Cust Where Email=" + textBoxEmail + "and Passwd=" + textBoxPW;

SqlCommand cmd = new SqlCommand(strSQL,conn);
int ctr=(int)cmd.ExecuteScalar();
if (ctr == 1)
MessageBox.Show("Correct");
else
MessageBox.Show("Wrong");
conn.Close();
}

i have this code for my login form. when i remove conn.Open(); in the code
it says... ExecuteScalar requires an open and available Connection. The connection's current state is closed.

and when i put conn.Open();
it says... An attempt to attach an auto-named database for file C:... failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

what is the problem?

View 6 Replies View Related

ExecuteScalar() Not Returning Value?

Dec 12, 2007

Okay so here's a wierd one.  I use SQLYog to peek into/administrate my databases.I noticed that this chunk of code is not producing a value... Using Conn As New MySqlConnection(Settings.MySqlConnectionString)
Using Cmd As New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE '%?CurrentPage%') AND Enabled=1", Conn)
With Cmd.Parameters
.Add(New MySqlParameter("?CurrentPage",thisPage))
End With
Conn.Open()
Exists = Cmd.ExecuteScalar()
End Using
End Using Exists is declared outside of that block so that other logic can access it.  thisPage is a variable declared outside, as well, that contains a simple string, like 'index.aspx'. With the value set to 'index.aspx' a count of 1 should be returned, and is returned in SQLYog. SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE '%index.aspx%') AND Enabled=1 This produces a value of 1, but NO value at all is returned from Cmd.ExecuteScalar().  I use this method in MANY places and don't have this problem, but here it rises out of the mist and I can't figure it out.  I have no Try/Catch blocks so any error should be evident in the yellow/red error screen, but no errors occur in the server logs on in the application itself. Does anybody have any ideas?

View 3 Replies View Related

ExecuteScalar() Returns -1

Oct 15, 2004

The following query returns 0 when executing in Query Analyzer:SELECT isnull(Count(*),0) as total FROM SplitDetail WHERE SiteCode = 14 AND ProjectID = 4367Yet ExecuteScalar() in vb.net return a -1.

Any ideas on what I might be doing wrong... ?

View 1 Replies View Related

ExecuteScalar Problems. Need Help

Apr 26, 2006

Hi all
I am currently developing a Help Desk for our company. One of my problems is Data lookups in other tables within a SQL 2000 DB. i.e. Client Details and Information in one table (hd_clients) and Client History (hd_history) in another.
'hd_history' contains a column called 'c_id' which references the 'hd_clients' table 'c_id' column A typical One-to-Many relationship. When a user goes to the Help Desk's Service page. I want to display the client's name in one of my GridView's Databound Columns. See Below:
...
<asp:TemplateField HeaderText="Client" SortExpression="c_id">
   <ItemTemplate>
      <asp:Label ID="lblClient" runat="server" Text='<%#GetClient(Eval("c_id")) %>' />
   </ItemTemplate>
</asp:TemplateField>
...
This then calls: GetClientName - Which is as follows.
...
Public Function GetClientName(ByVal ClientID)
Dim ScalarValue As String = ""
Dim myConnection As New SqlConnection("Data Source=XXX; Initial Catalog=XXX; uid=XXX; pwd=XXX")
Dim myCommand As New SqlCommand("SELECT [Name] FROM [hd_clients] WHERE [c_id] = @ClientID", myConnection)
myCommand.Parameters.Add("@ClientID", SqlDbType.Int)
myCommand.Parameters("@ClientID").Value = ClientID
   Try
      myConnection.Open()
      ScalarValue = myCommand.ExecuteScalar
   Catch ex As Exception
      Console.Write(ex.Message)
   End Try
 
   If ScalarValue > "" Then
      Return ScalarValue.ToString
   Else
      Return "<span style='color: #CCCCCC'>- NULL -</span>"
   End If

 
End Function
...
This works perfectly on my Laptop (which runs the IIS and SQL Server Instances + VS2005). But, when placed on our production server brings back the '- null -' value instead of the Client's Name. I have set both machines up in exaclty the same way - and cannot get this to work. I have tried 'ExecuteReader' but from what I understand is 'ExecuteScalar' is better for single value lookups.
 
Any help in this matter would be great and really appreciated. Thanks.
 
David
 

View 2 Replies View Related

SqlCommand.ExecuteScalar()

May 4, 2006

Is there documentation on what ExecuteScalar() will return if the SQL statement is returning an image?

View 1 Replies View Related

How To Store ExecuteScalar Value Into Variable ?

Oct 16, 2006

I having a strange problem, my code is as below: cmd.connection = cnncmd.commandtext = "select count(*) from member" dim i as integeri = cInt(cmd.executescalar) However, what the result tat i get is "&H0" ! When I use the same query in sqlquery, it did show out the result as "11".I have no idea abt wat is goin on, can anyone gv me some guide ?  Thanks.

View 2 Replies View Related

ExecuteScalar And 2 Rows In My DB On Every Write?

May 30, 2008

Hey folks, I was just learning how to work with ScopeIdentity and I found out I needed somthing called executescalar. Things seem to be working really well, and I think I did it write, except I'm having one strange little oddity. Everytime my page writes to my databases, it writes rows. I'm really curious to know what I did wrong. Any ideas? Thank you as always :-)
 Public Sub newoptic(ByVal sender As Object, ByVal e As System.EventArgs) Handles newpostBTN.Click
If newpostTXTBX.Text = "" Then
Exit Sub
End If
Dim mySQL As String = "insert into msg_topics (topic_title, topic_forum_id) values (@topicTITLE, @forumID); Select Scope_Identity()"Dim myConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("DBconnect"))
Dim cmd As New SqlCommand(mySQL, myConn)cmd.Parameters.AddWithValue("@topicTITLE", newposttitleTXTBX.Text)
cmd.Parameters.AddWithValue("@forumID", HDN_topic_forum_ID_LBL.Text)
myConn.Open()Dim topic_ID_holder As Integer = cmd.ExecuteScalar()
HDN_topic_id_holder_LBL.Text = (topic_ID_holder)
cmd.ExecuteNonQuery()
myConn.Close()
End Sub
 
Oh, one more quick question if whoever responds knows the answer. Why do I need a semi-colon here "@forumID); Select" ? The websites I learned about this from didn't explain that, and I've never used a semicolon in my select statements before, so I figured there must be something special about it.
 
 Thank you

View 2 Replies View Related

Getting GUID Value From StoredProcedure.ExecuteScalar()

Jun 12, 2008

I need to execute stored procedure which is suppose to return GUID to my IF statement and if it is Nothing I execute other Stored procedures else some other procedures. My problem is that even though by looking at the data I know that after the execution of the procedure it should return some guid value it doesn't anybody who had the same issue??? That is the code block where I am trying to return guid from my stored procedure:   getGroupID.Parameters("@GroupName").Value = dr.Item("Group ID").ToString()            If getGroupID.ExecuteScalar() = Nothing Then                'Find Group by IP address if input Data Table doesn't have group                getGroupIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString()                If getGroupIDByIP.ExecuteScalar() = Nothing Then                    insertGroup.Parameters("@GroupID").Value = Guid.NewGuid                    insertGroup.Parameters("@Group").Value = dr.Item("Group ID")                    insertGroup.Parameters("@ACCID").Value = getAccID.ExecuteScalar()                    insertGroup.ExecuteNonQuery()                    command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar()                Else                    command.Parameters("@Group_ID").Value = getGroupIDByIP.ExecuteScalar()                End If            Else                command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar()            End If Thank you 

View 2 Replies View Related

ExecuteScalar Returns Null

Oct 25, 2006

I am using the following C# code and T-SQL to get result object from aSQL Server database. When my application runs, the ExecuteScalarreturns "10/24/2006 2:00:00 PM" if inserting a duplicated record. Itreturns null for all other conditions. Does anyone know why? Doesanyone know how to get the output value? Thanks.------ C# -----aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};object oRtnObject = null;StoredProcCommandWrapper =myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);------ T-SQL ---ALTER PROCEDURE [dbo].[procmyCalendarInsert]@pBegin datetime,@pEnd datetime,@pUserId int,@pOutput varchar(200) outputASBEGINSET NOCOUNT ON;select * from myCalendarwhere beginTime >= @pBegin and endTime <= @pEnd and userId = @pUserIdif @@rowcount <0beginprint 'Path 1'set @pOutput = 'Duplicated reservation'select @pOutput as 'Result'return -1endelsebeginprint 'Path 2'-- check if upperlimit (2) is reachedselect rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30))),count(rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30))))from myCalendargroup by rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30)))having count(rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30)))) =2and (rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30))) =rtrim(cast(@pBegin as varchar(20)))+ ', ' + rtrim(cast(@pEnd asvarchar(20))))-- If the @@rowcount is not equal to 0 then-- at the time between @pBegin and @pEnd the maximum count of 2 isreachedif @@rowcount <0beginprint 'Path 3'set @pOutput = '2 reservations are already taken for the hours'select @pOutput as 'Result'return -1endelsebeginprint 'Path 4'--safe to insertinsert dbo.myCalendar(beginTime, endTime,userId)values (@pBegin, @pEnd, @pUserId)if @@error = 0beginprint 'Path 4:1 @@error=' + cast(@@error as varchar(1))print 'Path 4:1 @@rowcount=' + cast(@@rowcount as varchar(1))set @pOutput = 'Reservation succeeded'select @pOutput as 'Result'return 0endelsebeginprint 'Path 4:2 @@rowcount=' + cast(@@rowcount as varchar(1))set @pOutput = 'Failed to make reservation'select @pOutput as 'Result'return -1endendendEND

View 1 Replies View Related

Getting NullReferenceException When Executing ExecuteScalar()

Nov 20, 2007

I'm trying to add user information to the database however I'm getting a NullReferenceException.

Here's the code:


SqlCommand UserAddCommand = new SqlCommand();

int AssignedUserID = 0;

UserAddCommand.CommandType = CommandType.StoredProcedure;

UserAddCommand.Connection = m_DBConnection;

UserAddCommand.CommandText = "SP_UserAdd";

UserAddCommand.Parameters.AddWithValue("@User_Name", DbType.String);

UserAddCommand.Parameters.AddWithValue("@Street_Address", DbType.String);

UserAddCommand.Parameters.AddWithValue("@City", DbType.String);

UserAddCommand.Parameters.AddWithValue("@State", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@Zip_Code", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@Email_Address", DbType.String);

UserAddCommand.Parameters.AddWithValue("@Phone_Number", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@User_Login_Name", DbType.String);

UserAddCommand.Parameters.AddWithValue("@User_Password", DbType.String);

UserAddCommand.Parameters.AddWithValue("@Referrer_Name", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@User_Type", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters["@User_Name"].Value = UserInfo.Name;

UserAddCommand.Parameters["@Street_Address"].Value = UserInfo.StreetAddress;

UserAddCommand.Parameters["@City"].Value = UserInfo.City;

UserAddCommand.Parameters["@State"].Value = UserInfo.State;

UserAddCommand.Parameters["@Zip_Code"].Value = UserInfo.ZipCode;

UserAddCommand.Parameters["@Email_Address"].Value = UserInfo.EmailAddress;

UserAddCommand.Parameters["@Phone_Number"].Value = UserInfo.PhoneNumber;

UserAddCommand.Parameters["@User_Login_Name"].Value = UserInfo.UserName;

UserAddCommand.Parameters["@User_Password"].Value = UserInfo.UserPassword;

UserAddCommand.Parameters["@Referrer_Name"].Value = UserInfo.ReferrerName;

if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.FAMILY)

{

UserAddCommand.Parameters["@User_Type"].Value = "Family";

}

else if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.FRIEND)

{

UserAddCommand.Parameters["@User_Type"].Value = "Friend";

}

else if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.MANAGER)

{

UserAddCommand.Parameters["@User_Type"].Value = "Manager";

}

else if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.OWNER)

{

UserAddCommand.Parameters["@User_Type"].Value = "Owner";

}

try

{

AssignedUserID = (int)UserAddCommand.ExecuteScalar(); this line of code produces the NullReferenceException

UserInfo.UserID = AssignedUserID;

m_UserInfo = UserInfo;

}

catch (Exception Ex)

{

Console.WriteLine(Ex.ToString());

}



Please help me figure out what I am doing wrong.

View 1 Replies View Related

Calling Method To Do Executescalar With Parameters

Feb 15, 2008

I have a method like follows:
string EmpCount = null;
DateTime _dtstart = Convert.ToDateTime(txtStart.Text.Trim());
DateTime _dtend = Convert.ToDateTime(txtEnd.Text.Trim());SQLProvider sqlp = new SQLProvider(System.Configuration.ConnectionStrings["ConString"].ConnectionString);string SqlText = @"
select count(*) from employee
where activeemp=1 and startdate BETWEEN  @dtstart and @dtend;
using (sqlp.Connection) {
sqlpm [] param = {
 new sqlpm("dtStart", _dtstart),
 new SqlP("dtEnd", _dtend)
 };
 }EmpCount = sqlp.ExecuteScalar(SqlText, param).ToString();
return Convert.ToInt32(mbrCount);
 
Then the method I am calling is:public Object ExecuteScalar(String sqlText, Sqlp[] param)
{try
{
//Some Code here
}
}
So in the calling method (ExecuteScalar), the second parameter is defined as an array, is it ok to have an array in the called method too?

View 1 Replies View Related

ExecuteScalar Returns 0 (null) But INSERT Is Successful.

Sep 25, 2007



I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:


RecordID = cmd.ExecuteScalar


I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

View 7 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

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

ExecuteScalar --&> How To Get The OrderID(Identity) From A Table To Another Table ??

Mar 23, 2006

I am new to asp.net and studying on book.. currently i am stuck with a problem which not understand what is it !! Can anyone help me ?? I trying a shopping cart "Check Out" method, and when i am done the process.. My order_lines Table can update the OrderID which just generated !! What wrong with the statement ??  
 
Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick        ' Insert the order and order lines into the database        Dim conn As SqlConnection = Nothing        Dim trans As SqlTransaction = Nothing        Dim cmd As SqlCommand
        Try            conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)            conn.Open()            trans = conn.BeginTransaction            cmd = New SqlCommand()            cmd.Connection = conn            cmd.Transaction = trans
            ' set the order details            cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@MemberName, @OrderDate, @Name, @Address, @City,@State, @PostCode, @Country, @Total)"            cmd.Parameters.Add("@MemberName", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@OrderDate", Data.SqlDbType.DateTime)            cmd.Parameters.Add("@Name", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@Address", Data.SqlDbType.VarChar, 255)            cmd.Parameters.Add("@City", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@State", SqlDbType.VarChar, 50)            cmd.Parameters.Add("@PostCode", Data.SqlDbType.VarChar, 15)            cmd.Parameters.Add("@Country", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@Total", Data.SqlDbType.Money)
            cmd.Parameters("@MemberName").Value = User.Identity.Name            cmd.Parameters("@OrderDate").Value = DateTime.Now()            cmd.Parameters("@Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text            cmd.Parameters("@Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text            cmd.Parameters("@City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text            cmd.Parameters("@State").Value = CType(Wizard1.FindControl("txtState"), TextBox).Text            cmd.Parameters("@PostCode").Value = CType(Wizard1.FindControl("txtPostCode"), TextBox).Text            cmd.Parameters("@Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text            cmd.Parameters("@Total").Value = Profile.Basket.Total
            Dim OrderID As Integer            OrderID = Convert.ToInt32(cmd.ExecuteScalar())  <-- Is it wrong or need to add wat ?            ' change the query and parameters for the order lines            cmd.CommandText = "INSERT INTO OrderLines(OrderID, ProductID,Quantity, Price) VALUES (@OrderID, @ProductID, @Quantity, @Price)"            cmd.Parameters.Clear()            cmd.Parameters.Add("@OrderID", Data.SqlDbType.Int)            cmd.Parameters.Add("@ProductID", Data.SqlDbType.Int)            cmd.Parameters.Add("@Quantity", Data.SqlDbType.Int)            cmd.Parameters.Add("@Price", Data.SqlDbType.Money)            cmd.Parameters("@OrderID").Value = OrderID
            For Each item As CartItem In Profile.Basket.Items                cmd.Parameters("@ProductID").Value = item.ProductID                cmd.Parameters("@Quantity").Value = item.Quantity                cmd.Parameters("@Price").Value = item.UnitPrice                cmd.ExecuteNonQuery()            Next            ' commit the transaction            trans.Commit()        Catch SqlEx As SqlException            ' some form of error - rollback the transaction            ' and rethrow the exception            If trans IsNot Nothing Then                trans.Rollback()            End If            ' Log the exception            Throw
        Finally            If conn IsNot Nothing Then                conn.Close()            End If        End Try        ' we will only reach here if the order has been created successfully        ' so clear the cart        Profile.Basket.Items.Clear()    End Sub

View 4 Replies View Related

Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?

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

Count For Varchar Field - How To Get Distinct Count

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

In SQL 2000 Can I Use Count() To Count A Column?

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

Table Row Count + Index Row Count

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

Obtain Unit Percent With Unit Count Divided By Total Count In Query

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

Inserted Rows Count From SSIS Not Like Table Rows Count

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

Count(*) Vs Count(columnname)

Aug 28, 2007

 Is there a difference in performance when using count(*) or count(columnname)?

View 10 Replies View Related

SQL Query Automatically Count Month Events B4 Today; Count Events Today + Balance Of Month

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

Name Count

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

How To Count This ?

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

COUNT And TOP

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

Get Count From A Sql

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

Count

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

Sql Count

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

Help With COUNT(*)

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

Using Count() In Sql

Feb 19, 2004

I have a table(sometable) in my db that looks like this:
user item
bob books
bob pens
bob frogs
jay pencils
rob cups
rob plantsI run this script:SELECT DISTINCT user AS users
FROM sometable to get:users
bob
jay
robHow would I get it to include the # of rows ror each user like this:
users number
bob 3
jay 1
rob 2Thanks in advance.

View 7 Replies View Related

Count(*)

Dec 3, 2001

What is wrong with this query?

SELECT *, (SELECT Count(*) FROM TPlanObjects where FMainID=tpm.FID and FType='ARTICLE') as FArtCount, (SELECT Count(*) FROM TPlanObjects where FMainID=tpm.FID and FType='FILE') as FObjCount FROM tPlanMain as tpm

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved