ACCESSING Data from SQL Server...

Hi I am trying to access Data from SQL Server database and so far I am successful only if the record exists...The code I using is as follows...

VB Code:

Imports System.Data.SqlClient
 
Public Class Main
    Inherits System.Web.UI.Page
    Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
    Protected WithEvents TextBox2 As System.Web.UI.WebControls.TextBox
    Protected WithEvents TextBox3 As System.Web.UI.WebControls.TextBox
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button
 
#Region " Web Form Designer Generated Code "
 
    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
 
    End Sub
 
    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
 
    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
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strUserName As String
        Dim strPassword As String
 
        Dim sqlcnnValidate As SqlConnection
        Dim sqlcmdValidate As SqlCommand
        Dim sqldtrValidate As SqlDataReader
        Dim strSQL As String
 
        sqlcnnValidate = New SqlConnection("server=WRACK\SQLDB;uid=sa;pwd=;database=NW;")
 
        strSQL = "SELECT [UserName], [Password], [Role] FROM tblUsers Where UserName = '" & TextBox1.Text & "' ORDER BY [UserName]"
 
        sqlcmdValidate = New SqlCommand(strSQL, sqlcnnValidate)
 
        sqlcnnValidate.Open()
 
        sqldtrValidate = sqlcmdValidate.ExecuteReader
 
        sqldtrValidate.Read()
 
        TextBox1.Text = sqldtrValidate("UserName")
        TextBox2.Text = sqldtrValidate("Password")
        TextBox3.Text = sqldtrValidate("Role")
 
        sqldtrValidate.Close()
        sqlcnnValidate.Close()
    End Sub
End Class

If the username I put in Textbox1 doesn't exists in database then I am getting this error (please see the picture attached). Is there anyway to make sure that the record exists...like RecordSet.RecordCount (in VB 6)...

I am using ASP.NET with VB.NET (ASP.NET Web Application from VB.NET)...

Also if someone can show me how to add data to database from textboxes then I would really appreciate it...

Cheers...

You shoud use 'Read' properly, before accessing data:

VB Code:

While  sqldtrValidate.Read()
 
        TextBox1.Text = sqldtrValidate("UserName")
        TextBox2.Text = sqldtrValidate("Password")
        TextBox3.Text = sqldtrValidate("Role")
 
End While

Edits: Or you may use:

VB Code:

If sqldtrValidate.Read()
 
        TextBox1.Text = sqldtrValidate("UserName")
        TextBox2.Text = sqldtrValidate("Password")
        TextBox3.Text = sqldtrValidate("Role")
 
End If

Quote:

---

Originally posted by Lunatic3
You shoud use 'Read' properly, before accessing data:

VB Code:

While  sqldtrValidate.Read()
 
        TextBox1.Text = sqldtrValidate("UserName")
        TextBox2.Text = sqldtrValidate("Password")
        TextBox3.Text = sqldtrValidate("Role")
 
End While

Edits: Or you may use:

VB Code:

If sqldtrValidate.Read()
 
        TextBox1.Text = sqldtrValidate("UserName")
        TextBox2.Text = sqldtrValidate("Password")
        TextBox3.Text = sqldtrValidate("Role")
 
End If

---

Thanks M8...works nice...so basically .Read() works something like RecordCount being 1 or more...??? Correct me if I am wrong...

Cheers...

Not exactly what you said:
sqldatareader.Read does two things:
-Advances to the next record
-Returns true if there are more rows; otherwise, false.

Quote:

---

Originally posted by Lunatic3
Not exactly what you said:
sqldatareader.Read does two things:
-Advances to the next record
-Returns true if there are more rows; otherwise, false.

---

what if I want to go back to previous record...???

Cheers...

I am afraid you can not do that with datareader as datareader is Forward Only

Quote:

---

Originally posted by Lunatic3
I am afraid you can not do that with datareader as datareader is Forward Only

---

Can u please give me an example of another object through which I can do all the stuff I used to do with VB 6 and ADODB.Recordset...

If can't then atleast something which allows me to go back to previous record...last record...first record...

Cheers...

Use a DataSet.

Quote:

---

Originally posted by DevGrp
Use a DataSet.

---

To be honest I am completly new to .NET and I will highly appreciate if you post me a small example on how to use the dataset with connection object...!!!

Cheers...

Can you search?
http://www.google.com/search?hl=en&l...aset+in+vb.net

Quote:

---

Originally posted by hellswraith
Can you search?
http://www.google.com/search?hl=en&l...aset+in+vb.net

---

sure M8...I will try...just looking for shortcut as I have not much time and exams are coming closer...

Cheers...

Quote:

---

Originally posted by wrack
Thanks M8...works nice...so basically .Read() works something like RecordCount being 1 or more...??? Correct me if I am wrong...

Cheers...

---

Also if you are using 1.1 version of the framework, the DataReader has a HasRows property, so you can do this

VB Code:

If (sqldtrValidate.HasRows = True) Then
   While sqldtrValidate.Read()
        TextBox1.Text = sqldtrValidate("UserName")
        TextBox2.Text = sqldtrValidate("Password")
        TextBox3.Text = sqldtrValidate("Role")
   End While
End If

Quote:

---

Originally posted by DevGrp
Also if you are using 1.1 version of the framework, the DataReader has a HasRows property, so you can do this

VB Code:

If (sqldtrValidate.HasRows = True) Then
   While sqldtrValidate.Read()
        TextBox1.Text = sqldtrValidate("UserName")
        TextBox2.Text = sqldtrValidate("Password")
        TextBox3.Text = sqldtrValidate("Role")
   End While
End If

---

I have version 1 but I will install 1.1 and try that one...Just wondering that which method is much better...

Cheers...

Just to say you cannot use Framework 1.1 within Visual Studio 2002. If you Want to use Framework 1.1, you have to run visual studio 2003.

Quote:

---

Just to say you cannot use Framework 1.1 within Visual Studio 2002. If you Want to use Framework 1.1, you have to run visual studio 2003.

---

This is not true, you can use 1.1 with 2002.

Quote:

---

Originally posted by matt3011
Just to say you cannot use Framework 1.1 within Visual Studio 2002. If you Want to use Framework 1.1, you have to run visual studio 2003.

---

I think you are wrong here bcos my friends are running 2002 with framework 1.1 installed...

Cheers...

Quote:

---

I think you are wrong here bcos my friends are running 2002 with framework 1.1 installed...

---

He is not wrong because of what you are saying. Normally VS.NET 2002 compiles against Framework 1.0 even if u have framework 1.1 installed. However you can force your code to to compile against 1.1. Still your ASP.NET application will run under 1.1 even if you dont force it.

Thanks Lunatic3, that is exactly what i wanted to say. Maybe I wasn't clear enough

Thanks M8...Now I understand the difference and I think I should move to .NET 2003...

Cheers...