By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,137 Members | 2,242 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,137 IT Pros & Developers. It's quick & easy.

Retreive Out Parameter from stored Procedue in ASP page

Soniad
P: 66
Hello,

I am excecuting a stored procedure in my ASP page , it has one out parameter (@confirm) . after executing the procedure i want to retreive this out parameter and assign it to variable (confirmation) declared in page.

Expand|Select|Wrap|Line Numbers
  1. Dim RsSp , SQLSp
  2. Set  RsSp    =  Server.CreateObject("ADODB.Recordset")
  3.  
  4.  
  5. SQLSp = "Declare @confirm varchar(1)"
  6. SQLSp =  SQLSp & "Exec SendMsg_proc  "&  "'" & UniCode &"' , '" & DintUserId &"' , '" & DintOrg_id &"' , '" & Gdate &"' , '" & Gtime  &"' ,'" & ProductId &"' , '" &recids&"' , '" &grpids&"' , '" & PreGrp &"' , '" & UnregRec &"' , '" &Message&"' , '" &EmailSubject&"'  , '" &MsgType &"','" 
  7. SQLSp = SQLSp & EMsgBal &"' , '" &GrBal&"' , '" & Acctype &"' , '" &SmsGeneral&"' , '" & EmailGeneral&"' , '" & Free &"' , '" & Normal &"' , '" & Invite &"' , '" & vchStatus &"' , '" & vchStatus1 &"' ," & " @confirm OUTPUT"
  8.  
  9.  
  10. RsSp.open SQLSp,openconnection()
  11. Set RsSp = Nothing
  12.  
  13.  

how to get this out parameter in page.

Any suggestions.

Regards,
"D"
Feb 1 '09 #1
Share this Question
Share on Google+
6 Replies


Soniad
P: 66
Hello,

I am calling stored procedure in ASP page, but the syntax which i have written is not working, and i am not getting whats the problem. here i have given ASP code for calling stored procedure also SQl stored procedure parameters,
cant figure out the problem. as far i have searched on net i have found that it might be the size of parameter that matters .

and one more question :
out these parameters some of the parameter (recids,grpids,PreGrp,UnregRec) is string (comma seperated) . is this the cause for error : wrong number of arguments .


ASP Code :
Expand|Select|Wrap|Line Numbers
  1. Dim adCmdSPStoredProc,adVarChar,adParamInput,adnumeric,adParamOutput
  2. adCmdSPStoredProc=4
  3. adVarChar = 200
  4. adParamInput = 1
  5. adInteger = 4
  6. adParamOutput = 2
  7. adLongVarChar = 8000
  8.  
  9.     Dim oCmdDim
  10.     Set oCmd = Server.CreateObject("ADODB.Command")
  11.  
  12.     With  oCmd 
  13.     .ActiveConnection = openconnection()
  14.     .CommandType = adCmdSPStoredProc
  15.     .CommandText = "SendMsg_proc"
  16.  
  17.     .Parameters.Append(.CreateParameter("@UniCode",adVarChar, adParamInput, len(UniCode),  UniCode ))
  18.     .Parameters.Append(.CreateParameter("@DintUserId", adInteger, adParamInput, len(DintUserId),  DintUserId  ))
  19.     .Parameters.Append(.CreateParameter("@DintOrg_id", adInteger, adParamInput, len(DintOrg_id),  DintOrg_id  ))
  20.     .Parameters.Append(.CreateParameter("@Gdate", adVarChar, adParamInput, len(Gdate),  Gdate  ))
  21.     .Parameters.Append(.CreateParameter("@GTime", adVarChar, adParamInput, len(Gtime),  Gtime  ))
  22.     .Parameters.Append(.CreateParameter("@ProductId", adVarChar, adParamInput, len(ProductId),  ProductId  ))
  23.     .Parameters.Append(.CreateParameter("@recids", adLongVarChar, adParamInput, len(recids),  recids ))        
  24.     .Parameters.Append(.CreateParameter("@grpids", adLongVarChar, adParamInput, len(grpids),  grpids  ))        
  25.     .Parameters.Append(.CreateParameter("@preferedGroup", adLongVarChar, adParamInput, len(PreGrp),   PreGrp  ))        
  26.     .Parameters.Append(.CreateParameter("@unRegRec", adVarChar, adParamInput, len(UnregRec),  UnregRec ))        
  27.     .Parameters.Append(.CreateParameter("@Message", adVarChar, adParamInput, len(Message), Message ))
  28.     .Parameters.Append(.CreateParameter("@EmailSub", adVarChar, adParamInput, len(EmailSubject), EmailSubject ))    
  29.     .Parameters.Append(.CreateParameter("@DvchMsgType", adVarChar, adParamInput, len(MsgType),  MsgType  ))                
  30.     .Parameters.Append(.CreateParameter("@EmailMsgBal", adInteger, adParamInput, len(EMsgBal),  EMsgBal  ))        
  31.     .Parameters.Append(.CreateParameter("@GrBal", adInteger, adParamInput, len(GrBal),  GrBal  ))
  32.     .Parameters.Append(.CreateParameter("@AccType", adVarChar, adParamInput,len(Acctype),  Acctype  ))                    
  33.     .Parameters.Append(.CreateParameter("@SmsGeneral", adInteger, adParamInput,len(SmsGeneral),  SmsGeneral  ))                    
  34.     .Parameters.Append(.CreateParameter("@EmailGeneral", adInteger, adParamInput,len(EmailGeneral),  EmailGeneral  ))                    
  35.     .Parameters.Append(.CreateParameter("@vchfree", adVarChar, adParamInput, len(Free),  Free  ))                
  36.     .Parameters.Append(.CreateParameter("@vchnormal", adVarChar, adParamInput, len(Normal), Normal  ))                
  37.     .Parameters.Append(.CreateParameter("@invite", adVarChar, adParamInput, len(Invite),   Invite  ))        
  38.     .Parameters.Append(.CreateParameter("@status", adVarChar, adParamInput, len(vchStatus),  vchStatus  ))                    
  39.     .Parameters.Append(.CreateParameter("@status1", adVarChar, adParamInput, len(vchStatus1), vchStatus1 ))                        
  40.     .Parameters.Append(.CreateParameter("@Confirm", adVarChar, adParamOutput))
  41.  
  42.  
  43.     'On Error Resume Next
  44.     .Execute 
  45.  
  46.  
  47.     confirmation = .Parameters("@Confirm")
  48.  
  49.    End With 
  50.     'On Error Goto 0
  51.     If oCmd.ActiveConnection.Errors.Count > 0 Then
  52.         Response.Write("An error occured while trying to process your SP!")
  53.     Else
  54.         Response.Write("DONE - "& confirmation)
  55.     End If
  56.  
  57.     Set oCmd = Nothing

SQl Stored Procedure parameters :

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.SendMsg_proc             
  2.  
  3. @UniCode  varchar(1),           
  4. @DintUserId numeric(9),            
  5. @DintOrg_id numeric(9),          
  6. @Gdate varchar(25),            
  7. @GTime varchar(25),    
  8. @ProductId varchar(50),          
  9. @recids   varchar(8000),          
  10. @grpids   varchar(8000),          
  11. @preferedGroup  varchar(8000),          
  12. @unRegRec  varchar(8000),          
  13. @Message varchar(8000),    
  14. @EmailSub varchar(50),          
  15. @DvchMsgType varchar(50),          
  16. @EmailMsgBal numeric(9),          
  17. @GrBal numeric(9),          
  18. @AccType varchar(15),          
  19. @SmsGeneral int,          
  20. @EmailGeneral int,          
  21. @vchfree  varchar(10),           
  22. @vchnormal  varchar(10),          
  23. @invite varchar(10),          
  24. @status varchar(3),          
  25. @status1 varchar(3),      
  26. @Confirm varchar(1) output  

Since, the above ASP code not working , i used another way, and this is working .
but the problem is , i want the out parameter, dont know how to retreive the out parameter with this syntax :

Expand|Select|Wrap|Line Numbers
  1. SQLSp = "Declare @confirm varchar(1)"
  2. SQLSp =  SQLSp & "Exec SendMsg_proc  "&  "'" & UniCode &"' , '" & DintUserId &"' , '" & DintOrg_id &"' , '" & Gdate &"' , '" & Gtime  &"' , '" & SendDate &"' , '" & ProductId &"' , '" &recids&"' , '" &grpids&"' , '" & PreGrp &"' , '" & UnregRec &"' , '" &Message&"' , '" &EmailSubject&"'  , '" &MsgType &"','" 
  3. SQLSp = SQLSp & EMsgBal &"' , '" &GrBal&"' , '" & Acctype &"' , '" &SmsGeneral&"' , '" & EmailGeneral&"' , '" & Free &"' , '" & Normal &"' , '" & Invite &"' , '" & vchStatus &"' , '" & vchStatus1 &"' ," & " @confirm OUTPUT"
Kindly help me through out this.

Regards,
"D"
Feb 2 '09 #2

jhardman
Expert 2.5K+
P: 3,405
D,

I've never tried this before but I'm interested. If you can, send me the code for your SP (OK to mock up and give me something with a similar output) and I will test, see if I can get it to work.

Jared
Feb 2 '09 #3

Soniad
P: 66
@jhardman
I have given rough SP , and syntax to execute .
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.SendMsg_proc_demo              
  2.  
  3. @UniCode  varchar(1),             
  4. @DintUserId int,              
  5. @DintOrg_id int,            
  6. @Gdate varchar(25),              
  7. @GTime varchar(25),      
  8. @ProductId varchar(20),            
  9. @recids   varchar(8000),            
  10. @grpids   varchar(8000),            
  11. @preferedGroup  varchar(8000),            
  12. @unRegRec  varchar(8000),            
  13. @Message varchar(1120),      
  14. @EmailSub varchar(50),            
  15. @DvchMsgType varchar(10),            
  16. @EmailMsgBal numeric(9),            
  17. @GrBal numeric(9),            
  18. @AccType varchar(15),            
  19. @SmsGeneral int,            
  20. @EmailGeneral int,            
  21. @vchfree  varchar(10),             
  22. @vchnormal  varchar(10),            
  23. @invite varchar(10),            
  24. @status varchar(3),            
  25. @status1 varchar(3),        
  26. @Confirm varchar(1) output           
  27.  
  28. AS               
  29. Begin    
  30.  
  31. Declare @DvchEMessage varchar(50),@DSentdttime varchar(50),@pos int,@nextpos int,@valuelen int,@PreGrppos int,@nextGrppos int,
  32. @valuePreGrplen int,@MsgSendGroup varchar(25),@RecId int,@GrpId int,@unReg varchar(50)
  33.  
  34. /*Email Message*/            
  35. set @DvchEMessage = @Message            
  36.  
  37. /*Email Subject*/            
  38. If @EmailSub = ''    
  39. Begin    
  40. Set @EmailSub = NULL    
  41. End   
  42.  
  43. set @DSentdttime = (select @Gdate +' '+  @GTime)    
  44.  
  45.  If (Convert(int,DATALENGTH(@Message))) > 130       
  46.  Begin          
  47.  set @DvchMsgType = 'lText'               
  48.  End            
  49.  Else       
  50.  Begin      
  51.  set @DvchMsgType = 'Text'                   
  52.  End      
  53.  
  54.  
  55.  
  56. If @recids <> ''
  57. Begin
  58.  
  59.  SELECT @pos = 0, @nextpos = 1            
  60.  
  61.    Print 'Recid befor split'               
  62.    Print @recids
  63.  
  64.    Print 'Recids after split'
  65.  
  66.    WHILE @nextpos > 0            
  67.    BEGIN            
  68.       SELECT @nextpos = charindex(',', @recids, @pos + 1)            
  69.       SELECT @valuelen = CASE WHEN @nextpos > 0            
  70.                               THEN @nextpos            
  71.                               ELSE len(@recids) + 1            
  72.                               END - @pos - 1            
  73.  
  74.       SET @RecId = convert(int, substring(@recids, @pos + 1, @valuelen))            
  75.  
  76.  
  77.       Print @RecId
  78.  
  79.  
  80.       SELECT @pos = @nextpos  
  81.  
  82.    END
  83.  
  84. End
  85.  
  86.  
  87. If @grpids <> ''
  88. Begin
  89.   Print '@GrpIds before split'
  90.   Print  @grpids
  91.  
  92.   Print '@preferedGroup before split'
  93.   Print @preferedGroup
  94.  
  95.   Print '@GrpIds and @preferedGroup after split'
  96.  
  97.   SELECT @pos = 0, @nextpos = 1            
  98.   SELECT @PreGrppos = 0,@nextGrppos = 1            
  99. /* While Group Id's*/            
  100.    WHILE @nextpos > 0            
  101.    BEGIN            
  102.  
  103.       /*Group Id's*/              
  104.       SELECT @nextpos = charindex(',', @grpids, @pos + 1)            
  105.       SELECT @valuelen = CASE WHEN @nextpos > 0            
  106.                               THEN @nextpos            
  107.                               ELSE len(@grpids) + 1            
  108.                               END - @pos - 1            
  109.  
  110.       SET @GrpId = convert(int, substring(@grpids, @pos + 1, @valuelen))              
  111.  
  112.       Print  @GrpId          
  113.  
  114.       /*PrefferedGroup*/               
  115.       SELECT @nextGrppos = charindex(',', @preferedGroup, @PreGrppos + 1)            
  116.       SELECT @valuePreGrplen = CASE WHEN @nextGrppos  > 0            
  117.                               THEN @nextGrppos  ELSE len(@preferedGroup) + 1            
  118.                               END - @PreGrppos - 1               
  119.  
  120.       SET @MsgSendGroup = substring(@preferedGroup, @PreGrppos + 1, @valuePreGrplen)             
  121.  
  122.       Print @MsgSendGroup      
  123.  
  124.       SELECT @pos = @nextpos            
  125.       SELECT @PreGrppos = @nextGrppos   
  126.  
  127.    END
  128.  
  129. End
  130.  
  131.  
  132. If @unRegRec <> ''
  133. Begin
  134.  
  135.    Print '@unRegRec before split'     
  136.    Print @unRegRec
  137.  
  138.    Print '@unRegRec After split'    
  139.  
  140.    SELECT @pos = 0, @nextpos = 1            
  141.  
  142.    WHILE @nextpos > 0            
  143.    BEGIN            
  144.  
  145.       SELECT @nextpos = charindex(',', @unRegRec, @pos + 1)            
  146.       SELECT @valuelen = CASE WHEN @nextpos > 0            
  147.                               THEN @nextpos            
  148.                               ELSE len(@unRegRec) + 1            
  149.                               END - @pos - 1            
  150.  
  151.       SET @unReg = substring(@unRegRec, @pos + 1, @valuelen)  
  152.  
  153.       Print @unReg            
  154.  
  155.       SET @pos = @nextpos  
  156.  
  157.     END
  158.  
  159. End
  160.  
  161. End
  162.  
  163. Set @Confirm = 'Y'
  164.  
  165. Print '@UniCode'
  166. Print @UniCode               
  167. Print '@DintUserId'
  168. Print @DintUserId 
  169. Print '@DintOrg_id'
  170. Print @DintOrg_id 
  171. Print '@Gdate'
  172. Print @Gdate   
  173. Print '@GTime'           
  174. Print @GTime 
  175. Print '@ProductId' 
  176. Print @ProductId 
  177. Print '@recids'   
  178. Print @recids   
  179. Print '@grpids'   
  180. Print @grpids   
  181. Print '@preferedGroup'  
  182. Print @preferedGroup  
  183. Print '@unRegRec'  
  184. Print @unRegRec  
  185. Print '@Message' 
  186. Print @Message 
  187. Print '@EmailSub'
  188. Print @EmailSub 
  189. Print '@DvchMsgType' 
  190. Print @DvchMsgType 
  191. Print '@EmailMsgBal' 
  192. Print @EmailMsgBal 
  193. Print '@GrBal' 
  194. Print @GrBal 
  195. Print '@AccType' 
  196. Print @AccType 
  197. Print '@SmsGeneral' 
  198. Print @SmsGeneral 
  199. Print '@EmailGeneral' 
  200. Print @EmailGeneral 
  201. Print '@vchfree'  
  202. Print @vchfree  
  203. Print '@vchnormal'
  204. Print @vchnormal  
  205. Print '@invite' 
  206. Print @invite 
  207. Print '@status'
  208. Print @status 
  209. Print '@status1'
  210. Print @status1 
  211. Print '@Confirm' 
  212. Print @Confirm 
Execute SP (with this syntax in ASP, works):

Expand|Select|Wrap|Line Numbers
  1. Declare @Confirm varchar(1)
  2. Exec SendMsg_proc_demo 'N','63','1','03/02/2009','10:15:02 AM','demo','1,2,3,4','1,2','default,sms','45,46,47','demo message','demo Sub',
  3. 'Text','130','6','DEMO ACCOUNT','2','2','','','invite','no','no',@Confirm OUT

Now try the same thing with following syntax in ASP, also, try with some empty parameters , if the variable is empty it throws the following error :

Expand|Select|Wrap|Line Numbers
  1. ADODB.Parameters error '800a0e7c' 
  2.  
  3. Parameter object is improperly defined. Inconsistent or incomplete information was provided. 
  4.  
ASP CODE Another way which is causing the problem:

Expand|Select|Wrap|Line Numbers
  1. Const adVarChar = 200
  2. Const adInteger = 3
  3. Const adParamInput = &H0001
  4. Const adParamOutput = &H0002
  5. Const adCmdSPStoredProc = 4
  6.  
  7.  
  8.     Dim oCmdDim
  9.     Set oCmd = Server.CreateObject("ADODB.Command")
  10.  
  11.     With  oCmd 
  12.     .ActiveConnection = openconnection()
  13.     .CommandType = adCmdSPStoredProc
  14.     .CommandText = "SendMsg_proc_demo"
  15.  
  16.     .Parameters.Append .CreateParameter("@UniCode",adVarChar, adParamInput, len(UniCode),  UniCode )
  17.     .Parameters.Append .CreateParameter("@DintUserId", adInteger, adParamInput, len(DintUserId),  DintUserId  )
  18.     .Parameters.Append .CreateParameter("@DintOrg_id", adInteger, adParamInput, len(DintOrg_id),  DintOrg_id  )
  19.     .Parameters.Append .CreateParameter("@Gdate", adVarChar, adParamInput, len(Gdate),  Gdate  )
  20.     .Parameters.Append .CreateParameter("@GTime", adVarChar, adParamInput, len(Gtime),  Gtime  )
  21.     .Parameters.Append .CreateParameter("@ProductId", adVarChar, adParamInput, len(ProductId),  ProductId  )
  22.     .Parameters.Append .CreateParameter("@recids", adVarChar, adParamInput, len(recids),  recids)        
  23.     .Parameters.Append .CreateParameter("@grpids", adVarChar, adParamInput, len(grpids), grpids)        
  24.     .Parameters.Append .CreateParameter("@preferedGroup", adVarChar, adParamInput, len(PreGrp),  PreGrp)        
  25.     .Parameters.Append .CreateParameter("@unRegRec", adVarChar, adParamInput, len(UnregRec),  UnregRec)        
  26.     .Parameters.Append .CreateParameter("@Message", adVarChar, adParamInput, len(Message), Message )
  27.     .Parameters.Append .CreateParameter("@EmailSub", adVarChar, adParamInput, len(EmailSubject), EmailSubject )    
  28.     .Parameters.Append .CreateParameter("@DvchMsgType", adVarChar, adParamInput, len(MsgType),  MsgType  )                
  29.     .Parameters.Append .CreateParameter("@EmailMsgBal", adInteger, adParamInput, len(EMsgBal),  EMsgBal  )
  30.     .Parameters.Append .CreateParameter("@GrBal", adInteger, adParamInput, len(GrBal),  GrBal  )
  31.     .Parameters.Append .CreateParameter("@AccType", adVarChar, adParamInput,len(Acctype),  Acctype  )                    
  32.     .Parameters.Append .CreateParameter("@SmsGeneral", adInteger, adParamInput,len(SmsGeneral),  SmsGeneral  )                    
  33.     .Parameters.Append .CreateParameter("@EmailGeneral", adInteger, adParamInput,len(EmailGeneral),  EmailGeneral  )                    
  34.     .Parameters.Append .CreateParameter("@vchfree", adVarChar, adParamInput, len(Free),  Free  )                
  35.     .Parameters.Append .CreateParameter("@vchnormal", adVarChar, adParamInput, len(Normal), Normal  )                
  36.     .Parameters.Append .CreateParameter("@invite", adVarChar, adParamInput, len(Invite),   Invite  )        
  37.     .Parameters.Append .CreateParameter("@status", adVarChar, adParamInput, len(vchStatus),  vchStatus  )                    
  38.     .Parameters.Append .CreateParameter("@status1", adVarChar, adParamInput, len(vchStatus1), vchStatus1 )                        
  39.     .Parameters.Append .CreateParameter("@Confirm", adVarChar, adParamOutput)
  40.  
  41.  
  42.     'On Error Resume Next
  43.     .Execute 
  44.  
  45.     confirmation = .Parameters("@Confirm")
  46.  
  47.    End With 
  48.     'On Error Goto 0
  49.     If oCmd.ActiveConnection.Errors.Count > 0 Then
  50.         Response.Write("An error occured while trying to process your SP!")
  51.     Else
  52.         Response.Write("DONE - "& confirmation)
  53.     End If
  54.  
  55.     Set oCmd = Nothing

Regards,
"D"
Feb 3 '09 #4

Soniad
P: 66
@jhardman
Hello,
I am calling stored proc in my ASP page,
here d parameters which r passed to SP , are assigned to variables .the values assigned to variables can also be empty.
when i execute SP it throws an error to the line which has variable that is empty.
so, what to do if the variable is empty ..

here's my SP in ASP :
Expand|Select|Wrap|Line Numbers
  1. Const adChar = 129 
  2. Const adVarChar = 200
  3. Const adInteger = 3
  4. Const adParamInput = &H0001
  5. Const adParamOutput = &H0002
  6. Const adCmdSPStoredProc = 4
  7.     Dim oCmdDim
  8.     Set oCmd = Server.CreateObject("ADODB.Command")
  9.  
  10.     With  oCmd 
  11.     .ActiveConnection = openconnection()
  12.     .CommandType = adCmdSPStoredProc
  13.     .CommandText = "SendMsg_proc"
  14.  
  15.     .Parameters.Append .CreateParameter("@UniCode",adVarChar, adParamInput, len(UniCode),  UniCode )
  16.     .Parameters.Append .CreateParameter("@DintUserId", adInteger, adParamInput, len(DintUserId),  DintUserId  )
  17.     .Parameters.Append .CreateParameter("@DintOrg_id", adInteger, adParamInput, len(DintOrg_id),  DintOrg_id  )
  18.     .Parameters.Append .CreateParameter("@Gdate", adVarChar, adParamInput, len(Gdate),  Gdate  )
  19.     .Parameters.Append .CreateParameter("@GTime", adVarChar, adParamInput, len(Gtime),  Gtime  )
  20.     .Parameters.Append .CreateParameter("@ProductId", adVarChar, adParamInput, len(ProductId),  ProductId  )
  21.     .Parameters.Append .CreateParameter("@recids", adVarChar, adParamInput, len(recids),  recids)        
  22.     .Parameters.Append .CreateParameter("@grpids", adVarChar, adParamInput, len(grpids), grpids)        
  23.     .Parameters.Append .CreateParameter("@preferedGroup", adVarChar, adParamInput, len(PreGrp),  PreGrp)        
  24.     .Parameters.Append .CreateParameter("@unRegRec", adVarChar, adParamInput, len(UnregRec),  UnregRec)        
  25.     .Parameters.Append .CreateParameter("@Message", adVarChar, adParamInput, len(Message), Message )
  26.     .Parameters.Append .CreateParameter("@EmailSub", adVarChar, adParamInput, len(EmailSubject), EmailSubject )    
  27.     .Parameters.Append .CreateParameter("@DvchMsgType", adVarChar, adParamInput, len(MsgType),  MsgType  )                
  28.     .Parameters.Append .CreateParameter("@EmailMsgBal", adInteger, adParamInput, len(EMsgBal),  EMsgBal  )
  29.     .Parameters.Append .CreateParameter("@GrBal", adInteger, adParamInput, len(GrBal),  GrBal  )
  30.     .Parameters.Append .CreateParameter("@AccType", adVarChar, adParamInput,len(Acctype),  Acctype  )                    
  31.     .Parameters.Append .CreateParameter("@SmsGeneral", adInteger, adParamInput,len(SmsGeneral),  SmsGeneral  )                    
  32.     .Parameters.Append .CreateParameter("@EmailGeneral", adInteger, adParamInput,len(EmailGeneral),  EmailGeneral  )                    
  33.     .Parameters.Append .CreateParameter("@vchfree", adVarChar, adParamInput, len(Free),  Free  )                
  34.     .Parameters.Append .CreateParameter("@vchnormal", adVarChar, adParamInput, len(Normal), Normal  )                
  35.     .Parameters.Append .CreateParameter("@invite", adVarChar, adParamInput, len(Invite),   Invite  )        
  36.     .Parameters.Append .CreateParameter("@status", adVarChar, adParamInput, len(vchStatus),  vchStatus  )                    
  37.     .Parameters.Append .CreateParameter("@status1", adVarChar, adParamInput, len(vchStatus1), vchStatus1 )                        
  38.     .Parameters.Append .CreateParameter("@Confirm", adChar, adParamOutput,1)
  39.     .Prepared = true
  40.  
  41.     'On Error Resume Next
  42.     .Execute 
  43.      confirmation = .Parameters("@Confirm")     
  44.    End With 
  45.       Response.Write(oCmd.Parameters("@Confirm"))
  46.  
  47.     'On Error Goto 0
  48.     If oCmd.ActiveConnection.Errors.Count > 0 Then
  49.         Response.Write("An error occured while trying to process your SP!")
  50.     Else
  51.         Response.Write("DONE - "& confirmation)
  52.     End If
  53.  
  54.     Set oCmd = Nothing

here's the error :

Expand|Select|Wrap|Line Numbers
  1. ADODB.Parameters error '800a0e7c' 
  2.  
  3. Parameter object is improperly defined. Inconsistent or incomplete information was provided. 
  4.  
on this line ,where variable "MsgType " is blank . i.e . there's no value in this variable..it's empty:
Expand|Select|Wrap|Line Numbers
  1. .Parameters.Append .CreateParameter("@DvchMsgType", adVarChar, adParamInput, len(MsgType),  MsgType  )
  2.  
here's how i have assigned the values to variable in VBScript :
Expand|Select|Wrap|Line Numbers
  1. recids = mid(recids,2,len(recids))
  2. grpids  = mid(grpids,2,len(grpids))
  3.  
  4.  
  5. Dim UniCode,Gdate,Gtime,SendDate,PreGrp,UnregRec,Message,EmailSubject,MsgType,EMsgBal,GrBal,Acctype,Free,Normal,Invite,vchStatus,vchStatus1
  6. Dim confirmation
  7.  
  8. UniCode = request("unicode")
  9. Gdate = Request("Gdate")
  10. Gtime =  Request("GTime")
  11. 'SendDate = Request("Gdate") &" "& Request("GTime")
  12. PreGrp = Request("preferedGroup")
  13. PreGrp =  mid(PreGrp,2,len(PreGrp))
  14. UnregRec = Request("textareaunreg") 
  15.  
  16. Message = Request("textareamobile")
  17. Message=replace(Message,"'","''")
  18. Message=replace(Message,"%","%25")
  19. Message=replace(Message,"&","%26")
  20. Message=replace(Message,"+","%2B")
  21. Message=replace(Message,".","%2E")
  22. Message=replace(Message,"/","%2F")
  23. Message=replace(Message,Chr(34),"%22")
  24. Message=replace(Message,"<","%3C")
  25. Message=replace(Message,">","%3E")
  26. Message=replace(Message,"#","%23")
  27. Message=replace(Message,"*","%2A")
  28. Message=replace(Message,"!","%21")
  29. Message=replace(Message,",","%2C")
  30. Message=replace(Message,"\","%5C")
  31. Message=replace(Message,"=","%3D")
  32.  
  33. EmailSubject = Request("txtsub")
  34. MsgType = Request("tflash") 
  35. if IsNull(MsgType)  or  MsgType = "" or  IsEmpty(MsgType)  then     MsgType = ""   else     MsgType = CStr(MsgType)  
  36. EMsgBal = Request("EmailBal")
  37. GrBal = Request("GrBal") 
  38. Acctype = lcase(trim(session("admin")))
  39. if IsNull(Acctype)  or  Acctype = "" or  IsEmpty(Acctype)  then     Acctype = "" else     Acctype = CStr(Acctype)
  40. Free = Request("Free") 
  41. if IsNull(Free)  or  Free = "" or  IsEmpty(Free)  then     Free = ""  else     Free = CStr(Free)
  42. Normal = Request("NORMAL") 
  43. if IsNull(Normal)  or  Normal = "" or  IsEmpty(Normal)  then     Normal = ""  else     Normal = CStr(Normal)
  44. Invite = Request("invite")
  45. if IsNull(Invite)  or  Invite = "" or  IsEmpty(Invite)  then     Invite = "" else     Invite = CStr(Invite)
  46. vchStatus = Request("status") 
  47. vchStatus1 = lcase(trim(session("status1")))
  48. if IsNull(vchStatus1)  or  vchStatus1 = "" or  IsEmpty(vchStatus1)  then     vchStatus1 = ""  else     vchStatus1 = CStr(vchStatus1)
  49.  
Plz , any suggestion, any help .
i also want the Out parameter in my ASP page.

Regards,
"D"
Feb 7 '09 #5

Soniad
P: 66
Hello,

After a long try, i got the solution for my problem and i have solved it.

Expand|Select|Wrap|Line Numbers
  1. recids = mid(recids,2,len(recids))
  2. grpids  = mid(grpids,2,len(grpids))
  3.  
  4. Dim UniCode,Gdate,Gtime,SendDate,PreGrp,UnregRec,Message,EmailSubject,MsgType,EMsgBal,GrBal,Acctype,Free,Normal,Invite,vchStatus,vchStatus1
  5. Dim confirmation
  6.  
  7. UniCode = request("unicode")
  8. Gdate = Request("Gdate")
  9. Gtime =  Request("GTime")
  10. 'SendDate = Request("Gdate") &" "& Request("GTime")
  11. PreGrp = Request("preferedGroup")
  12. PreGrp =  mid(PreGrp,2,len(PreGrp))
  13. UnregRec = Request("textareaunreg") 
  14.  
  15. Message = Request("textareamobile")
  16. Message=replace(Message,"'","''")
  17. Message=replace(Message,"%","%25")
  18. Message=replace(Message,"&","%26")
  19. Message=replace(Message,"+","%2B")
  20. Message=replace(Message,".","%2E")
  21. Message=replace(Message,"/","%2F")
  22. Message=replace(Message,Chr(34),"%22")
  23. Message=replace(Message,"<","%3C")
  24. Message=replace(Message,">","%3E")
  25. Message=replace(Message,"#","%23")
  26. Message=replace(Message,"*","%2A")
  27. Message=replace(Message,"!","%21")
  28. Message=replace(Message,",","%2C")
  29. Message=replace(Message,"\","%5C")
  30. Message=replace(Message,"=","%3D")
  31.  
  32. EmailSubject = Request("txtsub")
  33. MsgType = Request("tflash") 
  34. if IsNull(MsgType)  or  MsgType = "" or  IsEmpty(MsgType)  then     MsgType = "''"   else     MsgType = CStr(MsgType)  
  35. EMsgBal = Request("EmailBal")
  36. GrBal = Request("GrBal") 
  37. Acctype = lcase(trim(session("admin")))
  38. if IsNull(Acctype)  or  Acctype = "" or  IsEmpty(Acctype)  then     Acctype = "''" else     Acctype = CStr(Acctype)
  39. Free = Request("Free") 
  40. if IsNull(Free)  or  Free = "" or  IsEmpty(Free)  then     Free = "''"  else     Free = CStr(Free)
  41. Normal = Request("NORMAL") 
  42. if IsNull(Normal)  or  Normal = "" or  IsEmpty(Normal)  then     Normal = "''" else     Normal = CStr(Normal)
  43. Invite = Request("invite")
  44. if IsNull(Invite)  or  Invite = "" or  IsEmpty(Invite)  then     Invite = "''" else     Invite = CStr(Invite)
  45. vchStatus = Request("status") 
  46. vchStatus1 = lcase(trim(session("status1")))
  47. if IsNull(vchStatus1)  or  vchStatus1 = "" or  IsEmpty(vchStatus1)  then     vchStatus1 = "''"  else     vchStatus1 = CStr(vchStatus1)
  48.  
  49. '----- METHOD ONE TO CALL SP WITH  OUTPUT PARAMETER--------------
  50. Const adChar = 129 
  51. Const adVarChar = 200
  52. Const adInteger = 3
  53. Const adParamInput = &H0001
  54. Const adParamOutput = &H0002
  55. Const adCmdSPStoredProc = 4
  56.  
  57.  
  58.     Dim oCmdDim
  59.     Set oCmd = Server.CreateObject("ADODB.Command")
  60.  
  61.     With  oCmd 
  62.     .ActiveConnection = openconnection()
  63.     .CommandType = adCmdSPStoredProc
  64.     .CommandText = "SendMsg_proc"
  65.  
  66.     .Parameters.Append .CreateParameter("@UniCode",adVarChar, adParamInput, len(UniCode),  UniCode )
  67.     .Parameters.Append .CreateParameter("@DintUserId", adInteger, adParamInput, len(DintUserId),  DintUserId  )
  68.     .Parameters.Append .CreateParameter("@DintOrg_id", adInteger, adParamInput, len(DintOrg_id),  DintOrg_id  )
  69.     .Parameters.Append .CreateParameter("@Gdate", adVarChar, adParamInput, len(Gdate),  Gdate  )
  70.     .Parameters.Append .CreateParameter("@GTime", adVarChar, adParamInput, len(Gtime),  Gtime  )
  71.     .Parameters.Append .CreateParameter("@ProductId", adVarChar, adParamInput, len(ProductId),  ProductId  )
  72.     .Parameters.Append .CreateParameter("@recids", adVarChar, adParamInput, len(recids),  recids)        
  73.     .Parameters.Append .CreateParameter("@grpids", adVarChar, adParamInput, len(grpids), grpids)        
  74.     .Parameters.Append .CreateParameter("@preferedGroup", adVarChar, adParamInput, len(PreGrp),  PreGrp)        
  75.     .Parameters.Append .CreateParameter("@unRegRec", adVarChar, adParamInput, len(UnregRec),  UnregRec)        
  76.     .Parameters.Append .CreateParameter("@Message", adVarChar, adParamInput, len(Message), Message )
  77.     .Parameters.Append .CreateParameter("@EmailSub", adVarChar, adParamInput, len(EmailSubject), EmailSubject )    
  78.     .Parameters.Append .CreateParameter("@DvchMsgType", adVarChar, adParamInput, len(MsgType),MsgType)                
  79.     .Parameters.Append .CreateParameter("@EmailMsgBal", adInteger, adParamInput, len(EMsgBal),  EMsgBal  )
  80.     .Parameters.Append .CreateParameter("@GrBal", adInteger, adParamInput, len(GrBal),  GrBal  )
  81.     .Parameters.Append .CreateParameter("@AccType", adVarChar, adParamInput,len(Acctype),  Acctype  )                    
  82.     .Parameters.Append .CreateParameter("@SmsGeneral", adInteger, adParamInput,len(SmsGeneral),  SmsGeneral  )                    
  83.     .Parameters.Append .CreateParameter("@EmailGeneral", adInteger, adParamInput,len(EmailGeneral),  EmailGeneral  )                    
  84.     .Parameters.Append .CreateParameter("@vchfree", adVarChar, adParamInput, len(Free),  Free  )                
  85.     .Parameters.Append .CreateParameter("@vchnormal", adVarChar, adParamInput, len(Normal), Normal  )                
  86.     .Parameters.Append .CreateParameter("@invite", adVarChar, adParamInput, len(Invite),   Invite  )        
  87.     .Parameters.Append .CreateParameter("@status", adVarChar, adParamInput, len(vchStatus),  vchStatus  )                    
  88.     .Parameters.Append .CreateParameter("@status1", adVarChar, adParamInput, len(vchStatus1), vchStatus1 )                        
  89.     .Parameters.Append .CreateParameter("@Confirm", adChar, adParamOutput,1)
  90.     .Prepared = true
  91.  
  92.     'On Error Resume Next
  93.     .Execute 
  94.  
  95.    End With 
  96. '        If Err.number <> 0 Then
  97. '        Response.Write "Error Number:        " & Err.number & "<BR>"
  98. '       Response.Write "Error Description: " & Err.Description & "<BR>"
  99. '      Response.Write "Error Source:      " & Err.Source & "<BR>"
  100. '      Response.End             
  101. '    End If   
  102.      confirmation =  oCmd.Parameters("@Confirm").value
  103.  
  104.  
  105.     'On Error Goto 0
  106.     If oCmd.ActiveConnection.Errors.Count > 0 Then
  107.         Response.Write("An error occured while trying to process your SP!")
  108.     'Else
  109.     '   Response.Write("DONE - "& confirmation)
  110.     End If
  111.  
  112.     Set oCmd = Nothing


Regards,
"D"
Feb 9 '09 #6

jhardman
Expert 2.5K+
P: 3,405
Sorry I couldn't be of more help, but I'm glad you got it solved. Thanks for posting your solution.

Jared
Feb 9 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.