Retrieving columns of complex queries

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

Code:

---

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

vb.net Code:

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!

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

Quote:

---

Originally Posted by 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

---

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.