Re: Need a tip: How do you streamwrite from two different db tables?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Seth,

That is exactly what I am trying to do! I didn't think I could pull
from both tables using 1 connection?

If I can, I'd just combine the following two statements:

cmd = New System.Data.OleDb.OleDbCommand("SELECT Dex, Personal,
Description, Pits, sort, Dates FROM MasterList ORDER BY Sort" , cn)

cmdstp = New System.Data.OleDb.OleDbCommand("select " & stepper & "
from Steps", cn)

How would that look? Like:
cmd = New System.Data.OleDb.OleDbCommand("SELECT Dex, Personal,
Description, Pits, sort, Dates FROM MasterList ORDER BY Sort" & "select
" & stepper & " from Steps", cn)


rowe_newsgroups wrote:
Bingo! I had a suspician you were using Access, but I wasn't sure. Why
not create your SQL text to pull the data from both tables and input
that into a datareader. Then just loop through that datareader and
write your data in the desired format. Also, if you are having trouble
writing the data in the correct format, please provide a snippet of
what the finished data should look like and I'll see what I can do!

Note, I might have misread what you are trying to accomplish since I
can't recreate what you are trying to do (as I don't have access to
your database). If my above suggestion is totally off, please post back
and let me know.

Thanks,

Seth Rowe


Blarneystone wrote:
Hi,

Do you mean this?

cn = DbConnection1
Friend WithEvents DbConnection1 As System.Data.OleDb.OleDbConnection
.Input, False, CType(0, Byte), CType(0, Byte), "Type",
System.Data.DataRowVersion.Original, Nothing))
'
'DbConnection1
'
Me.DbConnection1.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb"

Thanks!

rowe_newsgroups wrote:
Could you post the dimensioning and declaring code for your connection
string (variable cn I believe)? I'm trying to see what type of database
you're connecting to.

Thanks,

Seth Rowe


Blarneystone wrote:
rowe_newsgroups wrote:
Could you elaborate on what you are trying to accomplish? And could you
post the code you are using? The "normal" (imo) way to do this is to
build a sql command to pull the records from both tables, execute the
command into a dataset or datareader (depending on your needs) and then
do whatever you need to do with the data. Is this what you're doing?

Thanks,

Seth Rowe

Hi Seth,

I was trying to do it without having to do a dataset because I'm still
confused on how to pull the data using DTR connections going from 1st
table to the 2nd and then back to the first...

Below is my code:

Dim stepper, n, filename As String
Dim SW As StreamWriter
Dim FS As FileStream
Dim ST1 As String = "Nothing here."

Dim cmd As System.Data.OleDb.OleDbCommand
Dim cmdstp As System.Data.OleDb.OleDbCommand
Dim DTR As System.Data.oledb.OleDbDataReader
Dim DTRstp As System.Data.oledb.OleDbDataReader 'steps database
reader

n = 1
stepper = "G" & n
cmd = New System.Data.OleDb.OleDbCommand("SELECT Dex, Personal,
Description, Pits, sort, Dates FROM MasterList ORDER BY Sort", cn)
cmdstp = New System.Data.OleDb.OleDbCommand("select " & stepper & "
from Steps", cn)


FS = New FileStream("Export.rtf", FileMode.Append)
SW = New StreamWriter(FS)
DTR = cmd.ExecuteReader()
Dim x As Integer = 0
'Pull data from first table into streamwriter
While DTR.Read()
x += 1
SW.WriteLine("Personal " & x & ": " & (DTR("Personal")) &
vbCrLf & "Date Due: " & (DTR("Dates") & vbCrLf))
'pull in the second table data
DTRstp = cmdstp.ExecuteReader()
While DTRstp.Read
If DTRstp(stepper) Is DBNull.Value Then
SW.WriteLine("No Steps entered" & vbCrLf)
Exit While
End If
ST1 = DTRstp(stepper)
Exit While
Else
SW.WriteLine(ST1 & vbCrLf)
End If

If ST1 = "Nothing entered..." Then
SW.WriteLine(ST1 & vbCrLf)
End If
ST1 = ""
End While

'go back and pull from 1st table

SW.WriteLine(vbTab & "Benefits: " & (DTR("description")) _
& vbCrLf & vbTab & "Pits: " & (DTR("pits")) _
& vbCrLf & vbCrLf)
End While

' Clean-up.
SW.Close()
FS.Close()
System.Diagnostics.Process.Start("export.rtf")

DTR.Close()

.

Quantcast