Question How to automatically append a letter to a record

HannahQD

New member
Joined
Jun 3, 2014
Messages
1
Programming Experience
3-5
Hi,

I have an sql table which has a letter and a number and the result in the third column

LetterNumberResult
A1A1A
B1B1A
A1A1B







The result is appending the Letter and Number and the first letter of the alphabet A, so result is A1A
On the third row, the Letter and Number is the same as the first row which is A and 1, so since it found already a duplicate Letter and number, the result is A1B, since B is next to A

Could someone help me create an append query for this result.

Dim sqlcmd As New SqlClient.SqlCommand("SELECT Letter, Number, FROM tbl WHERE Result IS NULL", cn)
Dim reader As SqlDataReader = sqlcmd.ExecuteReader()
If reader.HasRows Then

Do While reader.Read()

'Check current record Letter and Number already exist in tbl, if exist check the last alphabet associated with it.
'Result = (Letter + Number) + (char(ascii(last letter associated with it) + 1))

'update the field Result in tbl with Result

Loop
End If
 
Last edited:

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,553
Location
Norway
Programming Experience
10+
For example use a Dictionary where key is String (letter+number) and value Integer (for counting up).
For each pair either add key or add +1 to value of existing key, convert the value to Char as you go and you have what you need to produce the result.
Convert.ToChar(65) produces "A". You can start each key count at 65 or just add the value to get there.
 
Top Bottom