How to use recordset or equivalent after executing sql command in vb.net

ljhodgett
ljhodgett used Ask the Experts™
on
Hi Experts,

I have the following code: -

    Protected Sub cmdLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
        Dim cn As New System.Data.SqlClient.SqlConnection("Data Source=SSD365;Initial Catalog=test;User Id=sa;Password=123456;")
        Dim cmd As New System.Data.SqlClient.SqlCommand("select * from users", cn)
        On Error GoTo Error_Handler

        lblServerError.Visible = False
        cn.Open()
        cmd.ExecuteNonQuery()
        cn.Close()


        Session("Username") = txtUsername.Text
        Session("Password") = txtPassword.Text

        Response.Write("<script language=javascript> alert('This is a message\n" & txtUsername.Text & "\n" & txtPassword.Text & "');</script>")

        If lblInvalidPassword.Visible = True Then
            lblInvalidPassword.Visible = False
        Else
            lblInvalidPassword.Visible = True
        End If
Error_Handler:
        If Not Err.Number = 0 Then
            lblServerError.Visible = True
        End If

    End Sub

I’m afraid I am new to vb.net as I am more familiar with vb6. In vb6 I can excecute a sql command and place it into a record set but can not find this function within vb.net. Is there an equivalent and how do you use it please. I would want to populate the recordset or equivalent just before I close the connection using cn.close().

Best Regards
Lee
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You have many choices in .net depending on your needs.  in .net 2.0, you may want to research datasets and TableAdapters. The datatable is the replacement of resordsets which can be defined or generic.  A tableAdapter is associated with a datatable that contains the sql commands (text or Stored Procedures) with a connection object for select,update, insert, delete, and other query commands.  This should point you in the right direction.

Author

Commented:
Hi,

Than you for your comment. How would I use the above code with a datatable then please.

Many Thanks
Lee
After creating a dataset, ds, with a datatable Login with a select command.

Protected Sub cmdLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
  Try
    Dim daLogin as New ds.dtLoginTableAdapter()
    Dim dtLogin as ds.LoginDataTable = daLogin.GetData()

        lblServerError.Visible = False

   'Access Data by...
     if dtLogin.Count = 0 then
       Throw New Exception("No Records in Table")
     end if
     'if dtLogin(0).IsNull(0) then
       ' Checks if the first column of the first row is null
           ' or you can use the column name dtLogin(0).IsNull("LastLogin")
     'end if
     for each dr as ds.LoginDataRow in dtLogin
        Response.Write(string.format("User: {0}<br />",dr.userid))
     next

        Session("Username") = txtUsername.Text
        Session("Password") = txtPassword.Text

        Response.Write("<script language=javascript> alert('This is a message\n" & txtUsername.Text & "\n" & txtPassword.Text & "');</script>")

        If lblInvalidPassword.Visible = True Then
            lblInvalidPassword.Visible = False
        Else
            lblInvalidPassword.Visible = True
        End If
  Catch Ex as Exception
       lblServerError.Visible = True
    End Try

    End Sub

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial