How to execute 2 sql commands in one sql query?

[Vlad]

Hello,
I need to delete some records and insert new ones right
away. Is there any way to do it within the same query
string?

Set objRecordset = Server.CreateObject("ADODB.Recordset")
QueryString = "delete from Tbl_Therapy where proceduredate
= 06/02/04; insert into tbl_therapy(caseid, procedureid,
proceduredate) values(1, 99203, 06/03/04);"

Something of this nature.
It is giving me following error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Characters found
after end of SQL statement.

Please help.
Thank you in advance.

[Bob Barrows [MVP]]

Vlad wrote:

> Hello,
> I need to delete some records and insert new ones right
> away. Is there any way to do it within the same query
> string?

No. Jet does not support batch execution of sql statements..

>
> Set objRecordset = Server.CreateObject("ADODB.Recordset")

What is the reason for this recordset object? It does not appear that you
are planning to return records from this query. Why create an expensive
recordset object?

> QueryString = "delete from Tbl_Therapy where proceduredate
> = 06/02/04; insert into tbl_therapy(caseid, procedureid,
> proceduredate) values(1, 99203, 06/03/04);"

>
> Something of this nature.
> It is giving me following error:
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC Microsoft Access Driver] Characters found
> after end of SQL statement.
>

Nothing to do with your problem, but you should switch to using the native
Jet OLEDB provider rather than the ODBC provider. See
[url]www.able-consulting.com/ado_conn.htm[/url] for examples connection strings using
the Provider for Microsoft Jet.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[nick]

Bob Barrows [MVP] wrote:

> Vlad wrote:
>

>>Hello,
>>I need to delete some records and insert new ones right
>>away. Is there any way to do it within the same query
>>string?

>
>
> No. Jet does not support batch execution of sql statements..
>
>

>>Set objRecordset = Server.CreateObject("ADODB.Recordset")

>
>
> What is the reason for this recordset object? It does not appear that you
> are planning to return records from this query. Why create an expensive
> recordset object?
>
>

>>QueryString = "delete from Tbl_Therapy where proceduredate
>>= 06/02/04; insert into tbl_therapy(caseid, procedureid,
>>proceduredate) values(1, 99203, 06/03/04);"

>
>
>

>>Something of this nature.
>>It is giving me following error:
>>Error Type:
>>Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
>>[Microsoft][ODBC Microsoft Access Driver] Characters found
>>after end of SQL statement.
>>

>
>
> Nothing to do with your problem, but you should switch to using the native
> Jet OLEDB provider rather than the ODBC provider. See
> [url]www.able-consulting.com/ado_conn.htm[/url] for examples connection strings using
> the Provider for Microsoft Jet.
>
> Bob Barrows

what's the benifit of using native Jet OleDB provider rather than the
ODBC provider if it's just a simple asp page?

[Bob Barrows [MVP]]

nick wrote:

>
> what's the benifit of using native Jet OleDB provider rather than the
> ODBC provider if it's just a simple asp page?

Excuse me? Are you saying that you think it's all right to use inefficient,
obsolete techniques on simple pages, but not all right on more complex
pages? This does not compute.

IMO, there's no such thing as a "simple asp page". Don't forget: your
"simple asp page" is probably running on a web server that delivers other
asp pages, both simple and complex, as well as html pages.
Threading,performance, and robustness must be considered in this
environment. An inefficient simple asp page that gets called millions of
times will probably perform adequately when viewed in isolation, but may
have a severe impact on the other pages and applications being served by
your web server.

MS has deprecated the OLEDB Provider for ODBC drivers (MSDASQL). This means
that new functionality, perfiormance and stability enhancements, etc. that
will be incorporated in new versions of native OLEDB providers will possibly
be left out of the MSDASQL provider.
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/ado_deprecated_components.asp[/url]

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

So, what is the way to do if I need to delete some
recordes and then insert the new ones right away?
Using objRset.delete, objRsetAddNew and objRset.update?
Or perhaps stored procedure? But this is an Access and I
am not sure how to do it in Access. Any samples or links?
Thank you.

>-----Original Message-----
>nick wrote:

>>
>> what's the benifit of using native Jet OleDB provider

rather than the

>> ODBC provider if it's just a simple asp page?

>
>Excuse me? Are you saying that you think it's all right

to use inefficient,

>obsolete techniques on simple pages, but not all right on

more complex

>pages? This does not compute.
>
>IMO, there's no such thing as a "simple asp page". Don't

forget: your

>"simple asp page" is probably running on a web server

that delivers other

>asp pages, both simple and complex, as well as html pages.
>Threading,performance, and robustness must be considered

in this

>environment. An inefficient simple asp page that gets

called millions of

>times will probably perform adequately when viewed in

isolation, but may

>have a severe impact on the other pages and applications

being served by

>your web server.
>
>MS has deprecated the OLEDB Provider for ODBC drivers

(MSDASQL). This means

>that new functionality, perfiormance and stability

enhancements, etc. that

>will be incorporated in new versions of native OLEDB

providers will possibly

>be left out of the MSDASQL provider.
>[url]http://msdn.microsoft.com/library/en-[/url]

us/ado270/htm/ado_deprecated_components.asp

>
>HTH,
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed

in my From

>header is my spam trap, so I don't check it very often.

You will get a

>quicker response by posting to the newsgroup.
>
>
>.
>

[Bob Barrows [MVP]]

Are you nick?

[email]anonymous@discussions.microsoft.com[/email] wrote:

> So, what is the way to do if I need to delete some
> recordes and then insert the new ones right away?

Just perform two executions.

QueryString = "delete from Tbl_Therapy " & _
"where proceduredate= #06/02/04#; "
conn.Execute QueryString,,129

QueryString = "insert into tbl_therapy(caseid, " & _
"procedureid,proceduredate) " & _
"values(1, 99203, #06/03/04#);"
conn.Execute QueryString,,129

> Using objRset.delete, objRsetAddNew and objRset.update?

No.

> Or perhaps stored procedure? But this is an Access and I
> am not sure how to do it in Access. Any samples or links?

Search Google for posts by me containing the words "saved parameter
queries". I have posted information about this many times.
Unfortunately, even this technique (saved parameter queries) will not save
you from having to perform two executions. If you upgrade to SQL Server, you
will get the benefit of batch execution and stored procedures.

> Thank you.

You're welcome.
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Roland Hall]

"Bob Barrows [MVP]" wrote in message
news:eKhoV5GVEHA.3332@tk2msftngp13.phx.gbl...
: nick wrote:
: >
: > what's the benifit of using native Jet OleDB provider rather than the
: > ODBC provider if it's just a simple asp page?
:
: Excuse me? Are you saying that you think it's all right to use
inefficient,
: obsolete techniques on simple pages, but not all right on more complex
: pages? This does not compute.

Adding this to my list of "Questions Not To Ask" a.k.a. "How to Tick Off Bob
in One Easy Question". (O:=

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - [url]http://www.microsoft.com/technet/scriptcenter/[/url]
WSH 5.6 Documentation - [url]http://msdn.microsoft.com/downloads/list/webdev.asp[/url]
MSDN Library - [url]http://msdn.microsoft.com/library/default.asp[/url]

[Bob Barrows [MVP]]

Roland Hall wrote:

> "Bob Barrows [MVP]" wrote in message
> news:eKhoV5GVEHA.3332@tk2msftngp13.phx.gbl...

>> nick wrote:

>>>
>>> what's the benifit of using native Jet OleDB provider rather than
>>> the ODBC provider if it's just a simple asp page?

>>
>> Excuse me? Are you saying that you think it's all right to use
>> inefficient, obsolete techniques on simple pages, but not all right
>> on more complex pages? This does not compute.

>
> Adding this to my list of "Questions Not To Ask" a.k.a. "How to Tick
> Off Bob in One Easy Question". (O:=

:-)

I wasn't ticked off, but you're right: it does look like I was ticked off.
Believe it or not, I was chuckling as I wrote it. As I reread it, it's easy
to imagine myself snarling ...

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Roland Hall]

"Bob Barrows [MVP]" wrote in message
news:uI543QKVEHA.1036@TK2MSFTNGP12.phx.gbl...
: Roland Hall wrote:
: > "Bob Barrows [MVP]" wrote in message
: > news:eKhoV5GVEHA.3332@tk2msftngp13.phx.gbl...
: >> nick wrote:
: >>>
: >>> what's the benifit of using native Jet OleDB provider rather than
: >>> the ODBC provider if it's just a simple asp page?
: >>
: >> Excuse me? Are you saying that you think it's all right to use
: >> inefficient, obsolete techniques on simple pages, but not all right
: >> on more complex pages? This does not compute.
: >
: > Adding this to my list of "Questions Not To Ask" a.k.a. "How to Tick
: > Off Bob in One Easy Question". (O:=
:
: :-)
:
: I wasn't ticked off, but you're right: it does look like I was ticked off.
: Believe it or not, I was chuckling as I wrote it. As I reread it, it's
easy
: to imagine myself snarling ...

I thought you handled it well. The analogous question made me laugh out
loud.