A for loop,2 arrays and a stored proc - problems!

damalo · #1 (**permalink**) 07-15-04, 10:08

Hi folks,

Some background

i have 2 one dimension arrays.

One has the name of a skill such as Java,UML,C in each cell

in a corresponding array i have an id for each skill.

the arrays are populated and passed onto another page when a form is submitted. The forms POST looks like this :

Java=2&C=4&....

where 2 and 4 is a skill level. In my code i want to loop through the first array - get the skill name,2nd array- get the id of that skill and then do a trim request from the POST to get the value for that skill.

and guess what im having some errors!

Code:


For iLoop = LBound(skills_array) to UBound(skills_array) 	
			
			
		set objConn = Server.CreateObject("ADODB.Connection") 
		set objComm = Server.CreateObject("ADODB.Command")
		objConn.Open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=Skills; UId=sa; Pwd="
		objComm.ActiveConnection = objConn 
		objComm.CommandType = 4 'adCmdStoredProc 
		objComm.CommandText = "update_skills"
	
		set param_user = objComm.CreateParameter("nt_user", adInteger, adParamInput, , employee_rid) 
		objComm.Parameters.Append(param_user)
		
		set param_skillrid = objComm.CreateParameter("skill_rid", adInteger, adParamInput, , rids_array(jLoop)) 
		objComm.Parameters.Append(param_skillrid)
		
		
		rating = Trim(Request(skills_array(iLoop)))


		set param_rating = objComm.CreateParameter("rating", adInteger, adParamInput, , rating) 
		objComm.Parameters.Append(param_rating)
		
		objComm.Execute
jLoop = jLoop + 1
		Next

error :

ADODB.Command (0x800A0D5D)
Application uses a value of the wrong type for the current operation.

any ideas? Thanks

damalo

rokslide · #2 (**permalink**) 07-15-04, 18:29

Sounds like you are passing the wrong values into your paramaters...

Print them out before you assign them so you can see exactly what you are trying to set the values to. I suspect you will fine a null value or a string value that can't be converted to an int.

damalo · #3 (**permalink**) 07-15-04, 19:39

yeah thanks for that. I had tried that already to make sure they were all the proper type. Without appending them to the sproc the method im using worked grand. I tested to make sure everything was numeric and strings etc but once i went to add them to the sproc...bad times!

thanks anyways
damalo

rokslide · #4 (**permalink**) 07-15-04, 19:48

One thing I have found is that if you need to append the parameters to the stored proc in the order they are declared in the stored proc. eg if your stored proc is writen...

CREATE PROCEDURE [dbo].[spCreateAuditLog]
@Log_Time [datetime],
@Session_ID varchar(255)

You have to create/append log_time before you append/create Session_Id

I know you should have to but this is what I have had happen to me before.

Your parameters are all intergers though so that shouldn't be a problem for you.

The other thing I would do is use a CInt while doing the append to ensure they are properly converted before you append them.

damalo · #5 (**permalink**) 07-16-04, 04:07

thanks for the post....

yeah i tried to send them all off alright just liek they are declared in the procedure. One thing though..when i tried doing a cInt i got a type mismatch error which i thought was a little odd!

any ideas.

Thanks again,
damalo

rokslide · #6 (**permalink**) 07-18-04, 21:22

If you are getting a type mismatch on your CInt then you are passing a value that can't be represented as an integer... which goes back to my orginal post about the values you are using being incorrect for the parameter type.

Check your values that you are passing again and ensure they are valid integers....

Internet Services

Web Development

Digital Marketing

Consumer Tech