converting Access SQL statements to SQL 2000 Statements

0
August 16, 2011

I am having difficulty getting a simple counter I created in ASP using Access to work when converting for SQL 2000.

I recently purchased SQL 2000 and imported the counter table into sql

I immediately noticed a few problems that have to do with dates. During the conversion sql tried to make the date and time fields smalldatetime which produced errors on importing the data. I modified the fields to datetime and was able to import the data successfully.

Below is the syntax that works extremely well in access but does not work for SQL 2000. Here is the ACCESS syntax

<%

dim vbrowser, vip, vhost, today, connStr, rs, vs, bs, visitx, returnx, bscount

vbrowser=Request.ServerVar

iables("HT

TP_USER_AG

ENT")

vip=Request.ServerVariable

s("REMOTE_

ADDR")

vhost=Request.ServerVariab

les("HTTP_

REFERER")

today=cdate(date())

connStr = "Provider=Microsoft.Jet.OL

EDB.4.0; Data Source=" & Server.MapPath("database.m

db")

Set rs = Server.CreateObject("ADODB

.RecordSet

")

Set vs = Server.CreateObject("ADODB

.RecordSet

")

Set bs = Server.CreateObject("ADODB

.RecordSet

")

vs.Open "returning", connStr, 2, 2

rs.Open "select * from visit WHERE [visit.ipad] ='" & vip & "'AND cdate([visit.date]) ='" & date() &"' ORDER BY [visit.date] ASC", connStr, 3, 4

If rs.RecordCount =0 Then

visitx=1

returnx=0

rs.close

rs.Open "visit", connStr, 2, 2

rs.AddNew

rs("browser") = vbrowser

rs("ipad") = vip

rs("domain") = vhost

rs.Update

Else

visitx = 0

vs.AddNew

vs("browser") = vbrowser

vs("ipad") = vip

vs("domain") = vhost

vs.Update

End If

bs.Open "select * from visit", connStr, 3, 4

bscount= bs.RecordCount

dim lennum, strnum

strnum=trim(cstr(bs.record

Count))

lennum= len(bs.recordCount)

for i = 1 to lennum

response.write "<img src='" & mid(strnum,(i),1) & ".gif'>"

next

bs.close

vs.Close

rs.close

Set rs = Nothing

Set rs = Nothing

Set bs= Nothing

%>

This works well but I want to switch over to SQL 2000

I keep getting errors on Line 11 of the what I think is the correct conversion for sql. Here is the modified code for use with SQL. It does not work

The error is:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/counterx.asp, line 11

What is wrong?

<% dim vbrowser, vip, vhost, today, connStr, rs, vs, bs, cs, visitx, returnx, bscount

vbrowser=Request.ServerVar

iables("HT

TP_USER_AG

ENT")

vip=Request.ServerVariable

s("REMOTE_

ADDR")

vhost=Request.ServerVariab

les("HTTP_

REFERER")

today=cdate(date())

connStrx = "Provider=SQLOLEDB;User ID=XXXXX;Password=XXXXX;In

itial Catalog=XXX;Data Source=XXX.XXX.XXX.X;Netwo

rk Library=dbmssocn"

Set rs = Server.CreateObject("ADODB

.RecordSet

")

Set vs = Server.CreateObject("ADODB

.RecordSet

")

Set bs = Server.CreateObject("ADODB

.RecordSet

")

Set cs = Server.CreateObject("ADODB

.RecordSet

")

cs.Open "select count(*) as RecCount from visit WHERE ipad ='" & vip & "'AND cdate(datex)  = '" & date() & "'", connStr, 3, 4

rs.Open "select * from visit WHERE ipad ='" & vip & "'AND cdate(datex) ='" & date() &"' ORDER BY datex ASC", connStr, 3, 4

If cs("RecCount") = 0 Then

visitx=1

returnx=0

rs.close

rs.Open "visit", connStr, 2, 2

rs.AddNew

rs("browser") = vbrowser

rs("ipad") = vip

rs("domainx") = vhost

rs("datex") = date()

rs("timex") = time()

rs.Update

Else

vs.Open "returning", connStr, 2, 2

visitx = 0

vs.AddNew

vs("browser") = vbrowser

vs("ipad") = vip

vs("domainx") = vhost

vs("datex") = date()

vs("timex") = time()

vs.Update

End If

bs.Open "select count(*) as RecCount from visit", connStr, 3, 4

bscount= bs("RecCount")

dim lennum, strnum

strnum=trim(cstr(bs("RecCo

unt")))

lennum= len(bs("RecCount"))

for i = 1 to lennum

response.write "<img src='" & mid(strnum,(i),1) & ".gif'>"

next

bs.close

vs.Close

rs.close

cs.close

Set rs = Nothing

Set rs = Nothing

Set bs = Nothing

set cs = Nothing %>

Any help is greatly appreciated

16

answers

Comments See all(0)

Add comment
0
August 16, 2011

I am somewhat confused here. Is this the data in SQL server?

datex              timex

7/21/2004           8:22:13 AM

7/21/2004           3:49:03 PM

7/21/2004           9:06:51 PM

If so, and both are date/time fields, do you have some setting there regarding format?

FtB

0
August 16, 2011

Maybe this? SQL server does not support as many VBScript methods in the SQL langauge:

cs.Open "select count(*) as RecCount from visit WHERE ipad ='" & vip & "'AND datex = '" & date() & "'", connStr, 3, 4

rs.Open "select * from visit WHERE ipad ='" & vip & "'AND datex ='" & date() &"' ORDER BY datex ASC", connStr, 3, 4

0
August 16, 2011

I am pretty sure it has to do with the date fields somehow. I have been use to using RecordCount to get the number of records in the ACCESS table and this returns a -1 in SQL so I use count() instead in SQL and I get the records number.

The date and time fields in Access are simple date time fields that auto create when a record is added. I have not been able to find this function in SQL. Having said that the datetime in SQL must be different than in Access. I have tried using Datec() Cdate() to test and all fail.

I have used similar syntax when I converted a bible search engine and it worked very well. I had to remove the brackets[] though as SQL does not like them. or so it seems

0
August 16, 2011

In sql server, you can just insert Now() and that will give you the date/time stamp. You can still use RecordCount() if you use the correct type of recordset.

FtB

0
August 16, 2011

So then instead of using date() which gives the date in format xx/xx/xxxx

I use now()? what format does this produce? I want to be able to get the values of both functions date() and time() is this possible with Now()? and if so how?

0
August 16, 2011

Now should give you, depending on your regional settings, the following:

yyyy/mm/dd hh:mm:dd:ss

FtB

0
August 16, 2011

Also, as I recall, SQL server does have timestamp field doesn't it? That would automatically populate your field for you.

0
August 16, 2011

Finally, I think that SQL Server likes its dates in this format:

yyyy/mm/dd

so be sure to do inserts that way.

FtB

0
August 16, 2011

Never knew going to SQL server was going to be so difficult with dates.

Here is some of the data from the converted Access to SQL table for fields datex and timex both are datetime fields and the data is showing in this format

datex              timex

7/21/2004            8:22:13 AM

7/21/2004            3:49:03 PM

7/21/2004            9:06:51 PM

here is the format I get when I do a now()

7/27/2004 12:01:51 PM

So in order to even open the database to query dates would it be easier to just convert the datex and timex fields to nchar types and using the mid() function extract the date and time portions from now() to make comparisons?

It seems like Access 2000 is more advanced then SQL 2000 server. If I didn't have a problem with an extreme amount of visitor activity I would not even bother changing from Access. At least that works.

<Sigh>

Have date queries always been an issue for SQL 2000?

0
August 16, 2011

I think the issue has to do with balancing the user friendly flexibility inherent with Access vs. the speed, integrity, and scalability of SQL server...

As always, there are trade offs.

FtB

0
August 16, 2011

I thank you for your help

I will just start over using nvchar instead of datetime and use mid and see what I can do.

Thanks again for your assistance

0
August 16, 2011

Glad to have helped, but I recommend working on this a litlle more until we get what you need--type conversion can be a big pain in the butt and can limit future options.

FtB

0
August 16, 2011

I'll post my results and if I need further assistance I will post back

Thanks again :-)

0
August 16, 2011

I am so embarassed

it was a stupid spelling mistake all the time

My connection string variable I assigned was connStr

I had an x after it making it connStrx

However,

I changed the way the syntax works and now I am happy to sayy I have a sucessful counter for sql 2000

Here is the altered syntax

<% dim vbrowser, vip, vhost, today, connStr, rs, vs, bs, zs, visitx, returnx

vbrowser=Request.ServerVar

iables("HT

TP_USER_AG

ENT")

vip=Request.ServerVariable

s("REMOTE_

ADDR")

today=cdate(date())

connStr = "Provider=SQLOLEDB;User ID=XXX;Password=XXXXX;Init

ial Catalog=XXX;Data Source=XXX.XXX.XXX.X;Netwo

rk Library=dbmssocn"

Set rs = Server.CreateObject("ADODB

.RecordSet

")

Set vs = Server.CreateObject("ADODB

.RecordSet

")

Set bs = Server.CreateObject("ADODB

.RecordSet

")

Set zs = Server.CreateObject("ADODB

.RecordSet

")

zs.Open "select count(ipad) as RecCount from visit where (datex ='" & today & "')", connStr, 3, 4

If zs("RecCount") = 0 Then

visitx=1

returnx=0

rs.Open "visit", connStr, 2, 2

rs.AddNew

rs("browser") = vbrowser

rs("ipad") = vip

rs("datex") = cdate(date())

rs.Update

rs.close

Else

vs.Open "returning", connStr, 2, 2

visitx = 0

vs.AddNew

vs("browser") = vbrowser

vs("ipad") = vip

vs("datex") = cdate(date())

vs.Update

vs.close

End If

bs.Open "select count(*) as RecCount from visit", connStr, 3, 4

dim lennum, strnum

strnum=trim(cstr(bs("RecCo

unt")))

lennum= len(bs("RecCount"))

for i = 1 to lennum

response.write "<img src='" & mid(strnum,(i),1) & ".gif'>"

next

bs.close

zs.close

Set bs = Nothing

set zs = Nothing

Set rs = Nothing

set vs = Nothing

%>

More stream-lined and faster to load now.

Thanks again. Such a simple thing as a variable spelling error can mess up the entire works

0
August 16, 2011

Ah, you should use OPTION EXPLICIT

FtB

0
August 16, 2011

You are right I should and will in the future

Related Questions

PHP did not show any error message

Hello , I&#39;m using windows vista IIS7 I installed the php cgi and it working now except 1 problem, it doesn&#39;t give my any error message. I turn on the code below already error_reporting &#160;= Read More

Views

87

Votes

0

Answers

9

August 15, 2011

This is the very first question

Hello guys! This is the first post in this site Read More

Views

2k

Votes

4

Answers

2

January 08, 2016

report values not showing up.

I have a crystal report which I pass a DataSet to using VB.NET. The report was working fine, but then I make some changes to the import query and now nothing shows up in the crystal report. Crystal do Read More

Views

1k

Votes

0

Answers

12

September 06, 2005

Event log doesn't overwrite as needed in SP4

Hello All, After upgrading from SP3 to SP4 on several Windows 2000 PRO machines everything looked fine, but from time to time applications can&#39;t write to the event log. When I try to view the appl Read More

Views

1k

Votes

0

Answers

9

May 11, 2003

"Failed to self-register XYZ.dll"

Hi there, I wrote a OLE-automation-server DLL in VB4.0. I use Installshield Express to install it as part of my program on the target computers. Now on some computers I get the message &quot;Failed to Read More

Views

2k

Votes

0

Answers

2

November 08, 1998

Please help understand these notes on image processing

Can someone please help me understand these lecture notes... On the right of the page; What does &quot;normalised by one notion of the area of a pixel&quot; mean? On the first formula for A, Is that a Read More

Views

1k

Votes

0

Answers

3

February 05, 2009

Can someone please explain this paragram on the chain rule in image processing

Please see the screenshot, How is 3133030 got from 10103322? Read More

Views

1k

Votes

0

Answers

1

January 05, 2009

AD on 2003

We have created an AD Domain on Windows 2000 Server with no problems. We just created an AD Domain on Windows 2003 and we&#39;re getting some weird problems. Both of these domains are behind firewalls Read More

Views

1k

Votes

0

Answers

15

May 11, 2003

ADDT ASP Upload Error " Type mismatch: 'tNG_isFileInsideBaseFolder' "

I am trying to create a simple insert record and upload image function on an ASP page built using Adobe Dreamweaver Developer Toolkit. I have done this many times before with no problem, however, i ha Read More

Views

1k

Votes

0

Answers

0

November 02, 2008

MYSQL Select query with custom ORDER BY

Hi, is it possible to customize the order of the returned rows in mysql? Example: I have a Table with a column &quot;name&quot;, now I want to have all entries ordered by name, but I want the entries Read More

Views

1k

Votes

0

Answers

5

July 02, 2010

Folder Redirection in Server 2003

We have a Windows Server 2003 Standard Ed. Is there a way to redirect the users my documents to a folder that has already been created on the server? Read More

Views

1k

Votes

0

Answers

2

July 05, 2007

Partiton magic  version 8.0 having error 1523 while executing batch

I followed the partition magic 8.0 wizard to partition my hard disk while rebooting &#160;I came across this problem &#160;&quot;error 1523 while executing batch&quot; What can I do to solve this prob Read More

Views

1k

Votes

0

Answers

2

May 11, 2003

Please explain this paragraph in image processing (screenshot attached)

Two questions; How is the equation (10.1-15) formed? &quot;are isotropic for rotation increments of 90 degrees and 45 degrees respectively&quot; What does this mean Read More

Views

820

Votes

0

Answers

7

January 05, 2009

xp_cmdshell with net use

Hi, when I use &#39;net use \192.168.0.1\c$ password /user:username&#39; at the command prompt, it works fine. &#160;But if i try to use it in sql with &quot; exec master..xp_cmdshell &#39;net use \19 Read More

Views

767

Votes

0

Answers

5

April 11, 2003

hp ux11

i have a hp ux11 server. and i edited the /etc/resolv.conf to use my win2003 server dns server. That went fine, however, I would like to go the other way and add an entry on my win2003 dns to resolve Read More

Views

454

Votes

0

Answers

3

October 12, 2009

Using an application over a LAN

I have written an application that needs to be used on a LAN. &#160;The application setup wizard takes care of registering everything on a single PC, but how do you go about writing a setup program th Read More

Views

402

Votes

0

Answers

9

August 07, 1998

Need subnet of 12 IPs.

Please, Can someone help me to create a subnet consisting of 12 IP&#39;s. Thanks.. Read More

Views

408

Votes

0

Answers

5

April 11, 2003

Drill down in a cross-tabl report

Hello, Would some experts to show me if it is possible to do drill-up or drill-down (or both) in a cross-tab report? I would also like to know if it is possible to control what fields to be displayed/ Read More

Views

331

Votes

0

Answers

0

September 06, 2005

Using RDO, Enterprise V Professional

We would like to use RDO on our project made up of 4 developers. Do we need to have a copy of Enterprise for each developer or is it possible to use Professional for those developers not writing the R Read More

Views

391

Votes

0

Answers

6

January 07, 1998

Can't Edit IIS Metabase.xml

We do this on every windows 2003 server and never had an issue. We cannot edit the IIS Metabase.xml We have stopped IIS and made change click save, no problem but it does not take the new information. Read More

Views

387

Votes

0

Answers

0

July 05, 2007