Problem with DataReader

**kunal pawar** · Mar 10 '08, 12:56 PM

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** · Mar 11 '08, 07:03 AM

Originally posted by 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\www root\extra1\Log in.aspx.vb(45): 'System.Data.Sq lClient.SqlData Reader.Private Sub New(command As System.Data.Sql Client.SqlComma nd)' 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.Sql Client
Public Class Registration1
Inherits System.Web.UI.P age

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnos tics.DebuggerSt epThrough()> Private Sub InitializeCompo nent()

End Sub
Protected WithEvents Label1 As System.Web.UI.W ebControls.Labe l
Protected WithEvents TextBox1 As System.Web.UI.W ebControls.Text Box
Protected WithEvents Label2 As System.Web.UI.W ebControls.Labe l
Protected WithEvents TextBox2 As System.Web.UI.W ebControls.Text Box
Protected WithEvents Button1 As System.Web.UI.W ebControls.Butt on

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

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeCompo nent()
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.EventArg s) 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(s tr)
Try
con.Open()
Catch
End Try
End Sub

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

The coding I have given in loginform is

Imports System.Data
Imports System.Data.Sql Client

Public Class Login
Inherits System.Web.UI.P age

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnos tics.DebuggerSt epThrough()> Private Sub InitializeCompo nent()
Me.SqlConnectio n1 = New System.Data.Sql Client.SqlConne ction
'
'SqlConnection1
'
Me.SqlConnectio n1.ConnectionSt ring = "workstatio n id=""AURO-RA4"";packet size=4096;user id=sa;data source=""AURO-RA4"";pers" & _
"ist security info=False;init ial catalog=suganya "

End Sub
Protected WithEvents Panel1 As System.Web.UI.W ebControls.Pane l
Protected WithEvents Label1 As System.Web.UI.W ebControls.Labe l
Protected WithEvents Label2 As System.Web.UI.W ebControls.Labe l
Protected WithEvents Label3 As System.Web.UI.W ebControls.Labe l
Protected WithEvents Label4 As System.Web.UI.W ebControls.Labe l
Protected WithEvents Button1 As System.Web.UI.W ebControls.Butt on
Protected WithEvents LinkButton1 As System.Web.UI.W ebControls.Link Button
Protected WithEvents LinkButton2 As System.Web.UI.W ebControls.Link Button
Protected WithEvents SqlConnection1 As System.Data.Sql Client.SqlConne ction
Protected WithEvents TextBox1 As System.Web.UI.W ebControls.Text Box
Protected WithEvents TextBox2 As System.Web.UI.W ebControls.Text Box

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

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeCompo nent()
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.EventArg s) 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(s tr)
Try
con.Open()
Catch
End Try
End Sub

Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As System.EventArg s) Handles Button1.Click

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

rd = cmd.ExecuteRead er()

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

Exit Sub

Else
Response.Write( "Valid User")

End If

End Sub

Private Sub LinkButton2_Cli ck(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles LinkButton2.Cli ck
Response.Redire ct("Registratio n1.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

Code:

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,

Code:

Dim cmd As New SqlCommand

must be like this,

Code:

Dim cmd As SqlCommand

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

Code:

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

Problem with DataReader

Problem with DataReader

Comment

Comment