ADODB.Command error '800a0bb9' ???

[ASPSQLVB]

I am running into an error. This code was working before and now its not.
I am trying to take the TOP 3 HIGHEST TotalWeight values from a table and insert them into another table.
I can insert the First row of data but when it comes to inserting the next row that's when the error occurs.
Here is the ERROR I am getting.

ADODB.Command error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/CheckCustomerExpiration.asp, line 200

I pointed out the ERROR Line.


If AnglersPerTeam = "Double" Then

Dim RS4,STRSQL4,RS4_FishAngler2,STRSQL4_FishAngler2

Set RS4 = Server.CreateObject("ADODB.Recordset")

STRSQL4 = "SELECT TOP 3 MAX(TotalWeight.TotalWeight) AS TotalWeight, COUNT(*) AS Expr3, COUNT(*) AS Expr4, TotalWeight.TotalWeightId,BigFish.BigFish, FishAngler.Bio, FishAngler.AnglerPhoto, FishAngler.PegNumber, FishAngler.TeamName,FishAngler.FirstName, FishAngler.LastName, FishAngler.State, FishAngler.Country, FishAngler.Sponsor,FishAngler.SponsorWebAddress, FishAngler.AnglerPassword, TotalWeight.TotalWeight AS Expr1, FishAngler.FishId FROM BigFish INNER JOIN FishAngler ON BigFish.FishId = FishAngler.FishId INNER JOIN TotalWeight ON FishAngler.FishId = TotalWeight.FishId GROUP BY TotalWeight.TotalWeightId, BigFish.BigFish, FishAngler.Bio, FishAngler.AnglerPhoto, FishAngler.PegNumber, FishAngler.TeamName, FishAngler.FirstName, FishAngler.LastName, FishAngler.State, FishAngler.Country, FishAngler.Sponsor, FishAngler.SponsorWebAddress, FishAngler.AnglerPassword, TotalWeight.TotalWeight, FishAngler.FishId ORDER BY MAX(TotalWeight.TotalWeight) DESC"

RS4.Open STRSQL4,Conn

Do Until RS4.EOF
Set RS4_FishAngler2 = Server.CreateObject("ADODB.Recordset")

STRSQL4_FishAngler2 = "Select * from FishAngler2 Where PegNumber=" & RS4("PegNumber")

RS4_FishAngler2.Open STRSQL4_FishAngler2,Conn,adOpenKeyset,adLockOptimistic,adCmdText

Dim RS_Fish,STRSQL_Fish

Set RS_Fish = Server.CreateObject("ADODB.Recordset")

STRSQL_Fish = "Select FishCount from Fish Where FishId=" & RS4("FishId")

RS_Fish.Open STRSQL_Fish,Conn,adOpenStatic,adLockOptimistic,adCmdText

Set Comm2 = Server.CreateObject("ADODB.Command")

With Comm2
.ActiveConnection = Conn
.CommandText = "NewExpiredCarpAnglers"
.CommandType = adCmdStoredProc
End With

'ADD THE TOP 3 ANGLERS TO THE NEW TABLES

Comm2.Parameters.Append Comm2.CreateParameter("FishId",adInteger,adParamReturnValue,4)
Dim Bio1,Bio2

Bio1 = CheckData(RS4("Bio"))
Bio2 = CheckData(RS4_FishAngler2("Bio"))
Comm2.Parameters.Append Comm2.CreateParameter("@Bio1",adVarChar,adParamInput,3000,Bio1)
Comm2.Parameters.Append Comm2.CreateParameter("@Bio2",adVarChar,adParamInput,3000,Bio2)
Dim AnglerPhoto1,AnglerPhoto2
AnglerPhoto1 = RS4("AnglerPhoto")
AnglerPhoto2 = RS4_FishAngler2("AnglerPhoto")
Comm2.Parameters.Append Comm2.CreateParameter("@AnglerPhoto1",adVarChar,adParamInput,100,AnglerPhoto1)
Comm2.Parameters.Append Comm2.CreateParameter("@AnglerPhoto2",adVarChar,adParamInput,100,AnglerPhoto2)
Comm2.Parameters.Append Comm2.CreateParameter("@TeamName",adVarChar,adParamInput,50,RS4("TeamName"))
Comm2.Parameters.Append Comm2.CreateParameter("@PegNumber",adInteger,adParamInput,4,RS4("PegNumber"))
Dim FirstName1,FirstName2
FirstName1 = RS4("FirstName")
FirstName2 = RS4_FishAngler2("FirstName")
Comm2.Parameters.Append Comm2.CreateParameter("@FirstName1",adVarChar,adParamInput,50,FirstName1)
Comm2.Parameters.Append Comm2.CreateParameter("@FirstName2",adVarChar,adParamInput,50,FirstName2)
Dim LastName1,LastName2
LastName1 = RS4("LastName")
LastName2 = RS4_FishAngler2("LastName")
Comm2.Parameters.Append Comm2.CreateParameter("@LastName1",adVarChar,adParamInput,50,LastName1)
Comm2.Parameters.Append Comm2.CreateParameter("@LastName2",adVarChar,adParamInput,50,LastName2)
Dim State1,State2
State1 = RS4("State")
State2 = RS4_FishAngler2("State")
Comm2.Parameters.Append Comm2.CreateParameter("@State1",adChar,adParamInput,10,State1)
Comm2.Parameters.Append Comm2.CreateParameter("@State2",adChar,adParamInput,10,State2)

Comm2.Parameters.Append Comm2.CreateParameter("@Country",advarChar,adParamInput,50,RS4("Country"))
Comm2.Parameters.Append Comm2.CreateParameter("@Sponsor",adVarChar,adParamInput,50,RS4("Sponsor"))
Comm2.Parameters.Append Comm2.CreateParameter("@SponsorWebAddress",adVarChar,adParamInput,50,RS4("SponsorWebAddress"))
Comm2.Parameters.Append Comm2.CreateParameter("@NameofEvent",advarChar,adParamInput,50,RS2("NameofEvent"))
Comm2.Parameters.Append Comm2.CreateParameter("@StartDate",adDate,adParamInput,8,RS2("StartDate"))
Comm2.Parameters.Append Comm2.CreateParameter("@LengthofEvent",adInteger,adParamInput,4,RS2("LengthofEvent"))

Dim param
Set param = Comm2.CreateParameter("@BigFish",adDecimal,adParamInput,9,RS4("BigFish"))
param.Precision = 10
param.NumericScale = 2
param.Value = RS4("BigFish")

Comm2.Parameters.Append param

Dim param3
Set param3 = Comm2.CreateParameter("@TotalWeight",adDecimal,adParamInput,9,RS4("TotalWeight"))
param3.Precision = 10
param3.NumericScale = 2
param3.Value = RS4("TotalWeight")

Comm2.Parameters.Append param3

THE ERROR IS HERE ~~~~~~~> Comm2.Parameters.Append Comm2.CreateParameter("@FishCount",adInteger,adParamInput,4,RS_Fish("FishCount"))

Comm2.Execute,,adExecuteNoRecords

dim valueHolder2

valueHolder2 = Comm2.Parameters("FishId")

RS4.MoveNext

Loop

[russell]

sure that RS_Fish("FishCount") has a value?
also, sure that error isn't on the following line (.Execute) ?

[ASPSQLVB]

That is the line that generates the error everytime.
There is a value when I do a Response.Write.
Would the CONSTANTS in the RS_Fish.Open STRSQL_Fish,Conn,adOpenStatic,adLockOptimistic,adCmdText
statement need to be adOpenKeyset instead of adOpenStatic?

While Looping through the Recordset, I am thinking the cursor needs to be fully scrollable and not just take a snapshot of the data......adOpenKeyset will allow the cursor to move to whatever record whereas the adOpenStatic will not ?

[russell]

what happens if you change this

RS_Fish("FishCount")

to this

Clng(RS_Fish("FishCount"))

[ASPSQLVB]

I did not use the CLONG function but, I used the CINT function and that did not make a difference.
The value could be a number between either 1 and 100.

[russell]

works if u hard code a value, rather than pull it from RS_Fish?

[russell]

wait a minute, i see the problem...you are appending parameters in a loop...cant do that....

what does the SP NewExpiredCarpAnglers look like?

[ASPSQLVB]

CREATE PROCEDURE NewExpiredCarpAnglers(@Bio1 varchar(3000),@Bio2 varchar(3000),@AnglerPhoto1 varchar(100),@AnglerPhoto2 varchar(100),@TeamName varchar(50),@PegNumber int,@FirstName1 varchar(50),@FirstName2 varchar(50),@LastName1 varchar(50),@LastName2 varchar(50),@State1 char(10),@State2 char(10),@Country varchar(50),@Sponsor varchar(50),@SponsorWebAddress varchar(50),@NameofEvent varchar(50),@StartDate datetime,@LengthofEvent int,@BigFish decimal (9),@TotalWeight decimal(9),@FishCount int)
as
Insert into ExpiredDoubleTeam(Bio1,Bio2,AnglerPhoto1,AnglerPhoto2,TeamName,PegNumber,FirstName1,FirstName2,LastN ame1,LastName2,State1,State2,Country,Sponsor,SponsorWebAddress,NameofEvent,StartDate,LengthofEvent,B igFish,TotalWeight,FishCount)
Values(@Bio1,@Bio2,@AnglerPhoto1,@AnglerPhoto2,@TeamName,@PegNumber,@FirstName1,@FirstName2,@LastNam e1,@LastName2,@State1,@State2,@Country,@Sponsor,@SponsorWebAddress,@NameofEvent,@StartDate,@Lengthof Event,@BigFish,@TotalWeight,@FishCount)

Return @@identity

[ASPSQLVB]

There was three rows of data I was inserting and the field name FISHCOUNT for each row had the same value number ("1"). I just chnaged the value to the secong row column FishCount to number ("2") and now it works fine.
Can this be explained ?

[ASPSQLVB]

I tried again with the column name FishCount having the same value and it now seems to be working....very strange. But, if it works then I guess problem solved. I just dont know what the problem was.

[ASPSQLVB]

New question...Russell, I am trying to insert a decimal value from one table into another but, the insert table is not showing the numeric value to the right of the decimal....I did a Response.Write to make sure the value is there and it is 13.06. Which is correct. But after its inserted into the table it reads 13.
The scale ann precision in the table is 10 , 2 the datatype is decimal. Everything looks good in the Stored procediure also. I even tried wrapping the recordset value in CDBL before inserting. Not working either.


CODE!!!!!!!!!!!!!
Dim ConnFullResults, RSFullResults, STRSQLFullResults

Set ConnFullResults = Server.CreateObject("ADODB.Connection")
ConnFullResults.Open "Provider=SQLOLEDB;Data Source=whsql-v06.prod.mesa1.secureserver.net;Initial Catalog=DB_123978;User Id=ASPSQLVB;Password=ASPVBSQL"
Set RSFullResults = Server.CreateObject("ADODB.Recordset")

STRSQLFullResults = "SELECT TotalWeight.TotalWeight, COUNT(*) AS Expr3, COUNT(*) AS Expr4, TotalWeight.TotalWeightId,BigFish.BigFish, FishAngler.Bio, FishAngler.AnglerPhoto, FishAngler.PegNumber, FishAngler.TeamName,FishAngler.FirstName, FishAngler.LastName, FishAngler.State, FishAngler.Country, FishAngler.Sponsor,FishAngler.SponsorWebAddress, FishAngler.AnglerPassword, FishAngler.FishId FROM BigFish INNER JOIN FishAngler ON BigFish.FishId = FishAngler.FishId INNER JOIN TotalWeight ON FishAngler.FishId = TotalWeight.FishId GROUP BY TotalWeight.TotalWeightId, BigFish.BigFish, FishAngler.Bio, FishAngler.AnglerPhoto, FishAngler.PegNumber, FishAngler.TeamName, FishAngler.FirstName, FishAngler.LastName, FishAngler.State, FishAngler.Country, FishAngler.Sponsor, FishAngler.SponsorWebAddress, FishAngler.AnglerPassword, TotalWeight.TotalWeight, FishAngler.FishId" 'ORDER BY MAX(TotalWeight.TotalWeight) DESC"

RSFullResults.Open STRSQLFullResults,ConnFullResults

Do Until RSFullResults.EOF

Set RSFullResults2 = Server.CreateObject("ADODB.Recordset")

STRSQLFullResults2 = "Select * from FishAngler2 Where PegNumber=" & RSFullResults("PegNumber")

RSFullResults2.Open STRSQLFullResults2,ConnFullResults,adOpenKeyset,adLockOptimistic,adCmdText

Dim STRSQLFISHFullResults

Set RSFISHFullResults = Server.CreateObject("ADODB.Recordset")

STRSQLFISHFullResults = "Select FishCount from Fish Where FishId=" & RSFullResults("FishId")

RSFISHFullResults.Open STRSQLFISHFullResults,ConnFullResults,adOpenKeyset,adLockOptimistic,adCmdText

Set CommFullResults = Server.CreateObject("ADODB.Command")

With CommFullResults
.ActiveConnection = ConnFullResults
.CommandText = "NewExpiredDoubleTeam_FullResults"
.CommandType = adCmdStoredProc
End With

'Comm2.Parameters.Append Comm2.CreateParameter("FishId",adInteger,adParamReturnValue,4)

CommFullResults.Parameters.Append CommFullResults.CreateParameter("@NameofEvent",adVarChar,adParamInput,100,RS2("NameofEvent"))
CommFullResults.Parameters.Append CommFullResults.CreateParameter("@TeamNumber",adInteger,adParamInput,4,RSFullResults("PegNumber"))
CommFullResults.Parameters.Append CommFullResults.CreateParameter("@Team",adVarChar,adParamInput,100,RSFullResults("TeamName"))



Dim State
State = RSFullResults("State") & "," & RSFullResults2("State")

CommFullResults.Parameters.Append CommFullResults.CreateParameter("@State",adVarChar,adParamInput,50,State)


CommFullResults.Parameters.Append CommFullResults.CreateParameter("@Country",advarChar,adParamInput,50,RSFullResults("Country"))

'Comm2.Parameters.Append Comm2.CreateParameter("@BigFish",adDecimal,adParamInput,9,RS4("BigFish"))
Dim paramFullResults1
Set paramFullResults1 = CommFullResults.CreateParameter("@BigFish",adDecimal,adParamInput,9,RSFullResults("BigFish"))
paramFullResults1.Precision = 10
paramFullResults1.NumericScale = 2
paramFullResults1.Value = RSFullResults("BigFish")

CommFullResults.Parameters.Append paramFullResults1
'Comm2.Parameters.Append Comm2.CreateParameter("@TotalWeight",adDecimal,adParamInput,9,RS4("TotalWeight"))
'Response.Write RSFullResults("TotalWeight")
'Response.End

Dim paramFullResults2
Set paramFullResults2 = CommFullResults.CreateParameter("@TotalWeight",adDecimal,adParamInput,9,RSFullResults("TotalWeight"))
paramFullResults2.Precision = 10
paramFullResults2.NumericScale = 2
paramFullResults2.Value = RSFullResults("TotalWeight")

CommFullResults.Parameters.Append paramFullResults2

CommFullResults.Parameters.Append CommFullResults.CreateParameter("@FishCount",adInteger,adParamInput,4,CheckFishCountData(RSFISHFullResults("FishCount")))

Dim LastNameFullResults
LastNameFullResults = CheckData(RSFullResults("LastName")) & "," & CheckData(RSFullResults2("LastName"))

CommFullResults.Parameters.Append CommFullResults.CreateParameter("@LastName",adVarChar,adParamInput,100,LastNameFullResults)
CommFullResults.Parameters.Append CommFullResults.CreateParameter("@Sponsor",adVarChar,adParamInput,50,CheckData(RSFullResults("Sponsor")))

CommFullResults.Execute,,adExecuteNoRecords

RSFullResults.MoveNext

Loop

End If

STORED PROCEDURE

CREATE PROCEDURE NewExpiredDoubleTeam_FullResults(@NameofEvent varchar(100),@TeamNumber int,@Team varchar(100),@State char(50),@Country varchar(50),@BigFish decimal (9),@TotalWeight decimal(9),@FishCount int,@LastName varchar(100),@Sponsor varchar(50))
as
Insert into ExpiredDoubleTeam_FullResults(NameofEvent,TeamNumber,Team,State,Country,BigFish,TotalWeight,FishCoun t,LastName,Sponsor)
Values(@NameofEvent,@TeamNumber,@Team,@State,@Country,@BigFish,@TotalWeight,@FishCount,@LastName,@Sp onsor)

[russell]

STORED PROCEDURE
...
@BigFish decimal (9),@TotalWeight decimal(9)

needs to be decimal(10, 2)

[ASPSQLVB]

Thank You Russell,

That fixed the problem. I learn something new from you all the time.
Im lucky your here!!!