HELP: stored procedure (query) from out vb6 does not work?!

Similar Threads:

1.Update Stored Procedure do not work in Vb but with Query Analyzer

2.Variable not being passed to stored procedure or query

I have been trying to debug parameters on a stored procedure in
classic ASP using VB6 and ADO and as part of this process reduced to
checking on a simple query, but continue to get an error though it
varies depending on the circumstances. I've got similar queries
elsewhere in my code without any errors so not sure where the problem
is.

Anyway, this is one example of when an error is being returned.  Any
ideas?

    Dim oCmd
    Dim ID
    Set oCmd = Server.CreateObject("ADODB.Command")
    oCmd.CommandText = "select * from els_alias where als_id_num =
@ID"
    oCmd.ActiveConnection = Application("conn")
    ID = 7379
    oCmd.Parameters.Append oCmd.CreateParameter("@ID", adInteger,
adParamInput, 4, ID)
    oCmd.Parameters.Append oCmd.CreateParameter("@ID", adInteger,
adParamInput, 4, ID)
    oCmd.Execute

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Must declare the scalar variable "@ID".

3.Query Analyser and VB6 ADO Stored Procedure UPDATE Difference

Hi All

For the last few days I have been trying to figure out this problem I have
been having.
I have posted a question last week If you would like to read the background
to the problem the subject is below. (Sorry I haven't figured out how to
insert a link to it yet)

SUBJECT : Really tough ADO Stored Procedure Question. Please Help!!!



So after many hours of testing and saving of data and checking I finally
have pin pointed the problem to this one statement.

 UPDATE #tblBatchDataHolder
 SET #tblBatchDataHolder.PlainText = #tblNewData.PlainText,
#tblBatchDataHolder.PlainText2 = #tblNewData.PlainText2
 FROM #tblNewData INNER JOIN #tblBatchDataHolder
 ON #tblNewData.InstructionID = #tblBatchDataHolder.InstructionID

When run from Query Analyser works fine but when run through ADO doesn't
work at all.(But there is no error)
So I changed the Syntax to this.

  UPDATE #tblBatchDataHolder
   SET #tblBatchDataHolder.PlainText = #tblNewData.PlainText,
#tblBatchDataHolder.PlainText2 = #tblNewData.PlainText2
   FROM #tblNewData, #tblBatchDataHolder
   WHERE #tblNewData.InstructionID = #tblBatchDataHolder.InstructionID

And I get the same result as before. Fine from QA but Not fine from VB6 ADO
(No Error).



I cannot be sure if the statement is being run successfully and then rolled
back, but that is my impression of what is happening.
There are no errors to suggest other wise.

 So my question is this.

Can any one see any reason why these 2 statements would run successfully in
QA but not from VB6 ADO?
Or why they would not cause an error in the SP to force a rollback of the
entire SP rather than roll back just the last Update.

Thanks,

Ian

4.Stored Procedure works in Crystal Reports 8.5 but not in version 9

I am updating a set of reports that use SQL Stored Procedures as their
datasource.  All 10 of them worked well but there is one I cannot get
to work in Version 9.

I should be seeing about 10 fields for the stored procedure in
question but instead I only see one 'Settable Database Options:' which
seems to have no data in it.  I can't really proceed any further than
this.

I am new to Crystal 9.  Has anyone had issues with stored procedures
before?

Thanks ~JAN

5.Stored procedure works in Query Analyser, not ADP

I have the following Sproc that works in QA, but in my ADP as a report
recordsource I get 'provider command for child rowset does not produce
a rowset'.

Now this works if I take out the insert into RequestTracking.

ALTER Procedure [dbo].[usp_OpenClosed_CallRpts_48Hour_Updates]
	@CallID int = Null,
	@Status smallint = Null,
	@SubStatus smallint = Null,
	@DateOpened_Fr datetime = Null,
	@DateOpened_To datetime = Null,
	@DateResolved_Fr datetime = Null,
	@DateResolved_To datetime = Null,
	@ResolvedByEmployee smallint = Null,
	@OpenedBy smallint = Null,
	@AssignedTo smallint = Null,
	@ContactMethod smallint = Null,
	@ClientID smallint = Null,
	@ContactPrefID varchar(7) = Null,
	@BusLineName varchar(40) = Null,
	@ContactName varchar(40) = Null,
	@ActionType smallint = Null,
	@Dept smallint = Null,
	@SystemID smallint = Null,
	@FollowupRequired bit = Null
AS

--=== Strip the time element from dates (just to be safe) by
converting
--	  to whole days and back to a date.  Usually faster than CONVERT.
--	  0 is a date (01/01/1900 00:00:00.000)

	IF @DateOpened_Fr IS NOT NULL
	SELECT @DateOpened_Fr = DATEADD(dd,DATEDIFF(dd,0,@DateOpened_Fr),0)

	IF @DateOpened_To IS NOT NULL
	SELECT @DateOpened_To = DATEADD(dd,DATEDIFF(dd,0,@DateOpened_To),0)

	IF @DateResolved_Fr IS NOT NULL
	SELECT @DateResolved_Fr = DATEADD(dd,DATEDIFF(dd,0,@DateOpened_Fr),0)

	IF @DateResolved_To IS NOT NULL
	SELECT @DateResolved_To = DATEADD(dd,DATEDIFF(dd,0,@DateOpened_To),0)

--=== Track Request
INSERT INTO dbo.RequestTracking
	(RanByUser, RanProc, RanDateTime, CallID, Status, SubStatus,
DateOpened_Fr,
	DateOpened_To, DateResolved_Fr, DateResolved_To, ResolvedByEmployee,
OpenedBy,
	AssignedTo, ContactMethod, ClientID, ContactPrefID, BusLineName,
ContactName,
	ActionType, Dept, SystemID, FollowupRequired)
SELECT
	User, OBJECT_NAME(@@PROCID), getdate(), @CallID, @Status, @SubStatus,
@DateOpened_Fr,
        @DateOpened_To, @DateResolved_Fr, @DateResolved_To,
@ResolvedByEmployee, @OpenedBy,
	@AssignedTo, @ContactMethod, @ClientID, @ContactPrefID, @BusLineName,
	@ContactName, @ActionType, @Dept, @SystemID, @FollowupRequired

--=== Return the results
SELECT
<<Snip>>

6. Need help in executing the MySql Stored procedure in VB6

7. Form using stored procedure does not work in Access Runtime

8. Stored Procedure as RecordSource does not work in Access 2002