Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

"SELECT" wont recognize SQL Server 7 DB 2

Status
Not open for further replies.
Ralph8

Ralph8

IS-IT--Management
Joined
Mar 15, 2003
Messages
168
Location
US
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
 
Sort by date Sort by votes
Post your SQL statement and the VB line that uses it

________________________________________________________________
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?'
 
Upvote 0 Downvote
With frmFindReport.adoVuStdtSvcs.Recordset
SQL$ = SELECT ![StudentID], ![IEPReason], ![OriginalIEPDate], ![ServiceID], ![ServiceDesc], ![ServiceIDName], ![Amount}, ![Frequency], ![Location], ![BeginDate], ![EndDate], ![StudentName], ![TeacherName]
From frmFindReport.adoVuStdtSvcs.Recordset

I have tried every variation of this that I can think of.
With and without the 'With frm . . . .'
With and without the 'SQL$ = . . .'
With and without the ![]s

The connection string used in all the ado's contain the value in pCon:

pCon = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=;Initial Catalog=SEM4;Data Source="
pCon = pCon & Trim(gstrServ)

gstrServ will contain either "(local)" or a "Map" name of a server.



TIA

Ralph D. Meredith
 
Upvote 0 Downvote
Looks like a simple misunderstanding. Do your query against a table in the database and the result is returned in a recordset. Here is a quick snippet to get you started. I assume all declarations are done, and you have a reference to Microsoft Activex Data Objects set in your project:
Code: 
Dim rst As ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
' Then use your pCon assignment, which looks OK
pCon = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=;Initial Catalog=SEM4;Data Source="
pCon = pCon & Trim(gstrServ)
' and use it
conn.Open pCon
' Initialise recordset
Set rst = New ADODB.Recordset
' sort out the SQL string
SQL$ = [COLOR=red][b]"[/b][/color]SELECT [StudentID], [IEPReason] From [COLOR=red][b]TheTableName"[/b][/color]
' Then do the query
rst.Open SQL$
Finally make sure you put a password on the sa account!

________________________________________________________________
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?'
 
Upvote 0 Downvote
This is looking a lot better! Everything compiled clean and it executes down to the rst.Open SQL$, which returns a RTE 3709 "Closed or invalid connection". Seems a bit strange, since the conn.Open pCon worked fine.

By the way you were kind saying MIS understanding. It seems I had a LACK OF understanding how connection works for SQL.

Thanks

TIA

Ralph D. Meredith
 
Upvote 0 Downvote
If you're having connection problems you can often cure them by temporarily adding an ADODC control to your form. Right click on it, choose ADODC properties and use the wizard to generate your connection string and test it.

If that's OK and you're still hitting problems, try putting a
[tt]Debug.Print SQL$[/tt]
immediately before the
[tt]rst.Open SQL$[/tt]
then copy and paste the Immediate window result here.

________________________________________________________________
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?'
 
Upvote 0 Downvote
You didn't tell it what connection to use when opening the recordset. Try
[blue][tt]
rst.Open SQL$, conn
[/tt][/blue]
 
Upvote 0 Downvote
Well caught, Golom. I shouldn't cut&paste then edit!

________________________________________________________________
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?'
 
Upvote 0 Downvote
Dim SQL$

Set conn = New ADODB.Connection
conn.Open pCon
Set rst = New ADODB.Recordset

SQL$ = "SELECT [StudentID], [IEPReason], [OriginalIEPDate], [ServiceID], [ServiceDesc], [ServiceIDName], [Amount], [Frequency], [Location], [BeginDate], [EndDate], [StudentName], [TeacherName] FROM vu_ServicesReport" _
+ "Where [StudentID] = frmFindReport.dcStdtID" _
+ "Where [IEPReason] = frmFindReport.cboReason" _
+ "Where [OriginalIEPDate] = frmFindReport.cboDate" _
+ " ORDER BY StudentID, OriginalIEPDate, ServiceID, BeginDate"

Debug.Print SQL$

rst.Open SQL$, conn

Now I am getting RTE "Line 1: Incorrect syntax near "=". and the Debug.Print does not happen.

conn and SQL$ appear to contain the right values.


TIA

Ralph D. Meredith
 
Upvote 0 Downvote
1. Use & to concatenate strings, rather than +
2. Line continuation in VB should end with quote, space, ampersand, space and underbar. The next line starts with quote then the next part of the string.
3. Take the variables out of the quotes
4. If IEPReason contains a String, and OriginalIEPDate contains a Date then delimit them with single quotes

SQL$ = "SELECT [StudentID], [IEPReason], [OriginalIEPDate], [ServiceID], [ServiceDesc], [ServiceIDName], [Amount], " & _
"[Frequency], [Location], [BeginDate], [EndDate], [StudentName], [TeacherName] FROM vu_ServicesReport " & _
"Where [StudentID] = " & frmFindReport.dcStdtID & _
" AND [IEPReason] = '" & frmFindReport.cboReason & _
"' AND [OriginalIEPDate] = '" & frmFindReport.cboDate & _
"' ORDER BY StudentID, OriginalIEPDate, ServiceID, BeginDate"

________________________________________________________________
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?'
 
Upvote 0 Downvote
Another big step closer. Now it thinks that 'frmFindReport' is a column prefix and should have been named in the select statement. It is actually a form name as I am trying to find a match to three items supplied by the user via the form.

TIA

Ralph D. Meredith
 
Upvote 0 Downvote
Check that the values are as you expect. You seem to be relying on default properties (which can be misleading)

Does frmFindReport.dcStdtID refer to a TextBox control (so assuming that it's the Text property you want)?

Put in some Debug.Print statements to check what you're getting. Alternatively assign frmFindReport.dcStdtID to a local variable before assembling your SQL$

________________________________________________________________
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?'
 
Upvote 0 Downvote
I just tried passing the data from the form to local variables, and the result was the local variables are now seen as invalid column names.

Dim SQL$
Dim pstrStdtID As String
Dim pstrIEPReason As String
Dim pstrODate As Date

pstrStdtID = frmFindReport.dcStdtID
pstrIEPReason = frmFindReport.cboReason
pstrODate = frmFindReport.cboDate

Set conn = New ADODB.Connection
conn.Open pCon
Set rst = New ADODB.Recordset

SQL$ = "SELECT [StudentID], [IEPReason], [OriginalIEPDate], [ServiceID], [ServiceDesc], [Amount], [Frequency], [Location], [BeginDate], [EndDate], [FullName], [Expr3] FROM vu_ServicesReport " & _
"Where [StudentID] = pstrStdtID " & _
"AND [IEPReason] = pstrIEPReason " & _
"AND [OriginalIEPDate] = pstrODate " & _
"ORDER BY StudentID, OriginalIEPDate, ServiceID, BeginDate"


TIA

Ralph D. Meredith
 
Upvote 0 Downvote
You seem to have moved the quotes in the string concatenation and missed out the single quotes round the string parameters! To avoid misreading I'll use mono [tt]
....
"Where [StudentID] = '" & pstrStdtID & _
"' AND [IEPReason] = '" & pstrIEPReason & _
"' AND [OriginalIEPDate] = '" & pstrODate & _
"' ORDER BY StudentID, OriginalIEPDate, ServiceID, BeginDate"
....
[/tt]
As a final check, do a Debug.Print on the finished SQL$

________________________________________________________________
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?'
 
Upvote 0 Downvote
You were right. I missed the single quotes. I appreciate your patience.

Current results: Clean compile, "Stepped thru" OK. Should have selected three records from the view but the rst is empty.

TIA

Ralph D. Meredith
 
Upvote 0 Downvote
I commented out the & _ at the end of the Select line along with the WHERE and ORDER BY lines. Still came up with a empty rst.

TIA

Ralph D. Meredith
 
Upvote 0 Downvote
Early on we said <SQL$ = "SELECT [StudentID], [IEPReason] From TheTableName">

Can you just check that vu_ServicesReport is the actual name of the database table.

If that is the case, then put a :[tt]
Debug.Print SQL$[/tt]
immediately before the line:[tt]
rst.Open SQL$, conn[/tt]
and post the result here

________________________________________________________________
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?'
 
Upvote 0 Downvote
BEFORE OPEN:
SELECT [StudentID], [IEPReason], [OriginalIEPDate], [ServiceID], [ServiceDesc], [Amount], [Frequency], [Location], [BeginDate], [EndDate], [FullName], [Expr3] FROM vu_ServicesReport

Print.Debug after open just yielded the same info again, added on to this.


TIA

Ralph D. Meredith
 
Upvote 0 Downvote
And the name of the table was?

________________________________________________________________
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?'
 
Upvote 0 Downvote
vu_ServicesReport. The view was designed in EM using fields from three tables: tblStdtIEPServices, tblStdtPersonal, and tblTeacherPersonal.

TIA

Ralph D. Meredith
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BigTG
  • Locked
  • Question Question
Converting an old XP VB6 program to run on Windows 7 & Windows 10
Replies
5
Views
925
vb5prgrmr
vb5prgrmr
Niki_S
  • Locked
  • Question Question
Error in Excel/VB6 function: &quot;Constant expression required&quot;
2
Replies
31
Views
2K
strongm
strongm
Andrzejek
  • Locked
  • Question Question
Update SQL's varbinary(max) field from VB6
Replies
4
Views
573
Andrzejek
Andrzejek
Niki_S
  • Locked
  • Question Question
How to insert excel sheet into sql server using vb 6?
Replies
6
Views
749
Andrzejek
Andrzejek
versof
  • Locked
  • Question Question
What is a &quot;Public Object Module
Replies
3
Views
611
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top