Cannot retrieve data from data base

[krm08]

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.

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

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.

[nescio99]

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)

[krm08]

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

[nescio99]

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

[Teme64]

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.

[raul15791]

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

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...