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."

Incorrect Syntax Error

hi. i am relatively new to sql. i am wondering how to resolve my "incorrect syntax error" on an IP Address string i am trying to add.
the table has IPAddress as data type "text" and length 16 (which won't let me change the length). It seems that sql is looking for a data type with no more than one decimal (money, float, etc). How do i resolve this? thanks in advance. PeterYou need to change the datatype of the column. This can be done using SQL Enterprise Mangler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_5rg2.asp) or the ALTER TABLE ALTER COLUMN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp) command from SQL Query Analyzer (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/qryanlzr/qryanlzr_1zqq.asp).

-PatP|||Hi. THanks for the reply. Well, i am new to sql but not that new. i have tried changing the data type a number of times. I tried varchar, text, nvarchar, and a few others. I did this in the designer view of the SQL EM. I still get the same response... "incorrect syntax error". For numerical data types that expect one decimal place, i understand this, but i don't get why i am getting this error for string-like data types such as var char, etc. more suggestions appreciated.

Originally posted by Pat Phelan
You need to change the datatype of the column. This can be done using SQL Enterprise Mangler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_5rg2.asp) or the ALTER TABLE ALTER COLUMN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp) command from SQL Query Analyzer (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/qryanlzr/qryanlzr_1zqq.asp).

-PatP|||This requires the "digital ball pein" to fix it, because you can't change the type of a TEXT, NTEXT, or IMAGE column. The following script demonstrates:CREATE TABLE dbo.foo2 (
fooId INT IDENTITY
, thingie TEXT
)
GO

ALTER TABLE dbo.foo2
ALTER COLUMN thingie VARCHAR(20) -- Server: Msg 4928, Level 16, State 1, Line 1
-- Cannot alter column 'thingie' because it is 'text'.
GO

CREATE TABLE dbo.foo3 (
fooId INT IDENTITY
, thingie VARCHAR(20)
)
GO

INSERT INTO dbo.foo3 (thingie)
SELECT thingie
FROM dbo.foo2The short answer is that you must build a new table, and copy the data from the old table into the new table. Then you should be good to go.

-PatP|||hi. thanks again. i droped the table as you suggested and redesigned it
with the IPAddress row as varchar(30) instead of text and ran the insert statement and sql is still complaining about anything that comes after the first decimal point in the IP address. for and inserted IP of 121.111.12.1 sql complains that there is an "incorrect syntax near '.12'".
i am really stumped. if you have other suggestions, much appreciated.|||You never showed us what your insert statment looked like. I ran this and it worked fine.

insert into foo3 ([thingie]) values('121.111.12.1')|||Here's the whole thing(ie) ;-)

CREATE TABLE dbo.foo3 (
fooId INT IDENTITY
, thingie VARCHAR(20)
)
GO
insert into foo3 ([thingie]) values('121.111.12.1')
GO
SELECT [fooId], [thingie] FROM [TESTDB].[dbo].[foo3]
GO|||It would help if you posted the actual SQL command it's performing.

From what you have said it looks like you aren't quoting the IP string.

Since it's a VARCHAR field, you should insert/update the IP with single quotes around it.

Sloppy oracle-centric SQL warning!

insert into iplist using select 'SERVERNAME','121.111.12.1' from other_table

Incorrect Data appearing in report

I have setup a report. I am calling the report from a page which passes parameters in a query string to the report.
The parameters are to be used in a stored procedure which the "Object Data Source" of the report refers to.
The stored procedure queries the data ina table and this data should be displayed in the report.

What actually happens is that the report appears but it has only the first record in the table but this is not the record
specified by the parameters passed to the stored procedure. It look as if the report is attaching to the correct table but
the query is not being executed properly.

Anyone any ideas how to fix this?
macca

Does ur storeprocedure return the right data when u run in SSMS or in the Data layout of ur report?

|||

Thanks for the reply Karenros but how do I test what you are suggesting.

Thanks,

macca

|||

Go to sql server ManagementStudio and then go to the database u wanna access and then click on new query.. in the new query window type in ur sproc name and give the parameters for ur sproc and u should see the results in the same window...

Or u can go to ur report in VS2005 and then click on the data part of ur report and u will see the name of the dataset and the sproc name.. next to the sproc click the exceute button and it will prompt u for parameters and after u enter them they should display the results...

Hope this helps.

Regards

Karen

|||Thanks for that Karen that works.I am getting another problem though. I am calling the report via a Response.redirect but am getting an error with the dates formats. The error message is as follows:"An error has occurred during report processing.· String was not recognized as a valid DateTime." I am calling the report and the url looks like this:· · "/PaymentReport.aspx?Section=107&DateFrom=02/10/2007&DateTo=19/10/2007". When I try the method you suggested above I must put in the DateFrom as 10/2/2007 and DateTo as 10/19/2007. The report is obviously not getting the dates in the query string in the correct format and therefore the error.

|||

How are u storing ur date time in ur database. looks like the format you are using is dd/mm/yyyy... and by any chance is the date in ur database stored as mm/dd/yyyy?

Regards

Karen

|||

Macca,

while u are giving ur date Parameter try putting it in Single quote... take a look at this code.

Declare @.reqDeldatedatetimeSet @.reqDeldate ='1/31/2007'Select OrderId, OrderDate, PlanId, RequiredDeliveryDateFrom [Order]Where RequiredDeliveryDate = @.reqDeldate

If i just @.reqDeldate as 1/31/2007 instead of '1/31/2007' nothing shows up.

 So try giving it quotes..

Regards

Karen

|||

Thanks Karen that worked.

I now have another problem. The report is not bringing back the correct results from the query. It appears to just bring back one record multiple times.

But if I run the query it brings back the correct results.

I don't know why it is doing this.

Any ideas?

macca

|||

can u pls post ur store procedure code? and have u tried refreshing the data in VS studio.. cause sometimes i have seen display the old data... and after i refresh it works fine... and try deploying it to the report server to see if it gives the correct results.

Hope this helps.

Regards

Karen

|||

Here is my Stored procedure:

CREATE PROCEDURE sproc_PaymentReports

(
@.DateFrom Datetime,
@.DateTo Datetime,
@.Status Int,
@.Section Int
)

AS

BEGIN
SELECT cheq_id, cheq_daterec, cheq_pername, cheq_amt
FROM RecCheqs
WHERE cheq_DateRec >= coalesce(nullif(@.DateFrom, ' '),cheq_DateRec) AND cheq_DateRec <= coalesce(nullif(@.DateTo, ' '),cheq_DateRec)
AND cheq_dept = coalesce(nullif(@.Section , ' '),cheq_dept)
AND cheq_added = coalesce(nullif(@.Status , ' '),cheq_added)
END
GO

How do you deploy to the report server?

macca

|||

Do u have a report server setup? if yes

then in VS go to Project and then click on properties and in the window that comes up Give a

Name for the TargetReportFolder. and in the TargetServerURL ... give http:/Either localhost or an ipaddress/ReportServer

and click ok ... for the first time the overwriteDatasources to false...but sometimes i would have to make changes to the report to the dataset so i just set it to true.

Once u set ur project properties... Go to Build -- Deploy Project name and it should prompt for a user name and password if required...

and to run ur report from the report server... go to the address that u specified in the TargetReportServerURL and then navigate to the folder name and click on the main report that runs it... and then its like .... runing it from Visual studio

Hope this helps...

Regards

Karen

|||

I don't have a report server setup so can't do any of that. This is not doing it for me at all.

In .net 1.1 I used to create reports using a Repeater and I would run the stored procedure and it would "Bind" the data to the repeater and it would work fine.

I have tried this with the ReportViewer using the following code but cannot get it to Bind:

Dim oDSAs DataSet

Dim oChequeAsNew Cheque

Try

oDS =New DataSet

oDS = oCheque.GPAssignedReport(dateFrom, dateTo, Section, Status)

If oDS.Tables(0).Rows.Count > 0Then

rptVwPayment.DataBind()

rptVwPayment.Visible =True

This code works for the repeaters in 1.1 but cannot get it to bind to report.

Any ideas, as I am ready to abandon Reports as a pack of rubbish.

macca

|||

i have used the report viewer control... but only done it using remote processing cause all my reports are located in the report server.

|||

Karen,

Thanks for all your help I got that resolved.

macca

Incorrect connection string is working...

I have the following Connection String in VB.NET:
Friend DBConnectionString as String = "Persist Security
Info=False;Integrated Security=SSPI;Initial Catalog=MyApp;server=" &
ServerName
Friend cn as New SqlClient.SqlConnection(DBConnectionString)
The ServerName is read from the registry and contains the server computer IP
address. Working with local server, this value is naturally 127.0.0.1
On my desktop computer, the application is working OK accessing the local
database. I've also installed the application on several computers with no
problems with DB connection.
The strange thing is that I now installed the application on a computer
where there is a problem establishing the connection to the local DB. When
debugging the application I discovered that the DBConnectionString assignment
was being called before the registry value for ServerName was read.
Therefore the connection string value after assignement is:
DBConnectionString = "Persist Security Info=False;Integrated
Security=SSPI;Initial Catalog=MyApp;server="
which as suspected leads to connection to server problem.
My question is: How come that on all the other computers (where the
connection string has a value of nothing for server), the application is able
to access the local database?
Are you using threads?
Are any of your functions ASync?
-Walt
"isgooner" <isgooner@.discussions.microsoft.com> wrote in message
news:E88B3C5D-A350-4814-AA3C-E3C088100801@.microsoft.com...
> I have the following Connection String in VB.NET:
> Friend DBConnectionString as String = "Persist Security
> Info=False;Integrated Security=SSPI;Initial Catalog=MyApp;server=" &
> ServerName
> Friend cn as New SqlClient.SqlConnection(DBConnectionString)
> The ServerName is read from the registry and contains the server computer
IP
> address. Working with local server, this value is naturally 127.0.0.1
> On my desktop computer, the application is working OK accessing the local
> database. I've also installed the application on several computers with no
> problems with DB connection.
> The strange thing is that I now installed the application on a computer
> where there is a problem establishing the connection to the local DB. When
> debugging the application I discovered that the DBConnectionString
assignment
> was being called before the registry value for ServerName was read.
> Therefore the connection string value after assignement is:
> DBConnectionString = "Persist Security Info=False;Integrated
> Security=SSPI;Initial Catalog=MyApp;server="
> which as suspected leads to connection to server problem.
> My question is: How come that on all the other computers (where the
> connection string has a value of nothing for server), the application is
able
> to access the local database?
|||Neither
"Walter Holm" wrote:

> Are you using threads?
> Are any of your functions ASync?
> -Walt
> "isgooner" <isgooner@.discussions.microsoft.com> wrote in message
> news:E88B3C5D-A350-4814-AA3C-E3C088100801@.microsoft.com...
> IP
> assignment
> able
>
>