Professional Web Applications Themes

I think it's a datatype issue - ASP Database

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

  1. #1

    Default I think it's a datatype issue

    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 Guest

  2. #2

    Default Re: I think it's a datatype issue

    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"


    Bob Barrows Guest

  3. #3

    Default Re: I think it's a datatype issue

    >Bob Barrows" <reb01501NOyahoo.SPAMcom> wrote in message
    news:eWuTfft0DHA.3220tk2msftngp13.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 Guest

  4. #4

    Default Re: I think it's a datatype issue


    "Fox" <fox connexions .net> wrote in message
    news:OveOEmu0DHA.2412TK2MSFTNGP10.phx.gbl...
    > >Bob Barrows" <reb01501NOyahoo.SPAMcom> wrote in message
    > news:eWuTfft0DHA.3220tk2msftngp13.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



    Fox Guest

  5. #5

    Default Re: I think it's a datatype issue

    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"


    Bob Barrows Guest

  6. #6

    Default Re: I think it's a datatype issue

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

    Aaron Bertrand [MVP] Guest

  7. #7

    Default Re: I think it's a datatype issue


    "Bob Barrows" <reb01501NOyahoo.SPAMcom> wrote in message
    news:uaOdz0u0DHA.2240TK2MSFTNGP10.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


    Fox Guest

  8. #8

    Default Re: I think it's a datatype issue

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


    Aaron Bertrand [MVP] Guest

  9. #9

    Default Re: I think it's a datatype issue


    "Aaron Bertrand [MVP]" <aaronTRASHaspfaq.com> wrote in message
    news:#3T15Rv0DHA.2680tk2msftngp13.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.3496TK2MSFTNGP11.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 bersome 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


    Fox Guest

  10. #10

    Default Re: I think it's a datatype issue

    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"


    Bob Barrows Guest

  11. #11

    Default Re: I think it's a datatype issue


    "Bob Barrows" <reb01501NOyahoo.SPAMcom> wrote in message
    news:ersbMb40DHA.2872TK2MSFTNGP09.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


    Fox Guest

  12. #12

    Default Re: I think it's a datatype issue

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


    Bob Barrows Guest

Similar Threads

  1. jtds sqlServer driver text datatype issue
    By lnagy in forum Coldfusion Database Access
    Replies: 1
    Last Post: April 7th, 05:54 PM
  2. ASP.NET Insert into bit datatype
    By RajDogLives in forum Adobe Dreamweaver & Contribute
    Replies: 9
    Last Post: May 20th, 04:45 PM
  3. Function for retreiving datatype
    By Brendan Jurd in forum PostgreSQL / PGSQL
    Replies: 13
    Last Post: January 11th, 09:00 AM
  4. citext datatype
    By Sim Zacks in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: January 2nd, 12:20 PM
  5. Check datatype
    By Andrew Durstewitz in forum ASP
    Replies: 2
    Last Post: June 30th, 03:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139