stored procedure problem

[Leon Shaw]

I'm I Assign the value to the following store procedures correctly in
vs.net?
(The code is my only concern)
Me.cmdAddMember.Parameters("@Username").Value = Me.txtUsername.Text

Me.cmdAddMember.Parameters(2).Value = Me.txtPassword.Text

Me.cmdAddMember.Parameters(3).Value = Me.ddlSecretQuestion.SelectedItem

Me.cmdAddMember.Parameters(4).Value = Me.txtSecretAnswer.Text

Me.cmdAddMember.Parameters(5).Value = Me.txtFirstName.Text

Me.cmdAddMember.Parameters(6).Value = Me.txtLastName.Text

Me.cmdAddMember.Parameters(7).Value = Me.ddlBirthMonth.SelectedItem

Me.cmdAddMember.Parameters(8).Value = Me.ddlBirthDay.SelectedItem

Me.cmdAddMember.Parameters(9).Value = Me.txtBirthYear.Text

etc.

[Jurjen de Groot]

Leon,

You should always name the parameter, you only named the first (@Username)
the others you just assigned values. Why not try this, it's more readable
too !!

With Me.cmdAddMember
.Parameters.Add("@Username", Me.txtUsername.text)
.Parameters.Add("@Password", Me.txtPassword.text)
etc...
End With

Hope this helps.

Jurjen de Groot
G.I.T.S., Netherlands

"Leon Shaw" <vnality@msn.com> wrote in message
news:%23HANJWUVDHA.1316@TK2MSFTNGP12.phx.gbl...

> I'm I Assign the value to the following store procedures correctly in
> vs.net?
> (The code is my only concern)
> Me.cmdAddMember.Parameters("@Username").Value = Me.txtUsername.Text
>
> Me.cmdAddMember.Parameters(2).Value = Me.txtPassword.Text
>
> Me.cmdAddMember.Parameters(3).Value = Me.ddlSecretQuestion.SelectedItem
>
> Me.cmdAddMember.Parameters(4).Value = Me.txtSecretAnswer.Text
>
> Me.cmdAddMember.Parameters(5).Value = Me.txtFirstName.Text
>
> Me.cmdAddMember.Parameters(6).Value = Me.txtLastName.Text
>
> Me.cmdAddMember.Parameters(7).Value = Me.ddlBirthMonth.SelectedItem
>
> Me.cmdAddMember.Parameters(8).Value = Me.ddlBirthDay.SelectedItem
>
> Me.cmdAddMember.Parameters(9).Value = Me.txtBirthYear.Text
>
> etc.
>
>

[Ian Briscoe]

Running IDS 9.21UC4 on Unixware 7.11

We have a number of identically structured databases in our group - an
accounts system. We want to be able to link common accounts together
for reporting purposes.

Each site will have 2 stored procedures:
get_account - to which will be passed a company identifier, an
account number,and the name of the remote server (site_name). This
will be run locally.

get_account_details - to which will be passed the same company
identifier and the account number. This will be run remotely.

get_account has the line:
LET execute_string =
"mach4@"||trim(site_name)||":get_account_detai ls";
CALL execute_string(company_id,customer_acc) returning........

this in effect builds and executes the remote call (database always
called mach4).
Problem is:
in dbaccess....
If I run 'get_account' for a given accont number and site_name I get
back the results from the remote site as I expected.
But if I then run it for the same acount at a different site I'm
getting the SAME results as from the first site.
If I exit dbaccess and run the procedure for the second site it works
fine.
If I run "mach4@site_name:get_account_details" directly from a local
site to a remote site it works fine time after time.
The problem only seems to be when one SPL is calling another?
This is driving me bonkers!!!!

Any tips?
TIA

Ian

[preetinder dhaliwal]

It looks like - for 1 session , server resolves the procedure or
function just once.

Only way out looks like in script -

dbaccess<<!
database db1;
execute procedure proc1(acc,site1);
close database;
database db1;
execute procedure proc1(acc,site2);
close database;
!

( or put above in a for loop - but you have to close and open database )

If u turn the trace on - you will see that second time the call results
in an existing proc id .

Rgds
Preetinder

Ian Briscoe wrote:

>Running IDS 9.21UC4 on Unixware 7.11
>
>We have a number of identically structured databases in our group - an
>accounts system. We want to be able to link common accounts together
>for reporting purposes.
>
>Each site will have 2 stored procedures:
>get_account - to which will be passed a company identifier, an
>account number,and the name of the remote server (site_name). This
>will be run locally.
>
>get_account_details - to which will be passed the same company
>identifier and the account number. This will be run remotely.
>
>get_account has the line:
>LET execute_string =
>"mach4@"||trim(site_name)||":get_account_details" ;
>CALL execute_string(company_id,customer_acc) returning........
>
>this in effect builds and executes the remote call (database always
>called mach4).
>Problem is:
>in dbaccess....
>If I run 'get_account' for a given accont number and site_name I get
>back the results from the remote site as I expected.
>But if I then run it for the same acount at a different site I'm
>getting the SAME results as from the first site.
>If I exit dbaccess and run the procedure for the second site it works
>fine.
>If I run "mach4@site_name:get_account_details" directly from a local
>site to a remote site it works fine time after time.
>The problem only seems to be when one SPL is calling another?
>This is driving me bonkers!!!!
>
>Any tips?
>TIA
>
>Ian
>
>

sending to informix-list

[John Shaw]

Hi,

I'm not real experienced with this and I can't figure out why this
isn't working. I get results in the query analyzer with "exec
sp_Orders_SELECT @Exported='err'" but nothing when connecting via ASP.
I don't even get a record count.
response.write(rsOrdersSelect.RecordCount) doesn't display anything.

Thanks

ASP Call to SP:

Dim cmdOrdersSelect ' Command object
Dim rsOrdersSelect ' Recordset object
Dim arrOrdersSelect ' Array
Dim strExported

strExported = "err"

On Error Resume Next

' Get order list
Set cmdOrdersSelect = Server.CreateObject("ADODB.Command")
With cmdOrdersSelect
.ActiveConnection = Application("ConnString")
.CommandText = "sp_Orders_SELECT"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@Exported", adVarChar,
adParamInput, _
3, strExported)
set rsOrdersSelect = .Execute
End With


' Copy recordsets to arrays
arrOrdersSelect = rsOrdersSelect.GetRows()

' Close objects
DestroyObject(cmdOrdersSelect)
CloseObject(rsOrdersSelect)



SP:
CREATE PROCEDURE sp_Orders_SELECT

(
@Exported varchar(3),
@strQuery varchar(100) = NULL)

AS

SET @strQuery= 'SELECT * FROM Orders WHERE Exported ="' +
@Exported + '" AND Completed = "yes"'

BEGIN -- Execute search

EXECUTE (@strQuery)

RETURN(0)
END
GO

[Bob Barrows]

John Shaw wrote:

> Hi,
>
> I'm not real experienced with this and I can't figure out why this
> isn't working. I get results in the query analyzer with "exec
> sp_Orders_SELECT @Exported='err'" but nothing when connecting via ASP.
> I don't even get a record count.
> response.write(rsOrdersSelect.RecordCount) doesn't display anything.
>

<snip>

> On Error Resume Next
>

<snip>

> .Parameters.Append .CreateParameter("@Exported", adVarChar,
> adParamInput, _
> 3, strExported)

Your stored procedure requires two paramters. You've only appended one
parameter, so this command will not execute without error. Unfortunately,
your "On Error ... " statement above is masking the error.

With ADO, you have to append all parameters, even if they have default
values in the procedure declaration.

If you wish to persist with using a Command object, then you may benefit
from my stored procedure code generator which is available here:
[url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp[/url]

<snip>

> SP:
> CREATE PROCEDURE sp_Orders_SELECT

You should get rid of the "sp_". That prefix should be reserved for system
stored procedures. Using it on user-procedures will make SQL take a little
longer to execute the procedure due to its searching for the procedure in
the Master database before looking in the current database for the
procedure.

>
> (
> @Exported varchar(3),
> @strQuery varchar(100) = NULL)

Why have you declared @strQuery as a parameter? It does not appear as if you
are planning to pass a value to it. Even if you did, that value would be
lost in your SET statement below.

I suspect you actually intended this to be a local variable. It should be
declared in the body of your procedure after the "AS" using the DECLARE
keyword.

>
> AS
>

DECLARE @strQuery varchar(100)

> SET @strQuery= 'SELECT * FROM Orders WHERE Exported ="' +

<snip>


Now your procedure only has a single parameter, allowing the Command code
you wrote above to work correctly.

This procedure has no output parameters, and it does not appear as if you
intend to read the Return parameter value, so you may wish to skip creating
the Command object entirely. The following will execute your procedure
simply and quickly:


dim cn, rsOrdersSelect
set cn = server.createobject("adodb.connection")
cn.open Application("ConnString")
set rsOrdersSelect = server.createobject("adodb.recordset")
'assuming you've followed my advice to drop the "sp_":
cn.Orders_SELECT strExported, rsOrdersSelect

If you have a procedure that does not return records, then simply leave out
the recordset variable.


--
HTH,
Bob Barrows - ASP MVP
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.

[Aaron Bertrand - MVP]

> With ADO, you have to append all parameters, even if they have default

> values in the procedure declaration.

Do you mean with a command object? I have no problems keeping optional
parameters as optional, when using conn.execute...

[Bob Barrows]

Aaron Bertrand - MVP wrote:

>> With ADO, you have to append all parameters, even if they have
>> default values in the procedure declaration.

>
> Do you mean with a command object?

Yes, that is why I talked about appending parameters. :-)

> I have no problems keeping
> optional parameters as optional, when using conn.execute...

I know, that's because when you send the dynamic sql statement, SQL Server
creates an execution plan. ADO is not involved with the process, except that
it is sending the string to be executed.
When you use a Command object with adExecuteStoredProc, ADO needs to match
the parameters in the collection with the parameters declared in the
procedure in order to do its datatype checking, etc. If you do not supply
the correct number of parameters, ADO raises an error before it even tries
to actually execute the procedure.

Someday I'll have to try running a procedure with optional parameters using
the procedure-as-connection-method technique. I suspect the optional
parameters will need to be supplied with this technique as well.

--
HTH,
Bob Barrows - ASP MVP
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.

[Aaron Bertrand - MVP]

> Someday I'll have to try running a procedure with optional parameters
using

> the procedure-as-connection-method technique. I suspect the optional
> parameters will need to be supplied with this technique as well.

No sir. With this procedure:

CREATE PROCEDURE dbo.optional
@foo INT = 1,
@bar INT = 2
AS
BEGIN
SET NOCOUNT ON
SELECT 1
END
GO

These all work fine:

<%
set conn = createobject("ADODB.Connection")
conn.open "Provider=SQLOLEDB; <dream on, etc.>;"

conn.execute "EXEC optional @foo = 1, @bar = 1",,129
conn.execute "EXEC optional @foo = 1",,129
conn.execute "EXEC optional @bar = 1",,129
conn.execute "EXEC optional 1, 1",,129
conn.execute "EXEC optional 1",,129
conn.execute "EXEC optional",,129
conn.optional 1, 2
conn.optional 1
conn.optional

set rs = conn.execute("EXEC optional")
response.write rs(0)
rs.close: set rs = nothing
conn.close: set conn = nothing
%>

Having to specify optional parameters kind of defeats the purpose of giving
them defaults, doesn't it? I don't touch the command object myself, but I'd
be really surprised it leaving out an optional parameter would create an
error.

A

[Bob Barrows]

Aaron Bertrand - MVP wrote:

>> Someday I'll have to try running a procedure with optional
>> parameters using the procedure-as-connection-method technique. I
>> suspect the optional parameters will need to be supplied with this
>> technique as well.

>
> No sir. With this procedure:
>
> CREATE PROCEDURE dbo.optional
> @foo INT = 1,
> @bar INT = 2
> AS

<snip>

> conn.optional 1, 2
> conn.optional 1
> conn.optional
>

Thanks, you've saved me some time.

<snip>

> Having to specify optional parameters kind of defeats the purpose of
> giving them defaults, doesn't it? I don't touch the command object
> myself, but I'd be really surprised it leaving out an optional
> parameter would create an error.

I learned the hard way. I've had to supply Nulls for default numbers and
datetimes. To me, it's a small price to pay. A couple extra lines of code in
exchange for:
- the security of strong data typing
- the ability to utilize Return and Output parameters

But, to each his own.

--
HTH,
Bob Barrows - ASP MVP
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.

[Aaron Bertrand - MVP]

> I learned the hard way. I've had to supply Nulls for default numbers and

> datetimes.

Can you provide a repro?

[Bob Barrows]

Aaron Bertrand - MVP wrote:

>> I learned the hard way. I've had to supply Nulls for default numbers
>> and datetimes.

>
> Can you provide a repro?

Nope. I just tried it and was able to run a procedure without appending the
optional parameter to the Command's Parameters collection. Thanks for making
me take another look at this. I could have sworn I was forced to include the
optional parameters at one point. Maybe it was an earlier version of ADO,
before it started supporting named parameters ...

--
HTH,
Bob Barrows - ASP MVP
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.

[Aaron Bertrand - MVP]

> Nope. I just tried it and was able to run a procedure without appending
the

> optional parameter to the Command's Parameters collection. Thanks for

making

> me take another look at this. I could have sworn I was forced to include

the

> optional parameters at one point. Maybe it was an earlier version of ADO,
> before it started supporting named parameters ...

Well, glad we know the answers now.

[Bob Barrows]

Bob Barrows wrote:

> John Shaw wrote:

Hmm, based on my conversation with Aaron, it seems I gave you an incorrect
answer. Please post a followup if you need further assistance.
..

--
HTH,
Bob Barrows - ASP MVP
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.

[-D-]

I am getting the following error when I try to execute a stored procedure:
'Application uses a value of the wrong type for the current operation' This is
the line where the error occurs: cmd_SpRevwrCmtsUpdate.Parameters.Append
cmd_SpRevwrCmtsUpdate.CreateParameter('@cmtid', 3,
1,4,cmd_SpRevwrCmtsUpdate__cmtid) This is the code where I define the
parameter: Dim cmd_SpRevwrCmtsUpdate__cmtid cmd_SpRevwrCmtsUpdate__cmtid = ''
if(Request('commentsID') <> '') then cmd_SpRevwrCmtsUpdate__cmtid =
Request('commentsID') Here is the stored procedure: CREATE PROCEDURE
dbo.ABR_REVWR_CMTS_UPDATE ( @cmtid int, @statusrevw varchar(50), @statusabr
varchar(50), @comments varchar(8000) ) AS BEGIN UPDATE C SET
ABR_REVWR_CMTS_LAST_UPD_DT=convert(char(10), getdate(), 101),
ABR_REVWR_CMTS_REVW_STATUS_ID= ABR_REVW_STATUS_ID, ABR_REVWR_CMTS_ABR_STATUS_ID
= ABR_STATUS_ID, ABR_REVWR_CMTS = substring(@comments, 1, 3999) FROM
dbo.ABR_REVWR_CMTS C, dbo.DIM_ABR_STATUS, dbo.DIM_ABR_REVW_STATUS WHERE
ABR_REVWR_CMTS_ID = @cmtid AND @statusrevw = ABR_STATUS AND @statusabr =
ABR_REVW_STATUS END GO It appears that the parameter is not recognized as an
integer value when the value is run in the procedure? Not sure what I'm
missing? I can switch the value to a varchar in the stored procedure and then
convert the value to integer. But, I'd like to figure out why this happening
and just pass the value into the stored procedure as is, which should be an
integer? Any help is appreciated. Thanks. -D-

[Franklin LO]

Hi everybody,

I tried to create a stored procedure in MSSQL and use Dreamweaver MX to get
the result but failed. The codes are as follow: (It's a little bit long but I
really need help~!)

In MSSQL:

CREATE PROCEDURE dbo.dt_calwppercent @ID nvarchar (10), @Season int AS

DECLARE @tbl_record table(eventDate datetime, race int, trainerChiName
nvarchar(30), course nvarchar(20),
track char(10), status char(10), class int, rating int, lbs int, equipment
nvarchar(30))

INSERT INTO @tbl_record

SELECT history.historyEventDate, history.historyRace, trainer.trainerChiName,
course.courseName,
track.trackName, status.statusName, history.historyClass,
history.historyRating, history.historylbs, history.historyEquipment

FROM dbo.tbl_history history, dbo.tbl_trainer trainer, dbo.tbl_distance
distance,
dbo.tbl_track track, dbo.tbl_course course, dbo.tbl_status status,
dbo.tbl_season season

WHERE history.historyHorseID = @ID AND season.seasonID = @Season
AND history.historyTrainerID = trainer.trainerID
AND history.historyTrackID = track.trackID AND history.historyCourseID =
course.courseID
AND history.historyStatusID = status.statusID AND history.historySeasonID =
season.seasonID

GROUP BY history.historyEventDate, history.historyRace,
trainer.trainerChiName, course.courseName,
track.trackName, status.statusName, history.historyClass,
history.historyRating, history.historylbs, history.historyEquipment
GO

The syntax is checked and has no problem.

In Dreamweaver:

<cfstoredproc procedure="dt_calwppercent"
datasource="#Application.datasource#" debug="yes">
<cfprocparam type="IN" CFSQLType="cf_sql_integer" dbvarname="Season"
value="#Application.seasonID#">
<cfprocparam type="IN" CFSQLType="cf_sql_longvarchar" dbvarname="ID"
value="#ID#">
<cfprocresult name="sp_Season">
</cfstoredproc>

But when I test the query, it just said "Error Executing Database Query"
without any other hints. Can someone help me to fix this problem? Many Thanks

[SQLMenace]

Put the parameters in the same order

<cfstoredproc procedure="dt_calwppercent"
datasource="#Application.datasource#" debug="yes">
<cfprocparam type="IN" CFSQLType="cf_sql_longvarchar" dbvarname="ID"
value="#ID#">
<cfprocparam type="IN" CFSQLType="cf_sql_integer" dbvarname="Season"
value="#Application.seasonID#">
<cfprocresult name="sp_Season">
</cfstoredproc>

[Franklin LO]

Thanks for your reply. I'll try = )

2 more questions:

1) the table @tbl_record I created is a temp table, according to the tech
note, right? Can I make it as a permanent table so that I can preserve it for
later use?

2) Is that possible for me to create another stored procedure and put the data
into the same table i.e. @tbl_record? Coz the data in @tbl_record need to be
calculated in a complex way

Many thanks

[Franklin LO]

Hi Menace, the problem is still there. Any other hints?

[SQLMenace]

Can you run the query in Query Analyzer?
What error message are you getting?