get value from SQL server 2005 select statement with datareader

[SFM]

I just want a simple datareader, that i can read the value returned
from a select statement executed on a SQL server 2005 db.

The code below should work in, but email[calc]= rdr[0].ToString();
when i want to read some data a get a exception saying:

System.InvalidOperationException was unhandled by user code
Message="Invalid attempt to read when no data is present."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
at System.Data.SqlClient.SqlDataReader.get_Item(Int32 i)
at _Default.Login_Click(Object sender, EventArgs e) in d:\My
Documents\Visual Studio 2005\WebSites\WebSite1\Default.aspx.cs:line 47
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String
eventArgument)
at
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection
postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

If anybody could advise me where my stupid mistake is then i would
highly appreciate it!



SqlConnection conn = new SqlConnection(getConnection());
SqlDataReader rdr = null;

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "SELECT * FROM Customer";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;

try
{
conn.Open();

rdr = cmd.ExecuteReader();

int calc = 0;

Boolean login = false;
string[] email = new string[100];
object[] password = new object[100];

while (rdr.HasRows) // or rdr.Read();
{
rdr.Read();
email[calc]= rdr[0].ToString();
password[calc] = rdr["Password"].ToString();
if (UserName.Text.Equals(email[calc]) &&
Password.Text.Equals(password[calc]))
{
login = true;
}
calc++;
}

}
finally
{
rdr.Close();
}





thanks....

 

[Frank]

I would try this:

while (rdr.Read) // or rdr.Read();
{
// remove rdr.Read();

instead of:
while (rdr.HasRows) // or rdr.Read();
{
rdr.Read();

 

[Dave Sexton]

Hi,

You might want to create a stored procedure that handles the verification for
you. Then, your code will be really simple, such as:

string userName = txtUserName.Text;
string password = txtPassword.Text;

// TODO: verify user input for null, empty strings, length, etc.

using (SqlConnection conn = new SqlConnection(str))
{
conn.Open();

using (SqlCommand comm = new SqlCommand(
"VerifyCustomerCredentials", conn))
{
comm.Type = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@UserName", userName);
comm.Parameters.AddWithValue("@Password", password);

bool success = (bool) comm.ExecuteScalar();
}
}


And the procedure could look something like the following:

CREATE PROC dbo.VerifyCustomerCredentials (
@UserName nvarchar(15),
@Password nvarchar(15)
) AS BEGIN

SET NOCOUNT ON;

IF EXISTS(SELECT * FROM dbo.Customer
WHERE [UserName] = @UserName
AND [Password] = @Password)
SELECT 1;
ELSE
SELECT 0;

END

(I haven't tested this code so it might require some modifications to work
correctly, but the general idea is there)

--
Dave Sexton

I just want a simple datareader, that i can read the value returned
from a select statement executed on a SQL server 2005 db.

The code below should work in, but email[calc]= rdr[0].ToString();
when i want to read some data a get a exception saying:

System.InvalidOperationException was unhandled by user code
Message="Invalid attempt to read when no data is present."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
at System.Data.SqlClient.SqlDataReader.get_Item(Int32 i)
at _Default.Login_Click(Object sender, EventArgs e) in d:\My
Documents\Visual Studio 2005\WebSites\WebSite1\Default.aspx.cs:line 47
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String
eventArgument)
at
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection
postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

If anybody could advise me where my stupid mistake is then i would
highly appreciate it!



SqlConnection conn = new SqlConnection(getConnection());
SqlDataReader rdr = null;

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "SELECT * FROM Customer";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;

try
{
conn.Open();

rdr = cmd.ExecuteReader();

int calc = 0;

Boolean login = false;
string[] email = new string[100];
object[] password = new object[100];

while (rdr.HasRows) // or rdr.Read();
{
rdr.Read();
email[calc]= rdr[0].ToString();
password[calc] = rdr["Password"].ToString();
if (UserName.Text.Equals(email[calc]) &&
Password.Text.Equals(password[calc]))
{
login = true;
}
calc++;
}

}
finally
{
rdr.Close();
}





thanks....