Question How to automatically append a letter to a record

[HannahQD]

Hi,

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

Letter	Number	Result
A	1	A1A
B	1	B1A
A	1	A1B

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

 

[JohnH]

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.