Solved

VB6 Recordset vs. VB.NET Dataset?

Posted on 2004-09-20
20
3,243 Views
Last Modified: 2011-10-03
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
Comment
Question by:gleznov
  • 9
  • 7
  • 2
  • +1
20 Comments
 
LVL 10

Expert Comment

by:123654789987
ID: 12101861
U have to use datareader class.
0
 
LVL 10

Expert Comment

by:123654789987
ID: 12101883
U can refer the following link

http://vbcity.com/forums/topic.asp?tid=23185
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12101894
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 3

Expert Comment

by:gillgates
ID: 12101897
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
 
LVL 3

Expert Comment

by:gillgates
ID: 12101906
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
 

Author Comment

by:gleznov
ID: 12102052
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12102154
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12102271
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
 

Author Comment

by:gleznov
ID: 12102462
Where in the code above would I add an Update/Delete/Insert statement?  Is it the "blabla"?

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12102553
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
 

Author Comment

by:gleznov
ID: 12103206
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
 

Author Comment

by:gleznov
ID: 12103265
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12103279
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12103330
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12103396
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
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 500 total points
ID: 12103613
Sorry wrong again

For x As Integer = 0 To DataSet11.Tables(0).rows.Count - 1
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12103629
or (in case "users" isn't the first table)
For x As Integer = 0 ToDataSet11.Tables("users").Rows.Count -1
0
 

Author Comment

by:gleznov
ID: 12104229
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
 

Author Comment

by:gleznov
ID: 12104289
But THIS works:

        da.Fill(DataSet11, "users")

:)

0
 

Author Comment

by:gleznov
ID: 12104297
Thanks for all your help!
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now