AutoIncrement according to Date

[Leodumbi]

Ho Gents

Is there any one who could help me on getting this sorted out?
I want to save a unique indetification record considering this criteria:
FULLDATE/PROCESS#.

I.E: 03052012/1
03052012/2
03052012/3
...

03062012/1
03062012/1

For each date, I want it to start from one allover again.

I have currently have no ideas from where to start, so, any guidance is very much appreciated.

Thanks in advance
Leo

[thehunk]

you have to use replace , for this ,try this

'if date format is like dd-mmm-yyyy
dim a as string 
a = datetimepicker.value.date.tostring()
a = a.replace("-","")
'now do this 
a = a & "/"
'now also add your number like this 
a = a& txtnumber.text

try this . :)

[adam_k]

If the db is SQL then :

SELECT CONVERT(varchar(10), GETDATE(), 112) + '/'+ convert(varchar(10), max(substring(id,charindex('/',id) + 1 ,len(id) - charindex('/',id))) +1 )
from #test
where left(id,charindex('/',id)-1) = CONVERT(varchar(10), GETDATE(), 112)

assuming id is your field and #test your table.
If it's not SQL then the logic should apply but the functions may vary.

Also I've used ISO format for the date, if you are not happy about it you can use substring and concatenation to bring it to your liking.

[Leodumbi]

Hi all

thanks for your prompt answer. I appreciate all support.
what didnt mention is that I intend to save it as string and just retrieve te last number and increment+1 as long as the date remain the same. If the date changes then is starts by one again

Thnks one more time
Leo

[Mitja Bonca]

Hi,
I did much of the code you need. The code retreives the new new date and number.
What is checks.
- If the date in the stirng retreived from datebase (the last one) if older then today, it creates new "number", with today` date and number 1 after slash
- if the date from the number is today`s date, it only add to last number +1

So here is the code that does all (Select, Insert and Changing the number):

Class Program
	Private Shared Sub Main(args As String())
		Dim strDate As String = GetLastDate()
		'string strDate = "03072012/2"; this was only for my test, and it worked OK!
		strDate = CreateNewNumber(strDate)
		'this will create new date with appropriate number!!
		'now you can save it back into the database as well!
		InsertNewDate(strDate)
	End Sub

	Private Shared Function GetLastDate() As String
		Dim result As String = String.Empty
		Dim query As String = "SELECT LAST(YourColumnName) AS LastDate FROM YourTable"
		Using conn As New SqlConnection("connString")
			Using cmd As New SqlCommand(query, conn)
				Using reader As SqlDataReader = cmd.ExecuteReader()
					result = DirectCast(reader(0), String)
				End Using
			End Using
		End Using
		Return result
	End Function

	Private Shared Sub InsertNewDate(str As String)
		'NOTE: I am only insetring one column!!
		'you might have more of them, but do as I do bellow:
		Dim query As String = "INSERT INTO MyTable VALUES (@param1)"
		Using conn As New SqlConnection("connString")
			Using cmd As New SqlCommand(query, conn)
				cmd.Parameters.Add("@param1", System.Data.SqlDbType.VarChar, 50).Value = str
				cmd.ExecuteNonQuery()
			End Using
		End Using
	End Sub

	Private Shared Function CreateNewNumber(strDate As String) As String
		Dim newDateNumber As String = String.Empty
		Dim data As String() = strDate.Split("/"C)
		Try
			Dim [date] As DateTime = DateTime.ParseExact(data(0), "MMddyyyy", System.Globalization.CultureInfo.InvariantCulture)
			Dim number As Integer = Convert.ToInt32(data(1))
			If DateTime.Today > [date] Then
				newDateNumber = String.Format("{0}/{1}", [date].ToString("MMddyyy"), "1")
			Else
				newDateNumber = String.Format("{0}/{1}", data(0), System.Threading.Interlocked.Increment(number))
			End If
		Catch
			Throw New Exception("There is some problem with the datetime string!")
		End Try
		Return newDateNumber
	End Function
End Class

Hope it helps,
bye

[adam_k]

I always find it better to handle keys on db. It will never create a duplicate key and it saves in time since it will connect to the db only to insert data and not to read the last key and then insert the data.

I think that all requirements are met with my SQL query. Use it in a function to keep your insert statements simple or use it directly in your insert statement. With an OUTPUT clause you can get the key for the inserted record with 1 connection to the db and save resources and conflicts.

[Leodumbi]

hi

how to i call thesse fuction?

thanks

[adam_k]

Read here how to create a function: http://msdn.microsoft.com/en-us/library/ms186755.aspx
Depending on the type of function you create you can execute it or use it in a select (like you would getdate()).
I suggest you go with a scalar type function that would return a varchar(or char) that you can directly use in your insert like:

insert into table1
(id, field1)
values 
(dbo.get_id_function(),'Value1')

[luvprogram]

I need to do the same thing except I need number increments for locker numbers 1-10 by date. My query is named lockernumber and I need to put this in a form that I created.

Can someone help me with this?