•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 428,379 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,576 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser: Programming Forums
Views: 2859 | Replies: 10
 |
•
•
Join Date: Aug 2007
Posts: 98
Reputation: 
Rep Power: 2
Solved Threads: 0
Junior Poster in Training
Hi,
I am trying to create a stored procedure to add a new column into a database from an ASP web application.
The SQL Code for this is:-
I have tried to insert this SQL into a stored procedure:-
when i try to execture this i get an error message:-
"Msg 102, Level 15, State 1, Procedure sp_addcolumn, Line 7
Incorrect syntax near '@tablename'."
How I want this to work is - The user chooses which table they want to add a new column to from a drop down box which holds all the table names then they insert the column name and datatype.
I'm not sure what i have done wrong here, if anyone has any ideas they would be greatly appreciated! Thanks!
GLT
I am trying to create a stored procedure to add a new column into a database from an ASP web application.
The SQL Code for this is:-
ALTER TABLE tablename ADD COLUMN columnname columndatatype
I have tried to insert this SQL into a stored procedure:-
CREATE PROCEDURE sp_addcolumn -- Add the parameters for the stored procedure here @tablename varchar(50) = 0, @columnname varchar(50) = 0, @datatype varchar (50) = 0 AS ALTER TABLE @tablename ADD COLUMN @columnname @datatype END
when i try to execture this i get an error message:-
"Msg 102, Level 15, State 1, Procedure sp_addcolumn, Line 7
Incorrect syntax near '@tablename'."
How I want this to work is - The user chooses which table they want to add a new column to from a drop down box which holds all the table names then they insert the column name and datatype.
I'm not sure what i have done wrong here, if anyone has any ideas they would be greatly appreciated! Thanks!
GLT
Last edited by GLT : May 23rd, 2008 at 9:04 am.
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,165
Reputation:
Rep Power: 7
Solved Threads: 59
Alter table can't take dynamic parameters. To do this you will have to concatenate the whole TSQL command as a string dynamically and then execute it. Also you don't need the COLUMN keyword.
tsql Syntax (Toggle Plain Text)
•
•
Join Date: Aug 2007
Posts: 98
Reputation:
Rep Power: 2
Solved Threads: 0
Junior Poster in Training
Thanks for your quick reply!
I now have this:-
I have also tried this:-
and my ASP code is:-
Sorry about all the code but i am very confused!
I am getting the error:-
"Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][SQL Native Client][SQL Server]The definition for column ' ' must include a data type.
/sqlsite/adminaddnewcolumn.asp, line 51"
Any ideas greatly appreciated! Thanks Guys!!!
GLT
I now have this:-
CREATE PROCEDURE sp_addcolumn -- Add the parameters for the stored procedure here @TABLE_NAME varchar(50) = 0, @columnname varchar(50) = 0, @datatype varchar (50) = 0 AS DECLARE @tsql varchar (200) SET @tsql = 'ALTER TABLE [ '+@TABLE_NAME+'] ADD ['+@columnname+']' + @datatype EXEC(@tsql)
I have also tried this:-
CREATE PROCEDURE sp_addcolumn -- Add the parameters for the stored procedure here @TABLE_NAME varchar(50) = 0, @columnname varchar(50) = 0, @datatype varchar (50) = 0 AS DECLARE @tsql varchar (200) SET @tsql = 'ALTER TABLE [ '+@TABLE_NAME+'] ADD ['+@columnname+@datatype+']' EXEC(@tsql)
and my ASP code is:-
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/Silverwingdatabase.asp" -->
<%
Dim cmd_addcolumn__TABLE_NAME
cmd_addcolumn__TABLE_NAME = " "
if(Request("TABLE_NAME") <> "") then cmd_addcolumn__TABLE_NAME = Request("TABLE_NAME")
Dim cmd_addcolumn__columnname
cmd_addcolumn__columnname = " "
if(Request("columnname") <> "") then cmd_addcolumn__columnname = Request("columnname")
Dim cmd_addcolumn__datatype
cmd_addcolumn__datatype = " "
if(Request("datatype") <> "") then cmd_addcolumn__datatype = Request("datatype")
%>
<%
set cmd_addcolumn = Server.CreateObject("ADODB.Command")
cmd_addcolumn.ActiveConnection = MM_Silverwingdatabase_STRING
cmd_addcolumn.CommandText = "dbo.sp_addcolumn"
cmd_addcolumn.Parameters.Append cmd_addcolumn.CreateParameter("@RETURN_VALUE", 3, 4)
cmd_addcolumn.Parameters.Append cmd_addcolumn.CreateParameter("@TABLE_NAME", 200, 1,50,cmd_addcolumn__TABLE_NAME)
cmd_addcolumn.Parameters.Append cmd_addcolumn.CreateParameter("@columnname", 200, 1,50,cmd_addcolumn__columnname)
cmd_addcolumn.Parameters.Append cmd_addcolumn.CreateParameter("@datatype", 200, 1,50,cmd_addcolumn__datatype)
cmd_addcolumn.CommandType = 4
cmd_addcolumn.CommandTimeout = 0
cmd_addcolumn.Prepared = true
cmd_addcolumn.Execute()
%>Sorry about all the code but i am very confused!
I am getting the error:-
"Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][SQL Native Client][SQL Server]The definition for column ' ' must include a data type.
/sqlsite/adminaddnewcolumn.asp, line 51"
Any ideas greatly appreciated! Thanks Guys!!!
GLT
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,165
Reputation:
Rep Power: 7
Solved Threads: 59
Here's a tip: amend the stored proc to select @tsql instead of EXEC(@tsl)
OPen QueryAnalyser or Ms Sql Management Studio (whatever you're using) connect to the DB and open a new query window put:
You will find you are missing a space between columnname and datatype, which is there in my code but somehow you have removed it when implementing it in your project.
I always do a select first when concatenating dynamic sql to see the resulting string first before executing anything, beacuase the syntax often obscures little bugs like this, especially when quotes need to be part of the string!!
Your second attempt tells me you don't grok the square brackets [] these are in case anyone tries to create a column name with a space in it.
tsql Syntax (Toggle Plain Text)
tsql Syntax (Toggle Plain Text)
You will find you are missing a space between columnname and datatype, which is there in my code but somehow you have removed it when implementing it in your project.
I always do a select first when concatenating dynamic sql to see the resulting string first before executing anything, beacuase the syntax often obscures little bugs like this, especially when quotes need to be part of the string!!
Your second attempt tells me you don't grok the square brackets [] these are in case anyone tries to create a column name with a space in it.
Last edited by hollystyles : May 23rd, 2008 at 11:01 am.
•
•
Join Date: Aug 2007
Posts: 98
Reputation:
Rep Power: 2
Solved Threads: 0
Junior Poster in Training
I keep getting the same error. I tried taking the datatype part out and putting an actual datatype in the code but i got this:-
"Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '0'.
/sqlsite/adminaddnewcolumn.asp, line 47"
I dont understand!
I used this code:-
and also changed SELECT back to EXEC which also did not work.
The code for the stored proc seems to work its when I go to run the web page im getting these errors but the error points to SQL Server and I cant see a problem with my ASP code.
Thanks again for your help!!!!
"Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '0'.
/sqlsite/adminaddnewcolumn.asp, line 47"
I dont understand!
I used this code:-
CREATE PROCEDURE sp_addcolumn -- Add the parameters for the stored procedure here @TABLE_NAME VARCHAR(50) = 0, @columnname VARCHAR(50) = 0, @datatype VARCHAR (50) = 0 AS DECLARE @tsql VARCHAR (200) SET @tsql = 'ALTER TABLE [ ' + @TABLE_NAME + '] ADD [ ' + @columnname + ' ] ' + @datatype SELECT @tsql
and also changed SELECT back to EXEC which also did not work.
The code for the stored proc seems to work its when I go to run the web page im getting these errors but the error points to SQL Server and I cant see a problem with my ASP code.
Thanks again for your help!!!!
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,165
Reputation:
Rep Power: 7
Solved Threads: 59
The table must exist in the database.
Also you have now introduced spaces between the [ and the table name, you need to remove those, same for the column name unless you want lots of subtle bugs in your app cos the column names have leading spaces in their names. USE MY CODE TO TO THE LETTER and you will not have any problems. It's important to make these mistakes though otherwise you would not have learned the importance of white space in some parts of TSQL syntax. Remember computers are not like us, they are stupifyingly exacting !! one byte out of place and you're stiffed.
Also you have now introduced spaces between the [ and the table name, you need to remove those, same for the column name unless you want lots of subtle bugs in your app cos the column names have leading spaces in their names. USE MY CODE TO TO THE LETTER and you will not have any problems. It's important to make these mistakes though otherwise you would not have learned the importance of white space in some parts of TSQL syntax. Remember computers are not like us, they are stupifyingly exacting !! one byte out of place and you're stiffed.
Last edited by hollystyles : May 23rd, 2008 at 12:09 pm.
•
•
Join Date: Aug 2007
Posts: 98
Reputation:
Rep Power: 2
Solved Threads: 0
Junior Poster in Training
what i have now is:-
I am following your code which is why i dont understand where i have gone wrong. I have take the spaces out.
the error that i am getting now is:-
'Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][SQL Native Client][SQL Server] Incorrect syntax near '0''
Any ideas would be great!
Thanks!
GLT
CREATE PROCEDURE sp_addcolumn -- Add the parameters for the stored procedure here @TABLE_NAME VARCHAR(50) = 0, @columnname VARCHAR(50) = 0, @datatype VARCHAR (50) = 0 AS DECLARE @tsql VARCHAR (200) SET @tsql = 'ALTER TABLE ['+@TABLE_NAME+'] ADD ['+@columnname+']'+@datatype EXEC @tsql GO
I am following your code which is why i dont understand where i have gone wrong. I have take the spaces out.
the error that i am getting now is:-
'Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][SQL Native Client][SQL Server] Incorrect syntax near '0''
Any ideas would be great!
Thanks!
GLT
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,165
Reputation:
Rep Power: 7
Solved Threads: 59
(Holly pulls his hair out ....) I see an important space disappeared in my second post! gah! ok use this:
Spot the difference:
Spot the difference:
--CORRECT CODE SET @tsql = 'ALTER TABLE [' + @tablename + '] ADD [' + @columnname + '] ' + @datatype
--SYNTAX ERROR !!! SET @tsql = 'ALTER TABLE ['+@TABLE_NAME+'] ADD ['+@columnname+']'+@datatype
•
•
Join Date: Aug 2007
Posts: 98
Reputation:
Rep Power: 2
Solved Threads: 0
Junior Poster in Training
Sorry for making you pull your hair out! 
You've been a great help!
What i have now is:-
and getting the error:-
The name 'ALTER TABLE [] ADD []' is not a valid identifier.
I tried removing the [] but i got the error message:-
Could not find stored procedure 'ALTER TABLE ADD'
I must be doing something wrong...??...
Sorry again!!
GLT
You've been a great help!
What i have now is:-
CREATE PROCEDURE sp_addcolumn -- Add the parameters for the stored procedure here @TABLE_NAME VARCHAR(50) = 0, @columnname VARCHAR(50) = 0, @datatype VARCHAR (50) = 0 AS DECLARE @tsql VARCHAR (200) SET @tsql = 'ALTER TABLE [' + @TABLE_NAME + '] ADD [' + @columnname + '] ' + @datatype EXEC @tsql GO
and getting the error:-
The name 'ALTER TABLE [] ADD []' is not a valid identifier.
I tried removing the [] but i got the error message:-
Could not find stored procedure 'ALTER TABLE ADD'
I must be doing something wrong...??...
Sorry again!!
GLT
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,165
Reputation:
Rep Power: 7
Solved Threads: 59
(Holyl is now bald as a coot)
I think
and getting the error:-
The name 'ALTER TABLE [] ADD []' is not a valid identifier.
To me this means you're passing zero length strings for the @TABLE_NAME and @columnname parameters.
I think
EXEC @tsql needs to be EXEC(@tsql) with parenthesis.and getting the error:-
The name 'ALTER TABLE [] ADD []' is not a valid identifier.
To me this means you're passing zero length strings for the @TABLE_NAME and @columnname parameters.
Last edited by hollystyles : May 27th, 2008 at 10:28 am.
|
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
- Populating a dropdown with data from an SqlServer stored procedure (ASP.NET)
- memory management in wndows 2000 (Windows NT / 2000 / XP / 2003)
- How can i retrieve data after i logod on..... (ASP.NET)
- UPDATE question for ms sql 2000 (MS SQL)
Other Threads in the MS SQL Forum
- Previous Thread: Preventing the repetition of charges when adding to columns?
- Next Thread: putting rows in colunm format
