Passing Parameters to a query

Discussion in 'ASP General' started by Johnd, Dec 16, 2003.

  1. Johnd

    Johnd Guest

    I am running into an issue when I try to write more than 1024 characters to
    a memo field. Apparantly the odbc connection I am using does not permit
    literals to be larger that 1024 characters. This Memo filed can take
    virtually infinate data, so the solution seems to be use parameters. My
    problem is I have never heard of this and need this to work ASAP. Does
    anyone have a simple example of how Parameters work using ASP?
     
    Johnd, Dec 16, 2003
    #1
    1. Advertising

  2. Johnd

    Bob Barrows Guest

    Johnd wrote:
    > I am running into an issue when I try to write more than 1024
    > characters to a memo field. Apparantly the odbc connection I am


    That's your first problem. You should be using the native Jet OLEDB
    provider. See www.able-consulting.com/ado_conn.htm for examples.

    > using does not permit literals to be larger that 1024 characters.
    > This Memo filed can take virtually infinate data, so the solution
    > seems to be use parameters. My problem is I have never heard of this
    > and need this to work ASAP. Does anyone have a simple example of how
    > Parameters work using ASP?


    http://www.google.com/groups?hl=en&...=&rnum=6

    HTH,
    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, Dec 16, 2003
    #2
    1. Advertising

  3. Johnd

    Johnd Guest

    Thanks Bob but heres a little more detail.

    I am using Advantage server 6.2

    here is my code:


    // Set Conn = Server.CreateObject("ADODB.Connection")
    // Conn.Open "DSN=LocalOP"
    // strQuery = "insert into NCRheadcomments (hdrcomid, headerid, comdate,
    userid, comments, comtime)" &_
    // "values(10084,10080, '" & FormatDateTime( Date(), VBshortDate ) &_
    // "', '" & session("CurrentUser") & "', '" & CurrentComment & "', '" &
    time & "')"
    // Set objRS = Conn.Execute(strQuery)
    // conn.close


    My problem is CurrentComment is larger than 1024 characters. Is there a way
    to get this to work without using a saved query as I do not believe that is
    available to my at this time.

    John,



    "Bob Barrows" <> wrote in message
    news:uXD3aT$...
    > Johnd wrote:
    > > I am running into an issue when I try to write more than 1024
    > > characters to a memo field. Apparantly the odbc connection I am

    >
    > That's your first problem. You should be using the native Jet OLEDB
    > provider. See www.able-consulting.com/ado_conn.htm for examples.
    >
    > > using does not permit literals to be larger that 1024 characters.
    > > This Memo filed can take virtually infinate data, so the solution
    > > seems to be use parameters. My problem is I have never heard of this
    > > and need this to work ASAP. Does anyone have a simple example of how
    > > Parameters work using ASP?

    >
    >

    http://www.google.com/groups?hl=en&...=&rnum=6
    >
    > HTH,
    > 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.
    >
    >
     
    Johnd, Dec 16, 2003
    #3
  4. Johnd

    Bob Barrows Guest

    Johnd wrote:
    > Thanks Bob but heres a little more detail.
    >
    > I am using Advantage server 6.2
    >
    > here is my code:
    >
    >
    > // Set Conn = Server.CreateObject("ADODB.Connection")
    > // Conn.Open "DSN=LocalOP"
    > // strQuery = "insert into NCRheadcomments (hdrcomid, headerid,
    > comdate,
    > userid, comments, comtime)" &_
    > // "values(10084,10080, '" & FormatDateTime( Date(), VBshortDate
    > ) &_ // "', '" & session("CurrentUser") & "', '" &
    > CurrentComment & "', '" &
    > time & "')"
    > // Set objRS = Conn.Execute(strQuery)


    Why are you creating a recordset object to run a non-records-returning
    query? Just do this:
    Conn.Execute strQuery,,129


    > // conn.close
    >
    >
    > My problem is CurrentComment is larger than 1024 characters. Is
    > there a way
    > to get this to work without using a saved query as I do not believe
    > that is
    > available to my at this time.


    Why not? According to their website, Advantage supports stored procedures.

    You will likely need to use AppendChunk
    (http://msdn.microsoft.com/library/en-us/ado270/htm/mdamth01_3.asp), a
    Command object and a Parameter object. Check out the example in that link.

    I'm sorry but, due to my non-familiarity with Advantage, I am not going to
    be able to get into specifics. Have you tried their support system?

    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, Dec 16, 2003
    #4
  5. Johnd

    Johnd Guest

    Thankyou for taking the time to reply on this issue. I realize this is not
    the best group for this post, but I can't find any leads elsewhere. Also I
    was hoping that this waas a limitation of SQL and therefore best handled
    here. I have looked for stored procedures and been told that they are not
    supported in the Novell version of advanage 6.2 I will look further into
    it. In the mean time I will look at the "chunk" solution you have suggested
    and post my results for future reference.

    John,

    BTW. It never occured to me to just execute the object Conn.Execute
    strQuery,,129



    "Bob Barrows" <> wrote in message
    news:...
    > Johnd wrote:
    > > Thanks Bob but heres a little more detail.
    > >
    > > I am using Advantage server 6.2
    > >
    > > here is my code:
    > >
    > >
    > > // Set Conn = Server.CreateObject("ADODB.Connection")
    > > // Conn.Open "DSN=LocalOP"
    > > // strQuery = "insert into NCRheadcomments (hdrcomid, headerid,
    > > comdate,
    > > userid, comments, comtime)" &_
    > > // "values(10084,10080, '" & FormatDateTime( Date(), VBshortDate
    > > ) &_ // "', '" & session("CurrentUser") & "', '" &
    > > CurrentComment & "', '" &
    > > time & "')"
    > > // Set objRS = Conn.Execute(strQuery)

    >
    > Why are you creating a recordset object to run a non-records-returning
    > query? Just do this:
    > Conn.Execute strQuery,,129
    >
    >
    > > // conn.close
    > >
    > >
    > > My problem is CurrentComment is larger than 1024 characters. Is
    > > there a way
    > > to get this to work without using a saved query as I do not believe
    > > that is
    > > available to my at this time.

    >
    > Why not? According to their website, Advantage supports stored procedures.
    >
    > You will likely need to use AppendChunk
    > (http://msdn.microsoft.com/library/en-us/ado270/htm/mdamth01_3.asp), a
    > Command object and a Parameter object. Check out the example in that link.
    >
    > I'm sorry but, due to my non-familiarity with Advantage, I am not going to
    > be able to get into specifics. Have you tried their support system?
    >
    > 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.
    >
    >
     
    Johnd, Dec 16, 2003
    #5
  6. Johnd

    Ray at Guest

    "Bob Barrows" <> wrote in message
    news:...

    >
    > Why are you creating a recordset object to run a non-records-returning
    > query? Just do this:
    > Conn.Execute strQuery,,129
    >


    What's this Bob? Is that a numeric value of 129 instead of defining a
    constant? :p

    Ray at work
     
    Ray at, Dec 16, 2003
    #6
  7. Johnd

    Bob Barrows Guest

    Ray at <%=sLocation%> wrote:
    > "Bob Barrows" <> wrote in message
    > news:...
    >
    >>
    >> Why are you creating a recordset object to run a
    >> non-records-returning query? Just do this:
    >> Conn.Execute strQuery,,129
    >>

    >
    > What's this Bob? Is that a numeric value of 129 instead of defining a
    > constant? :p
    >

    I was going to tell him to go look it up if he asked ;-)
    --
    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, Dec 16, 2003
    #7
  8. "Ray at <%=sLocation%>" wrote:
    >
    > What's this Bob? Is that a numeric value of 129 instead of
    > defining a constant?


    What would he call it? adCmdTextExecuteNoRecords ?


    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms. Please do not contact
    me directly or ask me to contact you directly for assistance. If your
    question is worth asking, it's worth posting.
     
    Dave Anderson, Dec 16, 2003
    #8
  9. Johnd

    Ray at Guest

    adChar?

    Ray at work

    "Dave Anderson" <> wrote in message
    news:...
    > "Ray at <%=sLocation%>" wrote:
    > >
    > > What's this Bob? Is that a numeric value of 129 instead of
    > > defining a constant?

    >
    > What would he call it? adCmdTextExecuteNoRecords ?
    >
    >
    > --
    > Dave Anderson
    >
    > Unsolicited commercial email will be read at a cost of $500 per message.

    Use
    > of this email address implies consent to these terms. Please do not

    contact
    > me directly or ask me to contact you directly for assistance. If your
    > question is worth asking, it's worth posting.
    >
    >
     
    Ray at, Dec 16, 2003
    #9
  10. Johnd

    Johnd Guest

    If you guys put as much effort into helping people as you do into you
    humour, I think there may be hope for some of us rookies :)


    "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    news:%...
    > adChar?
    >
    > Ray at work
    >
    > "Dave Anderson" <> wrote in message
    > news:...
    > > "Ray at <%=sLocation%>" wrote:
    > > >
    > > > What's this Bob? Is that a numeric value of 129 instead of
    > > > defining a constant?

    > >
    > > What would he call it? adCmdTextExecuteNoRecords ?
    > >
    > >
    > > --
    > > Dave Anderson
    > >
    > > Unsolicited commercial email will be read at a cost of $500 per message.

    > Use
    > > of this email address implies consent to these terms. Please do not

    > contact
    > > me directly or ask me to contact you directly for assistance. If your
    > > question is worth asking, it's worth posting.
    > >
    > >

    >
    >
     
    Johnd, Dec 16, 2003
    #10
  11. Johnd

    Ray at Guest

    Does that mean your problem is not solved?

    Bob yelled at me once for suggesting that people should memorize common
    constants and use their numeric values, so I had to call him on it. :]

    Ray at work

    "Johnd" <johnjob{a}myway.com> wrote in message
    news:%23U8K%...
    > If you guys put as much effort into helping people as you do into you
    > humour, I think there may be hope for some of us rookies :)
    >
    >
    > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    > news:%...
    > > adChar?
    > >
    > > Ray at work
    > >
    > > "Dave Anderson" <> wrote in message
    > > news:...
    > > > "Ray at <%=sLocation%>" wrote:
    > > > >
    > > > > What's this Bob? Is that a numeric value of 129 instead of
    > > > > defining a constant?
    > > >
    > > > What would he call it? adCmdTextExecuteNoRecords ?
    > > >
    > > >
    > > > --
    > > > Dave Anderson
    > > >
    > > > Unsolicited commercial email will be read at a cost of $500 per

    message.
    > > Use
    > > > of this email address implies consent to these terms. Please do not

    > > contact
    > > > me directly or ask me to contact you directly for assistance. If your
    > > > question is worth asking, it's worth posting.
    > > >
    > > >

    > >
    > >

    >
    >
     
    Ray at, Dec 16, 2003
    #11
  12. Johnd

    Johnd Guest

    I have been programming for 14 years and I hate trying to figure out what a
    constant means in a example. I know they make code more "readable", but I
    am never sure if it's a constant, function, Reserved word etc. so when I am
    trying to understand something new, I prefer to see it without constants and
    then I'll make my own constants if I need them.

    Anyway I have tried switching to native OLE, (so far unsuccessful) and have
    yet to find any good reference to using parameters on advantage 6.2 for
    Novell. I have rewritten my routine to truncate anything over 1024 chars
    and create a new record with this data. Since this is only tracking info,
    it doesn't matter, but I will need a solution because I have other memo
    fields that I can't just truncate.

    John,



    "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    news:...
    > Does that mean your problem is not solved?
    >
    > Bob yelled at me once for suggesting that people should memorize common
    > constants and use their numeric values, so I had to call him on it. :]
    >
    > Ray at work
    >
    > "Johnd" <johnjob{a}myway.com> wrote in message
    > news:%23U8K%...
    > > If you guys put as much effort into helping people as you do into you
    > > humour, I think there may be hope for some of us rookies :)
    > >
    > >
    > > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    > > news:%...
    > > > adChar?
    > > >
    > > > Ray at work
    > > >
    > > > "Dave Anderson" <> wrote in message
    > > > news:...
    > > > > "Ray at <%=sLocation%>" wrote:
    > > > > >
    > > > > > What's this Bob? Is that a numeric value of 129 instead of
    > > > > > defining a constant?
    > > > >
    > > > > What would he call it? adCmdTextExecuteNoRecords ?
    > > > >
    > > > >
    > > > > --
    > > > > Dave Anderson
    > > > >
    > > > > Unsolicited commercial email will be read at a cost of $500 per

    > message.
    > > > Use
    > > > > of this email address implies consent to these terms. Please do not
    > > > contact
    > > > > me directly or ask me to contact you directly for assistance. If

    your
    > > > > question is worth asking, it's worth posting.
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    Johnd, Dec 16, 2003
    #12
  13. Johnd

    Bob Barrows Guest

    Dave Anderson wrote:
    > "Ray at <%=sLocation%>" wrote:
    >>
    >> What's this Bob? Is that a numeric value of 129 instead of
    >> defining a constant?

    >
    > What would he call it? adCmdTextExecuteNoRecords ?

    :)

    I wouldn't create a new constant: I would add together the already-existing
    constants.

    adCmdText + adExecuteNoRecords

    Bob
    --
    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, Dec 16, 2003
    #13
  14. Johnd

    Bob Barrows Guest

    Ray at <%=sLocation%> wrote:
    > Does that mean your problem is not solved?
    >
    > Bob yelled at me once for suggesting that people should memorize
    > common constants and use their numeric values, so I had to call him
    > on it. :]
    >


    Now wait a minute, I have never yelled at anyone in these newsgroups ....
    well ... not until this week that is <blush>

    I expressed and defended my opinion ... yes, that's the ticket :)

    Bob

    --
    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, Dec 16, 2003
    #14
  15. Johnd

    Bob Barrows Guest

    Johnd wrote:
    > I have been programming for 14 years and I hate trying to figure out
    > what a constant means in a example. I know they make code more
    > "readable", but I am never sure if it's a constant, function,
    > Reserved word etc.


    That's where good naming conventions come to the rescue.

    > so when I am trying to understand something new,
    > I prefer to see it without constants and then I'll make my own
    > constants if I need them.
    >

    I'm the opposite. I hate having to look up what the magic numbers mean. I've
    got better use for my brainpower than memorizing a bunch of numbers:
    especially when the documentation shows their hex values <grr>

    HTH,
    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, Dec 16, 2003
    #15
  16. Johnd

    Ray at Guest

    "Bob Barrows" <> wrote in message
    news:...

    > >

    > I'm the opposite. I hate having to look up what the magic numbers mean.

    I've
    > got better use for my brainpower than memorizing a bunch of numbers:
    > especially when the documentation shows their hex values <grr>


    All programmers should be able to look at a hex number or a binary number
    and understand it without having to translate it back to the arbitrary base
    10 number system. If you're old school, octets are also good to know. The
    key, though, is to not think, "okay, 100101 is, eh, 37." No, it's 100101.

    This is tongue-in-cheek, of course...

    Ray at work
     
    Ray at, Dec 16, 2003
    #16
  17. Johnd

    Bob Barrows Guest

    Johnd wrote:

    >
    > Anyway I have tried switching to native OLE, (so far unsuccessful)
    > and have yet to find any good reference to using parameters on
    > advantage 6.2 for Novell. I have rewritten my routine to truncate
    > anything over 1024 chars and create a new record with this data.
    > Since this is only tracking info, it doesn't matter, but I will need
    > a solution because I have other memo fields that I can't just
    > truncate.
    >


    Here is a way to parameterize your dynamic sql statement:
    'you will need to define the constants used, either by including the
    'adovbs.inc file, or by using the method described here:
    http://www.aspfaq.com/show.asp?id=2112


    strQuery = "insert into NCRheadcomments (hdrcomid, " & _
    "headerid,comdate,userid, comments, comtime) " &_
    "values(10084,10080, ?,?,?,?)"

    Set cmd = server.createobject("adodb.command")
    cmd.CommandType = 1 'adCmdText
    set cmd.ActiveConnection = conn
    set params = cmd.Parameters
    with cmd

    'here is where I will have trouble. I am not sure of the proper
    datatype mappings for the Advantage datatypes. See here
    'for the mappings for various other databases:
    http://www.able-consulting.com/ADODataTypeEnum.htm
    'Here are my guesses:

    params.append .CreateParameter("Date",adVarChar, _
    adParamInput, 8, FormatDateTime( Date(), VBshortDate))
    params.append .CreateParameter("User",adVarChar, _
    adParamInput, 50, session("CurrentUser"))
    params.append .CreateParameter("Comment",adLongVarChar, _
    adParamInput, 50, CurrentComment)
    params.append .CreateParameter("Time",adVarChar, _
    adParamInput, 8, time)
    ..Execute ,,adExecuteNoRecords
    end with

    Now, you MAY need to use AppendChunk to set the value of the Comment
    parameter. I am not sure. Give this a try without using AppendChunk.

    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, Dec 16, 2003
    #17
  18. Johnd

    Bob Barrows Guest

    Ray at <%=sLocation%> wrote:
    > "Bob Barrows" <> wrote in message
    > news:...
    >
    >>>

    >> I'm the opposite. I hate having to look up what the magic numbers
    >> mean. I've got better use for my brainpower than memorizing a bunch
    >> of numbers: especially when the documentation shows their hex values
    >> <grr>

    >
    > All programmers should be able to look at a hex number or a binary
    > number and understand it without having to translate it back to the
    > arbitrary base 10 number system. If you're old school, octets are
    > also good to know. The key, though, is to not think, "okay, 100101
    > is, eh, 37." No, it's 100101.


    Guess I'm not a programmer ;-)

    --
    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, Dec 16, 2003
    #18
  19. "Ray at <%=sLocation%>" wrote:
    >
    > adChar?


    I think not. Just containing the numeric value 129 is insufficient
    justification for using that constant. Given the range of *options* for
    CN.Execute(), the only way to interpret 129 is adCmdText +
    adExecuteNoRecords (1 + 0x80).


    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms. Please do not contact
    me directly or ask me to contact you directly for assistance. If your
    question is worth asking, it's worth posting.
     
    Dave Anderson, Dec 17, 2003
    #19
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jonck van der Kogel
    Replies:
    2
    Views:
    988
    Jonck van der Kogel
    May 27, 2004
  2. Jason
    Replies:
    2
    Views:
    521
    Jonathan Mcdougall
    May 13, 2006
  3. Ramon F Herrera
    Replies:
    8
    Views:
    486
    Pascal J. Bourguignon
    Sep 13, 2009
  4. kito
    Replies:
    2
    Views:
    420
  5. davidbe
    Replies:
    1
    Views:
    126
    davidbe
    Jan 16, 2006
Loading...

Share This Page