Creating tables from ASP.

Hi list, i wanto to use an ASP page to create tables from the devolopers
intranet.
Inside Code, i have the following conn string:
set objRS = CreateObject("ADODB.Recordset")
objcon.connectionstring = "Provider=SQLOLEDB.1;User
ID=sa;Password=XXXXXX;Persist Security Info=True;Initial Catalog=master;Data
Source=" & server
I have one string called sqltxt that uses the Create table statement. this
is the execution code: "3704,Operation is not allowed when the object is
closed"
Whats wrong'
Thanks.
objRS.open sqltxt, objcon
response.write err.number & "," & err.description & "<p>"
response.write sqltxt
ObjRS.CLOSE
When i try to execute this, i recieve the message :As a general suggestion, consider using a ADO command object instead of a
recordset object.
Anith|||Can u tell me how?
"Anith Sen" wrote:

> As a general suggestion, consider using a ADO command object instead of a
> recordset object.
> --
> Anith
>
>|||Assuming you really wanted to do this, you should use the Execute method off
the
Connection object like so:
(ah VBScript...Forgot how joyous it was <insert sarcasm> ;-> )
Dim oConn
Dim sSQL
Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = ...
oConn.Open
sSQL = "Create Table...
oConn.Execute sSQL
Set oConn = Nothing
HTH
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:1CBE6886-30B6-4FD2-995E-DFFEC08D7123@.microsoft.com...
> Hi list, i wanto to use an ASP page to create tables from the devolopers
> intranet.
> Inside Code, i have the following conn string:
> set objRS = CreateObject("ADODB.Recordset")
> objcon.connectionstring = "Provider=SQLOLEDB.1;User
> ID=sa;Password=XXXXXX;Persist Security Info=True;Initial Catalog=master;Da
ta
> Source=" & server
> I have one string called sqltxt that uses the Create table statement. this
> is the execution code: "3704,Operation is not allowed when the object is
> closed"
> Whats wrong'
> Thanks.
> objRS.open sqltxt, objcon
> response.write err.number & "," & err.description & "<p>"
> response.write sqltxt
> ObjRS.CLOSE
> When i try to execute this, i recieve the message :|||Thanks for help Thomas, it works fine!!!
Now, i have a doubt.
When the transaction occurs, i use the err.number and err.description to see
any errors, but i coul not view anything. what do i need to see the "The
command(s) completed successfully."
Thanks again
"Thomas Coleman" wrote:

> Assuming you really wanted to do this, you should use the Execute method o
ff the
> Connection object like so:
> (ah VBScript...Forgot how joyous it was <insert sarcasm> ;-> )
> Dim oConn
> Dim sSQL
> Set oConn = CreateObject("ADODB.Connection")
> oConn.ConnectionString = ...
> oConn.Open
> sSQL = "Create Table...
> oConn.Execute sSQL
> Set oConn = Nothing
>
> HTH
>
> Thomas
>
>
> "Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
> news:1CBE6886-30B6-4FD2-995E-DFFEC08D7123@.microsoft.com...
>
>|||> When the transaction occurs, i use the err.number and err.description to seed">
> any errors, but i coul not view anything. what do i need to see the "The
> command(s) completed successfully."
From ASP? Of course not. From ADO code, you know that a command executed
successfully if an error was not thrown. If we are talking about VBScript,
you'll have to check for Err.Number right after you execute the statement li
ke
so:
sSQL = "Create Table...
oConn.Execute sSQL
If Err.Number <> 0 Then
..call error handler or do whatever in response to an error...
End If
Set oConn = Nothing
Since we're talking about the creation of the table, if the create table cal
l to
ADO fired successfully, then you should be ok to query against it or put dat
a in
it.
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:A2338E8C-02A9-48DB-936C-325B78F9E811@.microsoft.com...
> Thanks for help Thomas, it works fine!!!
> Now, i have a doubt.
> When the transaction occurs, i use the err.number and err.description to s
ee
> any errors, but i coul not view anything. what do i need to see the "The
> command(s) completed successfully."
> Thanks again
> "Thomas Coleman" wrote:
>
<snip>|||Tinchos,

> Hi list, i wanto to use an ASP page to create tables from the devolopers
> intranet.
> ...
> Whats wrong'
Although Thomas already answered your question, you should really rethink
your approach. Very little good can come from allowing tables to be created
by the application ... why are you doing this in the first place?
-- Alex Papadimoulis|||I want to offer a service to developers (only datareader and datawriters) th
e
possibility to create custom tables or execute some scripts. I think its the
best solution to have less workload for me.
"Alex Papadimoulis" wrote:

> Tinchos,
>
> Although Thomas already answered your question, you should really rethink
> your approach. Very little good can come from allowing tables to be create
d
> by the application ... why are you doing this in the first place?
> -- Alex Papadimoulis|||So, how can i parse errors like "Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'table' in the database" '
Objcon.execute sqltxt
if err.number = 0 then
Response.write "Ok...
else
response.write err.number & "," & err.description & "<p>"
end if
if i try to create the table twice i dont recieve any error messages...
Thanks again!!!!|||Firstly, the system will throw an error if you execute a Create Table statem
ent
and the table exists. You need to be mindful of the *owner* of the table.
Specifically, make sure you prefix the table name with the owner like so:
Create Table dbo.TableName
(
..
)
Otherwise, the table will be created under the user's database name. So you
might get Joe.TableName, Fred.TableName and even dbo.TableName.
Secondly, you need to look at the Errors collection on the Connection object
and
trap the specific error number associated with the a duplicate table name.
Thus..
oConn.Execute sSQL
Select Case Err.Number
Case 0
'do nothing
Case 12344 <-- place the actual error number here
..tell user there was a duplicate
Case Else
..display general error
End Select
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:AB4A1EB6-1374-43DD-9722-48BEA85AF2D5@.microsoft.com...
> So, how can i parse errors like "Server: Msg 2714, Level 16, State 6, Line
1
> There is already an object named 'table' in the database" '
> Objcon.execute sqltxt
> if err.number = 0 then
> Response.write "Ok...
> else
> response.write err.number & "," & err.description & "<p>"
> end if
> if i try to create the table twice i dont recieve any error messages...
> Thanks again!!!!