Check for column & insert

Can you tell me if this is possible? (and how to do it!!)

The application is VS2005, with sql database.

I want to check if a specific column exists in a specific table in the database and if not then add it, all via my application.

I'm happy knowing how to connect to the database & pass sql commands (as I'm doing that anyway to set off backups), but not the actual queries I'd need.

Hi,

Yes it is possible. This is an example (SQL 2005) that adds the column NewColumn to the Person.Contact table (AdventureWorks) if the column does not exists.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Person'
AND TABLE_NAME = 'Contact'
AND COLUMN_NAME = 'NewColumn')
BEGIN
ALTER TABLE Person.Contact
ADD NewColumn int
END

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Great thanks,

got that working now...

Changing the value of the Database Field?

Hi all,

I'm working on Crystal reports in VS2005.

My database fields got the values in integer format whereas I want to display some string instead.
for example... if the database field value is 0, I must display "No Value" and for 1, "Low", for 2, "High".... so on!

I've been trying this using formula fields. But I couldn't get the correct results. the text of the formula fields is not varying according to the database field value.

So, how can I display a value based on the database field value instead of the database field value itself??

Pls help me solving this prob out!

Thank you.What do you currently have as your formula?
Also, look at the help for 'case' or 'switch'.|||Actually, select expressions are more what you're after, like

Select {table.field}
Case 0:
'No Value'
Case 1:
'Low'
Case 2:
'High'
...and so on
default: 'Unknown!';|||I dont know if this works out in your case but you can give a try
right click and select format field
click on the formula editor for suppress
write the formula
if
{dbfield i.e your field} = o then no value else if

{dbfield i.e your field} = 1 then low value else if

{dbfield i.e your field} = 2 then high value.

should work

do reply if it works...
regards|||I dont know if this works out in your case but you can give a try
right click and select format field
click on the formula editor for suppress
write the formula
if
{dbfield i.e your field} = o then no value else if

{dbfield i.e your field} = 1 then low value else if

{dbfield i.e your field} = 2 then high value.

should work

do reply if it works...
regards

Hi...

It worked out, but not with the exact format! I used UnboundString Field and inserted a formula in it for every Database field in my table. My formula contains something similar to what you've suggested!

Any way... I got the result in time and thank you very much for the reply!!