Incrementing value along with text

See more:

SQL-Server

MS-Excel

macros

VBA

Hello All,

I got an Excel sheet which is pulled from Database and after editing 1 or 2 column values in Excel, I should send back that Excel sheet to Database but it should get stored as New Table in Database so the original table should be as it is.

Along with this i need 1 column to be auto incremented which has text + value (eg:ver1).If original table has column Ver1 then the altered table which comes from Excel should be placed as Ver2 for all the values in it.

Please help to sort this out.
Im using VBA macros for this.

Private Sub CommandButton3_Click()
Dim adoCN As ADODB.Connection
    Dim sConnString As String
    Dim sSQL As String
    Dim lRow As Long, lCol As Long
     
    sConnString = "Provider=sqloledb;Server=;Database=;User Id=;Password="
     
    Set adoCN = CreateObject("ADODB.Connection")
     
    adoCN.Open sConnString
     
    'Assumes that you have Field1, Field2 and Field3 in columns A, B and C
    'For this example we can assume that the data exists on Sheet1, with a header on row
    '1 and data in rows 2-11
    'Also assume that the fields are defined as character (e.g. varchar or char)
    'Text values must be enclosed in apostrophes whereas numeric values should not.
     
    'adoCN.Open
    
    For lRow = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
'
    sSQL = "Select * INTO Ctss From Sheets(1)"

    adoCN.Execute sSQL

    sSQL = "INSERT INTO Ctss VALUES (" & "'" & Sheets(1).Cells(lRow, 4) & "', " & "'" & Sheets(1).Cells(lRow, 5) & "')"
'
   adoCN.Execute sSQL
''
'    sSQL = "UPDATE Tabs " & _
'" SET Version = 'ver6' "
'
'adoCN.Execute sSQL


'sSQL = "create function CustomerNumber (@id int)" & _
'"returns char(5)" & _
'"as begin return 'C' + right('0000' + convert(varchar(10), @id), 4)" & _
'"End"
    
    sSQL = "create function NextCustomerNumber()" & _
"returns char(5)" & _
"as begin declare @lastval char(5)" & _
"set @lastval = (select max(customerNumber) from Sheets(1) if @lastval is null set @lastval = 'C0001'" & _
"declare @i int" & _
"set @i = right(@lastval,4) + 1 return 'C' + right('000' + convert(varchar(10),@i),4)" & _
"End"

        adoCN.Execute sSQL
     
    Next lRow
     
    adoCN.Close
     
    Set adoCN = Nothing
     
     
    'On Error GoTo 0
End Sub

sSQL = "create function CustomerNumber (@id int)" & _
'"returns char(5)" & _
'"as begin return 'C' + right('0000' + convert(varchar(10), @id), 4)" & _
'"End"
    
    sSQL = "create function NextCustomerNumber()" & _
"returns char(5)" & _
"as begin declare @lastval char(5)" & _
"set @lastval = (select max(customerNumber) from Sheets(1) if @lastval is null set @lastval = 'C0001'" & _
"declare @i int" & _
"set @i = right(@lastval,4) + 1 return 'C' + right('000' + convert(varchar(10),@i),4)" & _
"End"
 
        adoCN.Execute sSQL

these are 2 functions im trying to increment text + value in VBA so tht it shuld increment in DB but its not working. Both are same type.