String Help

Hello.


Im creating an interface like MS Access to create MySQL tables, it is almost done, BUT i have a "small" problem with a string concat, here is the code.

Code:

---

strSQL = "CREATE TABLE " & txtTableName & " ( "

For dimintRow = 1 To 19
    For dimintCol = 1 To 2
        TempStr = fgMySQLAccess.TextMatrix(dimintRow, dimintCol)
        strSQL = strSQL & " " & TempStr
        DoEvents
    Next dimintCol
    If TempStr <> "" Then
    strSQL = strSQL & " , "
    End If
Next dimintRow
strSQL = strSQL & " )"
txtSQLQuery = strSQL

---

I need to separate the table fields with a comma (","), but the way im doing it i always get an undesired come at the end of the sql

Code:

---

CREATE TABLE MyTable (  OneField Text ,  AnotherField Text ,  EvenAnotherField Text ,                                  )

---

Is there a way to get rid of it?


Thanks in advance!

If you add the first field to the string before the loop and then add the commas before the fields in the loop, you will not have any extra commas.

Something like this outside the loop structure:


txtsqlstr = Left(strsql, Len(Trim(strsql)) - 1) & ")"


JS

Code:

---


strSQL = "CREATE TABLE " & txtTableName & " ( "

For dimintRow = 1 To 19
    For dimintCol = 1 To 2
        TempStr = fgMySQLAccess.TextMatrix(dimintRow, dimintCol)
        strSQL = strSQL & " " & TempStr
        DoEvents
    Next dimintCol
    If TempStr <> "" Then
    strSQL = strSQL & " , "
    End If
Next dimintRow
strSQL = left(strSQL, len(strSQL) - 2)
strSQL = strSQL & " )"
txtSQLQuery = strSQL

---

Should fix it

this will remove the last character from your string.. do this before you execute the SQL statement

VB Code:

strSQL = left(strSQL,len(strSQL) - 2)

That's easy,
you put a comma after each strSQL (if its not empty).
So also do this for the last one, which you don't want to have. One way to get rid of it is to remove the last three characters of the strSQL just before you put the ")" at the end.

VB Code:

strSQL=right(strSQL,len(strSQL-3))

It's 3 char'S because you add " , ", which are 3 char's.


There are other ways to do it, but.....

Kleinma he added " , " not only " ," !

Quote:

---

Originally posted by opus
Kleinma he added " , " not only " ," !

---

well i think that they would get the idea.. you are correct however

even though it should work with 1 space in there anyway