Resolved TrimEnd Fails to Remove Last Character

[benshaws]

.Net Framework 4.6
Visual Studio 2019

    Public Shared Function AddRow(ByVal databaseUser As String, ByVal databasePassword As String, ByVal databasePath As String, ByVal databaseTable As String, ByVal databaseColumns As Array, ByVal databaseValues As Array) As Boolean
        Dim connection As OleDb.OleDbConnection = BuildConnectionString(databaseUser, databasePassword, databasePath)
        Try
            If databaseColumns.Length = databaseValues.Length Then
                Dim insertString As String = "INSERT INTO " + databaseTable + " ("
                For Each column In databaseColumns
                    'insertString += $"{column}, "
                    insertString += column + ","
                Next
                insertString.TrimEnd(",")
                insertString += ") VALUES("
                For Each value In databaseValues
                    'insertString += $"'{value}', "
                    insertString += "'" + value + "',"
                Next
                insertString.TrimEnd(",")
                insertString += ")"
                Dim insertCommand As New OleDb.OleDbCommand With {
                .CommandText = insertString,
                .Connection = connection
            }
                Using insertCommand
                    If connection.State = ConnectionState.Closed Then connection.Open()
                    insertCommand.ExecuteNonQuery()
                    insertCommand.Dispose()
                End Using
                Return True
            Else
                Return False
                Exit Function
            End If
        Catch ex As Exception
            Return False
        Finally
            connection.Close()
        End Try
    End Function

I am getting a syntax error at the insertCommand.ExecuteNonQuery() .

In the Locals window I can see that the insertString still includes the last , that the two insertString.TrimEnd(",") code lines should be removing.

The one thing I have noticed is the insertString is surronded by quotes - one at each end of the string. Is this the problem?

It's been a while since I last wrote any code so I must be missing something but this is code I am reusing from a past project that I thought was working. Could someone please have a look and advise?

Thanks,
Kevin.

 

[jmcilhinney]

There's no need to use TrimEnd in the first place because there's no need to add the spurious comma in the first place.

Dim insertString = $"INSERT INTO {databaseTableName} ({String.Join(",", databaseColumns)}) VALUES ('{String.Join("','", databaseValues)}')"

 

[benshaws]

Thanks - was not expecting such a quick reply.

I've used your line of code but now by insertString value in the Locals window is "INSERT INTO Test (System.String[]) VALUES ('System.String[]')". This Fuction is part of a class that I have imported from a past project, if that matters.

 

[JohnH]

Locals window is "INSERT INTO Test (System.String[]) VALUES ('System.String[]')"

You should change method parameter types from As Array to As String()

In post 1 you had code insertString.TrimEnd(","), but TrimEnd is a function that returns the modified string:

insertString = insertString.TrimEnd(",")

 

[benshaws]

Again thanks.

You should change method parameter types from As Array to As String()

Just worked that out myself.

The last time I fired up Visual Studio was Jan 2023 so I am a bit rusty/slow at the moment.

Thank you both jmcilhinney and JohnH.

I am marking this resolved.

 

[JohnH]

Be absolutely sure you don't open for SQL injection attacks when you're not using parameterized queries.

 

[benshaws]

Be absolutely sure you don't open for SQL injection attacks when you're not using parameterized queries.

I know. I won't be a problem.

If others are interested there is a blog post here:-

Using Parameters in ADO.NET

As a general rule, parameters should always be used when inserting values into SQL statements. Many people, generally new developers, don't...

jmcilhinney.blogspot.com