How to check if a column does exist?

[Setta]

Hi!

Well, I have a page - in ASP VB - that creates a temporary column in my DB, and in the end of my code Dropp the temporary Column.

What happen, is that sometimes the column is NOT dropped and when I get my page to work again it gives me an error (The column is already created).

So, I need a way to check if a given column exists or not.
If does exist executesome code, if not execute other code.

How can I do that?

 

[mwolf00]

What database are you using? In sql server query sysColumns...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[Setta]

I forgot to say that.

I'm using MS Access 2000

 

[mwolf00]

use the code in thread701-385452 and look for the column in a group...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[Setta]

Thanks mwolf00 but...

The idea is good and it might work, I can't create an object field to check it's name inside a loop that scans all fields of my table.

I'm kind of newbie to all this "ADO" and "DAO" stuff... Some times I really don't know what I am doing.

Could u give a hand?

My code is like this:
-----------------------------

dim myset_checa_coluna
myset_checa_coluna = "SELECT * FROM tb_estoque"

set myset_checa = server.CreateObject("adodb.recordset&quot
myset_checa.ActiveConnection = MM_conexao_STRING
myset_checa.Source = myset_checa_coluna
myset_checa.CursorType = 0
myset_checa.CursorLocation = 2
myset_checa.LockType = 3
myset_checa.open()

dim myfield
set myfield = server.CreateObject("adox.field&quot

For Each myfield In myset_checa
if myfield.name = "novacoluna"&mytemp_id then
response.Redirect("

http://www.google.com.br")

end if
Next
-----------------------------

I get an error on line:
set myfield = server.CreateObject("adox.field&quot

The system says it is an invalid class sequence.

and this part:

--------------
if myfield.name = "novacoluna"&mytemp_id then
response.Redirect("

http://www.google.com.br")

end if
--------------
is to check if my column exists or not.

 

[mwolf00]

dim searchCol, boolFound
searchCol = = "novacoluna"
boolFound = false

Dim cn As New Connection
Dim rs As Recordset, connString As String
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\yourDatabasePath.mdb;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString
Set rs = cn.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, "YourTable&quot)

While Not rs.EOF
if rs!column_name = seachColumn then boolFound = true
rs.MoveNext
Wend
rs.Close
Set cn = Nothing

if not boolFound then
'add your column here'
end if


Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[Setta]

WOW! Man you are too fast!

I took look at the code you've sent, it's great.
Tahnks a lot.

I have to make some changes, and the line:
------------
if rs!column_name = seachColumn then boolFound = true
------------
it's kinda new to me.


I never saw this kind of sintax before.
What means the "!"
And "column_name" it's a reserved word or should I use the name of the column I am looking for? (problaby the first gess)

Well, thanks again man you're helping a lot.

 

[onpnt]

the ! is a terney operator and I'm not sure it will work quite right in the unless you're using jscript as your language. it does the same as NOT

____________________________________________________
_{The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-3811}

http://www.onpntwebdesigns.com

 

[Setta]

Thanks onpnt! I am using VB and not JS. (or should I say !JS)

Well, now I'm getting an error that does not make sense to me.

On this line
---------------------
cn.ConnectionString = connString
---------------------

Then the system says:
Object necessary: "
(YES, just a lonely &quot

What could be wrong here?



<p align=&quot;center&quot;><font color=&quot;#FF3300&quot; size=&quot;1&quot; face=&quot;Verdana, Arial, Helvetica, sans-serif&quot;>-- 100% Webmonkey --</font></p>

 

[Setta]

mwolf00 were your code in JS or VB?

-- Webmonkey --

 

[Setta]

Well, thank you guys...now the problem is solved.

FYI: A friend of mine give the following code
-------------------
Dim my_column_exixts
my_column_exixts = false

set myConnection = Server.CreateObject(&quot;ADODB.Connection&quot
myConnection.Open YOURCONNECTION
set schema = myConnection.openSchema(4)

do while not schema.eof
if (schema.fields(&quot;TABLE_NAME&quot.value=&quot;tb_estoque&quot then
if(schema.fields&quot;COLUMN_NAME&quot.value=&quot;column_i_want&quot then
my_column_exixts = true
end if
end if
schema.movenext()
loop
------------------------

Case closed!
Thank you all.

-- Webmonkey --