Ad:

VB.NET Discussion Thread
Unsolved
Views: 932

Mar 25th, 2009

Cannot retrieve data from data base

Expand Post »

Hello friends,
I need an urgent help.I'm doing a project in asp.net which uses vb.net as back end language.I'm using sql 2005.My problem is that I can't retrieve the data from database,but I can insert the data into the table.

vb.net Syntax (Toggle Plain Text)
Partial Class MasterPage
    Inherits System.Web.UI.MasterPage
 
    Public login As New Class1
 
 
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim u As String
 
        login.cmd.Connection = login.con
        login.cmd.CommandText = "select * from REG where user = '" + RTrim(TextBox1.Text) + "'"
        login.cmd.ExecuteNonQuery()
 
        login.rdr = login.cmd.ExecuteReader()
 
        If login.rdr.HasRows = True Then
            login.rdr.Read()
            u = login.rdr.Item("pwd")
            If RTrim(TextBox2.Text) = Trim(u) Then
                Response.Redirect("student1.aspx")
            End If
        Else
            MsgBox("Enter correct password", MsgBoxStyle.Critical)
 
        End If
 
        'Try
 
        'Catch ex As Exception
        '    MsgBox(ex.Message)
 
        'End Try
    End Sub
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        login.connection()
 
    End Sub
End Class
Partial Class MasterPage
    Inherits System.Web.UI.MasterPage

    Public login As New Class1


    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim u As String

        login.cmd.Connection = login.con
        login.cmd.CommandText = "select * from REG where user = '" + RTrim(TextBox1.Text) + "'"
        login.cmd.ExecuteNonQuery()

        login.rdr = login.cmd.ExecuteReader()

        If login.rdr.HasRows = True Then
            login.rdr.Read()
            u = login.rdr.Item("pwd")
            If RTrim(TextBox2.Text) = Trim(u) Then
                Response.Redirect("student1.aspx")
            End If
        Else
            MsgBox("Enter correct password", MsgBoxStyle.Critical)

        End If

        'Try

        'Catch ex As Exception
        '    MsgBox(ex.Message)

        'End Try
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        login.connection()

    End Sub
End Class

I'm using a class to open the database

vb.net Syntax (Toggle Plain Text)
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
 
 
Public Class Class1
    Public con As SqlConnection
    Public cmd As New SqlCommand
    Public rdr As SqlDataReader
    Public Function connection()
        con = New SqlConnection("Data Source=ANGEL\SQLEXPRESS;Initial Catalog=swas;Integrated Security=True")
        con.Open()
        Return 0
 
    End Function
End Class
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient


Public Class Class1
    Public con As SqlConnection
    Public cmd As New SqlCommand
    Public rdr As SqlDataReader
    Public Function connection()
        con = New SqlConnection("Data Source=ANGEL\SQLEXPRESS;Initial Catalog=swas;Integrated Security=True")
        con.Open()
        Return 0

    End Function
End Class

Please help me it's really urgent.

Thanks in advance.

Similar Threads

error: The value for the useBean class attribute is invalid in JSP
Send data on a serial port in C++
How to retrieve binary data from string in Java
How can i retrieve records from data base in data grid through unbound coding in Visual Basic 4 / 5 / 6
How to retrieve data from one form to another/ through Data Base in VB.NET

krm08

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

11 posts
since Dec 2008

Permalink

Mar 25th, 2009

Re: Cannot retrieve data from data base

why not use ExecuteScalar instead of executenonquery? ExecuteNonQuery does not return anything. When you make your select statement like "SELECT count(*) FROM reg WHERE user= '" + RTrim(TextBox1.Text) + "' AND pwd = '" & RTrim(TextBox2.Text) & "'
you know immediately whether user + pw combination exists (if count > 0).

(I am a beginner and this is my first reply, so please excuse me when not helpful)

nescio99

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Mar 2009

Permalink

Mar 25th, 2009

Re: Cannot retrieve data from data base

I have tried executescalar also,but it's not working.I checked it with breakpoints,the problem is that the "login.rdr.HasRows " always return false.I don't how it's happen,but I can insert into same table,but I can't retrieve the inserted data

krm08

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

11 posts
since Dec 2008

Permalink

Mar 25th, 2009

Re: Cannot retrieve data from data base

VB.NET Syntax (Toggle Plain Text)
cmd.commandtext =  "SELECT count(*) FROM reg WHERE user= '" + RTrim(TextBox1.Text) + "' AND pwd = '" & RTrim(TextBox2.Text) & "'
 
cmd.connection.open
counter = cmd.ExecuteScalar
cmd.connection.close
 
if counter <= 0 then  ... (user + pw combination unknown)
 
cmd.commandtext =  "SELECT count(*) FROM reg WHERE user= '" + RTrim(TextBox1.Text) + "' AND pwd = '" & RTrim(TextBox2.Text) & "'

cmd.connection.open
counter = cmd.ExecuteScalar
cmd.connection.close

if counter <= 0 then  ... (user + pw combination unknown)

Or you could check if executeScalar lead to a DBNull.value

Good luck

nescio99

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Mar 2009

Permalink

Mar 25th, 2009

Re: Cannot retrieve data from data base

nescio99 has it right that you can't use ExecuteNonQuery() because you want to query something from the database. Either use ExecuteScalar() to check if a record exists i.e. returned number of records equals one, like nescio99 suggested, or if you need some information from the record, use simple Execute.

If neither works, check that the command's connection object is actually valid and opened at the point you're executing the command.

Teme64

Reputation Points: 218

Solved Threads: 201

Veteran Poster

Offline

1,024 posts
since Aug 2008

Permalink

Mar 25th, 2009

Re: Cannot retrieve data from data base

Perhaps you can create another function to check whether it exists or not?

 VB.net Syntax (Toggle Plain Text)
 
Dim conn As SqlConnection
Dim strConn As String = "Data Source=ANX134\SQLEXPRESS;Initial Catalog=Dyslexia_Begin;Integrated Security=True"
 
conn = New SqlConnection(strConn)
conn.Open()
 
SQLcmd = "select * FROM UserDetails where username = '" & txt_username.Text.Trim & "' and password = '" & txt_password.Text & "'"
 
    Public Function CheckExist(ByVal sSQL As String) As Boolean
        sqlcmd = New SqlCommand(sSQL, conn)
        DReader = sqlcmd.ExecuteReader()
        If DReader.Read() Then
            CheckExist = True
        Else
            CheckExist = False
        End If
        DReader.Close()
    End Function
 
'call this function in your code then...
Dim conn As SqlConnection
Dim strConn As String = "Data Source=ANX134\SQLEXPRESS;Initial Catalog=Dyslexia_Begin;Integrated Security=True"

conn = New SqlConnection(strConn)
conn.Open()

SQLcmd = "select * FROM UserDetails where username = '" & txt_username.Text.Trim & "' and password = '" & txt_password.Text & "'"

    Public Function CheckExist(ByVal sSQL As String) As Boolean
        sqlcmd = New SqlCommand(sSQL, conn)
        DReader = sqlcmd.ExecuteReader()
        If DReader.Read() Then
            CheckExist = True
        Else
            CheckExist = False
        End If
        DReader.Close()
    End Function

'call this function in your code then...

Hopes this help...

raul15791

Reputation Points: 37

Solved Threads: 7

Junior Poster

Offline

102 posts
since Jun 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: How to kill the process in vb.net

Next Thread in VB.NET Forum Timeline: Pls Help, i have a problem loading my gridview with data

DaniWeb Message

Cancel Changes

User Name
Password