Passing Parameters to a query

[Johnd]

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?

 

[Bob Barrows]

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&[email protected]&rnum=6

HTH,
Bob Barrows

 

[Johnd]

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]

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

 

[Johnd]

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

 

[Ray at]

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?

Ray at work

 

[Bob Barrows]

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

I was going to tell him to go look it up if he asked ;-)

 

[Dave Anderson]

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]

adChar?

Ray at work

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]

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]

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]

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,

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

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

 

[Bob Barrows]

What would he call it? adCmdTextExecuteNoRecords ?

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

adCmdText + adExecuteNoRecords

Bob

 

[Bob Barrows]

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

 

[Bob Barrows]

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

 

[Ray at]

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

 

[Bob Barrows]

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

 

[Bob Barrows]

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 ;-)

 

[Dave Anderson]

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.