code error problem

[former_member186095]

Dear All Experts ,

When running this following VB6 codes, I've got Run time error 13 type mismatch:

If pVal.ItemUID = "CMDBTN1" Then
    If pVal.EventType = et_ITEM_PRESSED And pVal.BeforeAction = True Then
       'Set oForm = SboApplication.Forms(pVal.FormUID)
       Set oForm = SboApplication.Forms.GetFormByTypeAndCount(pVal.FormType, pVal.FormTypeCount)
       Dim tgl5 As String
       Set oEdit = oForm.Items("1000004").Specific
       'tgl5 = oEdit.String

 StrSQL = "exec uspADCashReceipt '" & Tgl5 & "'"
       ViewReport "ADCashRCPT.rpt"

The SP code (StrSQL):

 [dbo].[uspADCashReceipt] @date varchar(10)

SELECT T0.DocDate, T1.DueDate, T2.BankName, T0.BankCountr,
       T1.AcctNum, T1.U_NSAcc, T1.CheckNum, T1.CheckSum,T0.CardName, 
       T3.City
FROM ORCT T0 INNER JOIN RCT1 T1 ON T0.DocNum = T1.DocNum
     INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode,
     ODSC T2 
      
WHERE T2.BankCode = T1.BankCode and T0.Canceled = 'N' and 
      T0.DocDate = (substring (@Date,6,3)+'/'+substring (@Date,1,3)+'/'+substring (@Date,8,3))
      and T0.Canceled ='N'

How to resolve ? Pls give advice.Thanks

Rgds,

[former_member186095]

Hi All,

I managed to solve this issue. Thx for your previous helps.

Rgds,

[former_member186095]

Hi Petr,

Sorry for made a mistaken in writing the code in this message. it is not date15 but it should be date1, so the correct codes (VB6 and SP) as follows:

This is the addon source code that gives run time error (13) :



If pVal.ItemUID = "CMDBTN1" Then
    If pVal.EventType = et_ITEM_PRESSED And pVal.BeforeAction = True Then
       'Set oForm = SboApplication.Forms(pVal.FormUID)
       Set oForm = SboApplication.Forms.GetFormByTypeAndCount(pVal.FormType, pVal.FormTypeCount)
       Dim date1 As String
       Set oEdit = oForm.Items("1000004").Specific
       date1= oEdit.String
 
 StrSQL = "exec uspADCashReceipt '" & Date1 & "'"
       ViewReport "ADCashRCPT.rpt"
 



The SP is as follows:



[dbo].[uspADCashReceipt] @date1 varchar(10)
 
SELECT T0.DocDate, T1.DueDate, T2.BankName, T0.BankCountr,
       T1.AcctNum, T1.U_NSAcc, T1.CheckNum, T1.CheckSum,T0.CardName, 
       T3.City
FROM ORCT T0 INNER JOIN RCT1 T1 ON T0.DocNum = T1.DocNum
     INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode,
     ODSC T2 
      
WHERE T2.BankCode = T1.BankCode and T0.Canceled = 'N' and 
      T0.DocDate = (substring (@Date1,6,3)+'/'+substring (@Date1,1,3)+'/'+substring (@Date1,8,3))
      and T0.Canceled ='N'
 

Running the above code in SAP 2007A and SQL 2005 can't be success but in the SAP B1 2004A can. do you think that there will possible to still use the VB codes ?

as about the date1, I think it is used to define the date variable in the report generated.

Rgds,

[Former Member]

I think that the problem is in sp in line

T0.DocDate = (substring (@Date,6,3)'/'substring (@Date,1,3)'/'substring (@Date,8,3))

parameters of substring are FROM, BEGIN POSITION, LENGTH and isnt allowed for using of date formats. Try to cast the @date to varchar or nchar.

DocDate is stored in db as datetime format, so you cannot parse it and T0.DocDate = @Date should be enough. Maybe the problem should be in input paramateres to sp - if zou have in edittext date in format 1/1/2008 - this is not correct date format for sp.

If it doesnt help, try the date hardcode in sp and check, if the error persist.

[former_member186095]

Thx for your answer.

In the 2nd paragraph i.e. :

Try to cast the @date to varchar or nchar.

and the 3rd paragraph i.e.:

DocDate is stored in db as datetime format, so you cannot parse it and T0.DocDate = @Date should be enough

which one is better ? could you give the example of the 2nd paragraph ? because currently I am just using the 3rd paragraph in the SP.

Rgds,

[Former Member]

Because DocDate is datetime filed, enough for you is using something like

convert(datetime, @date)

instead of substring sentence.

Try it and let me know.

[former_member186095]

Hi Petr, All,

Thx for answer but the report is still empty.

This is the addon source code that gives run time error (13) :

If pVal.ItemUID = "CMDBTN1" Then
    If pVal.EventType = et_ITEM_PRESSED And pVal.BeforeAction = True Then
       'Set oForm = SboApplication.Forms(pVal.FormUID)
       Set oForm = SboApplication.Forms.GetFormByTypeAndCount(pVal.FormType, pVal.FormTypeCount)
       Dim date1 As String
       Set oEdit = oForm.Items("1000004").Specific
       date1= oEdit.String
 
 StrSQL = "exec uspADCashReceipt '" & Date15 & "'"
       ViewReport "ADCashRCPT.rpt"

The SP is as follows:

[dbo].[uspADCashReceipt] @date1 varchar(10)
 
SELECT T0.DocDate, T1.DueDate, T2.BankName, T0.BankCountr,
       T1.AcctNum, T1.U_NSAcc, T1.CheckNum, T1.CheckSum,T0.CardName, 
       T3.City
FROM ORCT T0 INNER JOIN RCT1 T1 ON T0.DocNum = T1.DocNum
     INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode,
     ODSC T2 
      
WHERE T2.BankCode = T1.BankCode and T0.Canceled = 'N' and 
      T0.DocDate = (substring (@Date1,6,3)+'/'+substring (@Date1,1,3)+'/'+substring (@Date1,8,3))
      and T0.Canceled ='N'

I have changed the addon to be as follows:

If pVal.ItemUID = "CMDBTN1" Then
    If pVal.EventType = et_ITEM_PRESSED And pVal.BeforeAction = True Then
       'Set oForm = SboApplication.Forms(pVal.FormUID)
       Set oForm = SboApplication.Forms.GetFormByTypeAndCount(pVal.FormType, pVal.FormTypeCount)
       Dim date1 As Date
       Set oItem = oForm.Items("1000004")
       
 
 StrSQL = "exec uspADCashReceipt '" & Date1 & "'"
       ViewReport "ADCashRCPT.rpt"

and the SP to be as follows (like what you suggested) :

[dbo].[uspADCashReceipt] 
@date1 varchar(10)
 
SELECT T0.DocDate, T1.DueDate, T2.BankName, T0.BankCountr,
       T1.AcctNum, T1.U_NSAcc, T1.CheckNum, T1.CheckSum,T0.CardName, 
       T3.City
FROM ORCT T0 INNER JOIN RCT1 T1 ON T0.DocNum = T1.DocNum
     INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode,
     ODSC T2 
      
WHERE T2.BankCode = T1.BankCode and T0.Canceled = 'N' and 
      T0.DocDate = convert(datetime, @date1)
      and T0.Canceled ='N'

but the report is empty. There is no report resulted.

if the date is set hardcode e.g. between '01/01/2005' and '12/31/2008' , the addon generated more than 100 report pages and limited.

bsaed on your answer, it seems there is no possibility to use string but is there any other solution that could make this code can still be used in the SP:

 T0.DocDate = (substring (@Date1,6,3)+'/'+substring (@Date1,1,3)+'/'+substring (@Date1,8,3))

Pls give advice. TIA

Rgds,

[YatseaLi]

Convert(DateTime, 'YYYYMMDD', 112)
E.g. Convert(DateTime, '20081230', 112)

[former_member186095]

Hi Yatsea,

Thx for answer but beside changing the SP code, I prefer the VB6 code changing. I mean if there is another code that can make the substring in the SP working.

Pls advice

Rgds.

[Former Member]

Jimmy,

let me know, what for value is in the variable Date15 when you call the stored procedure.

exec uspADCashReceipt '" & Date15 & "'"