[RESOLVED] Going Crazy 2 - The SQL

[jhize]

OK. I understand that the punctuation is critical in SQL statements but, come on:

 Sql = "SELECT Occupant, Telephone#, EmergencyContact, EmergencyCntNum FROM Occupant WHERE PropertyCommonName = '" & Propertyname & "'"

The only field I don't want returned is PropertyCommonName.....and it's just not cooperating.

I have seen examples that use SELECT Table1.Field1 as field That doesn't cut it either.

Can anyone see what I can't here?

[MaximilianMayrhofer]

Sql = "SELECT Occupant, Telephone#, EmergencyContact, EmergencyCntNum " & _
      "FROM Occupant " & _ 
      "WHERE PropertyCommonName = '" & Propertyname & "';"

For one thing, you forgot the semicolon that tells the sql reader that your command has finished. Also, I wonder about the use of the hash in your field name, as the hash symbol is a protected symbol within sql, used to identify variables as being date/time datatypes, such as

"WHERE mydate = #" & datefield & "#;"

Check out the SQL tutorial in my sig for a more in-depth look at sql statements.

[Hack]

You don't mention which database you are using.

The # will work with Access, but not other types.

[jhize]

Oops. I'm using vb6 and access 2003. The above sql statement gave me the following error:

Run time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.

Incidently, I changed the Telephone# field to TelephoneNum.

[Hack]

That means it can't find the field you are using in your query. Make sure you typed everything correctly.

[FunkyDexter]

With that crash I'm guessing that the sql statement runs correctly but you get the crash when you try to use one of the values returned (eg myVal = rs("Occupant")). If so, check the spelling of the fieldname, if you've spelt it differently when you access the field to the way you spelt it in the SQL query you will get this error.

[MaximilianMayrhofer]

Just to make sure, when you changed the field in your database to TelephoneNum, did you also change the field in your sql query to TelephoneNum?

[jhize]

Yeah, there's no typos. I'm reading the SQL tutorial now. I'm pretty sure it is the missing semicolon. I just have to get my computer to make sure. I'm on my sidekick now.

[si_the_geek]

The semicolon doesn't matter (or if it did, that would not be the error - you would get something like "SQL syntax error" when you .Open the recordset).

The problem is as Hack and FunkyDexter said - you are trying to read a field that does not exist in the recordset. If you can't find the issue yourself, post the code here (from the SQL statement up to the line with the error).

[jhize]

Well, the field in the recordset is definately there. I am no longer getting the runtime error. However, its debugging in the sub fillgrid().

It is stopping at a line that grabs the column headers.

I am going to replace that sub with the one provided in your signature. I'll bet that's the issue. Between the tutorial. you, Hack and the others - I know the sql is right.

[szlamany]

The ; (semi-colon) character is becoming more required with SQL 2005.

Since new keywords have been added the syntax parser is having a harder time determining end-of-statement spots.

It certainly has been optional in the MS SQL world - and with 2005 it still is.

We have encountered two bugs when porting SPROCS from 2000 to 2005 where the addition of a single ; character fixed the parsing of the SPROC.

This is not enough for me to start using the ; character to end all my statements.

As for your error

Item cannot be found in the collection corresponding to the requested name or ordinal.

That has absolutely nothing to do with how you build or execute your SQL string. That error is clearly telling you that you are trying to refer to a RS() object that does not exist. Either you called it XYZ in the SELECT LIST and are attempting to reference it with WXYZ in the RS() or you are trying to get it via ordinal position - something like RS(8) when only 5 columns are in the select list (for example).

Dropping your routine to use another doesn't help you fix your problem. You should be setting the run environment to break on all errors (right click on a code window and toggle that setting) and the IDE will break at exactly the line of code that's failing. There will be no question as to what is wrong - it will be highlighted by the IDE.

[jhize]

That's REAL good info. However, the routine I'm using actually enumerates the columns from the db. My sql is asking for the first (0), the third (2), the fourth (3) etc. columns. So I'm skipping the second (1). With that said, if I move the one I'm skipping to the last column in the db and set my grid to only 4 columns instead of 5, it will clip the unwanted column.

The error I'm getting is definately related to the columns not being in order or in this case skipping one.

I know (pretty sure) this will work because I was accidently clipping a field I needed because I didn't have enough columns set up in the grid.

[szlamany]

If you are using ORDINAL position rs(0), rs(1) and so on then use the RS.Fieldcount to protect yourself.

For i = 0 to RS.FieldCount - 1
     debug.print RS(i).Name;"=";RS(i).Value
Next i

http://msdn2.microsoft.com/en-us/library/ms677568.aspx

[MaximilianMayrhofer]

Hey I didn't know that the ";" wasn't required. Thanks for teaching me something new

[jhize]

OK. That worked. I set the properties of the grid to show one less columns that were actually present in the table and I moved the foreign key to be the last in the table.