|
 |
asp_databases thread: Connecting & Running T-SQL INSERT, UPDATE or DELETE
Message #1 by "Pete" <petemiles_misc@h...> on Mon, 23 Apr 2001 20:56:39
|
|
Hitting a major flaw in the two books I've been using
in that the asp one (and all online docs it seems) think
you are using an Access database & non-SQL INSERT
statements etc) and the SQL book imagines that you
are mostly using the Query Analyser and doesn't
instruct on the different procedures to setup access
to the db for the different kind of situations you'll
encounter. (Beginning ASP & Professional SQL - Wrox)
I try using ADODB Connection or Recordset and
get errors all over the shop. Recordset not open
when I've opened it etc. I'm also running into a problem
of found some info on getting an okay read & write to the
db (which works), but it seems I cannot get the query
to run longer than one line without getting an error.
I'm unfamiliar with the code to extend it to more than
one line. Perhaps the approach is wrong.
The two books are a bit of a mismatch and that's
the errors I'm getting when I try to cobble together
a way to create a record, search for an existing record,
update a record etc.
What I really need is a bunch of ready code I can
just slot it that works with any INSERT, UPDATE, DELETE
with examples for diff lock types etc. Even if I can find a
solution for one, as I've discovered in the example below
it doesn't work across lock types, cursor types etc.
The code I got from Brinkster in the end (after the book code
really didn't work) :-
<%Option Explicit%>
<HTML>
<HEAD>
<TITLE>Testing Givn Connection</TITLE>
</HEAD>
<BODY>
<%
Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
adOpenForwardOnly = 0
adLockReadOnly = 1
adCmdTable = 2
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
Dim connstr
connstr = "Provider=SQLOLEDB;Data
Source=www.anyaddress.com;DATABASE=dbname;UID=username;PWD=password"
conn.Open connstr
Dim rs, strSQL, i
' STORING DATA!!
strSQL = "INSERT INTO Test (Field1, Field2, Field3) VALUES
('Flipper', 'Dolphin', 'Everest')"
Set rs = conn.Execute(strSQL)
strSQL = "INSERT INTO Test (Field1, Field2, Field3) VALUES
('Guy', 'Andy', 'Ben')"
Set rs = conn.Execute(strSQL)
Set rs = conn.Execute("INSERT INTO Test (Field1, Field2, Field3) VALUES
('Good Fellas', 'Mobsters', 'Casino')")
' RETRIEVING DATA!!
strSQL = "SELECT * FROM Test"
Set rs = conn.Execute(strSQL)
Response.write("<table><tr>")
For i = 0 to rs.Fields.Count - 1
Response.write("<td>" & rs(i).Name & "</td>")
Next 'i
Response.write("</tr>")
Do while Not rs.eof
Response.write("<tr>")
For i = 0 to rs.Fields.Count - 1
Response.write("<td>" & rs(i) & "</td>")
Next 'i
Response.write("</tr>")
rs.MoveNext
Loop
Response.write("</table>")
Set rs = nothing
conn.close
Set conn = nothing
%>
</BODY>
</HTML>
Okay, so that worked. But the " strSQL = "INSERT INTO Test (Field1,
Field2, Field3) VALUES ('Flipper', 'Dolphin', 'Everest')" " only allows
for an entry on one line and I cannot extend it to a longer entry.
What I end up with is a lot of Execute commands and I presume each Execute
is creating a new record, so as it stands I can only create on one line a
record with 3 field (no good!) Further, I understand Execute is only good
for forward only cursor too.
Now this code below was what the books described and didn't work:
(SSI)
<%
Dim strConnect
'Create the database connection string
strConnect = "Provider=SQLOLEDB;Data
Source=www.anydomain.com;DATABASE=dbname;UID=username;PWD=password"
'Connection string is all on one line in the file
%>
(CODE)
<%
Option Explicit
%>
<!-- Collect connection string for the database from central location -->
<!-- include virtual="/ssi/Connect.asp -->
<html>
<head>
<title>Database Write Test</title>
</head>
<body>
<%
Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
adOpenForwardOnly = 0
adLockReadOnly = 1
adCmdTable = &H0002
Dim strConnect
Dim objCommand, objRS
Set objCommand = Server.CreateObject("ADODB.Command")
'Command properties
objCommand.ActiveConnection = strConnect
objCommand.CommandText = "Test"
objCommand.CommandType = adCmdTable
'Now execute the command and capture the selected records in the recordset
Set objRS = objCommand.Execute
'Now Command object has done it's job, so clean it up
Set objCommand = Nothing
'Now loop through the records
While Not objRS.EOF
Response.Write objRS("Field1")
Response.Write objRS("Field2")
Response.Write objRS("Field3")
objRS.MoveNext
Wend
'Now close & clean up
objRS.Close
Set objRS = Nothing
%>
</body>
</html>
Cheers,
Pete
Message #2 by "Charles Feduke" <webmaster@r...> on Mon, 23 Apr 2001 20:13:23 -0400
|
|
You won't gain any performance over using the Recordset's ability to commit
data to the database than using raw SQL. This is because all data that goes
thru ADO must first be converted to SQL 99a or SQL 92 standard by the ODBC or
OLE DB driver, then converted to T-SQL (if you're using MS SQL Server). You
don't actually send objects to SQL Server (you do if you're using an OLE DB...
well you send objects to the OLE DB driver which breaks them down into T-SQL).
If you want to do a bunch of INSERT statements all at once on SQL Server, try:
sSQL = "BEGIN TRANSACTION blah; " & _
"INSERT INTO Test (Field1, Field2, Field3) VALUES ("one", "two", "three"); "
& _
"INSERT INTO Test (Field1, Field2, Field3) VALUES ("one", "two", "three"); "
& _
' ...
sSQL = sSQL & "COMMIT TRANSACTION blah;"
You don't even need to set a Recordset Object for the return:
conConnection.Execute sSQL, , adExecuteNoRecords + adCmdText
In fact if your Test table *ONLY* has three fields (Field1, Field2, Field3)
and they appear in that exact order in the database, you don't even need to
specify the field names:
INSERT INTO Test VALUES ("one", "two", "three");
Hope that helps.
- Chuck
----- Original Message -----
From: "Pete" <petemiles_misc@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, April 23, 2001 8:56 PM
Subject: [asp_databases] Connecting & Running T-SQL INSERT, UPDATE or DELETE
> Hitting a major flaw in the two books I've been using
> in that the asp one (and all online docs it seems) think
> you are using an Access database & non-SQL INSERT
> statements etc) and the SQL book imagines that you
> are mostly using the Query Analyser and doesn't
> instruct on the different procedures to setup access
> to the db for the different kind of situations you'll
> encounter. (Beginning ASP & Professional SQL - Wrox)
>
> I try using ADODB Connection or Recordset and
> get errors all over the shop. Recordset not open
> when I've opened it etc. I'm also running into a problem
> of found some info on getting an okay read & write to the
> db (which works), but it seems I cannot get the query
> to run longer than one line without getting an error.
> I'm unfamiliar with the code to extend it to more than
> one line. Perhaps the approach is wrong.
>
> The two books are a bit of a mismatch and that's
> the errors I'm getting when I try to cobble together
> a way to create a record, search for an existing record,
> update a record etc.
>
> What I really need is a bunch of ready code I can
> just slot it that works with any INSERT, UPDATE, DELETE
> with examples for diff lock types etc. Even if I can find a
> solution for one, as I've discovered in the example below
> it doesn't work across lock types, cursor types etc.
>
>
> The code I got from Brinkster in the end (after the book code
> really didn't work) :-
>
> <%Option Explicit%>
> <HTML>
> <HEAD>
> <TITLE>Testing Givn Connection</TITLE>
> </HEAD>
> <BODY>
>
> <%
> Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
> adOpenForwardOnly = 0
> adLockReadOnly = 1
> adCmdTable = 2
>
>
> Dim conn
> Set conn = Server.CreateObject("ADODB.Connection")
>
> Dim connstr
> connstr = "Provider=SQLOLEDB;Data
> Source=www.anyaddress.com;DATABASE=dbname;UID=username;PWD=password"
>
> conn.Open connstr
>
>
> Dim rs, strSQL, i
>
> ' STORING DATA!!
> strSQL = "INSERT INTO Test (Field1, Field2, Field3) VALUES
> ('Flipper', 'Dolphin', 'Everest')"
> Set rs = conn.Execute(strSQL)
> strSQL = "INSERT INTO Test (Field1, Field2, Field3) VALUES
> ('Guy', 'Andy', 'Ben')"
> Set rs = conn.Execute(strSQL)
> Set rs = conn.Execute("INSERT INTO Test (Field1, Field2, Field3) VALUES
> ('Good Fellas', 'Mobsters', 'Casino')")
>
>
> ' RETRIEVING DATA!!
> strSQL = "SELECT * FROM Test"
> Set rs = conn.Execute(strSQL)
>
>
> Response.write("<table><tr>")
> For i = 0 to rs.Fields.Count - 1
> Response.write("<td>" & rs(i).Name & "</td>")
> Next 'i
> Response.write("</tr>")
> Do while Not rs.eof
> Response.write("<tr>")
> For i = 0 to rs.Fields.Count - 1
> Response.write("<td>" & rs(i) & "</td>")
> Next 'i
> Response.write("</tr>")
> rs.MoveNext
> Loop
> Response.write("</table>")
>
>
>
> Set rs = nothing
> conn.close
> Set conn = nothing
>
> %>
>
> </BODY>
> </HTML>
>
>
>
>
>
> Okay, so that worked. But the " strSQL = "INSERT INTO Test (Field1,
> Field2, Field3) VALUES ('Flipper', 'Dolphin', 'Everest')" " only allows
> for an entry on one line and I cannot extend it to a longer entry.
>
> What I end up with is a lot of Execute commands and I presume each Execute
> is creating a new record, so as it stands I can only create on one line a
> record with 3 field (no good!) Further, I understand Execute is only good
> for forward only cursor too.
>
>
> Now this code below was what the books described and didn't work:
>
> (SSI)
>
> <%
>
> Dim strConnect
>
> 'Create the database connection string
>
> strConnect = "Provider=SQLOLEDB;Data
> Source=www.anydomain.com;DATABASE=dbname;UID=username;PWD=password"
>
> 'Connection string is all on one line in the file
>
> %>
>
>
>
> (CODE)
>
> <%
> Option Explicit
> %>
>
> <!-- Collect connection string for the database from central location -->
>
> <!-- include virtual="/ssi/Connect.asp -->
>
> <html>
> <head>
> <title>Database Write Test</title>
> </head>
>
> <body>
>
> <%
>
> Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
> adOpenForwardOnly = 0
> adLockReadOnly = 1
> adCmdTable = &H0002
>
> Dim strConnect
> Dim objCommand, objRS
>
> Set objCommand = Server.CreateObject("ADODB.Command")
>
>
> 'Command properties
>
> objCommand.ActiveConnection = strConnect
> objCommand.CommandText = "Test"
> objCommand.CommandType = adCmdTable
>
>
> 'Now execute the command and capture the selected records in the recordset
>
> Set objRS = objCommand.Execute
>
>
> 'Now Command object has done it's job, so clean it up
>
> Set objCommand = Nothing
>
>
> 'Now loop through the records
>
> While Not objRS.EOF
> Response.Write objRS("Field1")
> Response.Write objRS("Field2")
> Response.Write objRS("Field3")
> objRS.MoveNext
> Wend
>
>
> 'Now close & clean up
> objRS.Close
> Set objRS = Nothing
>
> %>
>
> </body>
> </html>
>
>
> Cheers,
>
> Pete
Message #3 by "Pete" <petemiles_misc@h...> on Tue, 24 Apr 2001 01:55:56
|
|
Hey Chuck,
Well I was using an OLEDB Connection.
Perhaps I should switch to something easier to use.
In terms of working outside of that, what do you suggest.
I was trying to run statements including specifying Recordset or
Connection and on occasion I was getting errors that the connection I just
opened wasn't opened after all.
Do you have a sample of some record inserts, selects or deletes that I
could use with an SQL db? The mixing and matching wasn't working, so I
need to see something concreate that works and then I can examine that a
bit (the books didn't have examples that worked on SQL).
Pete
> You won't gain any performance over using the Recordset's ability to
commit
> data to the database than using raw SQL. This is because all data that
goes
> thru ADO must first be converted to SQL 99a or SQL 92 standard by the
ODBC or
> OLE DB driver, then converted to T-SQL (if you're using MS SQL Server).
You
> don't actually send objects to SQL Server (you do if you're using an OLE
DB...
> well you send objects to the OLE DB driver which breaks them down into T-
SQL).
> If you want to do a bunch of INSERT statements all at once on SQL
Server, try:
>
> sSQL = "BEGIN TRANSACTION blah; " & _
> "INSERT INTO Test (Field1, Field2, Field3) VALUES
("one", "two", "three"); "
> & _
> "INSERT INTO Test (Field1, Field2, Field3) VALUES
("one", "two", "three"); "
> & _
> ' ...
> sSQL = sSQL & "COMMIT TRANSACTION blah;"
>
> You don't even need to set a Recordset Object for the return:
>
> conConnection.Execute sSQL, , adExecuteNoRecords + adCmdText
>
> In fact if your Test table *ONLY* has three fields (Field1, Field2,
Field3)
> and they appear in that exact order in the database, you don't even need
to
> specify the field names:
>
> INSERT INTO Test VALUES ("one", "two", "three");
>
> Hope that helps.
>
> - Chuck
>
> ----- Original Message -----
> From: "Pete" <petemiles_misc@h...>
> To: "ASP Databases" <asp_databases@p...>
> Sent: Monday, April 23, 2001 8:56 PM
> Subject: [asp_databases] Connecting & Running T-SQL INSERT, UPDATE or
DELETE
>
>
> > Hitting a major flaw in the two books I've been using
> > in that the asp one (and all online docs it seems) think
> > you are using an Access database & non-SQL INSERT
> > statements etc) and the SQL book imagines that you
> > are mostly using the Query Analyser and doesn't
> > instruct on the different procedures to setup access
> > to the db for the different kind of situations you'll
> > encounter. (Beginning ASP & Professional SQL - Wrox)
> >
> > I try using ADODB Connection or Recordset and
> > get errors all over the shop. Recordset not open
> > when I've opened it etc. I'm also running into a problem
> > of found some info on getting an okay read & write to the
> > db (which works), but it seems I cannot get the query
> > to run longer than one line without getting an error.
> > I'm unfamiliar with the code to extend it to more than
> > one line. Perhaps the approach is wrong.
> >
> > The two books are a bit of a mismatch and that's
> > the errors I'm getting when I try to cobble together
> > a way to create a record, search for an existing record,
> > update a record etc.
> >
> > What I really need is a bunch of ready code I can
> > just slot it that works with any INSERT, UPDATE, DELETE
> > with examples for diff lock types etc. Even if I can find a
> > solution for one, as I've discovered in the example below
> > it doesn't work across lock types, cursor types etc.
> >
> >
> > The code I got from Brinkster in the end (after the book code
> > really didn't work) :-
> >
> > <%Option Explicit%>
> > <HTML>
> > <HEAD>
> > <TITLE>Testing Givn Connection</TITLE>
> > </HEAD>
> > <BODY>
> >
> > <%
> > Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
> > adOpenForwardOnly = 0
> > adLockReadOnly = 1
> > adCmdTable = 2
> >
> >
> > Dim conn
> > Set conn = Server.CreateObject("ADODB.Connection")
> >
> > Dim connstr
> > connstr = "Provider=SQLOLEDB;Data
> > Source=www.anyaddress.com;DATABASE=dbname;UID=username;PWD=password"
> >
> > conn.Open connstr
> >
> >
> > Dim rs, strSQL, i
> >
> > ' STORING DATA!!
> > strSQL = "INSERT INTO Test (Field1, Field2, Field3) VALUES
> > ('Flipper', 'Dolphin', 'Everest')"
> > Set rs = conn.Execute(strSQL)
> > strSQL = "INSERT INTO Test (Field1, Field2, Field3) VALUES
> > ('Guy', 'Andy', 'Ben')"
> > Set rs = conn.Execute(strSQL)
> > Set rs = conn.Execute("INSERT INTO Test (Field1, Field2, Field3) VALUES
> > ('Good Fellas', 'Mobsters', 'Casino')")
> >
> >
> > ' RETRIEVING DATA!!
> > strSQL = "SELECT * FROM Test"
> > Set rs = conn.Execute(strSQL)
> >
> >
> > Response.write("<table><tr>")
> > For i = 0 to rs.Fields.Count - 1
> > Response.write("<td>" & rs(i).Name & "</td>")
> > Next 'i
> > Response.write("</tr>")
> > Do while Not rs.eof
> > Response.write("<tr>")
> > For i = 0 to rs.Fields.Count - 1
> > Response.write("<td>" & rs(i) & "</td>")
> > Next 'i
> > Response.write("</tr>")
> > rs.MoveNext
> > Loop
> > Response.write("</table>")
> >
> >
> >
> > Set rs = nothing
> > conn.close
> > Set conn = nothing
> >
> > %>
> >
> > </BODY>
> > </HTML>
> >
> >
> >
> >
> >
> > Okay, so that worked. But the " strSQL = "INSERT INTO Test (Field1,
> > Field2, Field3) VALUES ('Flipper', 'Dolphin', 'Everest')" " only allows
> > for an entry on one line and I cannot extend it to a longer entry.
> >
> > What I end up with is a lot of Execute commands and I presume each
Execute
> > is creating a new record, so as it stands I can only create on one
line a
> > record with 3 field (no good!) Further, I understand Execute is only
good
> > for forward only cursor too.
> >
> >
> > Now this code below was what the books described and didn't work:
> >
> > (SSI)
> >
> > <%
> >
> > Dim strConnect
> >
> > 'Create the database connection string
> >
> > strConnect = "Provider=SQLOLEDB;Data
> > Source=www.anydomain.com;DATABASE=dbname;UID=username;PWD=password"
> >
> > 'Connection string is all on one line in the file
> >
> > %>
> >
> >
> >
> > (CODE)
> >
> > <%
> > Option Explicit
> > %>
> >
> > <!-- Collect connection string for the database from central location -
->
> >
> > <!-- include virtual="/ssi/Connect.asp -->
> >
> > <html>
> > <head>
> > <title>Database Write Test</title>
> > </head>
> >
> > <body>
> >
> > <%
> >
> > Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
> > adOpenForwardOnly = 0
> > adLockReadOnly = 1
> > adCmdTable = &H0002
> >
> > Dim strConnect
> > Dim objCommand, objRS
> >
> > Set objCommand = Server.CreateObject("ADODB.Command")
> >
> >
> > 'Command properties
> >
> > objCommand.ActiveConnection = strConnect
> > objCommand.CommandText = "Test"
> > objCommand.CommandType = adCmdTable
> >
> >
> > 'Now execute the command and capture the selected records in the
recordset
> >
> > Set objRS = objCommand.Execute
> >
> >
> > 'Now Command object has done it's job, so clean it up
> >
> > Set objCommand = Nothing
> >
> >
> > 'Now loop through the records
> >
> > While Not objRS.EOF
> > Response.Write objRS("Field1")
> > Response.Write objRS("Field2")
> > Response.Write objRS("Field3")
> > objRS.MoveNext
> > Wend
> >
> >
> > 'Now close & clean up
> > objRS.Close
> > Set objRS = Nothing
> >
> > %>
> >
> > </body>
> > </html>
> >
> >
> > Cheers,
> >
> > Pete
>
|
|
|
View Cart
