Myth buster re posting to Access stored procs

[Laphan]

Hi All

I know I should be using MySQL or SQL Server, but I do quite a few sites
where Access will do just fine.

The reason for this posting is to try and dispell a possible myth/problem
regarding the posting of data (INSERTING and UPDATING that is) via Access
stored procs rather than in-line SQL queries.

I'm sure that when I used to use Access 97 DBs a few years ago that if I
posted too much data (more than 255 chars I believe) to a stored proc albeit
an INSERT or an UPDATE it would pop the site, because the DB couldn't take
it.

However if I sent the exact same data via in-line SQL code, it worked fine.

Because of this, up until recently I have continued to do all my INSERTS and
UPDATES using in-line code and only SP my SELECT statements.

This obviously isn't the proper way to go, so could somebody please confirm:

a) Am I correct in saying this limitation existed in Access 97?

b) Has this been fixed in Access 2000, as I use this DB format for my sites
now?

Thanks

Laphan

[Bob Barrows]

Laphan wrote:

> Hi All
>
> I know I should be using MySQL or SQL Server, but I do quite a few
> sites where Access will do just fine.
>
> The reason for this posting is to try and dispell a possible
> myth/problem regarding the posting of data (INSERTING and UPDATING
> that is) via Access stored procs rather than in-line SQL queries.
>
> I'm sure that when I used to use Access 97 DBs a few years ago that
> if I posted too much data (more than 255 chars I believe) to a stored
> proc albeit an INSERT or an UPDATE it would pop the site, because the
> DB couldn't take it.
> .

I've never heard this. There is a KB article dealing with a Jet 3.51 problem
with handling more than 64K characters, but 255???

> However if I sent the exact same data via in-line SQL code, it worked
> fine.

I have never seen this issue. I used to work with Access a lot, and I've
never run into this.

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"

[Adrian Forbes [ASP MVP]]

> a) Am I correct in saying this limitation existed in Access 97?

If you're talking about what I think you're talking about (I might not be
but I' talking about passing param data via the Parameters collection) then
although it seems like there was no longer type than varchar (limited to
255) you could still post more than that to a MEMO field by adding a varchar
param of the desored length. Or were you talking about the 255 char limit
of the text field? If you need more than 255 you use a MEMO.

> b) Has this been fixed in Access 2000, as I use this DB format for my

sites

> now?

See above, this issue might be your choice of fields or your coding. Maybe
some sample code/table structures will clarify things.

[Astra]

Hi Adrian

Thanks for coming back to me on this.

Basically when I used Access97, when I submitted my data via a stored proc
it would generate an error if the submitted data was over 255 chars even
though the 'receiving' field was a memo one.

The way I send my stored procs is as follows:

Set oCmd=Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = oConn
oCmd.CommandText = "Admin_SubmitNews('" & strNews & "')"
oCmd.CommandType = 4
'Response.Write oCmd.CommandText
Set oRSv=Server.CreateObject("ADODB.recordset")
oRSv.open oCmd

strNews being the data from a <TEXTAREA> field, which sometimes could be
over 255 chars at a time.

The thing was that if I swapped the stored proc for INSERT INTO NEWS VALUES
(' & etc... it worked fine.

Due to this, I have shyed away from using stored procs for posting data ever
since. I always use in-line statements, but I would like to go the SProc
route if Access2000 is different.

Rgds

Robbie


"Adrian Forbes [ASP MVP]" <sorry@noemail.zzz> wrote in message
news:uLtf6tb9DHA.2416@TK2MSFTNGP10.phx.gbl...

> a) Am I correct in saying this limitation existed in Access 97?

If you're talking about what I think you're talking about (I might not be
but I' talking about passing param data via the Parameters collection) then
although it seems like there was no longer type than varchar (limited to
255) you could still post more than that to a MEMO field by adding a varchar
param of the desored length. Or were you talking about the 255 char limit
of the text field? If you need more than 255 you use a MEMO.

> b) Has this been fixed in Access 2000, as I use this DB format for my

sites

> now?

See above, this issue might be your choice of fields or your coding. Maybe
some sample code/table structures will clarify things.

[Bob Barrows]

Astra wrote:

> Hi Adrian
>
> Thanks for coming back to me on this.
>
> Basically when I used Access97, when I submitted my data via a stored
> proc it would generate an error if the submitted data was over 255
> chars even though the 'receiving' field was a memo one.
>
> The way I send my stored procs is as follows:
>
> Set oCmd=Server.CreateObject("ADODB.Command")
> Set oCmd.ActiveConnection = oConn
> oCmd.CommandText = "Admin_SubmitNews('" & strNews & "')"

That is because you were using the wrong technique to pass your parameter.
You should have done this:
with oCmd
..CommandType=4
..CommandText = "Admin_SubmitNews"
..Parameters.Append .CreateParameter("p1",201,1,1000000,strNews)

'201=adLongVarChar
'1=adParamInput

End With

With the Jet 4.0 Provider (Access 2000+) you can use the simpler technique I
showed you in my previous post.

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"

[Bob Barrows [MVP]]

Laphan wrote:

> Many thanks Adrian/Bob.
>
> Just to complete my education, what does the 1000000 stand for?
>

That would be the size parameter. I chose 1000000 just to write something in
there. You should choose something that's more reasonble for your
application

--
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.

[Laphan]

Many thanks Adrian/Bob.

Just to complete my education, what does the 1000000 stand for?

Rgds

Laphan

Bob Barrows <reb01501@NOyahoo.SPAMcom> wrote in message
news:OMMUYvh9DHA.2472@TK2MSFTNGP10.phx.gbl...
Astra wrote:

> Hi Adrian
>
> Thanks for coming back to me on this.
>
> Basically when I used Access97, when I submitted my data via a stored
> proc it would generate an error if the submitted data was over 255
> chars even though the 'receiving' field was a memo one.
>
> The way I send my stored procs is as follows:
>
> Set oCmd=Server.CreateObject("ADODB.Command")
> Set oCmd.ActiveConnection = oConn
> oCmd.CommandText = "Admin_SubmitNews('" & strNews & "')"

That is because you were using the wrong technique to pass your parameter.
You should have done this:
with oCmd
..CommandType=4
..CommandText = "Admin_SubmitNews"
..Parameters.Append .CreateParameter("p1",201,1,1000000,strNews)

'201=adLongVarChar
'1=adParamInput

End With

With the Jet 4.0 Provider (Access 2000+) you can use the simpler technique I
showed you in my previous post.

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"

[Bob Barrows [MVP]]

Laphan wrote:

> Many thanks Adrian/Bob.
>
> Just to complete my education, what does the 1000000 stand for?

You may want to bookmark this:
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoapireference.asp[/url]
--
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.

[Adrian Forbes [ASP MVP]]

use len(sYourText) instead. Note this will bomb if the var is empty.

"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:OJA6djm9DHA.3204@tk2msftngp13.phx.gbl...

> Many thanks Adrian/Bob.
>
> Just to complete my education, what does the 1000000 stand for?
>
> Rgds
>
> Laphan
>
> Bob Barrows <reb01501@NOyahoo.SPAMcom> wrote in message
> news:OMMUYvh9DHA.2472@TK2MSFTNGP10.phx.gbl...
> Astra wrote:

> > Hi Adrian
> >
> > Thanks for coming back to me on this.
> >
> > Basically when I used Access97, when I submitted my data via a stored
> > proc it would generate an error if the submitted data was over 255
> > chars even though the 'receiving' field was a memo one.
> >
> > The way I send my stored procs is as follows:
> >
> > Set oCmd=Server.CreateObject("ADODB.Command")
> > Set oCmd.ActiveConnection = oConn
> > oCmd.CommandText = "Admin_SubmitNews('" & strNews & "')"

>
> That is because you were using the wrong technique to pass your parameter.
> You should have done this:
> with oCmd
> .CommandType=4
> .CommandText = "Admin_SubmitNews"
> .Parameters.Append .CreateParameter("p1",201,1,1000000,strNews)
>
> '201=adLongVarChar
> '1=adParamInput
>
> End With
>
> With the Jet 4.0 Provider (Access 2000+) you can use the simpler technique

I

> showed you in my previous post.
>
> 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"
>
>
>
>