Problem with DataReader

[sweatha]

Hi friends
This is sweatha, fresher MCA 2007 batch. Right now I am working in asp.net platform.
My dillema is that I have created a registration form with the fields as username & password. And if once the user register the username and password, it will be stored in the database(SQL Server 2000) by showing "Records Inserted".

Then I have created the login form just like the fields as username and password.And if the user enters the values it should check in the database & it should display whether the data is valid or invalid. Here, I have written the coding but the problem is with the datareader. If I run the page it is showing the error as "c:\inetpub\wwwroot\extra1\Login.aspx.vb(45): 'System.Data.SqlClient.SqlDataReader.Private Sub New(command As System.Data.SqlClient.SqlCommand)' is not accessible in this context because it is 'Private'." And also if I run the page instead of displaying valid or invalid user, It is displaying as "records inserted" which I have given in the registration page.

The Coding I have given in Registration form is

Imports System.Data
Imports System.Data.SqlClient
Public Class Registration1
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()

End Sub
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents TextBox2 As System.Web.UI.WebControls.TextBox
Protected WithEvents Button1 As System.Web.UI.WebControls.Button

'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
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim str, str1 As String
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
str = "user id=sa;password=;data source=AURO-RA4;initial catalog=suganya;server=AURO-RA4"

con = New SqlConnection(str)
Try
con.Open()
Catch
End Try
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
str = "user id=sa;password=;data source=AURO-RA4;initial catalog=suganya;server=AURO-RA4"
con = New SqlConnection(str)
Try
con.Open()
Catch
End Try
str1 = "Insert into login values('" & TextBox1.Text & "','" & TextBox2.Text & "')"
cmd = New SqlCommand(str1, con)
cmd.ExecuteNonQuery()
con.Close()
Response.Write("Inserted")
End Sub
End Class

The coding I have given in loginform is


Imports System.Data
Imports System.Data.SqlClient

Public Class Login
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
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation id=""AURO-RA4"";packet size=4096;user id=sa;data source=""AURO-RA4"";pers" & _
"ist security info=False;initial catalog=suganya"

End Sub
Protected WithEvents Panel1 As System.Web.UI.WebControls.Panel
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents Label3 As System.Web.UI.WebControls.Label
Protected WithEvents Label4 As System.Web.UI.WebControls.Label
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents LinkButton1 As System.Web.UI.WebControls.LinkButton
Protected WithEvents LinkButton2 As System.Web.UI.WebControls.LinkButton
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
Protected WithEvents TextBox2 As System.Web.UI.WebControls.TextBox

'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
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim rd As New SqlDataReader
Dim str As String

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
str = "user id=sa;password=;data source=AURO-RA4;initial catalog=suganya;server=AURO-RA4"
con = New SqlConnection(str)
Try
con.Open()
Catch
End Try
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim cmd As New SqlCommand("select count(*) from login where uname=' " & TextBox1.Text & " ' and pasword=' " & TextBox2.Text & " ' ", con)

rd = cmd.ExecuteReader()

If rd(0) = 0 Then
Response.Write("Invalid User")

Exit Sub

Else
Response.Write("Valid User")

End If

End Sub

Private Sub LinkButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton2.Click
Response.Redirect("Registration1.aspx")


End Sub
End Class

[kunal pawar]

I guess Problem is in ur declaration statement.
record set object should not declare with new keyword
Dim rd As New SqlDataReader

it should be
Dim rd As SqlDataReader

[saran23]

Hi friends
This is sweatha, fresher MCA 2007 batch. Right now I am working in asp.net platform.
My dillema is that I have created a registration form with the fields as username & password. And if once the user register the username and password, it will be stored in the database(SQL Server 2000) by showing "Records Inserted".

Then I have created the login form just like the fields as username and password.And if the user enters the values it should check in the database & it should display whether the data is valid or invalid. Here, I have written the coding but the problem is with the datareader. If I run the page it is showing the error as "c:\inetpub\wwwroot\extra1\Login.aspx.vb(45): 'System.Data.SqlClient.SqlDataReader.Private Sub New(command As System.Data.SqlClient.SqlCommand)' is not accessible in this context because it is 'Private'." And also if I run the page instead of displaying valid or invalid user, It is displaying as "records inserted" which I have given in the registration page.

The Coding I have given in Registration form is

Imports System.Data
Imports System.Data.SqlClient
Public Class Registration1
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()

End Sub
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents TextBox2 As System.Web.UI.WebControls.TextBox
Protected WithEvents Button1 As System.Web.UI.WebControls.Button

'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
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim str, str1 As String
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
str = "user id=sa;password=;data source=AURO-RA4;initial catalog=suganya;server=AURO-RA4"

con = New SqlConnection(str)
Try
con.Open()
Catch
End Try
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
str = "user id=sa;password=;data source=AURO-RA4;initial catalog=suganya;server=AURO-RA4"
con = New SqlConnection(str)
Try
con.Open()
Catch
End Try
str1 = "Insert into login values('" & TextBox1.Text & "','" & TextBox2.Text & "')"
cmd = New SqlCommand(str1, con)
cmd.ExecuteNonQuery()
con.Close()
Response.Write("Inserted")
End Sub
End Class

The coding I have given in loginform is


Imports System.Data
Imports System.Data.SqlClient

Public Class Login
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
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation id=""AURO-RA4"";packet size=4096;user id=sa;data source=""AURO-RA4"";pers" & _
"ist security info=False;initial catalog=suganya"

End Sub
Protected WithEvents Panel1 As System.Web.UI.WebControls.Panel
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents Label3 As System.Web.UI.WebControls.Label
Protected WithEvents Label4 As System.Web.UI.WebControls.Label
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents LinkButton1 As System.Web.UI.WebControls.LinkButton
Protected WithEvents LinkButton2 As System.Web.UI.WebControls.LinkButton
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
Protected WithEvents TextBox2 As System.Web.UI.WebControls.TextBox

'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
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim rd As New SqlDataReader
Dim str As String

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
str = "user id=sa;password=;data source=AURO-RA4;initial catalog=suganya;server=AURO-RA4"
con = New SqlConnection(str)
Try
con.Open()
Catch
End Try
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim cmd As New SqlCommand("select count(*) from login where uname=' " & TextBox1.Text & " ' and pasword=' " & TextBox2.Text & " ' ", con)

rd = cmd.ExecuteReader()

If rd(0) = 0 Then
Response.Write("Invalid User")

Exit Sub

Else
Response.Write("Valid User")

End If

End Sub

Private Sub LinkButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton2.Click
Response.Redirect("Registration1.aspx")


End Sub
End Class

Hi Swetha,
Going thru ur code, I found some mistakes,
I could not say the exact reason for the exception,but theres a lot to change in ur code,
1. Connection.Open() must be used carefully, it must be used just before executing the command, it must be

conn.open()
cmd.executequery()
conn.close()

The reason for this is in a real time the database server resides somewhere else, so the connection with the DB server must be minimum, opening the connection more than once and for a long time must be strictly avoided, as it creates network traffic and slow performance.
The connection string must be declared globally for a page and used it wherever needed,
there must be connection to the DB only when there is a need, In this u hv connected in PageLoad which does nothing,

2.Use the New keyword only when ur going to use the class,
for eg,

Dim cmd As New SqlCommand

must be like this,

Dim cmd As SqlCommand

-- this must be declared only once globally
and when ur using it anywhere

cmd=New Sqlcommand(ur command,conn)

3.When u want to just redirect to new page, without any code to be executed, just use the postbackurl property of the button, seperate event must not be used.

Im not much aware of VB, Im a C# guy, so im not sure whether there is Protected access modifier in Vb, If u hav better use it for Button Click events.
We do so. The datareader problem might be even due to access levels.

Though my info might not help to fix ur exact problem, it helps to know things in a professinal manner,as ur in Learning Phase.There is lot more to explore professionally, just search for some code in this forum and use it as a reference.

Thanks
Saravanan