0

I need to add 111 days to my STDATE but it stops and give an error after it reaches the 12th day:

Conversion failed when converting date and/or time from character string.

However, if I add 15 months to the same STDATE it works perfectly.

I = 0
For Z = 0 To 111

    If CTY = "D" Then
        STDATE = STDATE.AddDays(I)
        'STDATE2 = STDATE.ToString("dd-MM-yyyy 00:00:01")
        PAMTPART = Math.Round(TB3.Text / INSTN, 2)
        CINSTUNIQ = Guid.NewGuid()
        INTAMTPART = Math.Round(TB4.Text / INSTN, 2)
        INSTSTR = "INSERT INTO [dbo].[tb_CaseInst] ([CaseInstUniq],[CaseInst_OrgUniq],[CaseInst_CaseUniq],[CaseInstDueDate],[CaseInstAmount], [CaseInstBalAmt],[CaseInstStatus],[CaseInstRemks],[CaseInstDate],[CaseInstPAmt],[CaseInstInttAmt],[IsActive])" _
                   & "VALUES ('" & CINSTUNIQ.ToString & "','" & ORGUNQ.ToString & "','" & CSUNIQ.ToString & "','" & STDATE & "'," & INSTAMT & "," & INSTAMT & ",'D','Inst. Due','" & SRDATE & "'," & PAMTPART & "," & INTAMTPART & ",'A')"
        CINSTSTR = "INSERT INTO [dbo].[tb_CaseInstD] ([CaseInstDUniq],[CaseInstD_OrgUniq],[CaseInstD_CaseUniq],[CaseInstD_InstUniq],[CaseInstDTranDueDate],[CaseInstDAmt],[CaseInstDBalAmt],[CaseInstDStatus],[CaseInstDRemks],[CaseInstDChqNo],[CaseInstDBankName],[CaseInstDChqDate],[CaseInstDChqAmt],[CaseInstDDate],[CaseInstDIsActive],[CaseInstDTransType],[CaseInstDPAmt],[CaseInstDInttAmt],[CaseInstDType])" _
                   & "VALUES ('" & Guid.NewGuid().ToString & "','" & ORGUNQ.ToString & "','" & CSUNIQ.ToString & "','" & CINSTUNIQ.ToString & "','" & STDATE & "'," & INSTAMT & "," & INSTAMT & ",'D','Inst. Due','XX','XX','" & SRDATE & "',0,'" & SRDATE & "','A'," & CASEI & "," & PAMTPART & "," & INTAMTPART & ",'I')"
        I = 1    
    End If
ItsPete
  • 2,363
  • 3
  • 27
  • 35
Fin
  • 23
  • 6
  • 2
    First step: Turn on [Option Strict](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/option-strict-statement). That will give you compile-time errors with straightforward messages (which is much better than runtime exceptions). Second, use parameterized queries to [prevent SQL injection](https://stackoverflow.com/q/14376473/8967612). – 41686d6564 stands w. Palestine Feb 05 '20 at 05:07
  • 1
    Use `SqlParameter` to pass values to the query. With parameters you will get rid of this issue and other problems your code have at this moment. – Fabio Feb 05 '20 at 05:07
  • Thanks ahmed, that option thing helped alot but the issue i found is when date is fetched from the Database which is stored as datetime2 is "2020-02-05 11:32:47.0000000" changes to #2/5/2020 11:32:47 AM# when i try to add months it add to days keeping month same and vice versa for adddays in vb. which gives an error after 12 – Fin Feb 05 '20 at 06:10
  • `TB3.Text / INSTN` Don't try to do arithmetic with Strings. Use .TryParse on the text property of text boxes. Then do the arithemetic. – Mary Feb 06 '20 at 02:06
  • Apparently STDATE is a string. You can't use `.AddDays` on a string. What does STDATE look like before you try to add the days? – Mary Feb 06 '20 at 02:28
  • It looks like all you will be adding to days is 0 on the first iteration and 1 on subsequent iterations. – Mary Feb 06 '20 at 02:32
  • Please post the corrected code by editing your question after you have turned on Option Strict and set up parameters. – Mary Feb 06 '20 at 02:34

1 Answers1

0

I think that you have to use DateAdd function with DateInterval.Day,1,STDATE parameters :

STDATE = DateAdd(DateInterval.Day, 1, STDATE )

Hope this help you

gsimo83
  • 109
  • 2
  • 9