473,554 Members | 2,911 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Retreive Out Parameter from stored Procedue in ASP page

Soniad
66 New Member
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
6 7267
Soniad
66 New Member
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,P reGrp,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
3,406 Recognized Expert Specialist
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
66 New Member
@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
66 New Member
@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
66 New Member
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
3,406 Recognized Expert Specialist
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

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

Similar topics

3
16932
by: WGW | last post by:
Though I am a novice to MS SQL server (2000 I believe), I can do almost! everything I need. Maybe not efficiently, but usefully. However, I have a problem -- a complex query problem... I can create a parameter query in a stored procedure, but how do I use the result set of a parameter query in a select query (in the same or another sp)? In...
0
1606
by: Mark Oueis | last post by:
Is there any way I can retrieve the result set of a Stored Procedure in a function. ALTER FUNCTION dbo.fn_GroupDeviceLink ( @groupID numeric ) RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric) AS BEGIN
3
10199
by: gh | last post by:
I have made the following stored procedure with the following select statement select * from user select * from order, orderdetail where order.id=orderdetail.id I know I can uses DataReader class to get data from the store procedue. However, is there any way for me to use DataAdapter to get the data and put it in separate DataTables in a...
2
1571
by: Anitha | last post by:
Hi All, How to retrieve images stored in Access database. I am storing images(jpeg) as OleObject. I want display them on my web page. I am unable to do so.Please help me I am using C# The code is as // Put user code to initialize the page here int ImgID = System.Convert.ToInt32(Request.QueryString);
4
2748
by: Ranginald | last post by:
Hi, I'm having trouble passing a parameter from my default.aspx page to my default2.aspx page. I have values from a query in a list box and the goal is to pass the "catID" from default.aspx to a stored procedure on the details2.aspx page. I can successfully pass the values from the listbox control to a
2
3235
by: keyvez | last post by:
I am trying to find the tables and columns that depends on 'table1' sp_fkeys @pktable_name='table1' and this takes about eight seconds, whereas if I run it for finding all tables and columns that 'table1' depends on sp_fkeys @fktable_name='table1' this only takes a second.
5
4272
by: marcsirois | last post by:
I am maintaining an application where most of the business rules are in Triggers, Stored Procedures and User Defined Functions. When a bug arises, it can get very tedious to debug. Today for example, I wanted to modify a function that was being called by a trigger. The problem is that I don't want to change the function, for fear that it is...
7
8548
by: ashtek | last post by:
Hi, I have a generic function that executes a stored procedure & returns a data table. Code: === public static DataTable ExecuteStoredProcedure(string strProc,SqlParameter paramArray) { SqlCommand command = new SqlCommand(strProc, connection); command.CommandType = CommandType.StoredProcedure;
5
1769
by: bbawa1 | last post by:
I have the following stroed procedue. But whebnnI execute it it gives me following errors. Could you please tell me what is wrong Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 43 Incorrect syntax near the keyword 'SELECT'. Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 47 Incorrect syntax near the keyword 'select'....
0
7581
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7506
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7782
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8018
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6123
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5423
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3545
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2006
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
823
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.