Solved

VB6 Recordset vs. VB.NET Dataset?

Posted on 2004-09-20
20
3,240 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

14 Experts available now in Live!

Get 1:1 Help Now