Newbie Question accessing values in other data regions

Hi,
i have a simple report that has two data regions,
i want to use a calculated field in one data region that refers to a
field in the other region.
how do i achieve this?
sql server 2000 RS 2000
regardsThink of RS data regions as repeaters that expand to show all rows in the
underlying dataset. So, a data region cell is relative since its position is
known only during runtime. It is like trying to link two database tables
that don't have a common key. For this reason, you can only get an
aggregated value from another region, e.g. Sum(Fields!FieldName.Value).
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"cg" <colingresty@.yahoo.co.uk> wrote in message
news:1131975339.742358.216470@.z14g2000cwz.googlegroups.com...
> Hi,
> i have a simple report that has two data regions,
> i want to use a calculated field in one data region that refers to a
> field in the other region.
> how do i achieve this?
> sql server 2000 RS 2000
> regards
>|||thanks Teo,
that makes things a bit clearer.

Newbie question - Need count to return multiple values

Hi all,
This is probably a simple question but I still new enough that I can't
figure it out (this is only my second real query i'm so REALLY new).
I have a table that looks like this:
CustKey InvoiceDate
01 2006-05-19
02 2006-05-19
03 2006-05-19
04 2006-04-28
02 2006-05-19
03 2006-05-19
04 2006-05-19
04 2006-05-19
03 2006-05-19
I want my output to look like this:
CustKey Total for 2006-05-19
01 1
02 2
03 3
04 2
Basically I need a list that will tell me, by CustKey, how many Invoices
were done on a given day.
Now I can do a quick count that will tell me for a given customer and date
but I don't know how to have it check and return values for all 4 customers
based on date.
So far I have:
SELECT COUNT * FROM "Table1"
WHERE CustKey = '01'
AND "InvoiceDate" = ('2006-05-19')
Any help would be appreciated. I'm trying to save myself from having to do
a manual count of invoices on a wly basis.
Thanks in advance,
NancySELECT CustKey,COUNT( *) FROM Table1
WHERE InvoiceDate = ('2006-05-19')
GROUP BY CustKey
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks Denis, I figured it would be something simple.
This works perfectly!
"SQL" wrote:

> SELECT CustKey,COUNT( *) FROM Table1
> WHERE InvoiceDate = ('2006-05-19')
> GROUP BY CustKey
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>sql

Newbie Question

Hi,
I have a procedure that returns 5 different values. I want to run the procedure in another procedure and use those values in that procdure.

How? How? How?

CREATE Procedure get_Averages_EY @.Quality dec OUTPUT, @.Commitment dec OUTPUT,@.Change dec OUTPUT, @.Strategy dec OUTPUT, @.Leadership dec OUTPUT, @.Environment dec OUTPUT, @.id int

I want to use all of the output values in another procedure.

Thanks!!!Try this one.

create procedure proc1(@.id int,@.id2 int output,@.id3 int output)
as
set @.id2=@.id*2
set @.id3=@.id*10
go
create procedure proc2
as
declare @.id2 int,@.id3 int,@.id int
set @.id=3
exec proc1 @.id,@.id2 output,@.id3 output
select @.id,@.id2,@.id3
go
exec proc2

newbie query question

Hi,

I have a table [myRecipes] with 4 fields; Users can Modify existing recipes or add new recipes and my table stores the new values with a timestamp.

Question: Now that I have some sample data, I need a query or view to pull out each unique recipe in my table, but only return the latest and greatest recipe for each unique recipe.

[foodType] nvarchar

[recipeName] nvarchar

[lastSaved] datetime

[cupsOfSugar] float

Sample data:

foodType recipeName lastSaved cupsOfSugar

cookie, peanutButter, 3/1/2007, 1.0

cookie, peanutButter, 3/5/2007, 1.5

cookie, sugar, 2/28/2007, 5.0

How to:

What would be the query to return the latest and greatest recipes in my db? The resultset should return

cookie, sugar, 2/28/2007, 5

cookie, peanutButter, 3/5/2007, 1.5

...and not the original recipe for peanutButter Cookies with only 1.0 cups of sugar

thanks in advance,

bsierad

select TOP 1 foodType, recipeName, lastSaved, cupsOfSugar
FROM myRecipes Order by cupsOfSugar DESC --Returns greatest
UNION
select TOP 1 foodType, recipeName, lastSaved, cupsOfSugar

FROM myRecipes Order by lastSaved -- Returns latest

PS. Best forum for this question is Transact-SQL
|||

Thanks,

but, doesn't this only return one row?

I'm looking for:

For each unique foodType and recipeName, please return all the fields in my table, and, if there are any duplicate records with foodType and recipeName, please only return that record whose lastSaved field is the max for that particular set.

This table basically holds a history of all saved recipes created by the user, but he/she should only ever see the latest and greatest...

PS: The primary key on this table is foodType + recipeName + lastSaved

thanks again in advance,

ben

|||Check my response in TransactSQL

newbie query question

Hi,

I have a table [myRecipes] with 4 fields; Users can Modify existing recipes or add new recipes and my table stores the new values with a timestamp.

Question: Now that I have some sample data, I need a query or view to pull out each unique recipe in my table, but only return the latest and greatest recipe for each foodType / recipeName pair.

Primary Key = foodType + recipeName + lastSaved

[foodType] nvarchar

[recipeName] nvarchar

[lastSaved] datetime

[cupsOfSugar] float

Sample data:

foodType recipeName lastSaved cupsOfSugar

cookie, peanutButter, 3/1/2007, 1.0

cookie, peanutButter, 3/5/2007, 1.5

cookie, sugar, 2/28/2007, 5.0

How to:

What would be the query to return the latest and greatest recipes in my db? The resultset should return

cookie, sugar, 2/28/2007, 5

cookie, peanutButter, 3/5/2007, 1.5

...and not the original recipe for peanutButter Cookies with only 1.0 cups of sugar

thanks in advance,

bsierad

This should get you started. It will provide the lastest of each reciept variation.

SELECT
[FoodType],
[RecipeName],
max( [LastSaved] )
FROM [myRecipes]
GROUP BY
[FoodType],
[RecipeName]
ORDER BY
[FoodType],
[RecipeName]

This gets you the PK of each qualifying row, and then you could use it as a subquery or a JOIN derived table to get the remaining ingredients.

|||

Thanks, this really helps me out!

this is great...don't have to use a JOIN with this?

I'm under the impression subqueries as input to a parent query can only return one field?

I also did this:

SELECT FoodType,
recipeName,
LastSaved,
cupsOfSugar
FROM myRecipes q
WHERE cast(q.LastSaved as varchar(10)) in
(select MAX(cast(LastSaved as varchar(10)) ) from myRecipes
where FoodType= q.FoodType
and
recipeName= q.recipeName)

thanks again in advance,

bsierad

|||I was thinking as a sub-query in a WHERE clause to return the PK. Also, as a derived table for a JOIN.

Newbie pie chart report problem

Hi, I've just started using Pie Charts and I'm stuck on a value problem (I think!). My category will be "Connection Status", and the values can be: success:human; success:machine; Connected; Null.

What I want to do is merge the "success:human" and "connected" values into one slice of pie. Is this possible?

Thanks, Dan

Sorry, I was confused, just needed to change the SQL to include both values.

Newbie Parameter Problem

Hi, I have 3 parameters on my form. StartDate (datetime), EndDate (datetime) and CompanyName(string). The default values are: StartDate (Non-queried) 1-1-2005, EndDate (Non-queried) 1-1-2008, CompanyName (From query) DataSetBelow, Value field (AccountFamily):

SELECT DISTINCT AccountFamily
FROM CallDataRecords

The table on the form contains the following DataSet:

SELECT Salutation, InboundTimeMS, OutboundTimeMS, ModifiedOn, IsRightParty, AccountFamily

FROM CallDataRecords

WHERE AccountFamily = @.CompanyName
AND ModifiedOn
BETWEEN @.StartDate AND @.EndDate

The error I get is: "Query execution failed for data set (one directly above)".

"Must declare the scalar variable "@.CompanyName".

Can anybody shed light please?

Thanks, Dan

I believe you have to declare the variable first and then use it the query..

DECLARE @.CompanyName nvarchar(25)

--Initilize the declared variable

SELECT DISTINCT @.CompanyName = AccountFamily
FROM CallDataRecords

-- use it

SELECT Salutation, InboundTimeMS, OutboundTimeMS, ModifiedOn, IsRightParty, AccountFamily

FROM CallDataRecords

WHERE AccountFamily = @.CompanyName
AND ModifiedOn
BETWEEN @.StartDate AND @.EndDate

Hope this helps.....

|||

I tried that, but I got the following error:

"The report parameter 'CompanyName' uses the field 'AccountFamily' in a data set reference, but the data set 'DistinctComanyName' does not contain that field".

I also tried editing the Dataset and adding in the parameters tab of the Dataset. However that doesn;t help either (?).

|||

Sorry, please ignore my last post. I fixed it by adding the parameters to the second dataset. (They were not defined).

Thanks!

|||

cool ... all the best

newbie needs help

Hello all, I'm learning SQL and need help with a query. I have a table that
stores employee ID and values per a given year. EmplID is stored in field 1
,
Yr1 val in field 2, Yr2 val in field 3... and so on up to any given number o
f
Yr fields. Not all of the Yr values exist for all Yr fields. I need to be
able to to pull the most recent value for a given row/record. So, for
instance, if EmplID ABCD stores the following:
EmplID Yr1 Yr2 Yr3 Yr4
ABCD 41 42 43 0
I need to pull 41 if Yr1 is the most recent year.
If Emplid EFGH stores the following:
EmplID Yr1 Yr2 Yr3 Yr4
EFGH 0 42 0 0
I need to pull 42, the Yr2 value, because no value exists for Yr1.
I am using SQL Server on a WinXP box. Any help would be greatly appreciated
.First of all, this is a poor design for a table. What are you doing,
continually adding new fields (we call them columns, or attributes) each
year? Better would be to have a column named "Year".
EmplID Year Value
ABCD 1 41
ABCD 2
You should, when possible, learn a bit about data modeling and
normalization... But
"kiloez" wrote:
> Hello all, I'm learning SQL and need help with a query. I have a table th
at
> stores employee ID and values per a given year. EmplID is stored in field
1,
> Yr1 val in field 2, Yr2 val in field 3... and so on up to any given number
of
> Yr fields. Not all of the Yr values exist for all Yr fields. I need to b
e
> able to to pull the most recent value for a given row/record. So, for
> instance, if EmplID ABCD stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> ABCD 41 42 43 0
> I need to pull 41 if Yr1 is the most recent year.
> If Emplid EFGH stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> EFGH 0 42 0 0
> I need to pull 42, the Yr2 value, because no value exists for Yr1.
> I am using SQL Server on a WinXP box. Any help would be greatly appreciated.[/col
or]|||Sorry submitted that before done...
First of all, this is a poor design for a table. What are you doing,
continually adding new fields (we call them columns, or attributes) each
year? Better would be to have a column named "Year".
EmplID Year Value
ABCD Yr1 41
ABCD Yr2 42
ABCD Yr3 43
ABCD Yr4 44
etc. .............
Second, what is Yr1, Yr2, Yr3, Yr4, mean ? Are these the actual column
names in your table? Do they map to actual years spmewhere else in your data
model?
When possible, you should learn a bit about data modeling and
normalization...
Given your problem, a solution based on the columns that exist in the table
right now would break when you add a new column for Yr5, unless the solution
was constructed to treat the "YrN" columns dynamically. That type of
solution would have to read system tables to determine which YR columns
existed, and then write dynamic sql to execute against the table.. A complex
but doable task. It would be much simpler (and therefore quicker more cost
effective) to restructure the data correctly.
"kiloez" wrote:
> Hello all, I'm learning SQL and need help with a query. I have a table th
at
> stores employee ID and values per a given year. EmplID is stored in field
1,
> Yr1 val in field 2, Yr2 val in field 3... and so on up to any given number
of
> Yr fields. Not all of the Yr values exist for all Yr fields. I need to b
e
> able to to pull the most recent value for a given row/record. So, for
> instance, if EmplID ABCD stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> ABCD 41 42 43 0
> I need to pull 41 if Yr1 is the most recent year.
> If Emplid EFGH stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> EFGH 0 42 0 0
> I need to pull 42, the Yr2 value, because no value exists for Yr1.
> I am using SQL Server on a WinXP box. Any help would be greatly appreciated.[/col
or]|||First off my dear friend, you might want to re-evaluate the database design.
You have just violated the second normal form (or is it the third? I get
) Anyways, the best solution would be to move your data to another
table and drop this table here.
I would suggest the following design for your table
CREATE TABLE Emp_Details (
EmpID int NOT NULL,
Year char(4) NOT NULL,
Value int NOT NULL DEFAULT 0
)
Then, populate it as follows:
INSERT INTO Emp_Details
SELECT EmplID, 'Yr1', Yr1
FROM Emp_Table
INSERT INTO Emp_Details
SELECT EmplID, 'Yr2', Yr2
FROM Emp_Table
INSERT INTO Emp_Details
SELECT EmplID, 'Yr3', Yr3
FROM Emp_Table
INSERT INTO Emp_Details
SELECT EmplID, 'Yr4', Yr4
FROM Emp_Table
and so on...
now you have a sensible looking table. you may want to drop your old table
and rename this table. Let everyone know that you have just got some sensibl
e
table design done and that the applications need to change the way they
access data.
Once this is done, you can run the following query:
SELECT Emp_ID, MAX(Year), Value
FROM Emp_Details
WHERE (Value <> 0)
GROUP BY Emp_ID, Value
--that said, if you want a quick fix or boss is not willing to let go of
his ideas of 3NF, you can run this query:
SELECT EmplID,
CASE
WHEN (Yr1 <> 0) THEN Yr1
ELSE
CASE
WHEN (Yr2 <> 0) THEN Yr2
ELSE
CASE
WHEN (Yr3 <> 0) THEN Yr3
ELSE Yr4
END CASE
END CASE
END CASE
FROM Empl_Details
Hope that helps,
Aneesh.
"kiloez" wrote:
> Hello all, I'm learning SQL and need help with a query. I have a table th
at
> stores employee ID and values per a given year. EmplID is stored in field
1,
> Yr1 val in field 2, Yr2 val in field 3... and so on up to any given number
of
> Yr fields. Not all of the Yr values exist for all Yr fields. I need to b
e
> able to to pull the most recent value for a given row/record. So, for
> instance, if EmplID ABCD stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> ABCD 41 42 43 0
> I need to pull 41 if Yr1 is the most recent year.
> If Emplid EFGH stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> EFGH 0 42 0 0
> I need to pull 42, the Yr2 value, because no value exists for Yr1.
> I am using SQL Server on a WinXP box. Any help would be greatly appreciated.[/col
or]|||Thanks for the help guys. I had nothing to do with the design and
development of the DB. I was only asked to query some of the tables.
Aneesh, the quick fix was perfect for what I was looking to accomplish.
Worked out just fine.
Many thanks,
kiloez
"Aneesh Aravind" wrote:
> First off my dear friend, you might want to re-evaluate the database desig
n.
> You have just violated the second normal form (or is it the third? I get
> ) Anyways, the best solution would be to move your data to another
> table and drop this table here.
> I would suggest the following design for your table
> CREATE TABLE Emp_Details (
> EmpID int NOT NULL,
> Year char(4) NOT NULL,
> Value int NOT NULL DEFAULT 0
> )
> Then, populate it as follows:
> INSERT INTO Emp_Details
> SELECT EmplID, 'Yr1', Yr1
> FROM Emp_Table
> INSERT INTO Emp_Details
> SELECT EmplID, 'Yr2', Yr2
> FROM Emp_Table
> INSERT INTO Emp_Details
> SELECT EmplID, 'Yr3', Yr3
> FROM Emp_Table
> INSERT INTO Emp_Details
> SELECT EmplID, 'Yr4', Yr4
> FROM Emp_Table
> and so on...
> now you have a sensible looking table. you may want to drop your old table
> and rename this table. Let everyone know that you have just got some sensi
ble
> table design done and that the applications need to change the way they
> access data.
> Once this is done, you can run the following query:
> SELECT Emp_ID, MAX(Year), Value
> FROM Emp_Details
> WHERE (Value <> 0)
> GROUP BY Emp_ID, Value
> --that said, if you want a quick fix or boss is not willing to let go of
> his ideas of 3NF, you can run this query:
> SELECT EmplID,
> CASE
> WHEN (Yr1 <> 0) THEN Yr1
> ELSE
> CASE
> WHEN (Yr2 <> 0) THEN Yr2
> ELSE
> CASE
> WHEN (Yr3 <> 0) THEN Yr3
> ELSE Yr4
> END CASE
> END CASE
> END CASE
> FROM Empl_Details
> Hope that helps,
> Aneesh.
> "kiloez" wrote:
>

Newbie help: sql string conversion

Hi,
I am trying to avoid a horrendous amount of coding and see if i can get away
with a complex sql statement.

I have data values (measurements) which I have stored in the database in the
form of STRING so that i can keep the original format.

They look like this:

000078 -> 7.8 degrees
-99999M -> Missing data
000345 -> 34.5 degrees
-00993 -> -99.3 degrees
000011 -> 1.1 degrees

you get the idea.
They represent numbers (positive or negative) the last place is the decimal
, they all have 6 characters except the missing data which is -99999M (7
places).

How would I construct an SQL querry to be able to allow the user to retrieve
temperature between e.g. > -2.5 and <12.4 ?

TIA
-steveWhy is the data stored in this format? If these are numeric measurements you
will be much better off storing them with a numeric datatype. Storing
numbers as strings will just make your queries difficult and slow and also
make it hard to maintain any data integrity. Fix the design and convert the
data to numeric form is my advice.

If you've no other choice you could try something like this:

SELECT col
FROM Measurements
WHERE CAST(LEFT(col,6) AS INTEGER) > -2.5
AND CAST(LEFT(col,6) AS INTEGER) < 12.4

--
David Portas
SQL Server MVP
--|||steve,

SELECT * FROM Table1
WHERE CAST(CASE RIGHT(Measurement, 1) WHEN 'M' THEN NULL ELSE Measurement
END AS decimal) * .1
BETWEEN -2.5 AND 12.4

-Andy

"steve" <noemail.@.try.com> wrote in message
news:S5wgd.49358$5t4.774343@.wagner.videotron.net.. .
> Hi,
> I am trying to avoid a horrendous amount of coding and see if i can get
> away with a complex sql statement.
> I have data values (measurements) which I have stored in the database in
> the form of STRING so that i can keep the original format.
> They look like this:
> 000078 -> 7.8 degrees
> -99999M -> Missing data
> 000345 -> 34.5 degrees
> -00993 -> -99.3 degrees
> 000011 -> 1.1 degrees
> you get the idea.
> They represent numbers (positive or negative) the last place is the
> decimal , they all have 6 characters except the missing data which
> is -99999M (7 places).
> How would I construct an SQL querry to be able to allow the user to
> retrieve temperature between e.g. > -2.5 and <12.4 ?
> TIA
> -steve
>|||hmm i see your point and thanx for your answer.
As I said I'd rather keep them in this format for now.
However!
I was thinking is there a fast way that through an sql querry that I can
duplicate a table with a different name of course that will have the same
data but on the "proper" format?
give me a couple of keywords and I'll look google them if you can

Thanx again!

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news: avqdndYSfcazCB_cRVn-pA@.giganews.com...
> Why is the data stored in this format? If these are numeric measurements
> you will be much better off storing them with a numeric datatype. Storing
> numbers as strings will just make your queries difficult and slow and also
> make it hard to maintain any data integrity. Fix the design and convert
> the data to numeric form is my advice.
> If you've no other choice you could try something like this:
> SELECT col
> FROM Measurements
> WHERE CAST(LEFT(col,6) AS INTEGER) > -2.5
> AND CAST(LEFT(col,6) AS INTEGER) < 12.4
> --
> David Portas
> SQL Server MVP
> --|||Create a new table, then use the query I gave you to INSERT into it:

INSERT INTO NewTable (...)
SELECT ...
FROM OldTable

But if the data is changing, maintaining two copies of it is hard work and
unnecessary. The usual practice is to validate and transform data once and
then maintain it in a consistent, strongly-typed relational format in the
database. If you validate the data properly once then you won't need the
original format again. If you don't then you pay the price every time you
query the table.

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Why is the data stored in this format? If these are numeric measurements
> you will be much better off storing them with a numeric datatype.
> Storing numbers as strings will just make your queries difficult and
> slow and also make it hard to maintain any data integrity. Fix the
> design and convert the data to numeric form is my advice.

And store the missing values as NULL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I think i *will* loose my mind!
Thanks both of you for your help.

You see, the problem is that i can have a few flags at the end of the string
which mean somehing. e.g. M means missing, T means Trace, E means estimated,
etc.Information that i should have. If i split each column into two ,
well...possible but a lot of work and too much overhead since most
measurements are "clean".

That's the reason i kept the data in their original format.

Now if i dont convert to strings how the heck am i going to create the sql
string to send to the database with things like:
temperature between so and so, humidity bigger than 50, blah blah blah...
The string is created by an interface in VB where the user scrolls down
various controls and selects things. But how would you enter the bounds of
temperature in a textbox since its not stored as an integer AND it might
have a stupid letter at the end of its value!!!

I could do the coding after the results are returned.
I think My Biggest problem is string comparisons!!! If the user wants a
temperature between -7 and +15 how well and reliably can I create code to
compare "-00007" and "000015".

Just some thoughts from my brainstorming...

"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> a crit dans le
message de news: CIwgd.1071$wN4.303@.newssvr16.news.prodigy.com...
> steve,
> SELECT * FROM Table1
> WHERE CAST(CASE RIGHT(Measurement, 1) WHEN 'M' THEN NULL ELSE Measurement
> END AS decimal) * .1
> BETWEEN -2.5 AND 12.4
> -Andy
> "steve" <noemail.@.try.com> wrote in message
> news:S5wgd.49358$5t4.774343@.wagner.videotron.net.. .
>> Hi,
>> I am trying to avoid a horrendous amount of coding and see if i can get
>> away with a complex sql statement.
>>
>> I have data values (measurements) which I have stored in the database in
>> the form of STRING so that i can keep the original format.
>>
>> They look like this:
>>
>> 000078 -> 7.8 degrees
>> -99999M -> Missing data
>> 000345 -> 34.5 degrees
>> -00993 -> -99.3 degrees
>> 000011 -> 1.1 degrees
>>
>> you get the idea.
>> They represent numbers (positive or negative) the last place is the
>> decimal , they all have 6 characters except the missing data which
>> is -99999M (7 places).
>>
>> How would I construct an SQL querry to be able to allow the user to
>> retrieve temperature between e.g. > -2.5 and <12.4 ?
>>
>> TIA
>> -steve
>>
>>
>>
>>|||steve wrote:
> I think i *will* loose my mind!
> Thanks both of you for your help.
> You see, the problem is that i can have a few flags at the end of the string
> which mean somehing. e.g. M means missing, T means Trace, E means estimated,
> etc.Information that i should have. If i split each column into two ,
> well...possible but a lot of work and too much overhead since most
> measurements are "clean".

That's not a "problem"! Basic database design says you shouldn't keep
multiple pieces of information in the same column. Doing it right
wouldn't be that difficult code wise. You simply have a column with your
valid codes in a check constraint and have the default be the "clean"
code if that is the most common entry.

> That's the reason i kept the data in their original format.
> Now if i dont convert to strings how the heck am i going to create the sql
> string to send to the database with things like:
> temperature between so and so, humidity bigger than 50, blah blah blah...
> The string is created by an interface in VB where the user scrolls down
> various controls and selects things. But how would you enter the bounds of
> temperature in a textbox since its not stored as an integer AND it might
> have a stupid letter at the end of its value!!!

You can do all that manipulation on the front in via code.

> I could do the coding after the results are returned.
> I think My Biggest problem is string comparisons!!! If the user wants a
> temperature between -7 and +15 how well and reliably can I create code to
> compare "-00007" and "000015".
> Just some thoughts from my brainstorming...

Honestly, your brainstorming is confusing the heck out of me. Or maybe
you don't understand the numeric data type. A query of a numeric column
for all values between -7 and +15 would be very simple: WHERE Temp
BETWEEN -7 and 15. Numeric data is not stored with leading 0's.

Zach

>
> "Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> a crit dans le
> message de news: CIwgd.1071$wN4.303@.newssvr16.news.prodigy.com...
>>steve,
>>
>>SELECT * FROM Table1
>>WHERE CAST(CASE RIGHT(Measurement, 1) WHEN 'M' THEN NULL ELSE Measurement
>>END AS decimal) * .1
>> BETWEEN -2.5 AND 12.4
>>
>>-Andy
>>
>>"steve" <noemail.@.try.com> wrote in message
>>news:S5wgd.49358$5t4.774343@.wagner.videotron.net.. .
>>
>>>Hi,
>>>I am trying to avoid a horrendous amount of coding and see if i can get
>>>away with a complex sql statement.
>>>
>>>I have data values (measurements) which I have stored in the database in
>>>the form of STRING so that i can keep the original format.
>>>
>>>They look like this:
>>>
>>>000078 -> 7.8 degrees
>>>-99999M -> Missing data
>>>000345 -> 34.5 degrees
>>>-00993 -> -99.3 degrees
>>>000011 -> 1.1 degrees
>>>
>>>you get the idea.
>>>They represent numbers (positive or negative) the last place is the
>>>decimal , they all have 6 characters except the missing data which
>>>is -99999M (7 places).
>>>
>>>How would I construct an SQL querry to be able to allow the user to
>>>retrieve temperature between e.g. > -2.5 and <12.4 ?
>>>
>>>TIA
>>>-steve
>>>
>>>
>>>
>>>
>>
>>
>|||> You see, the problem is that i can have a few flags at the end of the
> string which mean somehing. e.g. M means missing, T means Trace, E means
> estimated

Then you have a non-atomic column, which is a violation of the most
fundamental relational design principles. This information belongs in a
separate column.

--
David Portas
SQL Server MVP
--|||> But how would you enter the bounds of temperature in a textbox since its
> not stored as an integer AND it might have a stupid letter at the end of
> its value!!!

> I think My Biggest problem is string comparisons!!! If the user wants a
> temperature between -7 and +15 how well and reliably can I create code to
> compare "-00007" and "000015".

Yep, it's lousy... So why waste time on it when you could just redesign the
table properly :-)

--
David Portas
SQL Server MVP
--

Newbie CURSOR question, insert values into a table?

How can I insert values into a table based on the fetch I do. The examples
I have been able to find all shows how to use "print" to show how Cursors
work.

I want to read through a table, and only insert values into another table if
some conditions on the row is true.

Can this be done, if so how?

Thanks in advance.

Henrik.You can do this, but in general you should avoid cursors unless there
is no other choice. It may be that your cursor can be written as a
single insert statement, in which case it will be a lot faster and
easier to code. See below for a rough example.

Simon

/* With a cursor */

declare @.var1 int, @.var2 int

declare cur cursor fast_forward for
select col1, col2
from dbo.table1

open cur

fetch cur into @.var1, @.var2

while @.@.fetch_status = 0
begin
if @.var1 > @.var2 -- check conditions
begin
insert into dbo.table2 (col1, col2)
select @.var1, @.var2
end

fetch cur into @.var1, @.var2
end

close cur
deallocate cur

/* With an insert */

insert into dbo.table2 (col1, col2)
select col1, col2
from dbo.table1
where col1 > col2

"Henrik Hjllund Hansen" <hh@.dlf.dk> wrote in message news:<be367e$2fng$1@.news.cybercity.dk>...
> How can I insert values into a table based on the fetch I do. The examples
> I have been able to find all shows how to use "print" to show how Cursors
> work.
> I want to read through a table, and only insert values into another table if
> some conditions on the row is true.
> Can this be done, if so how?
> Thanks in advance.
> Henrik.|||Hi,

Refer solution below:

Entity/Objects:
(Assume all fields in this example are varchar).

Table1 (Field1,Field2)
Table2 (Field1,Field2, Field3)

Script:
Declare @.F1 Varchar, @.F2 Varchar

--Get records
Declare RS cursor for Select Field1, Field2 from Table1 Where Field1=
A%'
Open RS --Open cursor

Fetch Next From RS into @.F1, @.F2
While @.@.Fetch_Status=0 --Check cursor if end of file
Begin
--Your insert in second table if condition matches.
If RTrim(@.F1) = ABC' And RTrim(@.F2) = Google'
Begin
--Post script to insert in Table2
Insert Into Table2

End

Fetch Next From RS into @.F1, @.F2
End

Close RS --Close cursor
Deallocate RS --relaese memory.

For additional information on updating selected columns in cursor and
complete syntax refer SQL Server Books Online sections.
a)DECLARE CURSOR
b)@.@.Fetch_Status

Hope this helps you.
Thanks Amit

"Henrik Hjllund Hansen" <hh@.dlf.dk> wrote in message news:<be367e$2fng$1@.news.cybercity.dk>...
> How can I insert values into a table based on the fetch I do. The examples
> I have been able to find all shows how to use "print" to show how Cursors
> work.
> I want to read through a table, and only insert values into another table if
> some conditions on the row is true.
> Can this be done, if so how?
> Thanks in advance.
> Henrik.|||Thanks a lot for your answers.

Henrik

"Henrik Hjllund Hansen" <hh@.dlf.dk> wrote in message
news:be367e$2fng$1@.news.cybercity.dk...
> How can I insert values into a table based on the fetch I do. The
examples
> I have been able to find all shows how to use "print" to show how Cursors
> work.
> I want to read through a table, and only insert values into another table
if
> some conditions on the row is true.
> Can this be done, if so how?
> Thanks in advance.
> Henrik.

Newbie - SqlConnection statement

I'm trying to get a combobox to fill with values from a table. I've
set up an example using the Northwind database with the Categories
table. Dragging in the CategoryID field onto my form as a combobox,
I've set the following properties for the CategoryID combobox:
Value member = "CategoriesBindingSource - CategoryID"
Display Member = "CategoriesBindingSource - CategoryID"
This seems to bring in the correct values in the two non-index fields
(CategoryName and Description). I've then added the following code to
the combobox:
Dim Conn As SqlConnection
Conn = New SqlConnection("Database=Northwnd.mdf")
'Conn.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID FROM Categories", Conn)
Dim ds As New DataSet
da.Fill(ds, "Categories")
CategoryIDComboBox.DataSource = ds
'CategoryIDComboBox.ValueMember = "CategoryID"
'CategoryIDComboBox.DisplayMember = "CategoryID"
End Sub
The lines that are commented out are other ideas that I've tried to no
avail. I left them here in case they are relevant.
I suspect that at least part of my problem is in the SqlDataAdapter
statement where I am pointing to the database. I am working on a
standalone pc.
Can anybody see where I am going wrong?
Thanks,
RandyHi Randy
"Randy" wrote:

> I'm trying to get a combobox to fill with values from a table. I've
> set up an example using the Northwind database with the Categories
> table. Dragging in the CategoryID field onto my form as a combobox,
> I've set the following properties for the CategoryID combobox:
> Value member = "CategoriesBindingSource - CategoryID"
> Display Member = "CategoriesBindingSource - CategoryID"
> This seems to bring in the correct values in the two non-index fields
> (CategoryName and Description). I've then added the following code to
> the combobox:
> Dim Conn As SqlConnection
> Conn = New SqlConnection("Database=Northwnd.mdf")
> 'Conn.Open()
> Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
> CategoryID FROM Categories", Conn)
> Dim ds As New DataSet
> da.Fill(ds, "Categories")
> CategoryIDComboBox.DataSource = ds
> 'CategoryIDComboBox.ValueMember = "CategoryID"
> 'CategoryIDComboBox.DisplayMember = "CategoryID"
> End Sub
> The lines that are commented out are other ideas that I've tried to no
> avail. I left them here in case they are relevant.
> I suspect that at least part of my problem is in the SqlDataAdapter
> statement where I am pointing to the database. I am working on a
> standalone pc.
> Can anybody see where I am going wrong?
> Thanks,
> Randy
>
For connection string information check out
http://www.connectionstrings.com/?carrier=sqlserver2005
You may also want to some of the examples such as
http://msdn.microsoft.com/library/d...opi
c.asp
John

Newbie - SqlConnection statement

I'm trying to get a combobox to fill with values from a table. I've
set up an example using the Northwind database with the Categories
table. Dragging in the CategoryID field onto my form as a combobox,
I've set the following properties for the CategoryID combobox:
Value member = "CategoriesBindingSource - CategoryID"
Display Member = "CategoriesBindingSource - CategoryID"
This seems to bring in the correct values in the two non-index fields
(CategoryName and Description). I've then added the following code to
the combobox:
Dim Conn As SqlConnection
Conn = New SqlConnection("Database=Northwnd.mdf")
'Conn.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID FROM Categories", Conn)
Dim ds As New DataSet
da.Fill(ds, "Categories")
CategoryIDComboBox.DataSource = ds
'CategoryIDComboBox.ValueMember = "CategoryID"
'CategoryIDComboBox.DisplayMember = "CategoryID"
End Sub
The lines that are commented out are other ideas that I've tried to no
avail. I left them here in case they are relevant.
I suspect that at least part of my problem is in the SqlDataAdapter
statement where I am pointing to the database. I am working on a
standalone pc.
Can anybody see where I am going wrong?
Thanks,
Randy
Hi Randy
"Randy" wrote:

> I'm trying to get a combobox to fill with values from a table. I've
> set up an example using the Northwind database with the Categories
> table. Dragging in the CategoryID field onto my form as a combobox,
> I've set the following properties for the CategoryID combobox:
> Value member = "CategoriesBindingSource - CategoryID"
> Display Member = "CategoriesBindingSource - CategoryID"
> This seems to bring in the correct values in the two non-index fields
> (CategoryName and Description). I've then added the following code to
> the combobox:
> Dim Conn As SqlConnection
> Conn = New SqlConnection("Database=Northwnd.mdf")
> 'Conn.Open()
> Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
> CategoryID FROM Categories", Conn)
> Dim ds As New DataSet
> da.Fill(ds, "Categories")
> CategoryIDComboBox.DataSource = ds
> 'CategoryIDComboBox.ValueMember = "CategoryID"
> 'CategoryIDComboBox.DisplayMember = "CategoryID"
> End Sub
> The lines that are commented out are other ideas that I've tried to no
> avail. I left them here in case they are relevant.
> I suspect that at least part of my problem is in the SqlDataAdapter
> statement where I am pointing to the database. I am working on a
> standalone pc.
> Can anybody see where I am going wrong?
> Thanks,
> Randy
>
For connection string information check out
http://www.connectionstrings.com/?carrier=sqlserver2005
You may also want to some of the examples such as
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclasstopic.as p[/url]
John

Newbie - SqlConnection statement

I'm trying to get a combobox to fill with values from a table. I've
set up an example using the Northwind database with the Categories
table. Dragging in the CategoryID field onto my form as a combobox,
I've set the following properties for the CategoryID combobox:
Value member = "CategoriesBindingSource - CategoryID"
Display Member = "CategoriesBindingSource - CategoryID"
This seems to bring in the correct values in the two non-index fields
(CategoryName and Description). I've then added the following code to
the combobox:
Dim Conn As SqlConnection
Conn = New SqlConnection("Database=Northwnd.mdf")
'Conn.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID FROM Categories", Conn)
Dim ds As New DataSet
da.Fill(ds, "Categories")
CategoryIDComboBox.DataSource = ds
'CategoryIDComboBox.ValueMember = "CategoryID"
'CategoryIDComboBox.DisplayMember = "CategoryID"
End Sub
The lines that are commented out are other ideas that I've tried to no
avail. I left them here in case they are relevant.
I suspect that at least part of my problem is in the SqlDataAdapter
statement where I am pointing to the database. I am working on a
standalone pc.
Can anybody see where I am going wrong?
Thanks,
RandyHi Randy
"Randy" wrote:
> I'm trying to get a combobox to fill with values from a table. I've
> set up an example using the Northwind database with the Categories
> table. Dragging in the CategoryID field onto my form as a combobox,
> I've set the following properties for the CategoryID combobox:
> Value member = "CategoriesBindingSource - CategoryID"
> Display Member = "CategoriesBindingSource - CategoryID"
> This seems to bring in the correct values in the two non-index fields
> (CategoryName and Description). I've then added the following code to
> the combobox:
> Dim Conn As SqlConnection
> Conn = New SqlConnection("Database=Northwnd.mdf")
> 'Conn.Open()
> Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
> CategoryID FROM Categories", Conn)
> Dim ds As New DataSet
> da.Fill(ds, "Categories")
> CategoryIDComboBox.DataSource = ds
> 'CategoryIDComboBox.ValueMember = "CategoryID"
> 'CategoryIDComboBox.DisplayMember = "CategoryID"
> End Sub
> The lines that are commented out are other ideas that I've tried to no
> avail. I left them here in case they are relevant.
> I suspect that at least part of my problem is in the SqlDataAdapter
> statement where I am pointing to the database. I am working on a
> standalone pc.
> Can anybody see where I am going wrong?
> Thanks,
> Randy
>
For connection string information check out
http://www.connectionstrings.com/?carrier=sqlserver2005
You may also want to some of the examples such as
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclasstopic.asp
John

Newbie - Help with Constraints

Dear all,
I am new to SQL Server (using the express edition.) I need to set up a
contraint that checks values from one column in three tables against
one column's value in a pending insertion. If the value that is
attempting to be inserted equals a value from either of the three
tables, the insertion should fail (throw a SQL exception').
Essentially this constraint should operate just like a "no duplicates"
constraint would operate if all the data was in a single column in a
single table and a client attempted to add a duplicate value.
Any advice is appreciated.
Thanks,
JohnnyJohnny,
You could embed the INSERT in a stored procedure and perform the table
checks (SELECTs) prior to the INSERT. Or you could create an INSERT trigger
that would ROLLBACK if the value existed in one of the tables. The former
would be better for performance.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148501225.921395.254460@.y43g2000cwc.googlegroups.com...
> Dear all,
> I am new to SQL Server (using the express edition.) I need to set up a
> contraint that checks values from one column in three tables against
> one column's value in a pending insertion. If the value that is
> attempting to be inserted equals a value from either of the three
> tables, the insertion should fail (throw a SQL exception').
> Essentially this constraint should operate just like a "no duplicates"
> constraint would operate if all the data was in a single column in a
> single table and a client attempted to add a duplicate value.
> Any advice is appreciated.
> Thanks,
> Johnny
>|||If I put this in a stored procedure, how could I ensure that the stored
procedure is called every time an insert statement is executed against
3 different tables? Is this a situation where a trigger is the only
choice?
Thanks,
Johnny|||Johnny,
Put the INSERT statement within the stored procedure and call the proc to
perform the INSERT.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148527276.838343.179960@.i39g2000cwa.googlegroups.com...
> If I put this in a stored procedure, how could I ensure that the stored
> procedure is called every time an insert statement is executed against
> 3 different tables? Is this a situation where a trigger is the only
> choice?
> Thanks,
> Johnny
>

Newbie - Help with Constraints

Dear all,
I am new to SQL Server (using the express edition.) I need to set up a
contraint that checks values from one column in three tables against
one column's value in a pending insertion. If the value that is
attempting to be inserted equals a value from either of the three
tables, the insertion should fail (throw a SQL exception').
Essentially this constraint should operate just like a "no duplicates"
constraint would operate if all the data was in a single column in a
single table and a client attempted to add a duplicate value.
Any advice is appreciated.
Thanks,
JohnnyJohnny,
You could embed the INSERT in a stored procedure and perform the table
checks (SELECTs) prior to the INSERT. Or you could create an INSERT trigger
that would ROLLBACK if the value existed in one of the tables. The former
would be better for performance.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148501225.921395.254460@.y43g2000cwc.googlegroups.com...
> Dear all,
> I am new to SQL Server (using the express edition.) I need to set up a
> contraint that checks values from one column in three tables against
> one column's value in a pending insertion. If the value that is
> attempting to be inserted equals a value from either of the three
> tables, the insertion should fail (throw a SQL exception').
> Essentially this constraint should operate just like a "no duplicates"
> constraint would operate if all the data was in a single column in a
> single table and a client attempted to add a duplicate value.
> Any advice is appreciated.
> Thanks,
> Johnny
>|||If I put this in a stored procedure, how could I ensure that the stored
procedure is called every time an insert statement is executed against
3 different tables? Is this a situation where a trigger is the only
choice?
Thanks,
Johnny|||Johnny,
Put the INSERT statement within the stored procedure and call the proc to
perform the INSERT.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148527276.838343.179960@.i39g2000cwa.googlegroups.com...
> If I put this in a stored procedure, how could I ensure that the stored
> procedure is called every time an insert statement is executed against
> 3 different tables? Is this a situation where a trigger is the only
> choice?
> Thanks,
> Johnny
>

newbie - Display rows with identical values

Hello,
how can i retrieve duplicate records?
I have this table with about 6500 records, and i know that there are a few
where a combination (Column1 - Column2) is identical
how can i retrieve these?SELECT Column1, Column2
FROM YourTable
GROUP BY Column1 - Column2
HAVING COUNT(*) > 1
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"benoit" <benoit@.discussions.microsoft.com> wrote in message
news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||hi benoit,
Just a question, your table own a indentity field?
If so, use this:
delete from table1
where <identityfield> not in
(select max(<identityfiedl> ) from table1
group by [<field1>,<field2>]
Otherwise, let me know or post DDL
regards,
"benoit" wrote:

> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||i was thinking the same way as roji
use northwind
select lastname,firstname into x from employees
union all
select top 5 lastname,firstname from employees
go
select lastname, firstname from x
group by lastname,firstname
having count(*)>1
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"benoit" wrote:

> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||works great !
thx
"Roji. P. Thomas" wrote:

> SELECT Column1, Column2
> FROM YourTable
> GROUP BY Column1 - Column2
> HAVING COUNT(*) > 1
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "benoit" <benoit@.discussions.microsoft.com> wrote in message
> news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
>
>|||Hi
This article had written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"benoit" <benoit@.discussions.microsoft.com> wrote in message
news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>

newbie - Decimals

how come that if I put in this value
tblMyTable.currency is a decimal(8,4)
insert into tblMyTable(currency) values ('0.4568')
I get this in return
.4568
or is this normal?Where are you doing this?. Depend which client application you are using to
select the data. In QA it is normal.
AMB
"Boonaap" wrote:

> how come that if I put in this value
> tblMyTable.currency is a decimal(8,4)
> insert into tblMyTable(currency) values ('0.4568')
> I get this in return
> .4568
> or is this normal?|||> or is this normal?
The formatting of data for display purposes is up to the client application.
It is normal for Query Analyzer to suppress leading zeros. Also, you should
specify a numeric literal rather than a string literal here since your
target data type is numeric. Don't enclose the value in quotes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Boonaap" <Boonaap@.discussions.microsoft.com> wrote in message
news:B7B55DE1-DF3E-495F-94A1-B9DC718CEF96@.microsoft.com...
> how come that if I put in this value
> tblMyTable.currency is a decimal(8,4)
> insert into tblMyTable(currency) values ('0.4568')
> I get this in return
> .4568
> or is this normal?|||"Boonaap" <Boonaap@.discussions.microsoft.com> wrote in message
news:B7B55DE1-DF3E-495F-94A1-B9DC718CEF96@.microsoft.com...
> how come that if I put in this value
> tblMyTable.currency is a decimal(8,4)
> insert into tblMyTable(currency) values ('0.4568')
> I get this in return
> .4568
Why are you worried that you got the same number out as you put in? Were
you expecting it to be a different number? What's the problem?!
Oh, and don't enclose numeric values in quotation marks, SQL server is
having to do a typecast to convert your string into a number.
Steve

newbe ? re:date format

Hi, I'm developing a page in asp.net using a sqldb and one of the values I'm sending to the database is a date/time value. I only want the table to show the date in short format, no time. Is there a way to restrict or convert the data in the table design mode?
Thanks
RaifSQL Server dates are stored as numeric values that interpreted as dates with time of day. Formatting of output should be handled by your user interface, not the database server.|||Thanks I sort suspected that as I started writing the post.
Thanks again|||Go with a smalldatetime in the sql table so that you are only using a (4). The go with a .ToString() and something like this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclasstostringtopic4.asp
Hope it helps.|||If you're doing straight SELECT from your ASP, you can use CONVERT(char(10), <your_date_field>, 101) on your date/time field.