Retrieving columns of complex queries

[condonethis]

This is my SQL query that pulls and organizes the information I need linked together in one query result.

SELECT     TRD100_REQSEQ_EVENT.req_seq_no,                        

                      TPM300_PAT_VISIT.med_rec_no, TPM300_PAT_VISIT.vst_ext_id

                         

FROM         TRD100_REQSEQ_EVENT WITH (NOLOCK) INNER JOIN

                      TPM300_PAT_VISIT WITH (NOLOCK) ON TRD100_REQSEQ_EVENT.vst_int_id = TPM300_PAT_VISIT.vst_int_id

Whenever I try to run my query in VB try to read the necessary columns to strings I can't seem to get the results

Try
 
            conn.Open()
            Dim command = New SqlCommand("SELECT TRD100_REQSEQ_EVENT.req_seq_no, TPM300_PAT_VISIT.med_rec_no, TPM300_PAT_VISIT.vst_ext_id FROM TRD100_REQSEQ_EVENT WITH (NOLOCK) INNER JOIN TPM300_PAT_VISIT WITH (NOLOCK) ON TRD100_REQSEQ_EVENT.vst_int_id = TPM300_PAT_VISIT.vst_int_id WHERE req_seq_no = '" & TxtACC.Text & "'", conn)
            Dim reader As SqlDataReader = command.ExecuteReader()
            While reader.Read()
                MsgBox(reader("med_rec_no").ToString)
            End While
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
            conn.Close()
        End Try

The query works fine in SQL Server Management... Any takers?

Thanks much in advance!

[techgnome]

They're not quite the same query... one has a where clause while the other doesn't... try removing the where clause off of the VB version of the query.... see if you get results then.

If you do ... two things... 1) make sure you're using a valid value for the lookup. 2) use a parameterized query instead... there's info on that in the FAQ/Tutorial section of the Database forum.

-tg

[condonethis]

They're not quite the same query... one has a where clause while the other doesn't... try removing the where clause off of the VB version of the query.... see if you get results then.

If you do ... two things... 1) make sure you're using a valid value for the lookup. 2) use a parameterized query instead... there's info on that in the FAQ/Tutorial section of the Database forum.

-tg

I have the WHERE clause so I can cone in on the certain instance that I specify in my textbox. I have used the query successfully with the WHERE in SQL Mgnt. Thank you for the tip I will look at the FAQ.