• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3291
  • Last Modified:

VB6 Recordset vs. VB.NET Dataset?

I'm rewriting an old inventory system for VB.NET that was originally written in VB6.  I'm confused about how to work the database connectivity.  For instance:

Login screen:

(outside)
Dim Adologin as Recordset


(formload)
set db = new connection
db.cursorlocation = adUseClient
db.open "blahblahblah"
set adologin = new recordset
adologin.open "select statement", db, adopenstatic, adlockoptimistic

for x = 1 to adologin.recordcount
     txtuserid.additem Adologin.Fields("User")
     Adologin.movenext
next x



(clicked ok)
Adologin.Movefirst
usercode = Adologin.Fields("User").value
do until found or adologin.eof                               ' found is a boolean set initially to false
     usercode = adologin.fields("user").value
     if usercode = txtUserid.text then
          found = true
          fieldvar = val(adologin!field)
          fieldvar2 = adologin!field2

          exit do
     else
          adologin.movenext
     endif
loop


(That last code is basically to verify password)
(fieldvar and fieldvar2 are global vars taken from the table to be used throughout all forms - I declared in a module)

Any ideas how to translate this would be great - dataset doesn't seem to have .movefirst and etc

JP

0
gleznov
Asked:
gleznov
  • 9
  • 7
  • 2
  • +1
1 Solution
 
123654789987Commented:
U have to use datareader class.
0
 
123654789987Commented:
U can refer the following link

http://vbcity.com/forums/topic.asp?tid=23185
0
 
RonaldBiemansCommented:
If it is just for filling a listbox or to verify the password then no dataset in needed use a datareader instead

something like (you still have to add some error checking to it ofcourse)

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim da As OleDb.OleDbDataReader
        Dim oc As New OleDb.OleDbCommand("select * from users ", OleDbConnection1)
        OleDbConnection1.Open()
        da = oc.ExecuteReader(CommandBehavior.CloseConnection)
        while da.read
            listbox1.Items.Add(da.Item("user"))
        end while
       da.close
end sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim fieldvar As String
        Dim fieldvar2 As String
        Dim da As OleDb.OleDbDataReader
        Dim oc As New OleDb.OleDbCommand("select * from users where user = '" & TextBox1.Text & "'", OleDbConnection1)
        OleDbConnection1.Open()
        da = oc.ExecuteReader(CommandBehavior.CloseConnection)
        If da.HasRows Then
            da.Read()
            fieldvar = da.Item("field")
            fieldvar2 = da.Item("field2")
            da.Close()
        Else
            MsgBox("not found")

        End If
    End Sub
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gillgatesCommented:
You probably want to use SqlDataReader

Dim myCommand As SqlCommand
Dim Reader As SqlDataReader

myCommand = con.CreateCommand
myCommand.CommandText = myQuery
Reader = myCommand.ExecuteReader

while Reader.Read
  MessageBox.Show(Reader.GetStrin(i))
  i = i + 1
Loop


SqlCommand myCommand = con.CreateCommand();
                        myCommand.CommandText = this.strCommand;
                        con.Open();
                        SqlDataReader Reader = myCommand.ExecuteReader();
                        Reader.
                        return Reader;
0
 
gillgatesCommented:
forget about the last part of my post...

---------
SqlCommand myCommand = con.CreateCommand();
                    myCommand.CommandText = this.strCommand;
                    con.Open();
                    SqlDataReader Reader = myCommand.ExecuteReader();
                    Reader.
                    return Reader;
----------
0
 
gleznovAuthor Commented:
These are good for the login form, but I'm going to need to be able to update tables from other forms - so I'm looking for more of an "input with potential to write SQL statements to a table or a database as well"

JP
0
 
RonaldBiemansCommented:
Ok, something like this then ?

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim da As New OleDb.OleDbDataAdapter("select * from users", OleDbConnection1)
        da.Fill(ds)
        For x As Integer = 0 To ds.Tables.Count - 1
            ListBox1.Items.Add(ds.Tables(0).Rows(x).Item("user"))
        Next

        ds.Tables(0).Rows(0).Item("yourfield") = "blabla"
        Dim cm As New OleDb.OleDbCommandBuilder(da)
        da.Update(ds)
    End Sub
0
 
RonaldBiemansCommented:
I'm using a commandbuilder in my example, performance wise this isn't the best choice. It is better that you write the update, delete, and insert commands yourself, or use the IDE to drag and drop your dataadapters  (if you use a sql server, use stored procedures)
0
 
gleznovAuthor Commented:
Where in the code above would I add an Update/Delete/Insert statement?  Is it the "blabla"?

JP
0
 
RonaldBiemansCommented:
No, ds.Tables(0).Rows(0).Item("yourfield") = "blabla"

means, give in the first row of the table the column with name 'yourfield' the value blabla

to write your one code use something like this (obviously the code below was written for my table you have to adjust it to fit your table)

        Me.OleDbDataAdapter1.DeleteCommand = Me.OleDbDeleteCommand1
        Me.OleDbDataAdapter1.InsertCommand = Me.OleDbInsertCommand1
        Me.OleDbDataAdapter1.UpdateCommand = Me.OleDbUpdateCommand1

        Me.OleDbInsertCommand1.CommandText = "INSERT INTO users(field, field2, [user]) VALUES (?, ?, ?)"
        Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("field", System.Data.OleDb.OleDbType.VarWChar, 50, "field"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("field2", System.Data.OleDb.OleDbType.VarWChar, 50, "field2"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("user", System.Data.OleDb.OleDbType.VarWChar, 50, "user"))

        Me.OleDbUpdateCommand1.CommandText = "UPDATE users SET field = ?, field2 = ?, [user] = ? WHERE (Id = ?)"
        Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("field", System.Data.OleDb.OleDbType.VarWChar, 50, "field"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("field2", System.Data.OleDb.OleDbType.VarWChar, 50, "field2"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("user", System.Data.OleDb.OleDbType.VarWChar, 50, "user"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Id", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Id", System.Data.DataRowVersion.Original, Nothing))

        Me.OleDbDeleteCommand1.CommandText = "DELETE FROM users WHERE (Id = ?)"
        Me.OleDbDeleteCommand1.Connection = Me.OleDbConnection1
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Id", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Id", System.Data.DataRowVersion.Original, Nothing))



You can use the IDE to generate this kind of code for you, just drag and drop an sqldataadapter or oledbdataadapter to your form, and look in the windows generated code in your code window
0
 
gleznovAuthor Commented:
Do you have any code by chance to do that for Sql adapter?  I can't seem to connect an ole adapter to my database so I imported it into SQL Server.

JP
0
 
gleznovAuthor Commented:
OK I think I have it, but I'm getting the same thing as with ole (I was wrong - ole was working, I switched to SQL to try and get around this problem)

       Dim da As New SqlClient.SqlDataAdapter("select * from users", SqlConnection1)
        da.Fill(DataSet11)
        For x As Integer = 0 To DataSet11.Tables.Count - 1
            txtUserID.Items.Add(DataSet11.Tables("users").Rows(x).Item("user"))
        Next

It's still telling me there's no row at position 0

The database has about 6 rows in that table.  I've tried position 1, same result.

JP
0
 
RonaldBiemansCommented:
Is very simple,

Dim da As New OleDb.OleDbDataAdapter("select * from users", OleDbConnection1)

dim da as new sqlclient.sqldataadapter(etc..)

so itstead of calling

System.Data.OleDb.

call like

System.Data.Sqlclient

and instead of oledbdataadapter,oledbdatareader, oledbconnection

use sqldataadapter,sqldatareader,sqlconnection



0
 
RonaldBiemansCommented:
Could put a messagebox like this and tell me the result

Dim da As New SqlClient.SqlDataAdapter("select * from users", SqlConnection1)
        da.Fill(DataSet11)
msgbox (dataset11.tables.count)
        For x As Integer = 0 To DataSet11.Tables.Count - 1
            txtUserID.Items.Add(DataSet11.Tables("users").Rows(x).Item("user"))
        Next
0
 
RonaldBiemansCommented:
Sorry I see it now

it is not

For x As Integer = 0 To DataSet11.Tables.Count - 1

but

For x As Integer = 0 To DataSet11.Tables.rows.Count - 1
0
 
RonaldBiemansCommented:
Sorry wrong again

For x As Integer = 0 To DataSet11.Tables(0).rows.Count - 1
0
 
RonaldBiemansCommented:
or (in case "users" isn't the first table)
For x As Integer = 0 ToDataSet11.Tables("users").Rows.Count -1
0
 
gleznovAuthor Commented:
I'm confused:

I used the msgbox - it says there's 2 tables..  Which is odd, because I only included one when I made the dataadapter/tableset

Secondly, if I msgbox for the rows, then it says there's 0 - but there's definately several in the table.  Could the fill statement not be working or something?

JP
0
 
gleznovAuthor Commented:
But THIS works:

        da.Fill(DataSet11, "users")

:)

0
 
gleznovAuthor Commented:
Thanks for all your help!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 9
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now