CODEWITHSUNDEEP
jsonvb.net
Teknas
Teknas

Reputation: 559

Convert JSON date to vb.net date format

Following is the vb.net code which reads json string and then it bulkcopy to SQL.

I am getting and error when it bulkcopy to sql server saying "Invalid Data type"

Issue is with date format of column ("idt"). If i don't include this column in SQLBulk copy then it successfully inserts rest of the data into SQL.

    Dim JsonP As JObject = JObject.Parse(decodedString)
            Dim SetPointerOut As JToken = JsonP("b2b")

            Try

                Dim i as Integer = 0
                Dim j as Integer = 0
                For Each itemout as Object In SetPointerOut

                    Dim SetPointerIn As JToken = JsonP("b2b")(i)("inv")
                    For Each itemin as Object In SetPointerIn

                        Dim SetPointerInSub As JToken = JsonP("b2b")(i)("inv")(j)("itms")
                        For Each iteminsub as Object In SetPointerInSub
                            Dim NewDRinsub As DataRow = TempDT.NewRow
                            NewDRinsub("ctin") = itemout("ctin")
                            NewDRinsub("cfs") = itemout("cfs")
                            NewDRinsub("val") = itemin("val")
                            NewDRinsub("inv_typ") = itemin("inv_typ")
                            NewDRinsub("pos") = itemin("pos")
                            NewDRinsub("idt") = itemin("idt") --- **source json has this date in format "dd-mm-yyyy"
                            NewDRinsub("rchrg") = itemin("rchrg")
                            NewDRinsub("inum") = itemin("inum")
                            NewDRinsub("chksum") = itemin("chksum")
                            TempDT.Rows.Add(NewDRinsub)
                       Next
                        j = j + 1
                    Next
                    i += 1
                    j = 0
                Next

    Using sqlBulkCopy As New SqlBulkCopy(Mycon.mConnection, SqlBulkCopyOptions.FireTriggers, Nothing)
                    sqlBulkCopy.DestinationTableName = "GST2_B2B"
                    sqlBulkCopy.ColumnMappings.Add("ctin", "PartyGSTNo")
                    sqlBulkCopy.ColumnMappings.Add("cfs", "CFS")
                    sqlBulkCopy.ColumnMappings.Add("val", "Invoice_Value")
                    sqlBulkCopy.ColumnMappings.Add("inv_typ", "Invoice_Type")
                    sqlBulkCopy.ColumnMappings.Add("pos", "StateCode")
                    sqlBulkCopy.ColumnMappings.Add("idt", "Invoice_Date") --- source json has this date in format "dd-mm-yyyy"
                    sqlBulkCopy.ColumnMappings.Add("rchrg", "ReverseCharge")
                    sqlBulkCopy.ColumnMappings.Add("inum", "Invoice_Number")
                    sqlBulkCopy.ColumnMappings.Add("chksum", "CHKSUM")
                    sqlBulkCopy.ColumnMappings.Add("period", "Period")
                    sqlBulkCopy.BulkCopyTimeout = 120
                    sqlBulkCopy.WriteToServer(TempDT) -- error at this stage
 --- Getting error at this stage, saying "Invalid datatype", and that is because of column sqlBulkCopy.ColumnMappings.Add("idt", "Invoice_Date")
                End Using

so as said, if i remove sqlBulkCopy.ColumnMappings.Add("idt", "Invoice_Date"), then it works well. I guess Bulkcopy has some issue with dateformt of "idt" column of vb.net datatable.

So, how do i resolve this dateformat issue?

how do i convert json date to vb.net format at code line marked above with comment?

Upvotes: 0

Views: 898

Answers (1)

Mary
Mary

Reputation: 15091

Just for grins give this a shot.

NewDRinsub("idt") = Date.ParseExact(itemin("idt"), "dd-MM-yyyy", CultureInfo.InvariantCulture)

https://learn.microsoft.com/en-us/dotnet/api/system.datetime.parseexact?view=netframework-4.8#System_DateTime_ParseExact_System_String_System_String_System_IFormatProvider_

Upvotes: 1

Related Questions