Solved

VB6 Recordset vs. VB.NET Dataset?

Posted on 2004-09-20
20
3,253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Convert datetime to time string 10 35
Help with error in Query 2 39
VB.net SQLCommandBuilder Insert New Records From DataGridView 3 33
VB.Net creating Contact in Outlook 1 60
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 …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

697 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