Problem with auto generated field.

[Spiel79]

Hello everyone.

 

I have a field which is auto generated in VB.Net. It is a string value made up of the following:

The first three are alphabets (lets just say ABC)

The next four are digits corresponding to this year (ie 2004)

The last three digits are obtained by seeing the number of rows in the database, and adding one to that number. Hence my tenth row will be ABC2004010. An immediate problem which arises is when the year changes. Suppose I have ten rows at the end of 2004. When 2005 comes, the value is going to be ABC2005011! How do I go about looking at the number of rows WITH 2004 and then adding one to it?

The relevant code is pasted below:

 

MyConnection.Open()
Dim rowcount As Integer
Dim autorefno As String
rowcount = ds.Tables("testtable").Rows.Count
'ds refers to my dataset
rowcount += 1
autorefno = Format(rowcount, "D3")
TextBox3.Text = "ABC" & Today.Year & autorefno

 

Thanks in advance

[The One Who Was]

Spiel79:

 

I am by far not experienced with this very much but......

 

You could try the following indexof method if there is no way that there would be 2004 in your statement other than the year.

 

Example... ABC200502004 (this would cause a problem because the year is 2005, but the reference number contains 2004)

 

The following statement will detect if textbox1 contains 1 or more instances of "2004"

 

I'm sure you'll probably have more than 2003 records in your table making this example useless, but it may give you an idea where to search??

 

If textbox1.text.indexof("2004") > 0 then
messagebox.show("Textbox1 contains 2004")
else
messagebox.show("Textbox1 does not contain 2004")
end if

 

I don't know for sure but I'd think it could be used to loop through your table.

 

 

Or.... could this be possibly usefull??

 

Dim strSelect As string
textbox1.selectstart = 3
textbox1.selectlength = 4
strselect = textbox1.selectedtext

 

It basically selects the 4 characters after the first 3 charaters of your string and allows you to do whatever you wish with it. Could be used with a loop as well I'd assume.

 

I do hope this is of some use... or maybe it'll point u in the right direction. I'm sure if there's an easier way someone will read this soon enough and correct me.

Edited February 18, 2004 by The One Who Was

[Spiel79]

Thanks the one who was.

I guess the idea is to look at the LAST row entry in the database read off the 4th to 7th characters and compare them with Today.Year. If its the same, I can use the old method. Otherwise, I would re-initialise the last seven digits to Today.Year & 001, and subsequently increment this by one for each added row. Will try working on it but if anyone else has any other pointers, please post them. Regards.

[The One Who Was]

Spiel79:

 

Could also try this out if it's at all usefull....

 

Dim drROW as datarow
for each drROW in dsDATASET.tables("TABLENAME").rows
Dim strTEXT as string = drROW.item.("COLUMNNAME")
if strTEXT.substring(3) = "2004" then
    'Do whatever comparing you need ect, editing or even move 
    'this record to a different copy of this table if it's required
end if
next

 

This will work provided that the are always 3 characters in front of the year.

[Spiel79]

Will try it out, the one who was.

 

I just need to change the "2004" to Today.Date. Otherwise, I will only be working with 2004 and years which are NOT 2004. And I don't intend to modify the code once every year :p. Oh, and yes, there are always 3, and only 3, characters before the year. Thanks.