Help in creating dynamic rows!

Hi

I am trying to create a dynamic row. Say for example, I want to list all the "Employees" belonging to a particular department say "Physics". So, I would want every employee name to be in a separate row. How do I go about doing this? Any help/suggestions is appreciated.

Thanks

Take a look at the Matrix report item.

With a matrix you can define a group of say 'department' and then show in the details the 'Employees'

Take a look at http://msdn2.microsoft.com/en-us/library/ms157334.aspx

Help In Coding

Dim objConn As New SqlConnection
Dim objCmd As New SqlCommand
Dim Value As String = EventCmb.SelectedItem.ToString()
objConn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Documents and Settings\HP\My Documents\Visual Studio 2005\WebSites\FYP2\App_Data\Event.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True"
Try
objConn.Open()
objCmd.Connection = objConn
objCmd.CommandType = CommandType.Text
objCmd.CommandText = "SELECT EventTel FROM Event WHERE (EventID = @.Value)" ' See how i changed Value to @.Value. This is called a Named Parameter
objCmd.Parameters.AddWithValue("@.Value", Value) ' Add the @.value withthe actual value that should be put. This makes it securer
Dim RetVal As Object = objCmd.ExecuteScalar() ' This returns the First Column of the first row regardless of how much data is returned.
If Not ((RetVal Is Nothing) Or (RetVal Is DBNull.Value)) Then
ContactLbl.Text = RetVal.ToString()
Else
' noting was returned
End If
Catch ex As Exception
Throw ex
Finally
objConn.Close()
End Try

There's an error for in line "Dim RetVal As Object = objCmd.ExecuteScalar() "

Error Message is as follow"Conversion failed when converting the nvarchar value 'LTA' to data type int."

It is due to "ExecuteScalar() "can only store int? I just need to display one data, andValue data comes form a combo box "EventCmb", which i wanted to find the selected String, compared to the "Event" database to get the "EventTel" data How do i solved this problem, any advice? Thanks!

In your code

Dim Value As String = EventCmb.SelectedItem.ToString()

you defined Value as a string, but in our sql

"SELECT EventTel FROM Event WHERE (EventID = @.Value)"

EventID look like a int, this is where the convering error come from

Hope this help

sql

Help in Coalescing distinct values

Hi all,

I want to know how to coalesce distinct values as comma seperated into a variable which is used elsewhere. Here are my ddl and the query I tried.
My Expected result is
[Java],[MySQL],[.Net]

Code Snippet

CREATE TABLE #Tbl_Request
(
ID INT,
SkillCategoryID INT
)
GO

CREATE TABLE #Lkp_SkillCategory
(
ID INT,
Skill varchar(50)
)
GO

INSERT INTO #Tbl_Request VALUES(1,0)
INSERT INTO #Tbl_Request VALUES(2,1)
INSERT INTO #Tbl_Request VALUES(3,2)
INSERT INTO #Tbl_Request VALUES(4,0)
INSERT INTO #Tbl_Request VALUES(5,2)
INSERT INTO #Tbl_Request VALUES(6,2)
INSERT INTO #Tbl_Request VALUES(7,1)
GO

INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')
INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')
INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')
GO

DECLARE @.listSkills nvarchar(max)
SELECT DISTINCT @.listSkills= COALESCE(@.listSkills+',','')+'['+ #Lkp_SkillCategory.Skill+']'
FROM #Tbl_Request INNER JOIN
#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID
SET @.listSkills=(SELECT Skill = @.listSkills)
SELECT @.listSkills

DROP TABLE #Lkp_SkillCategory,#Tbl_Request

The following is one of the method

Code Snippet

CREATE TABLE #Tbl_Request

(

ID INT,

SkillCategoryID INT

)

GO

CREATE TABLE #Lkp_SkillCategory

(

ID INT,

Skill varchar(50)

)

GO

INSERT INTO #Tbl_Request VALUES(1,0)

INSERT INTO #Tbl_Request VALUES(2,1)

INSERT INTO #Tbl_Request VALUES(3,2)

INSERT INTO #Tbl_Request VALUES(4,0)

INSERT INTO #Tbl_Request VALUES(5,2)

INSERT INTO #Tbl_Request VALUES(6,2)

INSERT INTO #Tbl_Request VALUES(7,1)

GO

INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')

INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')

INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')

GO

--Add Temp table to hold distinct values

create table #temp ( Skill varchar(50) )

Insert into #temp(Skill)

SELECT distinct #Lkp_SkillCategory.Skill

FROM #Tbl_Request INNER JOIN

#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID

--Coalesce from Temp table

DECLARE @.listSkills nvarchar(max)

SELECT @.listSkills= COALESCE(@.listSkills+',','')+'['+ #Temp.Skill+']'

FROM #Temp

SELECT @.listSkills

--Drop the tables

DROP TABLE #Temp,#Lkp_SkillCategory,#Tbl_Request

|||

or you can try this one

Code Snippet

CREATE TABLE #Tbl_Request
(
ID INT,
SkillCategoryID INT
)
GO

CREATE TABLE #Lkp_SkillCategory
(
ID INT,
Skill varchar(50)
)
GO

INSERT INTO #Tbl_Request VALUES(1,0)
INSERT INTO #Tbl_Request VALUES(2,1)
INSERT INTO #Tbl_Request VALUES(3,2)
INSERT INTO #Tbl_Request VALUES(4,0)
INSERT INTO #Tbl_Request VALUES(5,2)
INSERT INTO #Tbl_Request VALUES(6,2)
INSERT INTO #Tbl_Request VALUES(7,1)
GO

INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')
INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')
INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')
GO

DECLARE @.listSkills nvarchar(max)

select @.listSkills = COALESCE(@.listSkills+',','') + '[' + Skill + '] '
FROM
(
SELECT distinct Skill
FROM #Tbl_Request INNER JOIN
#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID
) tbl

SET @.listSkills=(SELECT Skill = @.listSkills)
SELECT @.listSkills

DROP TABLE #Lkp_SkillCategory,#Tbl_Request

Thanks,

R@.j

|||

Code Snippet

Declare @.OutputString varchar(max)

select @.OutputString = IsNull(@.OutputString + ', ' + sValue , sValue)

from (

select distinct '[' + Skill + ']' as [sValue]

from [MyTable]

) as [SubQuery]

select @.OutputString

|||Thank you both rusag2 and Raj for the answer. Works perfectly fine.

Help in Chosing the Software for Reporting Services

Hi All,

I have downloaded SQL Server 2005 Express Edition with Advanced Services SP2 from the following link

http://msdn.microsoft.com/vstudio/express/sql/download/

This I did after hearing Advanced Services provide Reporting Services tool with basic functionalities. After installion I am not able to find the SQL Server Business Intelligence Development Studio. Also there is no provision for installing the Reporting Services.

I already have Visual Studio Express installed.

Could someone tell me what else to download? Also it is asking for another software to be downloaded along with the existing one.

Regards

Karthik

In Visual Studio, when you try and create new projects, do you see a folder called "business intelligence"?|||

I am using Visual Web Developer 2005 Express Edition not Visual Studio. I don't have that option.

I was browsing through many other but couldn't understand clearly what is going wrong. Could you tell me the pre-requisites required for the installation?

|||http://www.microsoft.com/technet/prodtechnol/sql/2005/usingssrswithsqlexpress.mspx|||

Thanks for the link...

After lot of searching I figured out the option to install Reporting Services would not appear till IIS is installed. After doing this it is working fine now.

Could you please let me know if installation of SQL Server 2005 Express Edition with Advanced Services SP2 is possible in Vista Home Basic. Nowhere it is clearly explained if it can be done or not.

Help in choosing SQL Server edition

My company has a website that connects to a sql server (on a different box). I am trying to convince them to get sql server 2005. However, I do not know if SQL Server 2005 Workgroup edition is okay for our needs. Can someone please tell me if it is.

Basically, our setup is the following:

The SQL Server will only have one/two clients - the web server

The SQL Server will have a number of different user accounts though

The SQL Server will have its databases replicated to another SQL Server - both will be 2005

The SQL Server will not have more than 4Gb of RAM

The SQL Server will only have one processor - possible Xeon, and/or Dual Core

The database will not have complicated analysis done on them. The most that will be done are complicated selects which indexing should be able to handle.

We use crystal reports.

Thank you for any help.

Jagdip.

One other big consideration to make in your analysis is the volume of traffic you're going to have to the web server. If your web server is incredibly active and is going to be hammering the SQL server with requests, that will definitely affect your decision.

This page illustrates the differences between the editions:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Off the bat, it sounds like one feature that the Workgroup edition might lack that you need is Mirroring. Other than that, from what you described, I would think that the Workgroup edition would be fine for you. If you can find a way around the requirement for Mirroring (some other replication scheme--several exist), then you might be able to stick with the Workgroup rather than going with Standard.

Ryan

|||

Hi ya,

You have got a difficult choice. Well if you don't want to have that much and can replicate using your own logic then Sql Server express, the only limit for Database size which is a 4 GB. Workgroup would be better but you have got RAM limit of 3 GB although no limit on actual DB itself.

If you want to be cost effective then use SQL Express, if you can okay with small cost then workgroup

You can read the whole comparision about it from thislink

Hope that helps

Cheers

|||

Thank you both for your replies.

SQL Express is not an option as some of the databases are already over 4Gb.

The volume of traffic is not going to be great. If things go the way they are, we are looking at about 10 users per hour !!!

Thank you for the links as well. I had read these and was still unsure. Database Mirroring sounds good - but we are implementing a failover solution on the web server instead. If the primary web server/database fails over, then the secondaries will become active (using either Neverfail or Doubletake software). We are planning on using merge replication, so this should not be a problem.

The one thing I am worried about is the number of clients that can access the sql server simultaneously. Will the sql server restrict the number of users logged at once?

|||

I can't find anything anywhere about a limit on concurrent users for Workgroup Edition. If any of you SQL guys out there know of one, please chime in. It sounds like you're looking at VERY low usage though, so you should be fine. Were it not for your other requirements, SQL Express would be the perfect solution here. It sounds like you have a good plan in place and like you've got a good idea of which product is the right one for you. The nice thing about SQL is that it's easy to upgrade down the road as well, so if your requirements change in the future you should remain flexible.

|||

Hi ya,

If sql express is out of the question still workgroup would be more then enough as there is no hard core limit on concurrent no of users that can attach to a Sql Server. However it does depend on the actual machine performance which you already explained would be having 2-3 GB.

Hope that helps

Cheers

|||

Brilliant.

I just found out that we do not need to spend ￡300 for a server license of visual sourcesafe - so the money will hopefully go towards a new sql server.

|||Awesome. That worked out great. I'm glad you were able to find the right product for your needs.

Help in Case statement

Hi,

I have to use case statement in where condition of a sql query. I get an error

Incorrect syntax near '<'.

Declare @.iPostingID int

set @.iPostingId = 1

Select PostingID from JobsDB_JobPostings where DateDiff(day,PostingDate,getdate())

case @.iPostingId

when 1 then '<1'

when 2 then '<7'

when 3 then '<30'

when 4 then '<60'

when 5 then '<90'

end

Can anyone help me please

Thanks,

Uma Ramiya

Hi,

Maybe you want this:

Code Snippet

SELECT

CASE PostingID

WHEN 1 THEN '<1'

WHEN 2 THEN '<7'

WHEN 3 THEN '<30'

WHEN 4 THEN '<60'

WHEN 5 THEN '<90'

END

FROM JobsDB_JobPostings WHERE DATEDIFF(DAY,PostingDate,GETDATE())

Regards,

Janos

|||The parameter @.iPostingId is used for comparison and not the column field.|||Well, you need the select before the CASE|||Hi,

We use similar construct in our daily life here. Have a look on following query, this will really work.

DECLARE @.iPostingID int

SET @.iPostingId = 1

SELECT
PostingID
FROM
JobsDB_JobPostings
WHERE
DateDiff(day,PostingDate,getdate()) < (CASE
WHEN @.iPostingId = 1 THEN 1
WHEN @.iPostingId = 2 THEN 7
WHEN @.iPostingId = 3 THEN 30
WHEN @.iPostingId = 4 THEN 60
WHEN @.iPostingId = 5 THEN 90
END)|||

Hi I have the following case.i would like a precise and efficient SQL Statement for it.

I have 7 parameters which are used for comparison. I need to run the query with these parameters. which ever is not null should be compared and the null ones should be ignored. AS a result I end with lots of "if' statements as the parameters form a lot of copmbination. Is there any way to cut short the code

ALTER PROCEDURE [dbo].[JobsDb_Resumes_SelectForMatchingSkills]

@.sSkill varchar(50),

@.iCountryID int=NULL,

@.iStateID int=NULL,

@.iJobTypeID int=NULL,

@.iMinSal int=NULL,

@.iMaxSal int NULL,

@.iPeriodPosted int NULL

AS

IF @.iCountryID!=NULL AND @.iStateID=NULL AND @.iJobTypeID = NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID = NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted != NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

Help in CASE function

Thanks Rudy for the answer of my previous thread.
Here's another scenario of my problem that i hardly get the correct query.

I have a tables with fields and sample records in my database;

Table: tblPo
FIELDS RECORD1
po_number: PO10001
entered_by: chris
approved_by: albert

Table: tblUser
FIELDS RECORD1 RECORD2
user_id: chris albert
first_name: Christopher Alberto
mi: S W
last_name: Lambert Del Puerto

So this is how my database is structured. entered_by and approved_by fields in tblPo are both existed in the tblUser table as user_id so they would share the same table tblUser for First, Middle,Last Names since they both also must be a valid user to create and approve a PO.

Now, I need to get a result like this:

PO # Prepared by Approved by
PO10001 Christopher S. Lambert Alberto W. Del Puerto

My problem now is how to make a query to generate this report. I already joined the two tables with tblPo.user_id to tblUser.user_id and tblPo.approved_by to tblUser.user_id. Unfortunately, both Preparedby and Approvedby resulted to NULL values.

Any help? thanks in advance

BernieHere you go :

use pubs
go
create table tblPO (po_number varchar(100),entered_by varchar(100),approved_by varchar(100))
go
create table tblUser (user_id varchar(100),first_name varchar(100),mi varchar(100),last_name varchar(100))
go
insert into tblPO values('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')
go
select
a.po_number,
isnull(b.first_name,'') +' '+ isnull(b.mi,'') +'. '+ isnull(b.last_name,'') Prepared_by,
isnull(c.first_name,'') +' '+ isnull(c.mi,'') +'. ' + isnull(c.last_name,'') Approved_by
from
tblPO a,
tbluser b,
tbluser c
where
a.entered_by = b.user_id
and a.approved_by = c.user_id

go
drop table tbluser
go
drop table tblpo
go|||create table tblpo (po_number char(10), entered_by char(10), approved_by char(10))
create table tblUser (user_id char(10), first_name char(30), mi char(30), last_name char(30))
insert into tblpo values ('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')

select
tblpo.po_number as po#,
rtrim(EnteredBy.first_name)+' '+rtrim(EnteredBy.mi)+'. '+rtrim(EnteredBy.last_name) as PreparedBy,
rtrim(ApprovedBy.first_name)+' '+rtrim(ApprovedBy.mi)+'. '+rtrim(ApprovedBy.last_name) as ApprovedBy
from tblpo
left outer join tbluser as EnteredBy
on EnteredBy.user_id = tblpo.entered_by
left outer join tbluser as ApprovedBy
on ApprovedBy.user_id = tblpo.approved_by

po# PreparedBy ApprovedBy
---- -------- -------
PO10001 Christopher S. Lambert Alberto W. Del Puerto

(1 row(s) affected)|||Originally posted by cvandemaele
create table tblpo (po_number char(10), entered_by char(10), approved_by char(10))
create table tblUser (user_id char(10), first_name char(30), mi char(30), last_name char(30))
insert into tblpo values ('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')

select
tblpo.po_number as po#,
rtrim(EnteredBy.first_name)+' '+rtrim(EnteredBy.mi)+'. '+rtrim(EnteredBy.last_name) as PreparedBy,
rtrim(ApprovedBy.first_name)+' '+rtrim(ApprovedBy.mi)+'. '+rtrim(ApprovedBy.last_name) as ApprovedBy
from tblpo
left outer join tbluser as EnteredBy
on EnteredBy.user_id = tblpo.entered_by
left outer join tbluser as ApprovedBy
on ApprovedBy.user_id = tblpo.approved_by

po# PreparedBy ApprovedBy
---- -------- -------
PO10001 Christopher S. Lambert Alberto W. Del Puerto

(1 row(s) affected)

Thats better .. that would work even if userid does not exist in tblUser ... but remember to take care of null values .. coz null added to anything yields null|||SALAMAT! Thank you guyz...

I can proceed now with my report...

Berniesql