Nested Stored Procedure - Database Discussion Boards

WHERE Clause's Column from a Parameter [modified]

Brian C Hart31-Oct-07 7:13

31-Oct-07 7:13

Hello folks,

I am a Newbie. I know I could just do this dynamically, however I am looking for any possible way to keep as much stuff declared at design-time as possible (just a personal preference).

I want to do as is illustrated below -- using the 'Employees' table from the 'Northwind' database. My environment is VS.NET 2005 with SQL server 2005, language is C# and page is ASP.NET.

Basically, I have a DropDown with "City" and "LastName" as the choices, next to a TextBox. The user picks either of the field names in the DropDownList and then types a search filter in the TextBox. I want to use a Parameter to specify the column name and a Parameter to specify the value to search with a WHERE clause. The value is replaced with its appropriate SelectParameter but not the field to search, i.e. the code below is not working:

Default.aspx:

<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem>City</asp:ListItem>
<asp:ListItem>LastName</asp:ListItem>
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" Width="220px"></asp:TextBox><br />
<br />
<asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False"
DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" InsertVisible="False"
ReadOnly="True" SortExpression="EmployeeID" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [City] FROM [Employees] WHERE ([@Field] LIKE @Value)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="Field" PropertyName="SelectedValue" Type="String"/>
<asp:ControlParameter ControlID="TextBox1" Name="Value" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

Is there something I am doing wrong? Am I not allowed to say, i.e. SelectCommand="SELECT [field1] [field2] FROM [mytable] WHERE [@param] = @value" where @param upon replacement, specifies the field's name? It is not getting replaced, so naturally SQL Server complains with the error:

Invalid column name '@Field.'

What's up? Why cannot I use a parameter when specifying a field in a WHERE clause?

-- modified at 13:21 Wednesday 31st October, 2007

Re: WHERE Clause's Column from a Parameter

pmarfleet31-Oct-07 7:32

pmarfleet

31-Oct-07 7:32

This article[^] should give you the information you need.

Paul Marfleet

Count errors and not display any less than a value

solutionsville31-Oct-07 7:00

solutionsville

31-Oct-07 7:00

I have this query that works as it is. I want to modify it so that I do not see Alarm errors less than 50.

I guess it would have to be a sub query to this;

SELECT WaysideName, ST, AlarmMessage, COUNT(AlarmMessage) AS Error_Count, CONVERT(char(10), EventDate, 111) as ErrorDate
FROM AlarmsList
where EventDate >@StartDate and EventDate <dateadd(day, 1,@enddate)="" and="" alarmmessage="" not="" like="" ('%off')="" ('%restored')="" <=""> 'OPERATIONAL AND HEALTHY'
GROUP BY WaysideName, ST, AlarmMessage, CONVERT(char(10), EventDate, 111)

Thanks,

Re: Count errors and not display any less than a value

pmarfleet31-Oct-07 7:09

pmarfleet

31-Oct-07 7:09

Try this:

SELECT WaysideName, ST, AlarmMessage, COUNT(AlarmMessage) AS Error_Count, CONVERT(char(10), EventDate, 111) as ErrorDate
FROM AlarmsList
where EventDate >@StartDate and EventDate 'OPERATIONAL AND HEALTHY'
GROUP BY WaysideName, ST, AlarmMessage, CONVERT(char(10), EventDate, 111)
HAVING COUNT(AlarmMessage) >= 50

Adding the HAVING clause modifies the query so that only rows with an alarm message count >=50 are included in the output.

Paul Marfleet

Re: Count errors and not display any less than a value

solutionsville31-Oct-07 7:17

solutionsville

31-Oct-07 7:17

Thanks Paul. That did the trick. Boy was that simple!

Re: Count errors and not display any less than a value

pmarfleet31-Oct-07 7:28

pmarfleet

31-Oct-07 7:28

Thanks. The important thing to remember here is that the WHERE and HAVING clauses provide different ways of filtering the output of a query.

A WHERE clause is used to filter non-aggregated data.
A HAVING clause is used in conjunction with a GROUP BY clause to filter data aggregated in that GROUP BY clause.

Paul Marfleet

Re: Count errors and not display any less than a value

solutionsville31-Oct-07 7:29

solutionsville

31-Oct-07 7:29

Thank You.

It is always a good day when you learn something new!

Regards,

Brian

Nested Stored Procedure

lossy31-Oct-07 5:53

lossy

31-Oct-07 5:53

Hi!
I have the following two stored proceures:

SP1:

Create PROCEDURE SP1
	@arg1 varchar(100),
	@arg2 varchar(100) out
AS
BEGIN
SET @arg2=arg1+': SP1 Data'
return
END

SP2:

Create PROCEDURE SP2
AS
BEGIN
Declare @argloc varchar(100)
EXEC SP1 'SP2 Data',@argloc
print @argloc
END

On calling SP2 i want result like:

SP2 Data: SP1 Data

But it returns:

SP2 Data

How can can i fix this problem?
Thanks!

Re: Nested Stored Procedure

pmarfleet31-Oct-07 7:05

pmarfleet

31-Oct-07 7:05

This should work:

Create PROCEDURE SP2
AS
BEGIN
Declare @argloc varchar(100)
EXEC SP1 'SP2 Data',@argloc OUTPUT
print @argloc
END

You needed to mark @argloc with the OUTPUT keyword in order for this variable to receive the output value from SP1.

Paul Marfleet

Re: Nested Stored Procedure

lossy31-Oct-07 8:26

lossy

31-Oct-07 8:26

I tried and it succeeded because of your nice suggestion.
Thanks!

Re: Nested Stored Procedure

pmarfleet31-Oct-07 8:44

pmarfleet

31-Oct-07 8:44

That's OK. As long as my advice was helpful.

Paul Marfleet

automaticaly refresh

muharrem31-Oct-07 5:37

muharrem

31-Oct-07 5:37

Is there any mechanism to refresh a dataset automatically when the data source ,lets say
a table in SqlServer, is updated, deleted or inserted. No periodic fill or execuete a sql statement please.

Thanks

Re: automaticaly refresh

Paul Conrad31-Oct-07 14:10

Paul Conrad

31-Oct-07 14:10

muharrem wrote:
No periodic fill or execuete a sql statement please.

Why not?

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Re: automaticaly refresh

muharrem1-Nov-07 1:07

muharrem

1-Nov-07 1:07

I don't know when the original data source is updated by other user or
an other aplication.
My project is going to be part of a plant automation system. So the events from the
plant level my arise in very short time duration or too long. It depends on the condition
at that moment. So if I periodically fill my data set, what will be the period?
It is very difficult to gess id advance. Having it too short will bring unnecessary workload
for the system. Or having it too long will cause to miss the some indermediate update in
the data source.

I am not very good on Ado.Net. I need to know ,if possible, can I have my client application
to refresh its dataset (automatically) from the data source when it is updated.

Thanks for reply, anyhow

Re: automaticaly refresh

ALAQUNAIBI2-Jan-09 21:23

ALAQUNAIBI

2-Jan-09 21:23

The best way to make this , by using triggers with endpoint in SqlServer 2005

Re: automaticaly refresh

muharrem5-Jan-09 10:12

muharrem

5-Jan-09 10:12

Can you be more specific please?
And consider MySQL too.

Thanks,

Muharrem

SQL 2005 connectivity

nicetohaveyou31-Oct-07 2:37

nicetohaveyou

31-Oct-07 2:37

i have few questions before the forum...

first can we connect sql 2000 enterprise manager to sql 2005 server?

second can we connect to sql2005 server from query analyser of 2000?

do we have the client tools for sql 2005 server?

Re: SQL 2005 connectivity

pmarfleet31-Oct-07 2:46

pmarfleet

31-Oct-07 2:46

nicetohaveyou wrote:
first can we connect sql 2000 enterprise manager to sql 2005 server?

second can we connect to sql2005 server from query analyser of 2000?

Why would you want to do this? If you have SQL Server 2005, install the SQL 2005 client tools.

nicetohaveyou wrote:
do we have the client tools for sql 2005 server?

Only you can answer this question...

Paul Marfleet

Re: SQL 2005 connectivity

Hesham Amin31-Oct-07 11:15

Hesham Amin

31-Oct-07 11:15

nicetohaveyou wrote:
first can we connect sql 2000 enterprise manager to sql 2005 server?

I tried it and, no.. you'll get an error when you connect to sql server 2005 from SQL Server 2000 enterprise manager.

nicetohaveyou wrote:
second can we connect to sql2005 server from query analyser of 2000?

Yes...

nicetohaveyou wrote:
do we have the client tools for sql 2005 server?

If you need free tools, you can use MS SQL Server 2005 express edition + SQL Server management studio. you can get them all free here[^]
Select to download "Microsoft SQL Server 2005 Express Edition with Advanced Services"

Hesham A. Amin
My blog

Create Table statement

prithaa31-Oct-07 1:45

prithaa

31-Oct-07 1:45

Hello,

I want to write a sql statement to create a table with one field which is a decimal.But it doesn't work.
I did the following

Create Table Purchase (Name varchar,Amount numeric(10,2))

Prithaa

Re: Create Table statement

soni uma31-Oct-07 2:04

soni uma

31-Oct-07 2:04

Prithaa This Work fine Try it Again;

Laugh | :laugh:

Re: Create Table statement

neeraj_indianic31-Oct-07 2:09

neeraj_indianic

31-Oct-07 2:09

Actually you are not giving the size of varchar so by default it will take varchar(1) and you will not be able to insert the record which is greater than 1 character. You have to do like this:-

Create Table Purchase (Name varchar(50),Amount numeric(10,2))

datetime problem

niki_nilu31-Oct-07 0:38

niki_nilu

31-Oct-07 0:38

hello
i am gayatri patil
i have two table tAnalyst and tUser
tAnalyst:-

UserId varchar(20) notnull
CreateDate datetime(8) notnull
UpdateDate datetime(8) allow Null

tUser table:-
UserId varchar(20) notnull
firstname varchar(20) notnull
lastname varchar(20) notnull

i want sql query like
join the two tables and search data date wise.
my query is--------
select tU.UserID,tU.FirstName,tU.LastName,
tU.LastName+''+ tU.FirstName'UserId',
tA.UserID,tA.CreateDate,
tA.UpdateDate
from tAnalyst tA,
tUser tU
where tU.UserID=tA.UserID
and
tA.CreateDate = '7/25/2007'
in this query i haven't error but data is not display.
so pls guide me writeway.
regards
gayatri

Gayatri

Re: datetime problem

soni uma31-Oct-07 0:44

soni uma

31-Oct-07 0:44

1 . Gaytri it may possible that racords are not matche
2. Or Pass the Date as 07/25/2007 not 7/25/2007
Old tA.CreateDate = '7/25/2007' i think it is problem
New tA.CreateDate = '07/25/2007'

Re: datetime problem

niki_nilu31-Oct-07 1:11

niki_nilu

31-Oct-07 1:11

sir i am write query like you told me.
but its not working
gayatri

Gayatri

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.