ACCESSING Data from SQL Server...

[wrack]

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

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

[hellswraith]

You could do a count before you try to retrieve the records:
"SELECT Count(*) FROM tblUsers Where UserName = '" & TextBox1.Text & "'"

You would use it something like this:

Dim i as Integer
i = sqlcmdValidate.ExecuteScalar

[Edneeis]

Try looping through to get the results if any. Also if you are only trying to get the first row of results then you may want to use the SingleRow option for optimization.

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

[hellswraith]

I didn't even think about that, you could put the results in a DataRow object, then just check if it is null.

If the UserName is a unique column, you will only get one result back anyway.

[wrack]

Thanks for ur help guyz...I did it this way...

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

Cheers...