0

I'ld like to generate a salesID in the format ddmmyyyy/0000. The number after

/ should always start from 0001 for a new day.

example 

12072016/0001
12072016/0002
12072016/0003
13072016/0001
13072016/0002
13072016/0003
14072016/0001
14072016/0002

I tried reading the if a sale has been made on a date, if not then i start the first sale with the day's date and add 0001 and if there sales have been made then increment it. 

  myConnection.Open()
    Dim sel As SqlCommand = New SqlCommand("select Code as Code  from sales where saledate ='" & Format(Today, "yyyy-MM-dd") & "'", myConnection)
    Dim dr As SqlDataReader = sel.ExecuteReader
    If dr.HasRows Then


        While dr.Read

            dmyConnection.Open()
            Dim seld As SqlCommand = New SqlCommand("select max(Code) as Code from sales where saledate='" & Format(Today, "yyyy-MM-dd") & "'", dmyConnection)
            Dim r As SqlDataReader = seld.ExecuteReader
            code = Format((r("Code") + 1), "0000")
            Sales_ID = (Format(Today, "ddMMyyyy")) & "/" & code
            SalesID.Text = Sales_ID

            dmyConnection.Close()
        End While
        dr.Close()
        myConnection.Close()

    Else


        dr.Close()
        code = "0001"
        Sales_ID = (Format(Today, "ddMMyyyy")) & "/" & code
        SalesID.Text = Sales_ID
        myConnection.Close()
    End If

But I get an error

Invalid attempt to read no data is present

What am I doing wrong? Please help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vincent Prince Assah
  • 31
  • 1
  • 8
  • Possible duplicate of [Invalid attempt to read when no data is present](http://stackoverflow.com/questions/1147615/invalid-attempt-to-read-when-no-data-is-present) – Andrei Jul 14 '16 at 09:14
  • 1
    You are complicating your life (an de-normalising your data) by storing the date twice in two different formats. Just store the incremented number for the day and combine this with the date to the user – Matt Wilko Jul 14 '16 at 09:27
  • @MattWilko how do i do that, am new to sql and vb – Vincent Prince Assah Jul 14 '16 at 09:29
  • 1
    @VincentPrinceAssah : i think you forgot to mention 'r.Read' to your code inside While 'dr.Read' – Dandy Jul 14 '16 at 11:38
  • Additionally it would be helpful to throw some debug output into the code... so you can narrow down which of the readers fails. e.g. you don't check if r has rows - this would be a good point for an error... – Tyron78 Jul 14 '16 at 12:22
  • Similar to Matt, I think you're making your life unnecessarily difficult unless there is some unusual business need here. SalesID should be your primary key and shouldn't be a varchar2 (or whatever SQL Server calls strings) field. You should not be starting over at 0001 each day, but continuously increment for all time. Since you have a saledate field, you can easily pull all sales for a time period. – topshot Jul 14 '16 at 13:16

0 Answers0