Logo 
Search:

Asp.net Forum

Ask Question   UnAnswered
Home » Forum » Asp.net       RSS Feeds

How to use the DataReader.

  Asked By: Hamish    Date: Jul 19    Category: Asp.net    Views: 2281
  

i' m trying to make a login module and want to get the userid, username and
password of the dataBase (SQL server)

To do this, i add the parameters username and password to the objCmd
and work with an dataReader

But something is going wrong and i can't see my fault, i guess it is in that
double loop...
So far, i've never worked with dataReader but used to use dataSets to load data
in...

Someone of u see it?


CODE SNIPPET:

in the region:

Me.SqlInsertCommand1.CommandText = "[usp_InsertKlant]"
Me.SqlInsertCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlInsertCommand1.Connection = Me.SqlConnection2
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int,
4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@KNaam", System.Data.SqlDbType.VarChar, 20,
"KNaam"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Paswoord", System.Data.SqlDbType.VarChar,
20, "Paswoord"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@SesID", System.Data.SqlDbType.VarChar, 20,
"SesID"))





in the code:

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnLogin.Click
'Get values from input...
Dim myName As String = txtName.Text
Dim myPass As String = txtPassword.Text

SqlSelectCommand1.Parameters("@KNaam").Value = myName
SqlSelectCommand1.Parameters("@Paswoord").Value = myPass
SqlConnection2.Open()

Dim myDataReader As System.Data.SqlClient.SqlDataReader
myDataReader = SqlSelectCommand1.ExecuteReader

Do
While (myDataReader.Read)
If ((myName = myDataReader.GetString(1)) And (myPass =
myDataReader.GetString(2))) Then
foutmelding.Text = "Gelukt"
Session("KlantID") = myDataReader.GetString(0)
TextBox1.Text = myName
TextBox2.Text = myPass
'...

Else
foutmelding.Text = "Mislukt"
End If
End While

Loop While (myDataReader.NextResult)

myDataReader.Close()
SqlConnection2.Close()
End Sub

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Aabirah Khan     Answered On: Jul 19

Do <<--------------- don't need it
While (myDataReader.Read)
End While

Loop While (myDataReader.NextResult) <<--------------- don't need it

Reader stops reading when done.

 
Answer #2    Answered By: Utsav Shah     Answered On: Jul 19

But i've still 2 other problems:

* why can't i read my userid => stockated in the first column of my database
(primary key and identity on!)
* when i'm using sessions here i get a message that i have to set the
enableSessionState...
Where can i do that, on the other forms in the same project, i didn't have to
do it...



Stored procedure:
*********************


CREATE PROCEDURE dbo.usp_SelectKlant
(
@KNaam char(20),
@Paswoord char(20)
)
AS
SET NOCOUNT ON;
SELECT KlantID, KNaam, Paswoord, SesID FROM KLANT WHERE (KNaam = @KNaam) AND
(Paswoord = @Paswoord)
GO

in region:
***********

Me.SqlSelectCommand1.CommandText = "[usp_SelectKlant]"
Me.SqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand1.Connection = Me.SqlConnection2
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int,
4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@KNaam", System.Data.SqlDbType.VarChar, 20,
"KNaam"))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Paswoord", System.Data.SqlDbType.VarChar,
20, "Paswoord"))

in code:

*********
Dim myName As String = txtName.Text.ToLower.Trim
Dim myPass As String = txtPassword.Text.ToLower.Trim

SqlSelectCommand1.Parameters("@KNaam").Value = myName
SqlSelectCommand1.Parameters("@Paswoord").Value = myPass
SqlConnection2.Open()

Dim myDataReader As System.Data.SqlClient.SqlDataReader
myDataReader = SqlSelectCommand1.ExecuteReader

If (myDataReader.Read) Then

Dim myKlantID As String = CType(myDataReader.GetString(0), String)
Dim myNameDB As String = myDataReader.GetString(1).ToLower.Trim
Dim myPassDB As String = myDataReader.GetString(2).ToLower.Trim

If ((myName.ToLower = myNameDB) And (myPass.ToLower = myPassDB)) Then

foutmelding.Text = "Gelukt"
Session("KlantID") = myKlantID
TextBox1.Text = myNameDB
TextBox2.Text = myPassDB
'...
Else
'niet ingelogd
foutmelding.Text = "Mislukt"
TextBox1.Text = myKlantID.ToString & "-" & myNameDB
TextBox2.Text = myPassDB & "-" & myPass
End If
Else
'niet ingelogd
foutmelding.Text = "mislukt"

End If

 
Answer #3    Answered By: Ziza Mizrachi     Answered On: Jul 19

I've forgotton all my SQL since messing with controls (... and
good riddance, boring, boring, boring)


Dim myKlantID As String = CType(myDataReader.GetString(0), String)
Dim myNameDB As String = myDataReader.GetString(1).ToLower.Trim <<--- why no
Ctype ?
Dim myPassDB As String = myDataReader.GetString(2).ToLower.Trim <<--- why no
Ctype ?

anywhere I think, @Page , in code, in web.config, just
write "EnableSessionState=true" somewhere and that should do it, you could even
write it on the back of your hand in case you forget to do it.

Hey scoopie, I have some bad news for you .... someone has been through your
code and changed lots of words to German, no wonder you dunno whats going on.

 
Answer #4    Answered By: Fairuzah Alam     Answered On: Jul 19

you anwered:

Dunno on (a) ... I've forgotton all my SQL since messing with controls
(... and good
riddance, boring, boring, boring)

Dim myKlantID As String = CType(myDataReader.GetString(0), String)
Dim myNameDB As String = myDataReader.GetString(1).ToLower.Trim
<<--- why no Ctype ?
Dim myPassDB As String = myDataReader.GetString(2).ToLower.Trim
<<--- why no Ctype ?



the problem isn't in myNameDB and myPassDB, those work  just fine cos they are
already varchars in my database  i guess...
But myKlantID is an integer + primary key + identity (for autoincrementing) is
on
and there it don't work...

will give another code  snippet:


Stored procedure:
*********************


CREATE PROCEDURE dbo.usp_SelectKlant
(
@KNaam char(20),
@Paswoord char(20)
)
AS
SET NOCOUNT ON;
SELECT KlantID, KNaam, Paswoord, SesID FROM KLANT WHERE (KNaam = @KNaam) AND
(Paswoord = @Paswoord)
GO

in region:
***********

Me.SqlSelectCommand1.CommandText = "[usp_SelectKlant]"
Me.SqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand1.Connection = Me.SqlConnection2
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int,
4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@KNaam", System.Data.SqlDbType.VarChar, 20,
"KNaam"))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Paswoord", System.Data.SqlDbType.VarChar,
20, "Paswoord"))

in code:
*********
Dim myName As String = txtName.Text.ToLower.Trim
Dim myPass As String = txtPassword.Text.ToLower.Trim

SqlSelectCommand1.Parameters("@KNaam").Value = myName
SqlSelectCommand1.Parameters("@Paswoord").Value = myPass
SqlConnection2.Open()

Dim myDataReader As System.Data.SqlClient.SqlDataReader
myDataReader = SqlSelectCommand1.ExecuteReader

If (myDataReader.Read) Then

Dim myKlantID As String = CType(myDataReader.GetString(0), String)
<<<<<<<<<<<<<<<<<don't go...
Dim myNameDB As String = myDataReader.GetString(1).ToLower.Trim
Dim myPassDB As String = myDataReader.GetString(2).ToLower.Trim

If ((myName.ToLower = myNameDB) And (myPass.ToLower = myPassDB)) Then

foutmelding.Text = "Gelukt"
Session("KlantID") = myKlantID <<<<<<<<<<<<<<<<<<<<<<<<<<<<< don't
go...
TextBox1.Text = myNameDB
TextBox2.Text = myPassDB
'...
Else
'niet ingelogd
foutmelding.Text = "Mislukt"
TextBox1.Text = myKlantID.ToString & "-" & myNameDB
TextBox2.Text = myPassDB & "-" & myPass
End If
Else
'niet ingelogd
foutmelding.Text = "mislukt"

End If

 
Answer #5    Answered By: Gerardo Morgan     Answered On: Jul 19

just a quicky ... I hav had problems in the past with converting types.

Trouble is I'm a sharper

Sometimes the conversion (int)adouble; will fail , whereby
Convert.ToInt32(adouble); will work  ... what is the equivalent in VB ?

If GetString90) is an autoincrement in db then shouldn't you be using an integer
version ? at a guess myDataReader.GetInt32() or GetValue() or something ?

Once upon a time I used the following
(Convert.ToDecimal(reader.GetValue(1))*(decimal)(reader.GetValue(2))).ToString("\
N")

 
Answer #6    Answered By: Kawakib Mansour     Answered On: Jul 19

yes indeed,
had already found it this time
had to use mydatareader.getInt32 in stead of first get string  and than cast it
to a integer

and equivalent in vb for

c# =>(integer) myvar
vb => cint(myvar) or ctype (myvar, integer)

anyway, txs for your help

 
Didn't find what you were looking for? Find more on How to use the DataReader. Or get search suggestion and latest updates.




Tagged: