INCORRECT SYNTAX NEAR "STRING" FOR ALTER SQL

HELP

I am trying to create a new column for every file in a folder

but i keep getting an sql exception - incorrect syntax near ' whatever the value of the file name is'

it works if i just type in the value directly

my code look like this

fsofolder = CreateObject("Scripting.FileSystemObject")
folder = fsofolder.GetFolder("the path to the Files\")
files = folder.Files
For Each objfile In files
sname = objfile.Name

cmd3.CommandText = "ALTER TABLE NEW ADD " & "' " & sname & " ' " & " nvarchar(MAX)"

DatabaseConnection.Open()

Try

cmd3.Connection = DatabaseConnection
cmd3.ExecuteNonQuery()
Catch ex As SqlException
MsgBox(ex.Message)
End Try

DatabaseConnection.Close()

The syntax should be Alter TabletablenameADD COLUMNcolumnname datatype

There is no place for apostophe delimiters in the syntax, and the word COLUMN is needed too.

|||

Thanks

I figured out what the problem was

cmd3.CommandText = "ALTER TABLEtablename ADD " & "'[" & sname & "]" & " nvarchar(MAX)"

It was not accepting eg Q45654656.txt as a column name

but accepting [Q45654656]

|||

database objects can't have a '.' in their names

|||

It did actually

I missed-type in the last post

the difference was the [] that enclosed the string

it accepted

sname = [textfile.txt]

but not

sname = textfile.txt

as the column name

|||

I have another question however,

is it possible to have in one string a sql command to insert into database tableonlyif the column is empty or NULL ?

maybe something like

cmd.CommandText = "INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...) WHERE Columnvalue is NULL"

I appreciate the help

|||

Well, the a

fredi:

I have another question however,

is it possible to have in one string a sql command to insert into database tableonlyif the column is empty or NULL ?

maybe something like

cmd.CommandText = "INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...) WHERE Columnvalue is NULL"

I appreciate the help

Well, why don't you type in that sql statement for yourself and tell us? :)

However, wanting to do what you asked does not make sense to me. I could understand if you said you wanted to update a column only if it was null, because presumably you don't want to lose the old value. By definition, if you want to insert a record, the record shouldn't already exist, so how could a non-existent record have a value in any column?

FYI, it is possible to say (instead of the VALUES (value1, etc.)), SELECT value1, value2, etc.

|||

well here is what i am trying to do and able to do so far

-look into a folder

-create a database table in sql server using the create sql command

-alter the table and create a column named for each file name in the folder

-read each of the text file data into each column

however if i run the code again it adds the textfile data into the same columns again

I just need a statement to say if the column already has data then don't do the all the above steps

I hope this explains my situation

These work:

cmd2.CommandText = "CREATE TABLE " & DatabaseTableName & "(" & ISTCOLUMN& " nvarchar(MAX))"
cmd3.CommandText = "ALTER TABLE " & DatabaseTableName & " ADD " & sname1 & " nvarchar(MAX)"

cmd4.CommandText = "INSERT INTO " & DatabaseTableName & "( " & sname1 & " )" & "VALUES ( '" & filefields(i) & "' )"

How will I check if sname column is Null and only insert the values of filefields into it?

thanks

|||

Am I correct in saying the following?

If the column exists in the table, then you must have populated it with a value?

Because if that is true, then all you have to do is query INFORMATION_SCHEMA.COLUMNS and find out if the column exists.

If that is not true, then you can query the table to see if the column exists.

If it does, query the table to see if it has a row at all, and if so, a value in the column you are interested in.

If yes, do nothing.

If no, update the record.

Now, I have to tell you that what you are doing almost certainly violates relational data modeling.

I would be EXTREMELY SUSPICIOUS of a database design that required me to add a column to a table for every file in a directory.

The odds of this being a good database design are very, very low. Lower than the chance of my being hit by lightning this year.

Standard relational theory would tell us to create a ROW, not a COLUMN, for every file in the directory.

I am not telling you that your database design is wrong. I am telling you that it is very likely wrong, and that you should re-think your approach to be very, very sure the approach you are taking is the right one.

How many files might there be in the directory? Did you know there are limits as to how many columns can be defined for a table? Will you have more than that limit? Did you know that there are limits as to the number of bytes that can be returned for a row in a query? How many filename columns with their values will it take to go over that limit?

See <http://technet.microsoft.com/en-us/library/ms143432.aspx> for details on sql server limits.

Please reconsider your design or - to educate us all - explain why the situation you are in requires such an unusual design.

|||

Thanks David,

If the column exists in the table, then you must have populated it with a value?

is not true. I first create an empty table with at least one column then I add more columns as they show up (i.e as the text files get created). That might not be as important now as the structure of the database itself.

To say that I am fairly new to Database design would be an understatement. Thanks for enlightening me. I am still in an early stage of the design phase and you just showed me how flawed the database would be if I end up going over limits. I would reconsider my approach.

|||

Glad to have helped! I've got 25 years of computing mistakes behind me, so it's easier for me to recognize them.. Some of them are old friends. :)

So, to wrap up this thread, the correct answer is "Don't do it."