Question Locked

SQL in VB 6

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

I am trying to input all the contents from a table into a VB flex grid which I am able to do. Then I want to create another loop just for one item in the flex grid. In this loop, the original data in the flex grid is a number. I want to take this number and check for its corresponding string value in another table. With the code below, I get the same string value from the second table for all the original numbers in the flexgrid.

Below is my code:

=====
intj = 1
Do Until rs.EOF = True 'do until all records in recordset
DoEvents

inti = 0

Do Until inti = rs.Fields.Count 'loop start
DoEvents

If Not IsNull(rs.Fields(inti)) Then
MSHFlexGrid1.Row = intj
MSHFlexGrid1.Col = inti
MSHFlexGrid1.TextMatrix(intj, 0) = rs.Fields("CaseID").Value
MSHFlexGrid1.TextMatrix(intj, 1) = rs.Fields("UserID").Value
MSHFlexGrid1.TextMatrix(intj, 2) = rs.Fields("ApplicationID").Value
MSHFlexGrid1.TextMatrix(intj, 3) = rs.Fields("Problem").Value
MSHFlexGrid1.TextMatrix(intj, 4) = rs.Fields("Time_Working").Value
MSHFlexGrid1.TextMatrix(intj, 5) = rs.Fields("Time_Open").Value
MSHFlexGrid1.TextMatrix(intj, 6) = rs.Fields("QueueID").Value
End If

inti = inti + 1
Loop 'loop end

ApplicationID = MSHFlexGrid1.TextMatrix(intj, 2)

AppIDTemp = "select Application from tbl_Applications where ApplicationID = ApplicationID"
conn.Execute AppIDTemp
ApplicationName = rs1.Fields("Application").Value

MSHFlexGrid1.TextMatrix(intj, 2) = ApplicationName

MSHFlexGrid1.Rows = MSHFlexGrid1.Rows + 1

intj = intj + 1

rs.MoveNext
Loop
=====

Thanks for your help.

5 replies

by sidrajasekhar

Please let me know if you need more information or want me to explain it better.

Thanks

by tony hopkinson

AppIDTemp = "select Application from tbl_Applications where ApplicationID = ApplicationID"
should be

"select Application from tbl_Applications where ApplicationID = " & ApplicationID.

You would be far better off however with a join query and get all the grid info out at the same time.

Also I asssume from the code ApplicationID in Cases? must exist.

Another tip use the recordcount property (+ 1 if you have header row) to set the number of rows in the grid before you write the data into it. It's a good habit to take up memory in lumps than in bits and pieces.

Oh knock those DoEvents calls out and put back the minimum you need to make the UI responsive.

Last but not least, you would be better off designing a class to take a record, then an aggregate to hold a bunch of 'em.
Fill that from the query, then the grid from the list.

VB's ClassBuilder wizard will doit for you.

by sidrajasekhar

I tried the SQL statement fix but it still did not work.

Below is the full code:

=====
Private Sub Form_Load()

Dim conn As ADODB.Connection 'connection
Dim rs As ADODB.Recordset 'record set
Dim rs1 As ADODB.Recordset 'record set
Dim rs2 As ADODB.Recordset 'record set
Dim strconn As String 'conn string
Dim strsql As String
Dim inti
Dim intj

'open connection here to DB
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\TechLog.mdb"
Set conn = New ADODB.Connection
conn.Open strconn

'Open Rs
strsql = "Select * from tbl_Open"
Set rs = New ADODB.Recordset
rs.Open strsql, conn, adOpenDynamic, adLockOptimistic

strsql1 = "Select * from tbl_Applications"
Set rs1 = New ADODB.Recordset
rs1.Open strsql1, conn, adOpenDynamic, adLockOptimistic

strsql2 = "Select * from [User ID]"
Set rs2 = New ADODB.Recordset
rs2.Open strsql2, conn, adOpenDynamic, adLockOptimistic

MSHFlexGrid1.Clear
MSHFlexGrid1.Cols = 2
MSHFlexGrid1.Rows = 2
MSHFlexGrid1.Cols = 9

With MSHFlexGrid1

.TextMatrix(0, 0) = "Case"
.TextMatrix(0, 1) = "Firm Name"
.TextMatrix(0, 2) = "Name"
.TextMatrix(0, 3) = "User ID"
.TextMatrix(0, 4) = "Application"
.TextMatrix(0, 5) = "Problem"
.TextMatrix(0, 6) = "Working Time"
.TextMatrix(0, 7) = "Time Opened"
.TextMatrix(0, 8) = "Opened By"

.Row = 0
.Col = 0
.RowSel = 0
.ColSel = 8
.CellAlignment = flexAlignCenterCenter
.CellFontBold = True

.ColWidth(0) = 1700
.ColWidth(1) = 4000
.ColWidth(2) = 2000
.ColWidth(3) = 2000
.ColWidth(4) = 2000
.ColWidth(5) = 2000
.ColWidth(6) = 2000
.ColWidth(7) = 2000
.ColWidth(8) = 2000

End With

'===================================================================

intj = 1

Do Until rs.EOF = True 'do until all records in recordset
DoEvents

inti = 0

Do Until inti = rs.Fields.Count 'loop start
DoEvents

If Not IsNull(rs.Fields(inti)) Then
MSHFlexGrid1.Row = intj
MSHFlexGrid1.Col = rs.Fields.Count - 7
MSHFlexGrid1.TextMatrix(intj, 0) = rs.Fields("CaseID").Value
MSHFlexGrid1.TextMatrix(intj, 1) = rs.Fields("UserID").Value
MSHFlexGrid1.TextMatrix(intj, 2) = rs.Fields("ApplicationID").Value
MSHFlexGrid1.TextMatrix(intj, 3) = rs.Fields("Problem").Value
MSHFlexGrid1.TextMatrix(intj, 4) = rs.Fields("Time_Working").Value
MSHFlexGrid1.TextMatrix(intj, 5) = rs.Fields("Time_Open").Value
MSHFlexGrid1.TextMatrix(intj, 6) = rs.Fields("QueueID").Value
End If

inti = inti + 1
Loop 'loop end

MSHFlexGrid1.Rows = MSHFlexGrid1.Rows + 1

ApplicationID = MSHFlexGrid1.TextMatrix(intj, 2)
MsgBox ApplicationID
AppIDTemp = "select Application from tbl_Applications where ApplicationID = " & ApplicationID
conn.Execute AppIDTemp
ApplicationName = rs1.Fields("Application").Value

MSHFlexGrid1.TextMatrix(intj, 2) = ApplicationName

intj = intj + 1

rs.MoveNext

Loop

'===================================================================

rs.Close
Set rs = Nothing

rs1.Close
Set rs1 = Nothing

rs2.Close
Set rs2 = Nothing

conn.Close
Set conn = Nothing

End Sub
=====

by tony hopkinson

Have you stepped through in debug mode ?

I hate to point out the obvious but the rows do have different applicationIDs, and the application table different names ?

It's got to be something silly, (don't worry about that, happens to every coder).

You might want to consider splitting your code up so the application name is acquired in a separate loop.

When you find it don't smack your forehead too hard, not being able to see the wood for the trees, is a professional hazard.

PS I often find when I can't see why something I think should work doesn't, doing it in a different way often helped when I was on my own.

This discussion is closed to new replies.