Visual Basic Question

system · August 30, 2002, 8:59am

Thanks allot Jeff, you have probably saved my holiday weekend.
Jeremy Leonard
IT Manager
K-T Corporation

-----Original Message-----
From: Jeff Lewis [mailto:jeff@...]
Sent: Thursday, August 29, 2002 11:11 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Visual Basic Question

Jeremy,

Here are some pieces of our VB.NET code that shows one way to do it.

////////// Dim a few things you will need //////////

Dim cn As New ADODB.Connection()

Structure rsClone 'Structure contains all the details of Forecast,Job
and Order details
Public EDIKey As String
Public PartNum As String
Public Gen_Date As Date
Public Actual_Qty As Long
Public Del_Date As Date
Public Qty As Long
Public DueDate As Date
Public CreatedDate As Date
Public Date05 As Date
Public NeedByDate As Date
End Structure

Dim rsClone1() As rsClone
Dim oJobDetails() As JobDetails
Dim oOrderDetails() As OrderDetails
Const ConnectString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
Dim rs As New ADODB.Recordset(), rs1 As New ADODB.Recordset(), rs2 As
ADODB.Recordset, rs3 As ADODB.Recordset

////////// Set the connection on form load (you will have multiple
connections) /////////////
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim MyQry As String, i As Short

With cn
.ConnectionString = ConnectString &
"T:\DATA\ACCESS\?????\MyDatabase.mdb;Persist Security Info=False"
.Open()
End With

'Populate the records with some PartNum so that we have some sorta
dispplay when the app is executed
PopulateRecords("10090908-101")
LoadgrdGenDate() 'Load the Gen Date grid
LoadgrdDetails() 'Load appropriate Values into Details grid

and so on...

/////// Populate the recordsets using the queries -- then populate
structures from the recordsets ///////
Private Sub PopulateRecords(ByVal PartNum As String)
Dim MyQry As String, MyQry1 As String, i As Short, MQ As Long,
MyQry2 As String
Dim Gdate As Date

MyQry = "SELECT Distinct EDIDtl.EDIKey,EDIHead.Gen_Date,
EDIDtl.PartNum, EDIDtl.Revision, EDIDtl.Actual_Qty, EDIDtl.Del_Date " &
_
"FROM JitPartNum INNER JOIN (EDIDtl INNER JOIN EDIHead ON
EDIDtl.EDIKey = EDIHead.EDIKey) " & _
"ON JitPartNum.PartNum = EDIDtl.PartNum " & _
"WHERE(((EDIDtl.PartNum) = '" & PartNum & "')) " & _
"ORDER BY EDIHead.Gen_Date,EDIDtl.EDIKey, EDIDtl.Del_Date;"
rs.Open(MyQry, cn, ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockReadOnly)

If rs.RecordCount Then
MyQry1 = "SELECT JitOrders.ReqQty, JitOrders.Date05,
JitOrders.NeedByDate, JitOrders.PartNum " & _
"FROM JitOrders " & _
"WHERE (((JitOrders.NeedByDate)>#1/11/2001#) AND
((JitOrders.PartNum)='" & _
PartNum & "')) AND JitOrders.Date05> #" &
rs.Fields("Gen_Date").Value & "#;"
rs1 = New ADODB.Recordset()
rs1.Open(MyQry1, cn, ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockReadOnly)
'If rs1.RecordCount Then MsgBox("order present")

MyQry2 = "SELECT CreatedDate,DueDate,ProdQty + StockQty as
TotalQty FROM JitJobs WHERE PartNum='" & PartNum & _
"' AND CreatedDate> #" &
CDate(rs.Fields("Gen_Date").Value) & "# ORDER BY CreatedDate;"
rs2 = New ADODB.Recordset()
rs2.Open(MyQry2, cn, ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockReadOnly)
'If rs2.RecordCount Then MsgBox("job present")
MyQry2 = "SELECT CreatedDate,DueDate,ProdQty + StockQty as
TotalQty FROM JitJobs WHERE PartNum='" & PartNum & _
"' AND CreatedDate> #" &
CDate(rs.Fields("Gen_Date").Value) & "# ORDER BY DueDate;"
rs3 = New ADODB.Recordset()
rs3.Open(MyQry2, cn, ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockReadOnly)

'Initialise the StDate and EndDate
rs.MoveFirst()
StDate = rs.Fields("Gen_Date").Value
rs.MoveLast()
EndDate = rs.Fields("Del_Date").Value
rs.MoveFirst()
ReDim rsClone1(rs.RecordCount)

'populate values from the EDIDtl table
MaxQty = 0
While Not rs.EOF
With rsClone1(i)
.EDIKey = rs.Fields("EDIKey").Value
.PartNum = rs.Fields("PartNum").Value
.Actual_Qty = rs.Fields("Actual_Qty").Value
.Del_Date = rs.Fields("Del_Date").Value
.Gen_Date = rs.Fields("Gen_Date").Value
End With
If rs.Fields("Actual_Qty").Value > MaxQty Then MaxQty =
rs.Fields("Actual_Qty").Value
If rs.Fields("Gen_Date").Value < StDate Then StDate =
rs.Fields("Gen_Date").Value
If rs.Fields("Del_Date").Value > EndDate Then EndDate =
rs.Fields("Del_Date").Value
rs.MoveNext() : i += 1
End While
and so on...

//// write the values from the populated structure to the grid /////
'Loads the values into the Gen Date Grid from the rsclone1 object
Private Sub LoadgrdGenDate()
Dim i As Short, GDate As Date, j As Short = 1, EDIKey As String

With Me.grdGenDate
While i < rsClone1.GetUpperBound(0)

GDate = rsClone1(i).Gen_Date : EDIKey = rsClone1(i).EDIKey
If GDate = CDate("12:00:00 AM") Then Exit While
.Rows.Count += 1
.Item(j, 1) = rsClone1(i).Gen_Date
.Item(j, 2) = rsClone1(i).EDIKey
j += 1 : i += 1
While rsClone1(i).Gen_Date = GDate And EDIKey =
rsClone1(i).EDIKey
i += 1
If i >= rsClone1.GetUpperBound(0) Then Exit While
End While
End While
.AutoSizeCol(1)
.AutoSizeCol(2)
.Row = 1
End With

End Sub

A structure was used in this program because they are easy to work with
and the info is also needed to display graphically in a picture box.

Jeff

-----Original Message-----
From: Leonard, Jeremy [mailto:jleonard@...]
Sent: Thursday, August 29, 2002 5:49 AM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Visual Basic Question

I have a Visual Basic question I am hoping someone here can help
me with.
I have a standard data grid activeX control on my form. I need
to do a join
on two tables from two different data sources. In this case,
its MSSQL
Server and our Progress Vantage Database. Currently I
populating the data
grid by linking it to an ADODatacontrol activeX object. I
change the
select statement on the ADODatacontrol to change my recordset.
The problem
is, the ADO ActiveX control only allows a connection to one Data
source at a
time. I also run into the same problem if I try to use the Data
Environment
Tools in Visual Basic.

I'm sure that its a common task to do a join on two tables from
two
different data source in Visual Basic and I'm just not seeing
the obvious.

Any help would be greatly appreciated.

Thanks
Jeremy Leonard
IT Manager
K-T Corporation

Yahoo! Groups Sponsor
ADVERTISEMENT

<http://rd.yahoo.com/M=228862.2128520.3581629.2225242/D=egroupweb/S=1705
007183:HM/A=1182689/R=0/*http://adfarm.mediaplex.com/ad/ck/990-1736-1039
-335>

Useful links for the Yahoo!Groups Vantage Board are: ( Note:
You must have already linked your email address to a yahoo id to enable
access. )
(1) To access the Files Section of our Yahoo!Group for Report
Builder and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service <http://docs.yahoo.com/info/terms/> .

[Non-text portions of this message have been removed]

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

system · August 29, 2002, 6:49am

I have a Visual Basic question I am hoping someone here can help me with.
I have a standard data grid activeX control on my form. I need to do a join
on two tables from two different data sources. In this case, its MSSQL
Server and our Progress Vantage Database. Currently I populating the data
grid by linking it to an ADODatacontrol activeX object. I change the
select statement on the ADODatacontrol to change my recordset. The problem
is, the ADO ActiveX control only allows a connection to one Data source at a
time. I also run into the same problem if I try to use the Data Environment
Tools in Visual Basic.

I'm sure that its a common task to do a join on two tables from two
different data source in Visual Basic and I'm just not seeing the obvious.

Any help would be greatly appreciated.

Thanks
Jeremy Leonard
IT Manager
K-T Corporation

system · August 29, 2002, 12:10pm

Jeremy,

Here are some pieces of our VB.NET code that shows one way to do it.

////////// Dim a few things you will need //////////

Dim cn As New ADODB.Connection()

Structure rsClone 'Structure contains all the details of Forecast,Job
and Order details
Public EDIKey As String
Public PartNum As String
Public Gen_Date As Date
Public Actual_Qty As Long
Public Del_Date As Date
Public Qty As Long
Public DueDate As Date
Public CreatedDate As Date
Public Date05 As Date
Public NeedByDate As Date
End Structure

Dim rsClone1() As rsClone
Dim oJobDetails() As JobDetails
Dim oOrderDetails() As OrderDetails
Const ConnectString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
Dim rs As New ADODB.Recordset(), rs1 As New ADODB.Recordset(), rs2 As
ADODB.Recordset, rs3 As ADODB.Recordset

////////// Set the connection on form load (you will have multiple
connections) /////////////
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim MyQry As String, i As Short

With cn
.ConnectionString = ConnectString &
"T:\DATA\ACCESS\?????\MyDatabase.mdb;Persist Security Info=False"
.Open()
End With

'Populate the records with some PartNum so that we have some sorta
dispplay when the app is executed
PopulateRecords("10090908-101")
LoadgrdGenDate() 'Load the Gen Date grid
LoadgrdDetails() 'Load appropriate Values into Details grid

and so on...

/////// Populate the recordsets using the queries -- then populate
structures from the recordsets ///////
Private Sub PopulateRecords(ByVal PartNum As String)
Dim MyQry As String, MyQry1 As String, i As Short, MQ As Long,
MyQry2 As String
Dim Gdate As Date

MyQry = "SELECT Distinct EDIDtl.EDIKey,EDIHead.Gen_Date,
EDIDtl.PartNum, EDIDtl.Revision, EDIDtl.Actual_Qty, EDIDtl.Del_Date " &
_
"FROM JitPartNum INNER JOIN (EDIDtl INNER JOIN EDIHead ON
EDIDtl.EDIKey = EDIHead.EDIKey) " & _
"ON JitPartNum.PartNum = EDIDtl.PartNum " & _
"WHERE(((EDIDtl.PartNum) = '" & PartNum & "')) " & _
"ORDER BY EDIHead.Gen_Date,EDIDtl.EDIKey, EDIDtl.Del_Date;"
rs.Open(MyQry, cn, ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockReadOnly)

If rs.RecordCount Then
MyQry1 = "SELECT JitOrders.ReqQty, JitOrders.Date05,
JitOrders.NeedByDate, JitOrders.PartNum " & _
"FROM JitOrders " & _
"WHERE (((JitOrders.NeedByDate)>#1/11/2001#) AND
((JitOrders.PartNum)='" & _
PartNum & "')) AND JitOrders.Date05> #" &
rs.Fields("Gen_Date").Value & "#;"
rs1 = New ADODB.Recordset()
rs1.Open(MyQry1, cn, ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockReadOnly)
'If rs1.RecordCount Then MsgBox("order present")

MyQry2 = "SELECT CreatedDate,DueDate,ProdQty + StockQty as
TotalQty FROM JitJobs WHERE PartNum='" & PartNum & _
"' AND CreatedDate> #" &
CDate(rs.Fields("Gen_Date").Value) & "# ORDER BY CreatedDate;"
rs2 = New ADODB.Recordset()
rs2.Open(MyQry2, cn, ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockReadOnly)
'If rs2.RecordCount Then MsgBox("job present")
MyQry2 = "SELECT CreatedDate,DueDate,ProdQty + StockQty as
TotalQty FROM JitJobs WHERE PartNum='" & PartNum & _
"' AND CreatedDate> #" &
CDate(rs.Fields("Gen_Date").Value) & "# ORDER BY DueDate;"
rs3 = New ADODB.Recordset()
rs3.Open(MyQry2, cn, ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockReadOnly)

'Initialise the StDate and EndDate
rs.MoveFirst()
StDate = rs.Fields("Gen_Date").Value
rs.MoveLast()
EndDate = rs.Fields("Del_Date").Value
rs.MoveFirst()
ReDim rsClone1(rs.RecordCount)

'populate values from the EDIDtl table
MaxQty = 0
While Not rs.EOF
With rsClone1(i)
.EDIKey = rs.Fields("EDIKey").Value
.PartNum = rs.Fields("PartNum").Value
.Actual_Qty = rs.Fields("Actual_Qty").Value
.Del_Date = rs.Fields("Del_Date").Value
.Gen_Date = rs.Fields("Gen_Date").Value
End With
If rs.Fields("Actual_Qty").Value > MaxQty Then MaxQty =
rs.Fields("Actual_Qty").Value
If rs.Fields("Gen_Date").Value < StDate Then StDate =
rs.Fields("Gen_Date").Value
If rs.Fields("Del_Date").Value > EndDate Then EndDate =
rs.Fields("Del_Date").Value
rs.MoveNext() : i += 1
End While
and so on...

//// write the values from the populated structure to the grid /////
'Loads the values into the Gen Date Grid from the rsclone1 object
Private Sub LoadgrdGenDate()
Dim i As Short, GDate As Date, j As Short = 1, EDIKey As String

With Me.grdGenDate
While i < rsClone1.GetUpperBound(0)

GDate = rsClone1(i).Gen_Date : EDIKey = rsClone1(i).EDIKey
If GDate = CDate("12:00:00 AM") Then Exit While
.Rows.Count += 1
.Item(j, 1) = rsClone1(i).Gen_Date
.Item(j, 2) = rsClone1(i).EDIKey
j += 1 : i += 1
While rsClone1(i).Gen_Date = GDate And EDIKey =
rsClone1(i).EDIKey
i += 1
If i >= rsClone1.GetUpperBound(0) Then Exit While
End While
End While
.AutoSizeCol(1)
.AutoSizeCol(2)
.Row = 1
End With

End Sub

A structure was used in this program because they are easy to work with
and the info is also needed to display graphically in a picture box.

Jeff

-----Original Message-----
From: Leonard, Jeremy [mailto:jleonard@...]
Sent: Thursday, August 29, 2002 5:49 AM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Visual Basic Question

I have a Visual Basic question I am hoping someone here can help
me with.
I have a standard data grid activeX control on my form. I need
to do a join
on two tables from two different data sources. In this case,
its MSSQL
Server and our Progress Vantage Database. Currently I
populating the data
grid by linking it to an ADODatacontrol activeX object. I
change the
select statement on the ADODatacontrol to change my recordset.
The problem
is, the ADO ActiveX control only allows a connection to one Data
source at a
time. I also run into the same problem if I try to use the Data
Environment
Tools in Visual Basic.

I'm sure that its a common task to do a join on two tables from
two
different data source in Visual Basic and I'm just not seeing
the obvious.

Any help would be greatly appreciated.

Thanks
Jeremy Leonard
IT Manager
K-T Corporation

Yahoo! Groups Sponsor
ADVERTISEMENT

<http://rd.yahoo.com/M=228862.2128520.3581629.2225242/D=egroupweb/S=1705
007183:HM/A=1182689/R=0/*http://adfarm.mediaplex.com/ad/ck/990-1736-1039
-335>

Useful links for the Yahoo!Groups Vantage Board are: ( Note:
You must have already linked your email address to a yahoo id to enable
access. )
(1) To access the Files Section of our Yahoo!Group for Report
Builder and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service <http://docs.yahoo.com/info/terms/> .

[Non-text portions of this message have been removed]

Topic		Replies	Views
VB.NET and ADO.NET Yahoo Archive	1	376	July 30, 2003
Accessing another table in Form Event Yahoo Archive	13	735	April 3, 2009
Running some VB code when saving a record (Partially Solved) Yahoo Archive	0	533	September 7, 2011
Closer Blank Title 77998 Yahoo Archive	7	334	May 19, 2009
Custom code, extracting table data to a variable Yahoo Archive	2	1032	March 31, 2010

Visual Basic Question

Related Topics