Comparing Dates.

Hi All,

I have a database field (datestamp) which returns the date the records were inserted into the database. The datestamp was created with the now(); function in .net and is in the following format:5/23/2006 2:27:45 AM

I basically want to return all records that were inputted more than 28 days ago. I have had alook though some other posts and below is the closest query that i could find but unfortunately it does not work for me.

SELECT id, datestamp
FROM table
WHERE datestamp > DateAdd(d, 28, GetDate())

Thanks in advance,

Jake

You are looking for a date 28 days ago, so try -28:

SELECT id, datestamp
FROM table
WHERE datestamp > DateAdd(d,-28, GetDate())

|||

Spot on Douglas, thankyou for you quick reply!

Jake

compare data

I've created two tables. One table (Classes) stores data about classes
that we offer. The Classes table stores the class id (classid) and the
max number of students allows (maxstudents). The other table
(Students) stores student data and the class they register for.

When a user registers for a class, the classid column data from Classes
populates the class column in Students.

I'm not sure how to count the number of students who registered for
course X, subtract that from the max number of students in the Classes
table, and display that the class if the max is reached either in a
warning dialog box or as text on the page.

I'm also populating a drop-down field on the registration form with the
class information from Classes. Confused yet?

I don't know much about SQL or .ASP. Any help is appreciated.> When a user registers for a class, the classid column data from
Classes
> populates the class column in Students

That seems strange. Don't you want to allow a student to take more than
one class? I would have expected a joining table to implement a
many-to-many relationship:

CREATE TABLE StudentClasses (student_id INTEGER NOT NULL REFERENCES
Students (student_id), class_id INTEGER NOT NULL REFERENCES Classes
(class_id), PRIMARY KEY (student_id,class_id))

Try this for your query:

SELECT C.class_id,
C.maxstudents - COUNT(S.class_id)
FROM Classes AS C
LEFT JOIN Students AS S
ON C.class_id = S.class_id
GROUP BY C.class_id, C.maxstudents

--
David Portas
SQL Server MVP
--|||Your design is wrong. Students do not have a class attribute; you need
enrollment for many classes.

CREATE TABLE Students
(stud_id INTEGER NOT NULL PRIMARY KEY,
...);

CREATE TABLE Classes
(class_id INTEGER NOT NULL PRIMARY KEY,
room_size INTEGER NOT NULL,
..);

CREATE TABLE Enrollment
(stud_id INTEGER NOT NULL,
class_id INTEGER NOT NULL,
PRIMARY KEY (stud_id, class_id),
...);

>> count the number of students who registered for course X, subtract
that from the max number of students in the Classes table,<<

SELECT E1.class_id,
(SELECT room_size
FROM Classes AS C1
WHERE C1.class_id = E1.class_id)
- COUNT(E1.student_id) AS available_seats
FROM Enrollment AS E1
GROUP BY E1.class_id;

Company footer message on all reports

I've looked around, but can't find this anywhere. Does anyone know of a way to add a footer to all reports (including new ones being created via report builder)? So that when users create new reports, the global footer is there.

Maybe create a "Company Footer" as a subreport?

Granted you will still have to insert the subreport into each of your report footers, but any changes needing to be made to the footer will only need to be done once.

|||That is an option, but we want all users to be forced to use this.
|||

The best bet would be to create a report template and then have all report developers use the template.

To create a report template:

create a report with all the common elements, store the .rdl file in the Report Designer template directory (in a default installation this would be C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject|||This is an excellent suggestion, and one we will probably use for developers. However, the main area we want to catch is the Report Builder reports. Here we can't possibly train all the users to include a subreport, or something similar. What we need is a way to alter the predefined Report Builder template, to add a company footer. Any ideas?

Company - Address

I need some help devising my tables.

I have a company table and a address table. I have created a linked table with two foreign keys from the company table and the address table respectively.

This set up allows me to apply more than one address per company which is fine. My problem is that I need a way to prevent a company from entering exactly the same address twice.

In the address table i have the following fields:

AddressKey - PK
CompanyKey - FK
Address 1 - 3
Town
County
Country
PostCode

In the company table I have the following fields.

CompanyKey - PK
FirstName
Secondname

And finally in the link table I have the following:

CompanyKey - FK
AddressKey - FK.

Now, if i entered the following into the address table Assuming that company id of 1 was already entered into the company table.

AddressKey - 1, CompanyKey = 1, Address1 = 11 Address2= Taylor

I need a way of preventing this from happening.

AddressKey - 2, CompanyKey = 1, Address1 = 11 Address2= Taylor

As can be seen the PK - FK values are unique and are correct for referential integrity, but the actual address is the same.

Any help we be mostly appreciated.

Cheers

Wayneyou do not need the linking table at all, because you apparently want to link each address to its company using the CompanyKey as a FK in the address table

the only time you need a linking table is when multiple companies share the same address

and even if this does occur, it probably does not occur all that frequently (compared to the bulk of your data)

therefore if two companies have the same address, put that address into the table twice

now, as to your question...

to prevent the same company from entering the same address twice, declare a unique constraint on ( CompanyKey, Address 1 - 3, Town, County, Country, PostCode )|||r937.

Thanks for the reply.

ok I understand why you would not use the linked table, but In my case the address table is in effect the site address of a particular company, and companies can have multiple site addresses.

If i don't use a linked table then this would not be possible.

I tried using an instead of trigger - would this not be a good idea in this case then?

Cheers

Wayne|||let's make sure we are talking about the same linked table

you can support the one-to-many model of a company having multiple addresses just with Company and Address table

the Address table CompanyKey is the FK that says which company this is an address for

the 3rd table, the link table, is not necessary

unless, like i said earlier, you wnt two different companies to "share" an address!

as far as preventing a compnay from entering the same address twice, no, you would not use a trigger for that, you would use a unique constraint for that|||r937.

Thanks for clearing that up for me. I will give it a stab once i've finished browsing ebay. Ok to give you a shout if I have any problems?

Wayne|||if you were thinking of contacting me personally through a private message or email, i would prefer that you post a followup in this thread

:cool:|||sorry that's what I meant!|||here would be a way to solve your issue by using a composite key(or Unique index)and an intersecting entity
your business rule dictates that one customer can have many addresses and that no duplicate addresses can be used

you could create a customer table

customerid PK
companyname
addressid

then create a address table

Addressid PK
StreetAddress
city
state etc..

now the issue exists that you cannot get 2 addresses out of this erd but you can by modifying the erd with an intersecting entity..

create a customeraddress table

Customerid PK
addressid PK
(any dependent data on a customer at a particular address goes in this table)
the composite key on the two columns generates uniqueness for each of them. (no one customer can have a duplicate address)

The join path would look like this
Customer <----> CustomerAddress <----->Address|||Ruprect, that's beautiful, but that's the many-to-many relationship

i have my doubts about whether that's required

i think only a one-to-many is required

and in any case, if you go right back to the original question, the situation was

AddressKey - 1, Address1 = 11 Address2= Taylor
AddressKey - 2, Address1 = 11 Address2= Taylor

and thus possible to give the "same" address to a company twice, because the "same" address is actually in the table twice! (this is a problem of surrogate keys, not one-to-many versus many-to-many)

your 3-table many-to-many does not prevent that duplication either

here's what i recommend:
create table Companies
( CompanyKey
, FirstName
, Secondname
, primary key (CompanyKey)
)
create table Addresses
( AddressKey
, CompanyKey
, Address1
, Address2
, Address3
, Town
, County
, Country
, PostCode
, primary key (AddressKey)
, foreign key (CompanyKey) references Companies (CompanyKey)
, unique (Address1, Address2, Address3, Town, County, Country, PostCode)|||Sunday Morning.

Just read your replies guys. Ruprect as r937 states, this was my original solution and does not prevent a duplicate address from existing, ok it does allow me to create a unique constrant based on the companykey and addresskey of the respective tables but this was not what I was after. - Thanks for the input though.

r937 - going to that last reply a stab.

Thanks for all your helps guys.

Wayne|||Thats worked fine.

Next question is how do I take that sever error message that is created (Server msg 2627) and produce a user friendly error message stating that they have violated key constraints.

Would I design a trigger for this?|||wait
now hold on justa cotton picken minute rabbit! ( channeling yosemite sam)

you solution limits the address process the actual # of cols (in this case 3) what if someone has 4 addresses are you going to alter the table?

my solution which is typically a many to many solution also eliminates the dupes in the customeraddresstable by creating a composite unique constraint or index on the customerid and addressid columns

here are the customers and address tables with some data
customer c Address a
c1 a
c2 b
c3 c
c4 d
c5 e

here is the customeraddress table

c1 a
c1 b
c1 c
c1 d
c1 e

if i try to add customer c1 with the address a again i will violate the constraint. no dupes and there is no limit to the possible addresses that one customer can have.

i wouldnt like to use it because of the extra joins so the previous solution might have a perf advantage. but my solutin does work.|||in the end guys i cam up with this design|||cool! good for you

Common FTS catalog for more then one database

Hi All!
Can I use one FTS catalog for more then one database?
I'd like to use FTS catalog created by another (replicated) database.
Can I?
Many Thanks,
Vladimir.
--
v_rus@.bk.ru
--I believe you need to have individual for each database.

command scripts

I have created some stored procedures (SP) containing DBCC commands for
system maintenance and status checks. Does anybody know where to get sample
windows command scripts that will wrap all of my SPs, create logs, and also
where the windows scheduler could run at specific times. The idea is to
automate the maintenance instead of running individual SPs or DBCCs in Query
Analyzer.
Thanks...Schedule them using SQLServerAgent - Look up the service features in Books
online.
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:3AC72F6E-B0A3-4238-9021-DA964E4A8C06@.microsoft.com...
>I have created some stored procedures (SP) containing DBCC commands for
> system maintenance and status checks. Does anybody know where to get
> sample
> windows command scripts that will wrap all of my SPs, create logs, and
> also
> where the windows scheduler could run at specific times. The idea is to
> automate the maintenance instead of running individual SPs or DBCCs in
> Query
> Analyzer.
> Thanks...|||Create a job and use the SQL Server Agent for scheduling it.
Maybe you can check this link
http://www.sql-server-performance.c...erver_agent.asp
Hope this helps.|||Thanks..
"Omnibuzz" wrote:

> Create a job and use the SQL Server Agent for scheduling it.
> Maybe you can check this link
> http://www.sql-server-performance.c...erver_agent.asp
>
> Hope this helps.

Command Parameter default to current date?

I created a date parameter for my command that I'd like to default to the current date. How can I do that?

I've done it with regular parameters (create as string & parse via a formula, then use the formula result in the record selection formula-I can detect a blank parm & set the formula to the current date) but can't figure out how to do it with this since the selection uses the parm itself.

It pretty much needs to be done this way because performance sucks entering the date via a regular parm. Even on the test database (2K records) it's poor and it's expected to be totally unsatisfactory on the whole db.

Thanks.Maybe I'm too impatient but with no responses yet I'm wondering if my question is unclear?

After more reading it appears that the obvious way of setting a default value derived from a formula would be to make the parameter dynamic-but there's no way to do that from the Command Parameter screen & when I change it from the regular Parameter screen it changes the type from Date to String.

I really like it as a Date type so users can select the date from the calendar-but I also need it able to default to the current date. Maybe those two things are incompatible-but I see no reason why they should be so, if that is the case, I'd like it confirmed before I tell my boss that it can't do both.

Thanks.|||Have you resolved defaulting to current date? I am trying to do the same thing and did not see any replies to your thread. Thank you

Command Line Arguments in Debug mode

Hi,

I am passing some /SET commands on the command line when I execute packages I created in SSIS and have run from the SQL Server Job manager. When I put these same /SET commands on the command line arguments section of the project properties page I would expect they would be passed into the executed package when it is run thru BIDS. But the variables still have their defined values when the packages executes.

Is there a trick to getting command line arguments to work in the debugger for SSIS/BIDS?

DaveM

No, I do not think the application (DTSDebugHost) that runs packages in the debug mode knows how to parse those arguments like DTExec does.

You can simply set those variables inside their window.

Thanks.

|||

Setting the variables inside their window doesn't allow for dynamic selection of values based on runtime values.... which is what I am doing, and trying to test/debug. It works fine except when inside Bids/Visual Studio 2005.

Is this something that will be fixed in SP2 or SP3?

Thanks

|||

DaveM wrote:

Setting the variables inside their window doesn't allow for dynamic selection of values based on runtime values.... which is what I am doing, and trying to test/debug. It works fine except when inside Bids/Visual Studio 2005.

Is this something that will be fixed in SP2 or SP3?

Thanks

SP2 is out and the behavior isn't changed there. I tried it. Seems to me like it should work. The documentation implies that it works the same as dtexec.

From BOL:
CmdLineArguments. Run the package with the specified command-line arguments. For information about command-line arguments, see dtexec Utility.

comma separated values to stored procedures

Hi All,

i hv created a sp as

Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end

where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'

but this statement throws an conversion error ( int to char error).

is there any way to fetch records from a select statement using a
string?

Thanks in Advance.shark wrote:

Quote:

Originally Posted by

Hi All,
>
i hv created a sp as
>
>
Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)

This does not work as you probably want. You get a selection criterium
with a single string @.iClientid. You could have written

select * from clients where CONVERT(VACHAR(100),iClientid) = @.iclientid

Quote:

Originally Posted by

end
>
where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'
>
but this statement throws an conversion error ( int to char error).
>
is there any way to fetch records from a select statement using a
string?

If you need to pass in your id's the way you do, you can do it with
dynamic SQL, i.e. create a SQL statement and EXEC it.

robert|||See Erland's article on the subject:
http://www.sommarskog.se/arrays-in-sql.html
--
Hope this helps.

Dan Guzman
SQL Server MVP

"shark" <xavier.sharon@.gmail.comwrote in message
news:1152022816.209445.326230@.p79g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

Hi All,
>
i hv created a sp as
>
>
Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end
>
where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'
>
but this statement throws an conversion error ( int to char error).
>
is there any way to fetch records from a select statement using a
string?
>
Thanks in Advance.
>

|||Where ','+@.iClientid+',' like '%,+cast(iClientid as varchar(20))+',%'

Madhivanan

Dan Guzman wrote:

Quote:

Originally Posted by

See Erland's article on the subject:
http://www.sommarskog.se/arrays-in-sql.html
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"shark" <xavier.sharon@.gmail.comwrote in message
news:1152022816.209445.326230@.p79g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

Hi All,

i hv created a sp as

Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end

where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'

but this statement throws an conversion error ( int to char error).

is there any way to fetch records from a select statement using a
string?

Thanks in Advance.

comma delimited results help please

Would like to have a view created to display the result at the bottom of this message. We will be using Dreamweaver to display the information from this view. Also, for the record, we are using sql 2000. Any help would be greatly appreciated.

tblservers
servid servername
1 server1
2 server2
3 server3

tblapplications
appid appname
1 app1
2 app2
3 app3

tblapplink
id appid servid
1 1 1
2 1 2
3 1 3
4 2 1
5 2 3
6 3 1

we want to display this information as below:

appname serverlist
app1 server1, server2, server3
app2 server1, server3
app3 server1

Thank you very much,
mrtwohttp://www.dbforums.com/showthread.php?p=3710823#post3710823|||Thank you very much! It works just as we needed it to. We appreciate you taking time to submit a reply!!

One big frustration down, most likely several more to go!

Thanks again!

mrtwo

comma delimited list

Hello,
I have created a small query that will output a list of email addresses that
are separated by commas. I plan to copy and paste the list into a email
invite field (send to: field for an email app). But what is happening is tha
t
only one address appears in the field when I paste because of the added
spaces between the names, example currently outputs like:
blahblah@.blah.com,
blahblah@.blah.com,
blahblah@.blah.com
What I need is it to output like this:
blahblah@.blah.com,blahblah@.blah.com,blahblah@.blah.com
Here is the small query that I am using to extract the emails.
SELECT RTRIM(LTRIM(EMAIL_ADDRESS)) + ',' AS [Name]
FROM evPRUclinicala
WHERE CREATE_DATE BETWEEN '4/1/06' AND '4/30/06'
AND EMAIL_ADDRESS IS NOT NULL
Any help would be appreciated and thanks in advance.
Message posted via http://www.webservertalk.comIn general, a recommended approach is to extract the resultset outside the
server and massage the data to appropriate display format using some client.
Regarding the alterantives for doing it at the server, you can check out the
following links:
( For SQL 2005 only )
http://groups.google.com/group/micr...br />
9b9b968a
( For SQL 2000 & 2005 )
http://groups.google.com/group/micr...br />
6dd9e73e
Anith|||Anith Sen wrote:
>In general, a recommended approach is to extract the resultset outside the
>server and massage the data to appropriate display format using some client
.
>Regarding the alterantives for doing it at the server, you can check out th
e
>following links:
>( For SQL 2005 only )
>http://groups.google.com/group/micr...r />
a9b9b968a
>( For SQL 2000 & 2005 )
>http://groups.google.com/group/micr...r />
66dd9e73e
>
Thanks for the direction, but I am still have some problems. I elected to us
e
the cursor version of your solution: see below
DECLARE @.temptable TABLE ( list VARCHAR( 8000 ) )
SET NOCOUNT ON
DECLARE @.PRUemail VARCHAR( 1000 ), @.emailNext VARCHAR (100)
DECLARE c CURSOR FOR
SELECT EMAIL_ADDRESS
FROM evPRUclinicala
ORDER BY EMAIL_ADDRESS
OPEN c
FETCH NEXT FROM c INTO @.emailNext
SET @.PRUemail = @.emailNext
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF @.emailNext > @.PRUemail BEGIN
INSERT @.temptable SELECT @.PRUemail
SELECT @.PRUemail = @.emailNext
END ELSE
SET @.PRUemail = COALESCE( @.PRUemail + ',', SPACE( 0 ) ) +
@.emailNext
FETCH NEXT FROM c INTO @.emailNext
END
INSERT @.temptable SELECT @.PRUemail
CLOSE c
DEALLOCATE c
SET NOCOUNT OFF
SELECT * FROM @.temptable
But I am still getting a vertical list with no commas. Can you tell me what
I
am doing wrong? Thanks
Message posted via http://www.webservertalk.com|||>> I elected to use the cursor version of your solution:
Why? It is a bad choice to begin with.
I have no idea how the table evPRUclinicala is structured or what the nature
of the data is. Can you post your table DDLs & a few sample data? For
details, refer to: www.aspfaq.com/5006
Anith|||"Jay via webservertalk.com" <u7124@.uwe> wrote in message
news:601bd1f4c21b5@.uwe...
>.
> Can you tell me what I am doing wrong?
>.
Yeah, your not using Rac :)
www.rac4sql.net/onlinehelp.asp?topic=236|||Anith Sen wrote:
>Why? It is a bad choice to begin with.
>
>I have no idea how the table evPRUclinicala is structured or what the natur
e
>of the data is. Can you post your table DDLs & a few sample data? For
>details, refer to: www.aspfaq.com/5006
>
What would have been a better choice? If I should use a different method tha
n
I would appreciate your advise here. If not I will get you the DDLs.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||Cursors are generally performance hogs. In most cases, it is wiser to
utilize a set based solution in SQL where a set of rows are manipulated as a
whole rather than doing it one row at a time.
As a long term solution, consider returning the resultset to the client and
manipulate the data using a client application program -- this allows the
stored procedure to be sufficiently generic, eliminates the need to use
server's processing power for string manipulations & loop-based constructs
and leverages the string functionalities of the client language, which in
most cases are more versatile than t-SQL.
If you are convinced to use the server for such stuff, consider the table
valued UDF approach which might be better performing for medium sized
datasets. For SQL 2005, the XML kludge might be faster, though we do not
have much useful benchmarks with this approach.
Alternatively, for large volume transactions of this nature, consider using
3rd party tool's like RAC mentioned in Steve's post.
Anith|||Anith Sen wrote:
>Why? It is a bad choice to begin with.
>
>I have no idea how the table evPRUclinicala is structured or what the natur
e
>of the data is. Can you post your table DDLs & a few sample data? For
>details, refer to: www.aspfaq.com/5006
>
Ok I will look at the other solutions rather than the one chosen. Thanks
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1

come on SQLdatareader....read....read....read...ok help.

OK, I'm using VS2003 and I'm having trouble. The page works perfectly when I created it with WebMatrix but I want to learn more about creating code behind pages and this page doesn't work. I think it has some things to do with Query builder but I can't seem to get change outside "please register". I have the table populated and it is not coming back with "login successful" or "password wrong" when I've entered correct information. Enclosed is what I've done in VS2003. Can you see where my error is? Any help would be greatly appreciated.
Thanks again.

Imports System.data.sqlclient
Imports System.Data
Public Class login2
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "server=LAWORKSTATION;user id=sa;database=test;password=t3st"
'
'SqlCommand1
'
Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = 'txtusername.text')"
Me.SqlCommand1.Connection = Me.SqlConnection1

End Sub
Protected WithEvents lblUsername As System.Web.UI.WebControls.Label
Protected WithEvents lblPassword As System.Web.UI.WebControls.Label
Protected WithEvents txtUsername As System.Web.UI.WebControls.TextBox
Protected WithEvents txtPassword As System.Web.UI.WebControls.TextBox
Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button
Protected WithEvents lblMessage As System.Web.UI.WebControls.Label
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand

'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
SqlConnection1.Open()
Dim dr As SqlDataReader = SqlCommand1.ExecuteReader
If dr.Read() Then
If dr("password").ToString = txtPassword.Text Then
lblMessage.Text = "login successful"
Else
lblMessage.Text = "Wrong password"
End If
Else
lblMessage.Text = "Please register"
End If
dr.Close()
SqlConnection1.Close()
End Sub
End ClassTake a look at this line here:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = 'txtusername.text')"

What you are asking here is to retrieve all [pass] fields from the rows in [Customer] table where the value of field [email] is "txtusername.text".

What you probably meant to ask is retrieve all [pass] fields from the rows in [Customer] table where the value of field [email] is the same as the value of txtUsername.Text.

Which should translate into command text like so:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = '" & txtUsername.Text & "')"

That should work. However, now you need to think about the possibility of a SQL injection attacks. Suppose somebody enters this is string as an email (single qoutes are significant):

'); DELETE Customer; SELECT ('

As you might've guessed, that would neatly zap your Customer table.

There are many ways to protect yourself from such an attack. Here's one possibility:

Create a function like this (pardon my VB.NET, it's rusty):

Function Tick(s As String)
Tick = s.Replace("'", "''")
End Function

And modify the command text assignment line like so:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = '" & Tick(txtUsername.Text) & "')"

That would make sure that any attempts to close the tick marks in the statement would fail.

Anyways, happy coding!|||Better than trying to outsmart folks trying SQL Injection attacks,use parameters.|||OK, for testing purposes I did change it with querybuilder and this is what VS2003 enters in for the command:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = ' "" & txtUsername.text & "" ')"

It enters double "" by default...and it doesn't work.

I then manually entered you line precisely as typed above:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = '" & txtUsername.Text & "')"

I've also tried:
Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE email = '" & txtUsername.Text & "'"

It still doesn't work. The response is always "Please register".

I've tested the connection and it works. The table data is correct when I enter in the right login and password but the prompt is nothing but "Please register". This page without the code behind works flawlessly. I'm stumped.|||Erm unless I'm reading this incorrectly isn't the result of txtUserName.text ALWAYS empty? You're populating the command string before anyone's typed in their name.

Try moving the ...CommandText = ... line just before the ExecuteReader|||Good catch, pkr!

He is right. Although we've fixed the one problem, there's still the other one present: InitializeComponent is called (and the CommandText property initialized) at the time the page is loading, which prevents the actual submitted value of txtUsername from ever being submitted.

As "douglas.reily" noted, time to call in parameters for help.

First, let's modify our query to this:

SELECT pass FROM Customer WHERE (email = @.email)

Then, just before you call ExecuteReader, add parameter to the command:

Me.SqlCommand1.Parameters.Add(New SqlParameters("@.email", textUsername.Text)

That should do it.|||I think you are reading it incorrectly. SQLdatareader runs SQLcommand1 which, in turn, runs the query for getting the login and password. SQLdatareader is run only when the user has clicked on the "submit" button and txtUsername.text is populated then. At least, I think this is how it happens.
I'm going to move to stored procedures and parameters instead but this is just bugging me that this page works perfectly fine without a code behind page but it has problems when I redid it with VS2003.|||OK, I've made some changes and added a parameter. In the "click" sub I've populated the parameter like this:

SqlCommand1.Parameters.Item("@.email").Value = txtUsername.Text

This seems to work. Thanks for all your help.|||Glad it's working. But out of interest does it still work in you put the ....value = txtUserName.Text... line back to it's original position. i.e. not in the same function as the button event?