Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DAO in VB.net

Posted on 2004-08-21
23
Medium Priority
?
963 Views
Last Modified: 2012-08-13
I am trying to use DAO in vb.net.. I know I should use ado but after a days worth of work I am going to stick to DAO.
I am having problems making the connection in vb.net

can someone help me fix this code to connect to an access database in vb.net using dao.

thanks

   Dim db As dao.database
        Dim rs As dao.Recordset
        Dim strSQL As String
        Dim conn As String


        conn.open("Driver={Microsoft Access Driver (*.mdb)};Dbq=D:\Benefits\narvis.mdb;Uid=admin;Pwd=")

maybe my brain is fried cause I just can't seem to find the answer.


0
Comment
Question by:moralju
[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
  • 13
  • 10
23 Comments
 
LVL 1

Author Comment

by:moralju
ID: 11861584
I'm having trouble converting this to ado.net
can you help

Option Explicit
Dim db As Database
Dim rs As Recordset
Dim rst As Recordset

Private Sub Command1_Click()
Dim strID As String
Dim strLname As String
Dim StrFname As String
Dim rcdcnt As Integer
Dim nIndex
Dim nbar
Dim i

    Set rs = db.OpenRecordset("Select * from tblagents")
    Me.ctSchedule0.DateStart = 38215
   

      ctSchedule0.AddColumn "Agent ID", 50
        ctSchedule0.AddColumn "Last", 100
        ctSchedule0.AddColumn "First", 100
 Me.ctSchedule0.TimeType = 3
While Not rs.EOF
     Set rst = db.OpenRecordset("select * from tblTransactions where agent_id = " & rs!agent_id)

     
      strID = rs!agent_id
   strLname = rs!l_name
   StrFname = rs!f_name
           nIndex = Me.ctSchedule0.AddItem(strID + ";" + strLname + ";" + StrFname)
     
      If rst.RecordCount <> 0 Then
      rst.MoveLast
      rst.MoveFirst
      End If
     
       rcdcnt = rst.RecordCount
             
      For i = 1 To rcdcnt
    nbar = Me.ctSchedule0.AddTimeBar(nIndex, rst!TimeStart, rst!TimeEnd, rst!Date, rst!Date)
        rst.MoveNext
    Next i
   rs.MoveNext
Wend
End Sub

Private Sub Form_Load()

    ' Make sure your database file is with the same folder as your app resides.
    Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\narvis.mdb", False, False)

End Sub
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11861612
Sure.. I'll try...
first what these things are doing ? (In plain words)
>> nIndex = Me.ctSchedule0.AddItem(strID + ";" + strLname + ";" + StrFname)
>> nbar = Me.ctSchedule0.AddTimeBar(nIndex, rst!TimeStart, rst!TimeEnd, rst!Date, rst!Date)

and if possible, please explain your task .. :))

-Baan
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 1

Author Comment

by:moralju
ID: 11861634
I have a control http://www.dbi-tech.com/SolSched7ProductPage.htm that I've been using in VB6 and MS access for a while now..
I am switching over to vb.net and I'm having trouble loading the data to the control.

the additem method nIndex = ctSchedule.AddItem("text") load the list of agents and the nindex is the index it was loaded to kinda like a listbox.

the addtimebar [form.]ctSchedule.AddTimeBar( nIndex, lTimeStart, lTimeEnd, lDateStart, lDateEnd ) loads the time bars for each agent

well as you can see in vb6 I looped thru each agent in the agent table. and at the same time looped thru each transaction in the transaction table.

in vb6 it took 1-2 secs to load 4000 transactions

I can't seem to do the same in vb.net
I'm about to crack here.. please help







0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11861721
ok almost done here...  I need to know the data types and the column index of those fields in the "tblTransactions" table

TimeStart, TimeEnd, Date, Date   >>> you are inserting the "Date" field twice...
0
 
LVL 1

Author Comment

by:moralju
ID: 11861743
I'm adding one bar to the schedule.. each transac is only one day
0
 
LVL 1

Author Comment

by:moralju
ID: 11861745
so timestart, timeend, date start ,date end would only add one bar
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11861747
I need to know the data types of those fields  :((
0
 
LVL 1

Author Comment

by:moralju
ID: 11861748
If you help.. by gosh I will name my first born after you...
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11861750
lol... great...  
well...  you see this line...  
nbar = Me.ctSchedule0.AddTimeBar(nIndex, rst!TimeStart, rst!TimeEnd, rst!Date, rst!Date)
                                                                                                        ~~~~~~ ~~~~~
you are using same field values ... Is that what you want ??
-Baan
0
 
LVL 1

Author Comment

by:moralju
ID: 11861753
agent id in both tables is datatype long
time start is Integer.. its the number of minutes from midnight so 600 would be 10am
time end is integer.. same as time start
date is long.. the number of days from 01/01/1900 so 38215 would be 08/18/04
0
 
LVL 1

Author Comment

by:moralju
ID: 11861757
I am using the dame value b/c its the date the bar is supposed to start and end.
the transaction table consist of a one transaction per day. so the start date is the same as the end date
0
 
LVL 18

Accepted Solution

by:
DotNetLover_Baan earned 2000 total points
ID: 11861779
alright... Here you go...

        Dim strID As String
        Dim strLname As String
        Dim StrFname As String
        Dim rcdcnt As Integer
        Dim nIndex
        Dim nbar
        Dim i

        Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=narvis.mdb;User Id=admin;Password=;"
        Dim OleDbCon As New OleDbConnection(ConStr)
        Dim SqlStr As String
        SqlStr = "Select * from tblagents"
        Dim OleDA As New OleDbDataAdapter(SqlStr, OleDbCon)
        Dim DS As New DataSet
        OleDbCon.Open()
        OleDA.Fill(DS)
        OleDbCon.Close()

        Me.ctSchedule0.DateStart = 38215

        ctSchedule0.AddColumn("Agent ID", 50)
        ctSchedule0.AddColumn("Last", 100)
        ctSchedule0.AddColumn("First", 100)
        Me.ctSchedule0.TimeType = 3

        Dim DR As DataRow
        For Each DR In DS.Tables(0).Rows
            strID = RTrim(DR.Item("agent_id"))
            strLname = RTrim(DR.Item("l_name"))
            StrFname = RTrim(DR.Item("f_name"))
            nIndex = Me.ctSchedule0.AddItem(strID & ";" & strLname & ";" & StrFname)

            SqlStr = "select TimeStart,TimeEnd,Date from tblTransactions where agent_id = " & strID
            Dim OleDbCmd As New OleDbCommand(SqlStr, OleDbCon)
            OleDbCon.Open()
            Dim OleDtRdr As OleDbDataReader = OleDbCmd.ExecuteReader()
            While (OleDtRdr.Read)
                nbar = Me.ctSchedule0.AddTimeBar(nIndex, OleDtRdr.GetInt32(0), OleDtRdr.GetInt32(1), OleDtRdr.GetValue(2), OleDtRdr.GetValue(2))
            End While
            OleDbCmd.Dispose()
            OleDtRdr.Close()
            OleDbCon.Close()
        Next

Try it and let me know...

-Baan
0
 
LVL 1

Author Comment

by:moralju
ID: 11861812
I am getting a An Unhandled exception of type 'system.indexoutofRangeException' Occusred in system.data.dll
Addtional information: Index was outside the bounds of the array

location:
       nbar = Me.ctSchedule1.AddTimeBar(nIndex, OleDtRdr.GetInt32(4), OleDtRdr.GetInt32(5), OleDtRdr.GetValue(3), OleDtRdr.GetValue(3))
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11861854
do not replace the indices as you are doing here...
nbar = Me.ctSchedule1.AddTimeBar(nIndex, OleDtRdr.GetInt32(4), OleDtRdr.GetInt32(5), OleDtRdr.GetValue(3), OleDtRdr.GetValue(3))

you are just reading three fields from transaction table... TimeStart, TimeEnd, Date/  Use what I have shown..

nbar = Me.ctSchedule1.AddTimeBar(nIndex, OleDtRdr.GetInt32(0), OleDtRdr.GetInt32(1), OleDtRdr.GetValue(2), OleDtRdr.GetValue(2))
0
 
LVL 1

Author Comment

by:moralju
ID: 11861856
sorry I think that might have been my fault..
I'm testing it again
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11861857
see my SELECT statement...
SqlStr = "select TimeStart,TimeEnd,Date from tblTransactions where agent_id = " & strID
0
 
LVL 1

Author Comment

by:moralju
ID: 11861870
sorry I had been used to the table indices.. my fault.. well anyway.. it does work but it takes 1 to 2 min to pull just 1000 records..

0
 
LVL 1

Author Comment

by:moralju
ID: 11861873
Thank you for all your help by the way.. I don't think I would have gotten this far with out your help...My last strand of hair thanks you too..
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11861895
you are welcome... glad that I could help.. :))
0
 
LVL 1

Author Comment

by:moralju
ID: 11861900
is there anyway to speed this up??
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11861950
I would suggest, join the two tables in sql query and make it a single query. But, I guess here you can't do that , b/c you need...   nIndex = Me.ctSchedule0.AddItem(strID & ";" & strLname & ";" & StrFname)
and then you want to use that nIndex for nbar. I am not sure how to make it faster.
0
 
LVL 1

Author Comment

by:moralju
ID: 11861967
why does DAO load data so much faster?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

610 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