Is converting to SQL very hard?

[michaaal]

I have written a fairly simple 5-page ASP website that enters and removes
records into a MS Access database. I need to convert it to SQL. Is the
process of converting the commands so that they will read a SQL database
very hard? (I am really just searching for a general answer)

Thank you!

[Dan Boylett]

"michaaal" <res0gyio@verizon.net> wrote in message
news:Og9MJwn1DHA.2972@TK2MSFTNGP09.phx.gbl...

> I have written a fairly simple 5-page ASP website that enters and removes
> records into a MS Access database. I need to convert it to SQL. Is the
> process of converting the commands so that they will read a SQL database
> very hard? (I am really just searching for a general answer)

No.

[Bob Barrows]

michaaal wrote:

> I have written a fairly simple 5-page ASP website that enters and
> removes records into a MS Access database. I need to convert it to
> SQL. Is the process of converting the commands so that they will
> read a SQL database very hard? (I am really just searching for a
> general answer)
>
> Thank you!

It depends. Are you using any VBA functions such as Instr or Nz in those
queries? If so, you will need to learn about the corresponding T-SQL
functions. There are differences in syntax for UPDATE queries that involve
multiple tables (hopefully, you are using sql statements as opposed to
recordsets to do your updates, inserts and deletes). For more, see this
article:

[url]http://www.aspfaq.com/show.asp?id=2182[/url]
which includes a link to this article:
[url]http://www.aspfaq.com/show.asp?id=2214[/url]
(actually, I would start with the second article)

HTH,
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"

[Jeff Cochran]

On Fri, 9 Jan 2004 00:52:25 -0600, "michaaal" <res0gyio@verizon.net>
wrote:

>I have written a fairly simple 5-page ASP website that enters and removes
>records into a MS Access database. I need to convert it to SQL. Is the
>process of converting the commands so that they will read a SQL database
>very hard? (I am really just searching for a general answer)

In general, no. Access SQL translates to SQl Server very well, with a
few notable exceptions, such as how dates are delimited. But if you
take the query and paste it into query analyzer, you'll get a really
quick indicator of how tough it'll be.

Jeff

[Mark Schupp]

<nitpick>
SQL != Microsoft SQL Server.
SQL is a data manipulation language supported by many DBMSs.
MS SQL Server is a DBMS that supports SQL.
MS Access also supports SQL.
</nitpick>

Aside from date format and use of non-standard SQL functions the only thing
I have run into between Access and SQL Server has to do with nested
statements (where you loop through one result-set and execute update
statements inside the loop). SQL Server tends to give "hstmt busy" errors
where Access does not.

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"michaaal" <res0gyio@verizon.net> wrote in message
news:Og9MJwn1DHA.2972@TK2MSFTNGP09.phx.gbl...

> I have written a fairly simple 5-page ASP website that enters and removes
> records into a MS Access database. I need to convert it to SQL. Is the
> process of converting the commands so that they will read a SQL database
> very hard? (I am really just searching for a general answer)
>
> Thank you!
>
>

[Aaron Bertrand - MVP]

> statements (where you loop through one result-set and execute update

> statements inside the loop).

This kind of cursor operation is better handled with a single, set-based
UPDATE statement anyway...

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[michaaal]

"Dan Boylett" <parc_erom@crossdata.co.uk> wrote in message
news:eWb7RDp1DHA.2308@TK2MSFTNGP11.phx.gbl...

>
> "michaaal" <res0gyio@verizon.net> wrote in message
> news:Og9MJwn1DHA.2972@TK2MSFTNGP09.phx.gbl...

> > I have written a fairly simple 5-page ASP website that enters and

removes

> > records into a MS Access database. I need to convert it to SQL. Is the
> > process of converting the commands so that they will read a SQL database
> > very hard? (I am really just searching for a general answer)

>
> No.
>

Thank you!!

[michaaal]

There are differences in syntax for UPDATE queries that involve

> multiple tables (hopefully, you are using sql statements as opposed to
> recordsets to do your updates, inserts and deletes). For more, see this
> article:

ok, this is where I got a little lost....I always use recordsets to do my
updates, inserts, and deletes. Am I missing something? Could you post a
simple example of doing an insert without using a recordset?

Thanks!!

[Aaron Bertrand [MVP]]

[url]http://www.aspfaq.com/2191[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"michaaal" <res0gyio@verizon.net> wrote in message
news:u8AWhWz1DHA.1272@TK2MSFTNGP12.phx.gbl...

> There are differences in syntax for UPDATE queries that involve

> > multiple tables (hopefully, you are using sql statements as opposed to
> > recordsets to do your updates, inserts and deletes). For more, see this
> > article:

>
> ok, this is where I got a little lost....I always use recordsets to do my
> updates, inserts, and deletes. Am I missing something? Could you post a
> simple example of doing an insert without using a recordset?
>
> Thanks!!
>
>

[michaaal]

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:Oaoz2pz1DHA.2456@TK2MSFTNGP12.phx.gbl...

> [url]http://www.aspfaq.com/2191[/url]

Thanks! This was extremely helpful!

So when I am done with my INSERT, DELETE, or UPDATE, what do I need to do to
properly close the session?

[Aaron Bertrand [MVP]]

> So when I am done with my INSERT, DELETE, or UPDATE, what do I need to do
to

> properly close the session?

What "session" are you talking about?

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[michaaal]

> What "session" are you talking about?

Let me rephrase please....Is there anything I need to do when I am done?
For example, when I was using recordsets I would have to use something like:

recset.close

[Aaron Bertrand [MVP]]

> For example, when I was using recordsets I would have to use something
like:

>
> recset.close

You should also have been using set recset = nothing

If you're not using a recordset object, just make sure you close your
connection object.

conn.close
set conn = nothing

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[michaaal]

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:e8Etac71DHA.3116@TK2MSFTNGP11.phx.gbl...

> > For example, when I was using recordsets I would have to use something

> like:

> >
> > recset.close

>
> You should also have been using set recset = nothing
>
> If you're not using a recordset object, just make sure you close your
> connection object.
>
> conn.close
> set conn = nothing

Thanks!

[michaaal]

> It depends. Are you using any VBA functions such as Instr or Nz in those

> queries? If so, you will need to learn about the corresponding T-SQL
> functions. There are differences in syntax for UPDATE queries that involve
> multiple tables (hopefully, you are using sql statements as opposed to
> recordsets to do your updates, inserts and deletes). For more, see this
> article:

ok...I have done some research (hopefully correctly!). Are these statements
correct for NOT using recordsets?

(I am still a little confised, because I don't see a "ADODB.Recordset"
progID, however, I am stil able to move through "rs" which makes me wonder
if it is a recordset.)

strSQL = "SELECT * FROM InventoryItemList;"
set conn = Server.CreateObject("ADODB.Connection")
conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" &
Server.MapPath("../parts.mdb")
set rs = conn.execute (strSQL)
response.write(rs("Item"))
rs.movenext
response.write(rs("Item"))
'Now close everything...
conn.close
set conn = nothing

Thanks!!

[Bob Barrows]

michaaal wrote:

> ok...I have done some research (hopefully correctly!). Are these
> statements correct for NOT using recordsets?

No, you are still using a recordset.

>
> (I am still a little confised, because I don't see a "ADODB.Recordset"
> progID, however, I am stil able to move through "rs" which makes me
> wonder if it is a recordset.)

It is absolutely still a recordset. The Execute method causes an adodb
recordset to be created behind the scenes. When you use this syntax:

set rs = cn.Execute(<sql statement>)

a reference to that recordset will be set to the rs variable. One thing that
many people do not realize is that even this syntax:

cn.execute <sql statement>

will also cause a recordset to be created, even when the query being run
does not return records (as in the case of an UPDATE, INSERT or DELETE
query) This is wasteful of resources and processing time, so you should
always use the options argument (the third argument) to tell ADO:
1) what kind of command you are running (text vs table vs stored procedure)
and
2) whether or not you expect the query to return records.

Using your code as an example, here is the most efficient way to call the
Execute method:
Const adCmdText = 1
strSQL = "SELECT * FROM InventoryItemList;"
set rs = conn.execute (strSQL,,adCmdText)

Instead of making ADO guess, I've told it that I want it to execute a sql
statement passed as a string (text). Since it does not have to waste cpu to
make the guess, ADO will execute the statement a little quicker. Now, nobody
will ever notice the difference in speed, but that is not the real reason to
make sure you specify the type: sometimes (admittedly rarely, but it can
happen) ADO can make the wrong guess, giving you unexpected results.

Here is an example of using an UPDATE query that does not return records:

Const adCmdText = 1
Const adExecuteNoRecords = 128
strSQL = "UPDATE InventoryItemList SET Item=<some value>" & _
"WHERE Item=<original value>"
conn.execute (strSQL,,adCmdText + adExecuteNoRecords)

There are other options that can be specified. See these two pages for the
complete enumerations:

[url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdaenumac_7.asp[/url]
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdaenumdm_5.asp[/url]

and see this page for the explanation of the Execute method:
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdamth02_5.asp[/url]

HTH,
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"