0

below is my code to get the quarter and it works well

Private Function GetQuarter() As String
    initializeCon()
    Dim dt As DateTime
    Return ((dt.Month - 1) / 3) + 1
End Function

below is my code to get the year and it works well

Private Function AutoYear() As String
    initializeCon()
    Dim d As Date = Now
    Return d.ToString("yy")
End Function

below is my code for auto increment number. Here's my problem, first attempt it saves to the database but in the second time the code below gets error and it says Conversion from String 191001 to type Double is not valid, and the error refers to the code Return ProdID. Can somebody help me about this?

Private Function GetAutoNumber() As String
    initializeCon()

    Try
        cmd = New SqlCommand("SELECT LotNo FROM ProductionOrder ORDER BY ID", con)
        cmd.ExecuteNonQuery()
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        If (dr.Read) = True Then
            Dim id As Integer
            id = dr(0) + 1
            ProdID = id.ToString("000")

        ElseIf IsDBNull(dr) Then
            ProdID = "001"

        Else
            ProdID = "001"
        End If

    Catch ex As Exception
        MsgBox(ex.Message)

    Finally
        cmd.Dispose()
        con.Close()
    End Try
    Return ProdID
End Function

To get the 191001, I just called their function names. 19 is for the year, 1 is for the quarter, and 001 is for control number.

jay22
  • 1
  • 4
  • Which line does the exception happen on? To find out, comment out the Catch part. – Andrew Morton Jan 10 '19 at 09:52
  • It happens to id = dr(0) + 1. It says Inner Exception FormatException: Input String was not in a correct format. – jay22 Jan 10 '19 at 10:04
  • what data type is `dr(0)` ? – Cal-cium Jan 10 '19 at 10:15
  • If you use [`Option Strict On`](https://stackoverflow.com/a/29985039/1115360) it will highlight problems for you and guide you to correcting them. Getting the data type correct for the line of code with the problem will be included. – Andrew Morton Jan 10 '19 at 11:27
  • yes i use Option Strict On and the error says Conversion from String 191001 to type Double is not valid. Inner Exception FormatException: Input string was not in a correct format – jay22 Jan 10 '19 at 23:47
  • The error says whats happening. You need to convert the string to type Double. Try `Double.TryParse()` for converting – Cal-cium Jan 11 '19 at 09:03
  • What is the data type of the column `LotNo` in the database? Then we can tell you what needs to be changed on the line with the error. – Andrew Morton Jan 11 '19 at 09:55
  • @AndrewMorton its nvarchar() – jay22 Jan 13 '19 at 23:36
  • Convert `dr(0)` to an integer and that should hopefully fix the problem. If `LotNo` is nvarchar then you are trying to convert text to an integer. – Cal-cium Jan 14 '19 at 11:04
  • 1
    @jay22 1) `id = CInt(dr.GetString(0)) + 1`. 2) nvarchar seems a strange choice for a number. – Andrew Morton Jan 14 '19 at 11:09
  • @AndrewMorton might be safer to use tryparse if the database is using nvarchar, just in case he has got text in the fields. i agree it is a strange choice – Cal-cium Jan 14 '19 at 11:58
  • @jay22 If, and it is only a tiny chance, you call the functions `AutoYear` and `GetQuarter` just either side of midnight on 2019-12-31, you could get either the first quarter of 2019 or the last quarter of 2020, where it should be the first quarter of 2020. Just something to take in to consideration. – Andrew Morton Jan 15 '19 at 09:03

0 Answers0