checking if fields exist

Memnoch1207
08-07-2002, 09:44 AM
I am wanting to see what fields exist in a view in a database. before I execute a select statement. I have views which are dynamically created, depending on the data in the database. Is there some code I can run to check and see which fields exist in the view prior to running the select statement???

example:

'code that checks the fields in the dynamically created view
check to see which fields are in the view
get the names of the fields
if all needed fields are there
then run the select statement
if not then create the field

Thinker
08-07-2002, 09:52 AM
When you use Select *, it returns all fields. If you use something
that quickly returns a recordset without selecting all the records
(like looking for a record with a null primary key, or Top 0), you
should be able to loop through the Fields collection.

Rezner
08-07-2002, 11:52 AM
Here's a procedure that I came up with to do what you want:Private Sub CheckForField(strTable, strFieldName)

Set RS = Server.CreateObject("ADODB.Recordset")

RS.Open _
"SELECT TOP 1 * FROM [" & strTable & "]", Application("cnn")

'Attempt to find the field
For x = 0 to RS.Fields.Count-1
If LCase(strFieldName) = LCase(RS.Fields(x).Name) Then
RS.Close
Set RS = Nothing
Exit Sub
End If
Next

'If you make it this far, the field doesn't exist
'so add it to the table
Application("cnn").Execute _
"ALTER TABLE [" & strTable & "] ADD [" & strFieldName & "] varchar(30)"


RS.Close
Set RS = Nothing

End SubNote: The ADODB Connection object is at the application level. I usually set it in the global.asa

Thinker
08-07-2002, 01:31 PM
You really should add something to that Select so that it doesn't
return all the rows in the table. Select Top 0 * works in SQL
Server (not sure about Access).

Rezner
08-07-2002, 01:38 PM
Good point Thinker... that could be dangerous. I just tried "TOP 0" in Access and it didn't work, so I used "TOP 1"

Memnoch1207
08-07-2002, 08:30 PM
I'm using PostgreSQL for the database backend. I keep running into conflicts with MSSQL syntax and PostgreSQL syntax...(I wish they would make them all the same...it would be easier to remember!:D )

I was told I don't need to add the column to the table.

so, now I just need to check the fields to make sure they are there, so when I run the SQL statement I don't get an error... for now I have trapped the error, but I still need to get it working properly.

BTW, thanks for the help.

Rezner
08-07-2002, 08:34 PM
OK, then just modify that code I posted a little bit and make it a function:Private Function FieldExists(strTable, strFieldName)

Set RS = Server.CreateObject("ADODB.Recordset")

RS.Open _
"SELECT TOP 1 * FROM [" & strTable & "]", Application("cnn")

'Attempt to find the field
For x = 0 To RS.Fields.Count-1
If LCase(strFieldName) = LCase(RS.Fields(x).Name) Then
RS.Close
Set RS = Nothing
FieldExists = True
Exit Function
End If
Next

Field Exists = False
End Function

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum