Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim dateNow As String
       dateNow = DateTime.Now.ToString("yyMMdd")
 
        If CoopID.Text.Length > 0 And CoopName.Text.Length > 0 And RPerson.Text.Length > 0 And Problem.Text.Length > 0 And Solution.Text.Length > 0 And CSName.Text.Length > 0 And Status.Text.Length > 0 Then
 
            Try
                Dim strSQL As String = "INSERT INTO CSLog (CoopID, CoopName, CoopName1,  RPerson, Phone, Problem, Solution, CSName, Status) VALUES (@CoopID, @CoopName, @CoopName1,  @RPerson, @Phone, @Problem, @Solution, @CSName, @Status)"
 
                Dim cmd As New System.Data.SqlClient.SqlCommand(strSQL, conn)
 
                cmd.Parameters.Add(New SqlClient.SqlParameter("@DocID", SqlDbType.Char, 15))
                cmd.Parameters("@DocID").Value = dateNow + CoopID.Text + GetRunNo().ToString("D4")
 
                cmd.Parameters.Add(New SqlClient.SqlParameter("@CoopID", SqlDbType.Char, 5))
                cmd.Parameters("@CoopID").Value = CoopID.Text
 
                cmd.Parameters.Add(New SqlClient.SqlParameter("@CoopName", SqlDbType.NVarChar, 50))
                cmd.Parameters("@CoopName").Value = CoopName.Text
 
                cmd.Parameters.Add(New SqlClient.SqlParameter("@CoopName1", SqlDbType.NVarChar, 50))
                cmd.Parameters("@CoopName1").Value = CoopName1.Text
 
                cmd.Parameters.Add(New SqlClient.SqlParameter("@RPerson", SqlDbType.Char, 20))
                cmd.Parameters("@RPerson").Value = RPerson.Text
 
                cmd.Parameters.Add(New SqlClient.SqlParameter("@Phone", SqlDbType.Char, 15))
                cmd.Parameters("@Phone").Value = Phone.Text
 
                cmd.Parameters.Add(New SqlClient.SqlParameter("@Problem", SqlDbType.NVarChar, 100))
                cmd.Parameters("@Problem").Value = Problem.Text
 
                cmd.Parameters.Add(New SqlClient.SqlParameter("@Solution", SqlDbType.NVarChar, 100))
                cmd.Parameters("@Solution").Value = Solution.Text
 
                cmd.Parameters.Add(New SqlClient.SqlParameter("@CSName", SqlDbType.Char, 20))
                cmd.Parameters("@CSName").Value = CSName.Text
 
                cmd.Parameters.Add(New SqlClient.SqlParameter("@Status", SqlDbType.Char, 10))
                cmd.Parameters("@Status").Value = Status.Text
 
 
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                cmd.Connection.Close()
                conn.Close()
 
            Catch ex As Exception
 
                Label6.Text = "PLEASE FILL IN ALL THE FIELD LISTED."
                Label6.Visible = True
 
            Finally
 
 
                Label6.Text = "Cussecfully Saved ."
                Label6.Visible = True
                Clear()
 
            End Try
 
 
        End If
 
    End Sub
 
Private Function GetRunNo() As Long
 
        Dim ds As DataSet = Nothing
 
        Try
            Dim sql = "SELECT TOP 1 tarikh1, run_no from runNo"
            ' Dim comm = New SqlClient.SqlDataAdapter(sql, conn)
 
            Dim comm = New SqlClient.SqlCommand(sql, conn)
 
            Dim da = New SqlClient.SqlDataAdapter(comm)
 
            ds = New DataSet
 
            da.Fill(ds, "runNo")
 
            Dim lastDateUsed As Date = ds.Tables(0).Rows(0).Item("tarikh1")
            Dim lastRunNoUsed As Long = ds.Tables(0).Rows(0).Item("run_no")
 
            If DateTime.Now.Date > lastDateUsed.Date Then
                ' Chnage date to today's date
                lastDateUsed = DateTime.Now.Date
                ' reset runNo to 1
                lastRunNoUsed = 1
            Else
                ' Increment runNo
                lastRunNoUsed = lastRunNoUsed + 1
            End If
 
            ' Update database
            UpdateRunNo(lastDateUsed, lastRunNoUsed)
 
            Return lastRunNoUsed
            comm = Nothing
        Catch ex As Exception
            Label5.Visible = True
            Label5.Text = "Error. " & ex.Message
        Finally
            conn = Nothing
            ds = Nothing
        End Try
    End Function
 
 
    Private Sub UpdateRunNo(ByVal lastDateUsed As DateTime, ByVal lastRunNoUsed As Long)
        Dim comm As SqlClient.SqlCommand = Nothing
 
        Try
            Dim sql = "UPDATE runNo SET tarikh1 = @tarikh1, run_no = @run_no"
            comm = New SqlClient.SqlCommand(sql, conn)
 
            comm.Parameters.Add(New SqlClient.SqlParameter("@tarikh1", SqlDbType.DateTime))
            comm.Parameters("@tarikh1").Value = lastDateUsed
 
            comm.Parameters.Add(New SqlClient.SqlParameter("@run_no", SqlDbType.BigInt))
            comm.Parameters("@run_no").Value = lastRunNoUsed
 
 
            comm.ExecuteNonQuery()
            comm.Connection.Close()
 
        Catch ex As Exception
            Label5.Visible = True
            Label5.Text = "Error. " & ex.Message
 
        Finally
            conn = Nothing
            comm = Nothing
        End Try
 
    End Sub

Finally
   conn = Nothing
   ds = Nothing
End Try

Finally
  conn = Nothing
  comm = Nothing
End Try

cmd.Parameters.Add(New SqlClient.SqlParameter("@DocID", SqlDbType.Char, 15))
cmd.Parameters("@DocID").Value = dateNow + CoopID.Text + GetRunNo().ToString("D4")

cmd.Parameters.Add(New SqlClient.SqlParameter("@DocID", SqlDbType.Char, 15))
Dim DocIDParam As String
DocIDParam = dateNow + CoopID.Text + GetRunNo().ToString("D4")
If DocIDParam.Length > 15 Then
  MessageBox.Show("DocID length=" & DocIDParam.Length, "Parameter Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
  ' Truncate param
  ' DocIDParam = DocIDParam.Substring(0, 15)
  ' Or exit sub
  ' Exit Sub 
Else
  cmd.Parameters("@DocID").Value = DocIDParam
End If