mam0014

I am putting this sql statement into my program and I get no results, but if I run it through ISQLW I do get results any ideas?

SQL
-------------------------------------------------------------------
SELECT distinct VR.VIDEO_ID
FROM mpeg..VIDEOS_REQUIRED VR
JOIN mpeg..VIDEOS_REQUIRED_TEXT VT ON VR.STATUS = VT.STATUS
LEFT JOIN mpeg..VIDEO VD ON VD.VIDEO_ID = VR.VIDEO_ID
LEFT JOIN mpeg..VIDEOS_ARCHIVED VA ON VA.VIDEO_ID = VR.VIDEO_ID
LEFT JOIN mpeg..VIDEOS_ARCHIVED_TEXT VAT ON VA.STATUS = VAT.STATUS
where VD.VIDEO_ID = null and VAT.VALUE <> null
---------------------------------------------------------------------

VB Code
----------------------------------------------------------------------
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim Qy As New ADODB.Command
Dim sql As String
Dim ConnectionString As String
ConnectionString = "driver={SQL Server};" & _
"server=CTCDB1; uid=sa; pwd=; database=mpeg;"


With cn
.ConnectionString = ConnectionString
.ConnectionTimeout = 10
.Open
End With


Dim i%
Dim tst As Boolean
Dim tst1 As Boolean
Dim log$


sql = "SELECT distinct VR.VIDEO_ID"
sql = sql + vbCrLf + "FROM mpeg..VIDEOS_REQUIRED VR"
sql = sql + vbCrLf + "JOIN mpeg..VIDEOS_REQUIRED_TEXT VT ON VR.STATUS = VT.STATUS"
sql = sql + vbCrLf + "LEFT JOIN mpeg..VIDEO VD ON VD.VIDEO_ID = VR.VIDEO_ID"
sql = sql + vbCrLf + "LEFT JOIN mpeg..VIDEOS_ARCHIVED VA ON VA.VIDEO_ID = VR.VIDEO_ID"
sql = sql + vbCrLf + "LEFT JOIN mpeg..VIDEOS_ARCHIVED_TEXT VAT ON VA.STATUS = VAT.STATUS"
sql = sql + vbCrLf + "where VD.VIDEO_ID = null and VAT.VALUE <> null"
Text2.Text = sql
rs.Open sql, cn
On Error Resume Next
Do Until rs.EOF
List1.AddItem rs.Fields(0)
rs.MoveNext
Loop
rs.Close

End Sub
----------------------------------------------------------------------

Any suggestions?

Thanks

burningice96

SQL isn't picky about line breaks. As long as there are spaces between the different commands (that's all it sees a line break as, a space), the query will run fine. I've heard of people having problems with vbCrLf before. Why don't you try replacing your vbCrLf's with a " "?

DrPunk

If you want to use Carriage returns in SQL statements then use vbCR instead of vbCrLf (i.e. just a carriage return, and no line feed on the end). It's a good idea to use them if you are outputting your query some where for a person to read (even just the debug window) so that it formats the queries nice to read.

Your code would look much better type as so


This makes it much easier to read the query within the code. Please note the "_" at the end of every line which tells VB that the line does not finish at the end of the current line, but continues to the next, until there isn't a "_"

To try and fix your problem, what database are you connecting to? I guess it isn't Access!

Oh, yeah, I see, it's an SQL Server. OK, first guess, is it the double dots (i.e. mpeg..VIDEOS_...). Will it work with one dot (i.e. mpeg.VIDEOS_...)

mam0014

I can actually completely remove the mpeg.. because I set the database in my connection string. But, still no luck I am getting no return. Would it have anything to do with the JOIN's? I am just grasping now. I have tried it line by line and I get some sort of result until I use the where clause.

where VD.VIDEO_ID = null and VAT.VALUE <> null

Doesn't make sense that it would work in ISQLw, but not from VB.

Oh Well I keep trying.

Thanks

Briotti

Quote:

---

Originally Posted by mam0014


where VD.VIDEO_ID = null and VAT.VALUE <> null

---

I don't remember (i work with several database engines and each one has his own statement for "null") if it is correct for MSSQL:

where VD.VIDEO_ID = null and VAT.VALUE <> null

Try instead

where VD.VIDEO_ID isnull and not(VAT.VALUE isnull)

or something like this

where isnull(VD.VIDEO_ID) and not(isnull(VAT.VALUE))

check the statement syntax

Bye

Giuseppe