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

[Blarneystone]

Hi,

I am using VB.NET and trying to pull data from two different tables in
the database. I am using what I think is standard code. But the data
I am pulling is like the following:

Table1 Column1 Row1
Table2 Column1 Row1 ~ 20
Table1 Column1 Row2 ~ 3

So I am going from one table to the other and back to the first.

I am wondering if there is a simple way to do this in vb.net? If you
could post a snippet, it would help me a lot.

TIA.

 

[rowe_newsgroups]

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

 

[Blarneystone]

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()

 

[rowe_newsgroups]

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]

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]

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]

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]

I don't believe that will work, but I can't try it out either. My
suggestion is to use the Query designer in MS Access to build the
query. Then, after you have the query behaving like you want, switch to
the SQL view and then copy and paste that into your program. I'm afraid
I can't help to much more than that - I don't really understand what
your program does. If you need more help, could you explain what
exactly you wish to accomplish?

Hope that helps,

Seth Rowe

 

[Blarneystone]

My
suggestion is to use the Query designer in MS Access to build the
query.

AHA! Thanks...I always forget about that tool! I'm having good luck
so far. Thanks again,
Brad

 

[Blarneystone]

My
suggestion is to use the Query designer in MS Access to build the
query.

AHA! Thanks...I always forget about that tool! I'm having good luck
so far. Thanks again,
Brad