"SELECT" wont recognize SQL Server 7 DB 2

[Ralph8]

I suspect I will be embarrassed when I see the answer to this (I'm just starting SQL):

T-SQL statements are not recognizing the DB containing the data I'm referring to. No matter what I try, the "fields" named after "SELECT" are not recognized.

I am using VB 6/SQL Server 7 with adodc connections. I am using "Initial Catalog = SEM4" in my connection string, with SEM4 being the name of the database.

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[Ralph8]

Also, checking in EM, there are three good records in the view that match the selection values.

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[johnwm]

Try copying the SQL$ into Query analyser and see what you get from there

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Ralph8]

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.

This is what it gave, but I can't spot a syntax error.


TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[johnwm]

Just check that the StudentID field is a Char or a VarChar field rather than an Int. If it's an Int, then don't use single quotes around the value for that field, change that part of the query to:

[tt]"Where [StudentID] = " & pstrStdtID & _
" AND [IEPReason] = '" & pstrIEPReason & _[/tt]

Failing that, post back and I'll have another look tomorrow, as midnight is now looming!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Ralph8]

StdtID is Char, IEPReason is varchar, and OriginalIEPDate is datetime.

Since you are gone for the night, I will experiment with the datetime and get back to you.

Where are you that it is midnight? It is approaching 6 PM here in Central Missouri.

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[Ralph8]

I have experimented with every variation that I can think of, both in my regular code and in the Query analyzer.
Results are always the same. If I do "it" in Run Time I simply get a empty rst when it should find three records. QA always indicates an "Invalid syntax near the "=" no matter what I do.

I am just using the "SELECT" line for right now. No use trying the "WHERE" and "ORDER BY" until the first part is squared away.


TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[johnwm]

If you're only using the SELECT line, where does the '=' come from?

Just paste :[tt]

SELECT [StudentID], [IEPReason] From u_ServicesReport
[/tt]
into the QA window and see what you get back.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Ralph8]

That worked in QA, I got all three records, but when I try it in Run Time I get a "Expected Case" message without the SQL$ =.

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[bmdb]

What do the [] around the field names do? I have never used them in my SQL statments but then I too am extremely new and am using Pervasive SQL. Just a thought maybe try and remove them to see what happens.

SELECT StudentID, IEPReason From u_ServicesReport

 

[johnwm]

OK. So the actual query:
SELECT [StudentID], [IEPReason] From u_ServicesReport
now works.

To use this from VB, we need to put it into a string:
[tt]
SQL$ = "SELECT [StudentID], [IEPReason] From u_ServicesReport"
[/tt]
and then tell VB what to do with the string:
[tt]
rst.Open SQL$, pcon
[/tt]
That should get your 3 records into the recordset rst. To see them in the Immediate Window (CTRL-G):
[tt]
With rst
Do While Not .EOF
Debug.Print .Fields(0) & " " & .Fields(1)
Loop
End With
[/tt]
Then tidy up:
[tt]
rst.Close
Set rst = Nothing
pcon.Close
Set pcon = Nothing
[/tt]

Most of this stuff is well documented in SQL Books on Line, which is a free download from MS:

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Might be worth downloading it as a background read-up and a reference source.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Ralph8]

It is working fine now.

JohnWM, I really appreciate the help AND the education I received in the process.

If the system would let me vote more than once I would, because you had several very helpful items in this discourse.

Some one else apparently appreciated it also, as there is a second star which I could not give.

Thank you
Ralph


TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[johnwm]

You're welcome - glad it helped. And Thanks for the star

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'