kindly help me........

Public Sub JoinTable()

        Dim conn As ADODB.Connection
        Dim rs As ADODB.Recordset
        conn = New ADODB.Connection
        rs = New ADODB.Recordset

        With conn
            .ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
            & "SERVER=" & MySQL_SVR & ";" _
            & "DATABASE=documentdb;" _
            & "UID=" & MySQL_UID & ";" _
            & "PWD=" & MySQL_PWD & ";" _
            & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
            .CursorLocation = ADODB.CursorLocationEnum.adUseClient
            .Open()
            .Close()
        End With

        conn.Open()
        With rs
            inquery = ("Select documenttable.documentID, tracktable.datecreated, tracktable.datetimeupdated, tracktable.creator, tracktable.remainingtimeleft, tracktable.documentcurrentlocation, tracktable.currentlocation, tracktable.nextdestination, tracktable.title, tracktable.type, tracktable.description, tracktable.updatedby, tracktable.route1, tracktable.route2, tracktable.route3, tracktable.route4, tracktable.route5, tracktable.route6, tracktable.route7, tracktable.route8, documenttable.datecreated, documenttable.creator, documenttable.title, documenttable.type, documenttable.description, documenttable.currentstatus, documenttable.currentlocation, documenttable.destination, documenttable.timeduration, documenttable.route1, documenttable.route2, documenttable.route3, documenttable.route4, documenttable.route5, documenttable.route6, documenttable.route7, documenttable.route8 from documenttable inner join tracktable on documenttable.documentID = tracktable.documentID inner join tracktable on documenttable.datecreated = tracktable.datecreated inner join tracktable on documenttable.creator = tracktable.creator inner join tracktable on documenttable.title = tracktable.title inner join tracktable on documenttable.description = tracktable.description inner join tracktable on documenttable.currentlocation = tracktable.currentlocation inner join tracktable on documenttable.timeduration = tracktable.timeduration inner join tracktable on documenttable.route1 = tracktable.route1 inner join tracktable on documenttable.route2 = tracktable.route2 inner join tracktable on documenttable.route3 = tracktable.route3 inner join tracktable on documenttable.route4 = tracktable.route4 inner join tracktable on documenttable.route5 = tracktable.route5 inner join tracktable on documenttable.route6 = tracktable.route6 inner join tracktable on documenttable.route7 = tracktable.route7 inner join tracktable on documenttable.route8 = tracktable.route8 inner join tracktable on documenttable.timeduration = tracktable.timeduration inner join tracktable on documenttable.currentstatus = tracktable.currentstatus order by documentID ")
            
            .Open(inquery, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)

            If .RecordCount > 0 Then
                .MoveFirst()
                Do While Not .EOF
                    txtCreator.Text = .Fields("creator").Value
                    txtBarcodeID.Text = .Fields("documentID").Value
                    txtTimeDuration.Text = .Fields("timeduration").Value
                    lblRemaining.Text = .Fields("remainingtimeleft").Value
                    txtCurrentLocation.Text = .Fields("documentcurrentlocation").Value
                    txtOriginOfDocument.Text = .Fields("documentcurrentlocation").Value
                    txtTitle.Text = .Fields("title").Value
                    txtNextDestination.Text = .Fields("nextdestination").Value
                    txtType.Text = .Fields("type").Value
                    txtCurrent.Text = .Fields("currentstatus").Value
                    txtDescription.Text = .Fields("description").Value
                    txtUpdatedBy.Text = .Fields("updatedby").Value

                Loop
            End If

        End With
    End Sub
kvprajapati commented: Read forum rules. -2

Recommended Answers

All 9 Replies

...

tracktable is not unique means that you are using tracktable twice for two different things. figure out where you are using it the second time and rename it

you might want to add line breaks to that query

can you show me how?
because I have no idea...

SELECT
      documenttable.documentID,
      documenttable.datecreated,
      documenttable.creator,
      documenttable.title,
      documenttable.type,
      documenttable.description,
      documenttable.currentstatus,
      documenttable.currentlocation,
      documenttable.destination,
      documenttable.timeduration,
      documenttable.route1,
      documenttable.route2,
      documenttable.route3,
      documenttable.route4,
      documenttable.route5,
      documenttable.route6,
      documenttable.route7,
      documenttable.route8,

      tracktable.datecreated,
      tracktable.datetimeupdated,
      tracktable.creator,
      tracktable.remainingtimeleft,
      tracktable.documentcurrentlocation,
      tracktable.currentlocation,
      tracktable.nextdestination,
      tracktable.title,
      tracktable.type,
      tracktable.description,
      tracktable.updatedby,
      tracktable.route1,
      tracktable.route2,
      tracktable.route3,
      tracktable.route4,
      tracktable.route5,
      tracktable.route6,
      tracktable.route7,
      tracktable.route8

FROM  documenttable

INNER JOIN   tracktable on documenttable.documentID = tracktable.documentID

INNER JOIN   tracktable on documenttable.datecreated = tracktable.datecreated

INNER JOIN   tracktable on documenttable.creator = tracktable.creator

INNER JOIN   tracktable on documenttable.title = tracktable.title

INNER JOIN   tracktable on documenttable.description = tracktable.description

INNER JOIN   tracktable on documenttable.currentlocation = tracktable.currentlocation

INNER JOIN   tracktable on documenttable.timeduration = tracktable.timeduration

INNER JOIN   tracktable on documenttable.route1 = tracktable.route1

INNER JOIN   tracktable on documenttable.route2 = tracktable.route2

INNER JOIN   tracktable on documenttable.route3 = tracktable.route3

INNER JOIN   tracktable on documenttable.route4 = tracktable.route4

INNER JOIN   tracktable on documenttable.route5 = tracktable.route5

INNER JOIN   tracktable on documenttable.route6 = tracktable.route6

INNER JOIN   tracktable on documenttable.route7 = tracktable.route7

INNER JOIN   tracktable on documenttable.route8 = tracktable.route8

INNER JOIN   tracktable on documenttable.timeduration = tracktable.timeduration

INNER JOIN   tracktable on documenttable.currentstatus = tracktable.currentstatus

ORDER BY     documentID

you are innerjoining that track table like 10 times. you cant do this. join it onces. use where to determine what data you want, but just join it once.

SELECT
      documenttable.documentID,
      documenttable.datecreated,
      documenttable.creator,
      documenttable.title,
      documenttable.type,
      documenttable.description,
      documenttable.currentstatus,
      documenttable.currentlocation,
      documenttable.destination,
      documenttable.timeduration,
      documenttable.route1,
      documenttable.route2,
      documenttable.route3,
      documenttable.route4,
      documenttable.route5,
      documenttable.route6,
      documenttable.route7,
      documenttable.route8,

      tracktable.datecreated,
      tracktable.datetimeupdated,
      tracktable.creator,
      tracktable.remainingtimeleft,
      tracktable.documentcurrentlocation,
      tracktable.currentlocation,
      tracktable.nextdestination,
      tracktable.title,
      tracktable.type,
      tracktable.description,
      tracktable.updatedby,
      tracktable.route1,
      tracktable.route2,
      tracktable.route3,
      tracktable.route4,
      tracktable.route5,
      tracktable.route6,
      tracktable.route7,
      tracktable.route8

FROM  documenttable

INNER JOIN   tracktable on documenttable.documentID = tracktable.documentID

ORDER BY     documentID

like this

and then add a WHERE WHATEVERYOUWANT = WHATEVERYOUNEED

INNER JOIN tracktable on documenttable.documentID = tracktable.documentID

ORDER BY documentID

where documenttable.datecreated = tracktable.datecreated, where documenttable.creator= tracktable.creator

like that???

inner join tracktable on tacktable.documentid = documenttable.id where documenttable.datecreated = tracktable.datecreated and documentable.creator = tracktable.creator order by documentid

thanks a lot...

yup mark as solved please

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.