|
 |
asp_databases thread: trying to add a column
Message #1 by "Monique Angelich" <mangelich@m...> on Tue, 25 Sep 2001 00:15:09 -0400
|
|
I am trying to alter a table, add a column.. I get incorrect syntax near (
This is my code:
rsTemp = Server.CreateObject("ADODB.Connection")
sql="ALTER TABLE MainCategory ADD (hasChild integer NULL)"
set rsTemp = session("db_con").execute(sql)
sql="Update maincategory set hasChild=0 where id=1"
set rsTemp = session("db_con").execute(sql)
sql="select * from maincategory where hasChild=0"
set rsTemp = session("db_con").execute(sql)
if not rsTemp.EOF then
response.write("worked")
end if
the connection is NOT the problem. SQL 7 is what I am running against..
If the user id and password didn't have authority, wouldn't I get a
different error??
Please help, I am obsessed with this.. if I don't get it working I can't
sleep... and I am so tired!
Message #2 by David Cameron <dcameron@i...> on Tue, 25 Sep 2001 15:34:46 +1000
|
|
Which of the lines gives you an error?
suggestions:
1. You are using a reserved keyword (ID) with your SQL Select statement. For
a list of reserved keywords look into BOL (Books Online).
2. Use cn.Execute to execute the SQL strings that don't return a recordset.
3. Never store objects in Session or Application level variable.
4. Use stored procedures to execute SQL strings. They are precompiled and
faster, and also give a lot more options in executing code.
If you are fighting SQL7, generally you are doing something wrong. It is on
your side. <grin>
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Monique Angelich [mailto:mangelich@m...]
Sent: Tuesday, 25 September 2001 2:15 PM
To: ASP Databases
Subject: [asp_databases] trying to add a column
I am trying to alter a table, add a column.. I get incorrect syntax near (
This is my code:
rsTemp = Server.CreateObject("ADODB.Connection")
sql="ALTER TABLE MainCategory ADD (hasChild integer NULL)"
set rsTemp = session("db_con").execute(sql)
sql="Update maincategory set hasChild=0 where id=1"
set rsTemp = session("db_con").execute(sql)
sql="select * from maincategory where hasChild=0"
set rsTemp = session("db_con").execute(sql)
if not rsTemp.EOF then
response.write("worked")
end if
the connection is NOT the problem. SQL 7 is what I am running against..
If the user id and password didn't have authority, wouldn't I get a
different error??
Please help, I am obsessed with this.. if I don't get it working I can't
sleep... and I am so tired!
Message #3 by "Monique Angelich" <mangelich@m...> on Tue, 25 Sep 2001 09:06:31 -0400
|
|
The line that gives me an issue is the alter table statement.. It LOOKS
right.. so thinking it is a permission issue??
I know session level con strings are not generally accepted. It is just
easier during development.
I don't have a db access tool yet.. so stored procs aren't possible at this
point. I am severly handicapped with what I can do.. and have to request
help when I need to set up tables and permissions.
Anyhow.. the alter table statement is correct in syntax??? it is that string
alone that is a problem so far as I can tell at this point.
----- Original Message -----
From: "David Cameron" <dcameron@i...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, September 25, 2001 1:34 AM
Subject: [asp_databases] RE: trying to add a column
>
>
> Which of the lines gives you an error?
>
> suggestions:
> 1. You are using a reserved keyword (ID) with your SQL Select statement.
For
> a list of reserved keywords look into BOL (Books Online).
> 2. Use cn.Execute to execute the SQL strings that don't return a
recordset.
> 3. Never store objects in Session or Application level variable.
> 4. Use stored procedures to execute SQL strings. They are precompiled and
> faster, and also give a lot more options in executing code.
>
> If you are fighting SQL7, generally you are doing something wrong. It is
on
> your side. <grin>
>
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
>
> -----Original Message-----
> From: Monique Angelich [mailto:mangelich@m...]
> Sent: Tuesday, 25 September 2001 2:15 PM
> To: ASP Databases
> Subject: [asp_databases] trying to add a column
>
>
> I am trying to alter a table, add a column.. I get incorrect syntax near (
>
> This is my code:
>
> rsTemp = Server.CreateObject("ADODB.Connection")
>
> sql="ALTER TABLE MainCategory ADD (hasChild integer NULL)"
> set rsTemp = session("db_con").execute(sql)
>
> sql="Update maincategory set hasChild=0 where id=1"
> set rsTemp = session("db_con").execute(sql)
>
> sql="select * from maincategory where hasChild=0"
> set rsTemp = session("db_con").execute(sql)
>
> if not rsTemp.EOF then
> response.write("worked")
> end if
>
> the connection is NOT the problem. SQL 7 is what I am running against..
> If the user id and password didn't have authority, wouldn't I get a
> different error??
>
> Please help, I am obsessed with this.. if I don't get it working I can't
> sleep... and I am so tired!
Message #4 by "Drew, Ron" <RDrew@B...> on Tue, 25 Sep 2001 14:48:14 -0400
|
|
You may have to make the statement more explicit for database and
owner(leave the [] out of the example below)...also..I do not think you need
the ( ) around the new column you are adding...I also believe you can put a
default when you create it if you want (you know the 80/20 rule)...
i.e.
sql = "ALTER TABLE [database.[owner].]MainCategory ADD hasChild integer NULL
DEFAULT(0)"
-----Original Message-----
From: Monique Angelich [mailto:mangelich@m...]
Sent: Tuesday, September 25, 2001 9:07 AM
To: ASP Databases
Subject: [asp_databases] RE: trying to add a column
The line that gives me an issue is the alter table statement.. It LOOKS
right.. so thinking it is a permission issue??
I know session level con strings are not generally accepted. It is just
easier during development.
I don't have a db access tool yet.. so stored procs aren't possible at this
point. I am severly handicapped with what I can do.. and have to request
help when I need to set up tables and permissions.
Anyhow.. the alter table statement is correct in syntax??? it is that string
alone that is a problem so far as I can tell at this point.
----- Original Message -----
From: "David Cameron" <dcameron@i...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, September 25, 2001 1:34 AM
Subject: [asp_databases] RE: trying to add a column
>
>
> Which of the lines gives you an error?
>
> suggestions:
> 1. You are using a reserved keyword (ID) with your SQL Select statement.
For
> a list of reserved keywords look into BOL (Books Online).
> 2. Use cn.Execute to execute the SQL strings that don't return a
recordset.
> 3. Never store objects in Session or Application level variable.
> 4. Use stored procedures to execute SQL strings. They are precompiled and
> faster, and also give a lot more options in executing code.
>
> If you are fighting SQL7, generally you are doing something wrong. It is
on
> your side. <grin>
>
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
>
> -----Original Message-----
> From: Monique Angelich [mailto:mangelich@m...]
> Sent: Tuesday, 25 September 2001 2:15 PM
> To: ASP Databases
> Subject: [asp_databases] trying to add a column
>
>
> I am trying to alter a table, add a column.. I get incorrect syntax near (
>
> This is my code:
>
> rsTemp = Server.CreateObject("ADODB.Connection")
>
> sql="ALTER TABLE MainCategory ADD (hasChild integer NULL)"
> set rsTemp = session("db_con").execute(sql)
>
> sql="Update maincategory set hasChild=0 where id=1"
> set rsTemp = session("db_con").execute(sql)
>
> sql="select * from maincategory where hasChild=0"
> set rsTemp = session("db_con").execute(sql)
>
> if not rsTemp.EOF then
> response.write("worked")
> end if
>
> the connection is NOT the problem. SQL 7 is what I am running against..
> If the user id and password didn't have authority, wouldn't I get a
> different error??
>
> Please help, I am obsessed with this.. if I don't get it working I can't
> sleep... and I am so tired!
$subst('Email.Unsub')
Message #5 by David Cameron <dcameron@i...> on Wed, 26 Sep 2001 09:14:57 +1000
|
|
The syntax seems to be OK in query analyser. Unless the table name is wrong
they I can't see any reasons for errors. Ron has made some good suggestions
that may help. You might be right about the permission issue. What exactly
is the error message?
You should develop as you plan to continue. I keep getting bitten by
problems I have created when making things easier for development.
Recommended option is to create an include file, something like this:
<%
Dim cn
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "<Your connection string>"
%>
and include it in each page. Don't forget the close and set to nothing at
the bottom of each page. This could become a problem (as it did for us) if
you open up too many recordsets, but I imagine that won't be problem for
quite a while.
How can you have SQL Server without client tools? To develop using SQL
Server and not to use stored procs is crazy. It is like owning a car and
travelling around by pushing it. If for some reason you can't get client
tools the first thing I would do is write some basic ones. So a quick VB
program to execute a SQL string and to return any error messages. If you
don't own VB or don't program in it you could write a couple of ASP pages to
do the same work. It would seriously pay off in the time spent on it.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Monique Angelich [mailto:mangelich@m...]
Sent: Tuesday, 25 September 2001 11:07 PM
To: ASP Databases
Subject: [asp_databases] RE: trying to add a column
The line that gives me an issue is the alter table statement.. It LOOKS
right.. so thinking it is a permission issue??
I know session level con strings are not generally accepted. It is just
easier during development.
I don't have a db access tool yet.. so stored procs aren't possible at this
point. I am severly handicapped with what I can do.. and have to request
help when I need to set up tables and permissions.
Anyhow.. the alter table statement is correct in syntax??? it is that string
alone that is a problem so far as I can tell at this point.
----- Original Message -----
From: "David Cameron" <dcameron@i...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, September 25, 2001 1:34 AM
Subject: [asp_databases] RE: trying to add a column
>
>
> Which of the lines gives you an error?
>
> suggestions:
> 1. You are using a reserved keyword (ID) with your SQL Select statement.
For
> a list of reserved keywords look into BOL (Books Online).
> 2. Use cn.Execute to execute the SQL strings that don't return a
recordset.
> 3. Never store objects in Session or Application level variable.
> 4. Use stored procedures to execute SQL strings. They are precompiled and
> faster, and also give a lot more options in executing code.
>
> If you are fighting SQL7, generally you are doing something wrong. It is
on
> your side. <grin>
>
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
>
Message #6 by "Ken Schaefer" <ken@a...> on Wed, 26 Sep 2001 13:38:15 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Monique Angelich" <mangelich@m...>
Subject: [asp_databases] RE: trying to add a column
: I know session level con strings are not generally accepted. It is just
: easier during development.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
There is a difference between a session level *string* and a session level
*object*. You aren't storing a string in a session variable, you are storing
an object - and that's bad. You need to understand the difference. I don't
know if what you wrote was just a slip, but if it wasn't, then I suggest you
post to get clarification if required.
Cheers
Ken
|
|
|
View Cart
