|
Thanks its working fine.
SHAHZAD ASLAM
|
|
|
|
|
you can only group by things you select
select min(count(friendname)), userid
from customer_friends
where friendname<>'Me' group by userid
may work. I'm not sure this will work overall, I don't have SQL Server here to test it.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
it Still not working the same error occuring.
|
|
|
|
|
Shahzad.Aslam wrote: The following error in orruring!
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
This:
select min(count(friendname)) is the source of the error.
You have nested two aggregate functions inside each other. COUNT() has a single result, so the MIN() of a COUNT() is always the same as the result of COUNT().. Essentially, there is nothing to aggregate.
I really cannot see what your intent was with this statement, so I cannot help you correct the problem. All I can suggest is to remove the MIN() and just leave the COUNT().
If you can explain your intentions then it might be easier to help.
|
|
|
|
|
hi
I think its not possible to find the minimum or maximum value for an aggregate function like 'count' because that will return a single value only..like if there are 12 members whose name <> 'ME', then the output will be 12.
From this 12, how can you find the minimum value? Thats why the error has occured.
|
|
|
|
|
Hai friends,
In my project i want to get the data from 2 databases.? i don't have any idea about this i tried but still i didn't get any answer...please help me to find a solution...i am waiting for your kind reply....
Thanks in advance...
Alex.
|
|
|
|
|
select........... dbo.tablename, 2nddatabsename.dbo.tablename.......
Gautham
|
|
|
|
|
Hello,
I have a typed dataset which I want to display in an ultragrid (infragistics) and display the parent and the children.
I can display this ok when I want to display all of the parents and children. But the problem is when user will need to select one parent (by customerID number) from a list and display that parent with its children in the grid.
I have created the dataset schema dsAssembliedEquipment.xsd and my 2 tables assembly and AssembiledEquipment. And also created the relationship between them.
I have a stored procedure (getDataByCustomerID) that will find the customerID of the assembly (parent) table.
This is my code for setting the grid.
<br />
'Table Adapter to get the customer ID record<br />
Me.TA_Assembly1.GetDataCustomerID(customerID)<br />
'Fill the assembly (parent) table and assembliedEquipment table(child)<br />
Me.TA_Assembly1.Fill(Me.DsAssemblyEquipment1.Assembly) Me.TA_AssembliedEquipment1.Fill(Me.DsAssemblyEquipment1.AssembliedEquipment)<br />
Me.grdCustomersAssembly.DataMember = "Assembly"<br />
Me.grdCustomersAssembly.DataSource = Me.DsAssemblyEquipment1 <br />
Code for the stored procedure getDataCustomerID:
<br />
ALTER PROCEDURE [dbo].[Assembly_SelByCustomerID]<br />
(<br />
@CustomerID int<br />
)<br />
AS<br />
SET NOCOUNT ON;<br />
SELECT AssemblyID, CustomerID, AssemblyName<br />
FROM dbo.[Assembly]<br />
WHERE CustomerID = @CustomerID <br />
I have Assembly (parent) Table fields AssemblyID / customerID / AssemblyName
I have child table called AssemblyEquipment (child) fields SerialNo / make / Model / AssemblyID (Foreign Key)
Many thanks for any help with this problem,
Steve
|
|
|
|
|
If you haven't already solved your problem I think it would be helpful to clarify what part you are having a problem with. Are you trying to change the stored procedure or add a customer row to the datagrid after you bind the data?
|
|
|
|
|
Hi Guru's
I created a stored procedure which on a straight query it will appear like this
select BookTitle, Author, Status from LibBooks where Author like '%Buck%'
On my SP I created two @parameters to have flexibility on my query, @SearchCategory - column name (Author)
@SearchField - text to be searched (Buck)
Problem:
I noticed that my query does'nt return result, its it possible to use @parameter as substitute to column name on a condition?
Something like this:
select BookTitle, Author, Status from LibBooks where @SearchCategory like '%' + @SearchField + '%'
SP that I created
Create procedure [dbo].[sp_searchFromCat]
(
@SearchCategory varchar(100) = null,
@SearchField varchar (100) = null
)
as
begin
select
BookTitle,
Author,
Status
from
LibBooks
where
@SearchCategory like '%' + @SearchField + '%'
end
|
|
|
|
|
The easiest way to make the column in the where clause dynamic is to build the query in the procedure then execute the sql code. example from article:
Article on dynamic sql [^]
use Northwind
go
declare @RECCNT int
declare @ORDID varchar(10)
declare @CMD Nvarchar(100)
set @ORDID = 10436
SET @CMD = 'SELECT @RECORDCNT=count(*) from [Orders]' +
' where OrderId < @ORDERID'
print @CMD
exec sp_executesql @CMD,
N'@RECORDCNT int out, @ORDERID int',
@RECCNT out,
@ORDID
print 'The number of records that have an OrderId' +
' greater than ' + @ORDID + ' is ' +
cast(@RECCNT as char(5))
Another idea is if you only have a couple possible columns you could do something like this
Select *
From table
where column1 = isnull(@Column1Value, column1)
and column2 = isnull(@Column2Value, column2)
This might not perform well on a large table
|
|
|
|
|
Hi,
I guess I'll go to the second suggestion you gave, my brain got tangled after reading the first one. checking on this
Thanks
|
|
|
|
|
Based on what condition your query should be executed? where....??
Gautham
|
|
|
|
|
Hi Gautham,
Just got the query working, thanks!
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_searchFromCat]
(
@SearchCategory varchar(100) = null,
@SearchField varchar (100) = null
)
as
begin
declare @SQL varchar(4000)
set @SQL = 'select BookTitle, Author, Status from LibBooks ' +
case when @SearchCategory is null then ''
else 'where ' + @SearchCategory + ' like ' + '''%' + isnull(@SearchField,'') + '%'''
end
exec(@SQL)
end
Dom
|
|
|
|
|
I am running the following query:
select sum(total) as 'Total QA Hours For F.I Centralized Alert Int', task.name as 'Project' from trans
inner join task on task.name=trans.task where trans.project = '374'
and [date] >= '1/31/2007'
group by task.name
The following error is returned.
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
Trans.task = bigint
Task.Name = Varchar (250)
trans.Total = float
Prsumably I need to use cast?
I have not been able to figure this one out.
Any help would be greatly appreciated.
Regards,
Hulicat
|
|
|
|
|
select
sum(total) as 'Total QA Hours For F.I Centralized Alert Int'
, task.name as 'Project'
from trans
inner join task on task.name=Cast(trans.task as varchar(250))
where trans.project = '374'
and [date] >= '1/31/2007'
group by task.name
|
|
|
|
|
Thanks, I just read another one of your replies to someone elses problem that helped me with something else I am trying to do.
I really appreciate the help!
Regards,
Hulicat
|
|
|
|
|
Hi,
As a disclaimer, I'm new to DB programming and .NET in general. My questions regard using parameters. I am developing a local application, and placing the right types of quotes in SQL statements depending on the datatype being passed is cumbersome. So, after doing some reading I'm attempting to use parameters.
So, if I'm using parameters, does it suffice to specify the OleDbDataType to catch an attempt to pass an incorrect datatype into a database field.
I suppose I'm a little unsure as to how much type-checking I need to apply to user input when using parameters.
Thanks for your help.
|
|
|
|
|
Whenever I've used parameters, trying to execute SQL causes an error if I've provided the wrong data type. I typically use a generic Try Catch block around the code that executes the SQL to catch errors like that.
Example:
Try<br />
myDataAdapter.Fill(myDataTable)<br />
Catch ex As Exception<br />
MessageBox.Show("Error: " & ex.Message)<br />
End Try
Hope this helps.
|
|
|
|
|
Hi all,
I am building an employee search application depending on 6 criterias(emp name,emp grade,dept,hire date,qualification,work timings).I have 4 tables-Employee,Emp_Qualifications,Work_Details,dept which are as follows.
Employee
------------------------------------------------------------------------
empid(PK) | fname | lname | grade | Designation | Dept_id(foriegn key) |
------------------------------------------------------------------------
dept
-----------------------------
Dept_id (PK) |Dept_name(PK) |
-----------------------------
Emp_Qualifications
---------------------------------
empid (PK) |qualification(PK) |
---------------------------------
123 | J2ME |
----------------------------------
123 | SAP |
----------------------------------
100 | Java |
----------------------------------
Work_Details
---------------------------------------------------------------
empid(PK) | Clear_Date(Date time)(PK) | document_name(PK) |
---------------------------------------------------------------
123 | 12/01/2007 | Balance sheet |
----------------------------------------------------------------
123 | 12/01/2007 | data sheet |
---------------------------------------------------------------
i want to select empid,fname,lname,designation,dept_name,(Collection of all expertise seperated by comma) for that empid from all tables where empid,name,dept,grade,qualification,work timings are provided by user.
Thanks in advance!!!
|
|
|
|
|
I'm assuming that you are using SQL-Server. You will need to use a database function to get the list of qualifactions:
CREATE FUNCTION dbo.ConcatQualifications(@EmpId INT) RETURNS VARCHAR(200)
AS BEGIN
DECLARE @Expertise VARCHAR(200), @Qualification VARCHAR(20)
DECLARE c1 CURSOR LOCAL FAST_FORWARD FOR
SELECT Qualification FROM Emp_Qualifications
WHERE EmpId = @EmpId
ORDER BY Qualification
OPEN c1
WHILE (@@FETCH_STATUS = 0) BEGIN
FETCH c1 INTO @Qualification
IF (@@FETCH_STATUS = 0) BEGIN
SET @Expertise = IsNull(@Expertise + ', ', '') + @Qualification
END
END
CLOSE c1
RETURN @Expertise
ENDThe basic query will look something like:
SELECT Employee.empid, Employee.fname, Employee.lname, Employee.designation,
Department.dept_name, dbo.ConcatQualifications(Employee.empid) Expertise
FROM Employee
INNER JOIN Department
ON Department.deptid = Employee.deptid
WHERE ####Your criteria goes here####
ORDER BY Employee.empid You can then insert any criteria that is relevent (e.g. "Employee.lname LIKE 'Smith%').
Once you have this working, you need to do a search on this forum for "SQL Injection" because the above criteria will fail (potentially dangerously) if the employee's name of "O'Leary".
Hope this helps.
Andy
|
|
|
|
|
Hi,
I set up SQL Express 2005 on WinServer 2003 and my program works fine locally with following connection string: (as suggested in www.connectionstrings.com)
Provider=SQLNCLI;Server=myServerName\MYSQLEXPRESS;Database=myDataBase;Uid=myUsername; Pwd=myPassword
Now, I want to run the program from a client connected to the server. I tried the following string:
Provider=SQLNCLI;Server=myServerName\MYSQLEXPRESS,1433;Database=myDataBase;Uid=myUsername; Pwd=myPassword
and it failed to connect with the following error message:
Code = 800a0e7a<br />
Code meaning = Unknown error 0x800A0E7A<br />
Source = ADODB.Connection<br />
Description = Provider cannot be found. It may not be properly installed.
I have the following enabled:
- remote connection "using both tcp/ip and named pipes" (from Surface Area Configuration)
- shared memory (from SQL Server Configuration Manager)
- SQL Server Browser service
On side note: (I don't know if these matter)
- SQL Server 2000 (full version) is installed
- the server is a domain controller (I am not sure, since I didn't set it up)
- there is no firewall
Is it possible that 1433 is not the right port? How to get/check the right port?
Can anyone help me here? Thanks in advance.
|
|
|
|
|
Sounds like you need to install SQL 2005 Network Client on the client box.
|
|
|
|
|
I'm not sure if your "Server=..." setting is valid.
This article may help with connection strings: Using Connection String Keywords with SQL Native Client[^]
Maybe "Address=..." is the one you want?
To configure TCP or check the configured port I believe you can use SQL server Management Studio.
Right-click the server, choose SQL Server Configuration Manager and go to the network
configuration/ protocols section.
|
|
|
|
|
I finally found and fixed the problem. It was my original hunch, that 1433 is not the right LISTENING port (although the setting of SQL Express still points to 1433). To check the port, run cmd.exe and look at the result of "netstat -ano".
Hopefully someone find this useful and don't need to spend days figuring this out.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
