Solved

VB6 Recordset vs. VB.NET Dataset?

Posted on 2004-09-20
20
3,248 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 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