I think it's a datatype issue

[Fox]

I cannot seem to get this entry to be accepted
into the intended table. I am getting the common
error message which tells me
cannot be found in collection ... corresponding requested name ordinal
I am thinking this is a datatype issue. I tried cstr(currentyear) but that
did not work. The datatype in the table is SMALLINT.
I've inlcuded only the related lines.

currentyear = DatePart("YYYY", date)

objInsertPoints.Fields("Registration") = currentyear

objInsertPoints.Update

Thanks for any suggestions,
Fox

[Bob Barrows]

Fox wrote:

> I cannot seem to get this entry to be accepted
> into the intended table. I am getting the common
> error message which tells me
> cannot be found in collection ... corresponding requested name ordinal
> I am thinking this is a datatype issue. I tried cstr(currentyear) but
> that did not work. The datatype in the table is SMALLINT.
> I've inlcuded only the related lines.

You haven't shown us enough. Show us the sql statement used to open the
recordset.

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"

[Fox]

>Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eWuTfft0DHA.3220@tk2msftngp13.phx.gbl...

> Fox wrote:

> > I cannot seem to get this entry to be accepted
> > into the intended table. I am getting the common
> > error message which tells me
> > cannot be found in collection ... corresponding requested name ordinal
> > I am thinking this is a datatype issue. I tried cstr(currentyear) but
> > that did not work. The datatype in the table is SMALLINT.
> > I've inlcuded only the related lines.

>
> You haven't shown us enough. Show us the sql statement used to open the
> recordset.
>
> Bob Barrows
>

You are correct, apparently I did not include what was needed.
The problem proves to be in the statement to open the recordset.
When using the "open recordeset statement" below my ADD.NEW
works. It has been working that way for a year. But since I am now
trying to use variables, my syntax is bad. I have tried many variations
and cannot seem to get it right. Can you please help me with the
syntax needed to insert the variables which are shown below the
statement ?

Set objInsertPoints = Server.CreateObject("ADODB.Recordset")
objInsertPoints.Open "Points", "DSN= FST",2,3

These are the variables I need to insert in place of the related hard data.
They are defined on an include page and are working correctly
elsewhere in the application. But none of the other usage is in this
same type of statement.

onlySBP = "Points"
data = "FST"

Thanks again,
Fox

[Fox]

"Fox" <fox @ connexions .net> wrote in message
news:OveOEmu0DHA.2412@TK2MSFTNGP10.phx.gbl...

> >Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message

> news:eWuTfft0DHA.3220@tk2msftngp13.phx.gbl...

> > Fox wrote:

> > > I cannot seem to get this entry to be accepted
> > > into the intended table. I am getting the common
> > > error message which tells me
> > > cannot be found in collection ... corresponding requested name ordinal
> > > I am thinking this is a datatype issue. I tried cstr(currentyear) but
> > > that did not work. The datatype in the table is SMALLINT.
> > > I've inlcuded only the related lines.

> >
> > You haven't shown us enough. Show us the sql statement used to open the
> > recordset.
> >
> > Bob Barrows
> >

>
> You are correct, apparently I did not include what was needed.
> The problem proves to be in the statement to open the recordset.
> When using the "open recordeset statement" below my ADD.NEW
> works. It has been working that way for a year. But since I am now
> trying to use variables, my syntax is bad. I have tried many variations
> and cannot seem to get it right. Can you please help me with the
> syntax needed to insert the variables which are shown below the
> statement ?
>
> Set objInsertPoints = Server.CreateObject("ADODB.Recordset")
> objInsertPoints.Open "Points", "DSN= FST",2,3
>
> These are the variables I need to insert in place of the related hard

data.

> They are defined on an include page and are working correctly
> elsewhere in the application. But none of the other usage is in this
> same type of statement.
>
> onlySBP = "Points"
> data = "FST"
>
> Thanks again,
> Fox
>
>

I got it now !
objInsertPoints.Open "" & onlySBP & "", "DSN=" & data & ";",2,3

Thanks,
Fox

[Bob Barrows]

Fox wrote:

>
> You are correct, apparently I did not include what was needed.
> The problem proves to be in the statement to open the recordset.
> When using the "open recordeset statement" below my ADD.NEW
> works. It has been working that way for a year. But since I am now
> trying to use variables, my syntax is bad. I have tried many
> variations and cannot seem to get it right. Can you please help me
> with the
> syntax needed to insert the variables which are shown below the
> statement ?
>
> Set objInsertPoints = Server.CreateObject("ADODB.Recordset")
> objInsertPoints.Open "Points", "DSN= FST",2,3
>

Why are you using a DSN? This is not recommended unless your database
(whatever it is - <hint> this is relevant) does not have a native OLEDB
provider.

> These are the variables I need to insert in place of the related hard
> data. They are defined on an include page and are working correctly
> elsewhere in the application. But none of the other usage is in this
> same type of statement.
>
> onlySBP = "Points"
> data = "FST"
>
> Thanks again,
> Fox

Assuming onlySBP contains the name of a table, and data contains a DSN
(ugh!), here is the suggested syntax:

dim c
set cn = server.createobject("adodb.connection")
cn.open data
Set objInsertPoints = Server.CreateObject("ADODB.Recordset")
objInsertPoints.Open onlySBP, cn,1,3,2

I've taken the liberty to change the cursortype from 2 (adOpenDynamic) to 3
(adOpenForwardOnly). You do not need a dynamic cursor, which is very
expensive in terms of resources and network traffic. It is unlikely that you
will have a recordset open long enough to need a dynamic cursor, whose only
advantage is that it allows you to see changes in the table made by other
users. And, since all you are doing is adding a record, there is no need to
use more than a forward-only cursor.

I've also added the Options argument, specifying 2 (adCmdTable) - you should
always tell ADO what commandtype is being used.

Now, can you please explain why you are using the grossly inefficient
recordset object to add a record to the table? Why not use a SQL Insert
statement?

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"

[Aaron Bertrand [MVP]]

Use an UPDATE statement, instead of opening a heavy and expensive recordset
object.

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




"Fox" <fox @ connexions .net> wrote in message
news:uhpoLZt0DHA.3496@TK2MSFTNGP11.phx.gbl...

> I cannot seem to get this entry to be accepted
> into the intended table. I am getting the common
> error message which tells me
> cannot be found in collection ... corresponding requested name ordinal
> I am thinking this is a datatype issue. I tried cstr(currentyear) but that
> did not work. The datatype in the table is SMALLINT.
> I've inlcuded only the related lines.
>
> currentyear = DatePart("YYYY", date)
>
> objInsertPoints.Fields("Registration") = currentyear
>
> objInsertPoints.Update
>
> Thanks for any suggestions,
> Fox
>
>

[Fox]

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uaOdz0u0DHA.2240@TK2MSFTNGP10.phx.gbl...

> Fox wrote:

> >
> > You are correct, apparently I did not include what was needed.
> > The problem proves to be in the statement to open the recordset.
> > When using the "open recordeset statement" below my ADD.NEW
> > works. It has been working that way for a year. But since I am now
> > trying to use variables, my syntax is bad. I have tried many
> > variations and cannot seem to get it right. Can you please help me
> > with the
> > syntax needed to insert the variables which are shown below the
> > statement ?
> >
> > Set objInsertPoints = Server.CreateObject("ADODB.Recordset")
> > objInsertPoints.Open "Points", "DSN= FST",2,3
> >

> Why are you using a DSN? This is not recommended unless your database
> (whatever it is - <hint> this is relevant) does not have a native OLEDB
> provider.
>

> > These are the variables I need to insert in place of the related hard
> > data. They are defined on an include page and are working correctly
> > elsewhere in the application. But none of the other usage is in this
> > same type of statement.
> >
> > onlySBP = "Points"
> > data = "FST"
> >
> > Thanks again,
> > Fox

>
>
> Assuming onlySBP contains the name of a table, and data contains a DSN
> (ugh!), here is the suggested syntax:
>
> dim c
> set cn = server.createobject("adodb.connection")
> cn.open data
> Set objInsertPoints = Server.CreateObject("ADODB.Recordset")
> objInsertPoints.Open onlySBP, cn,1,3,2
>
> I've taken the liberty to change the cursortype from 2 (adOpenDynamic) to

3

> (adOpenForwardOnly). You do not need a dynamic cursor, which is very
> expensive in terms of resources and network traffic. It is unlikely that

you

> will have a recordset open long enough to need a dynamic cursor, whose

only

> advantage is that it allows you to see changes in the table made by other
> users. And, since all you are doing is adding a record, there is no need

to

> use more than a forward-only cursor.
>
> I've also added the Options argument, specifying 2 (adCmdTable) - you

should

> always tell ADO what commandtype is being used.
>
> Now, can you please explain why you are using the grossly inefficient
> recordset object to add a record to the table? Why not use a SQL Insert
> statement?
>
> Bob Barrows
>

Bob,

My books did not cause me to go in the direction of DSNless connections
and the books are how I started learning. I do now understand that I should
start using DNSless and have already committed that anything new that I do
will be DSNless. The pages I am working on here are from last year and are
just being updated to be more template-like (babysteps) . I will eventually
go back in and rework them regarding the connections. That is when I am
sure that I know what I am doing.

> dim c
> set cn = server.createobject("adodb.connection")
> cn.open data
> Set objInsertPoints = Server.CreateObject("ADODB.Recordset")
> objInsertPoints.Open onlySBP, cn,1,3,2

Thanks for the directions, I will change things out to your suggestions.

> Now, can you please explain why you are using the grossly inefficient
> recordset object to add a record to the table? Why not use a SQL Insert
> statement?

Simply ignorance. I believe you are the one who had me realize to use the
UPDATE
statement instead of first opening a recordset. That led me to think that
INSERT might
be what I want here, but I have never used it before. I got carried away
with what I was
doing and forgot to look further into it. These pages were created near a
year ago
and are in use. I was partly afraid to mess up the whole script and then no
one
would be able to use any of it. Although I realized that INSERT is probably
what I needed, starting right now with new syntax did not seem the right
time.

Thanks,
Fox

[Aaron Bertrand [MVP]]

> would be able to use any of it. Although I realized that INSERT is
probably

> what I needed, starting right now with new syntax did not seem the right
> time.

If changing one line of code is too much hassle, it'll never be "the right
time." :-(

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

[Fox]

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:#3T15Rv0DHA.2680@tk2msftngp13.phx.gbl...

> Use an UPDATE statement, instead of opening a heavy and expensive

recordset

> object.
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>
>
>
> "Fox" <fox @ connexions .net> wrote in message
> news:uhpoLZt0DHA.3496@TK2MSFTNGP11.phx.gbl...

> > I cannot seem to get this entry to be accepted
> > into the intended table. I am getting the common
> > error message which tells me
> > cannot be found in collection ... corresponding requested name ordinal
> > I am thinking this is a datatype issue. I tried cstr(currentyear) but

that

> > did not work. The datatype in the table is SMALLINT.
> > I've inlcuded only the related lines.
> >
> > currentyear = DatePart("YYYY", date)
> >
> > objInsertPoints.Fields("Registration") = currentyear
> >
> > objInsertPoints.Update
> >
> > Thanks for any suggestions,
> > Fox
> >
> >

I've got a lot to learn. You are surely correct that I am doing
things the cumbersome way. Advice on the NG is showing me
this. It was either Bob or you who first mentioned to me about
using the UPDATE statement instead of opening a recordset.
What I left out in my response to bob, in this thread, is that
I was unsure as to whether adding a new record required
INSERT or UPDATE and I have not yet asked or found out
the full scope of either. I should be able to find this in my books
and will look for info on this so I can update my previous
work. I am sure I can consolidate some things which are now
much longer processes then I think they need to be.

Thanks,
Fox

[Bob Barrows]

Fox wrote:

>
> Simply ignorance. I believe you are the one who had me realize to
> use the UPDATE
> statement instead of first opening a recordset. That led me to think
> that INSERT might

My mistake. I should have said "UPDATE" statement instead of "INSERT". For
some reason, I thought you were doing an AddNew instead of a simple update.

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"

[Fox]

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ersbMb40DHA.2872@TK2MSFTNGP09.phx.gbl...

> Fox wrote:

> >
> > Simply ignorance. I believe you are the one who had me realize to
> > use the UPDATE
> > statement instead of first opening a recordset. That led me to think
> > that INSERT might

>
> My mistake. I should have said "UPDATE" statement instead of "INSERT". For
> some reason, I thought you were doing an AddNew instead of a simple

update.

>
> Bob Barrows

You were not wrong.
Hoping to not confuse things, but you were right in the first place.
I am doing an ADD.NEW and wish I KNEW better.

As regards that INSERT statement. The records that are being
added are complex and are intricately mixed with a lot of ASP.
This is why I spared the amount of info posted.
For me they were too complex to figure out how to change them, this time
around.
I gave it a try and had to move on because there was so much depending
on first getting those records able to be added.

I guess the bottom line is a mixture of learning to use the SQL
I use in Enterprise and doing that in my ASP pages. This
is still something I do not know much about but I am learning.

I will look through my books/docs and try learning to start upgrading my
procedures ASAP. I just wish my books had led me in the same
direction that you and Arron are talking about, in the first place. I wonder
why they did things the expensive and hard way?

Thanks,
Fox

[Bob Barrows]

Fox wrote:

> I wonder why they did things the expensive and hard way?

I've often wondered this myself. It would certainly reduce the time we spend
on these newsgroups if they didn't.

It's partly sheer laziness. It is so much easier to describe how to use a
recordset to do an update than it is to explain how to create a sql
statement. It's so much easier to show the creation and execution of a
dynamic sql statement than it is to explain how to create and execute a
stored procedure/saved query. Also, many of the examples you see in the
documentation were originally created using VB/VBA, where the need to
maximize efficiency is not as intense as it is in an asp environment.

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.