[RESOLVED] Insert records in Ms Access Database sql error

[clausowitz]

I am trying to add records to a table of my access database.
When I create the SQL string I get an error. I am not sure what is the problem but I am guessing the path.

Private Sub AddRecords(sPath As String)

        Dim SQLstr As String
        Dim Command As OleDbCommand
        Dim icount As Integer
        Dim di As New DirectoryInfo(sPath)
        ' Get a reference to each file in that directory.
        Dim fiArr As FileInfo() = di.GetFiles()
        ' Display the names of the files.
        Dim fri As FileInfo

        Try

                Dim sName As String
                Dim sLastAcc As String
                Dim sType As String
                Dim Size As String

            For Each fri In fiArr

                sType = Path.GetExtension(fri.Name).ToLower()
                sLastAcc = fri.LastAccessTime
                Size = FileLen(sPath & fri.Name)
                sName = UCase(fri.Name)

                SQLstr = "INSERT INTO Documents ([Document Location],[Document Type],[Last Accessed],[File Size],[Document Name],[OldName]) VALUES '" & sPath & "','" & sType & "','" & sLastAcc & "','" & Size & "','" & sName & "','" & sName & "'"

                Command = New OleDbCommand(SQLstr, con)
                icount = Command.ExecuteNonQuery

            Next fri

        Catch ex As Exception
            MessageBox.Show(ex.Message & " - " & ex.Source)
        End Try

    End Sub

This is the SQLstr that creates the error:

INSERT INTO Documents ([Document Location],[Document Type],[Last Accessed],[File Size],[Document Name],[OldName]) VALUES 'C:\INFORMATIE OPSLAG\2021\','.pdf','17-4-2021 14:29:02','137992','MY.FILE.IN.DIRECTORY.20210414.PDF','MY.FILE.IN.DIRECTORY.20210414.PDF'

[jmcilhinney]

Firstly, what error? Secondly, don't use string concatenation to build SQL code in the first place. Always use parameters. If you do that then it will likely fix your problem. Follow the Blog link in my signature and check out my thread on using parameters in ADO.NET to learn how.

[clausowitz]

I solved it. An error in the sql string.

SQLstr = "INSERT INTO Documents ([Document Location],[Document Type],[Last Accessed],[File Size],[Document Name],[OldName]) VALUES ('" & sPath & "','" & sType & "','" & sLastAcc & "','" & Size & "','" & sName & "','" & sName & "')"

[jmcilhinney]

That is a bad solution. If you were doing it the right way in the first place, i.e. using parameters, then your code would be far more readable and you'd be less likely to make mistakes like that at all. Don't be lazy and keep doing it the wrong way because it would take effort to learn to do it the right way. Learn the right way now and do it the right way hereafter. It will save you time and effort in the long run.

[ChrisE]

I solved it. An error in the sql string.

SQLstr = "INSERT INTO Documents ([Document Location],[Document Type],[Last Accessed],[File Size],[Document Name],[OldName]) VALUES ('" & sPath & "','" & sType & "','" & sLastAcc & "','" & Size & "','" & sName & "','" & sName & "')"

you should take the advice from Post#2 = Always use parameters.

it really is a small change, something like this

  'Create the command.
                Dim insertSQL As String = "INSERT INTO Documents "
                insertSQL &= "([Document Location],[Document Type],[Last Accessed],[File Size],[Document Name],[OldName])"
                insertSQL &= "VALUES (?,?,?,?,?,?)"
                Dim cmd As New OleDb.OleDbCommand(insertSQL, Con)
                cmd.Parameters.AddWithValue("Document Location", sPath)
                cmd.Parameters.AddWithValue("Document Type", sType)
                'add the others

[jmcilhinney]

you should take the advice from Post#2 = Always use parameters.

it really is a small change, something like this

  'Create the command.
                Dim insertSQL As String = "INSERT INTO Documents "
                insertSQL &= "([Document Location],[Document Type],[Last Accessed],[File Size],[Document Name],[OldName])"
                insertSQL &= "VALUES (?,?,?,?,?,?)"
                Dim cmd As New OleDb.OleDbCommand(insertSQL, Con)
                cmd.Parameters.AddWithValue("Document Location", sPath)
                cmd.Parameters.AddWithValue("Document Type", sType)
                'add the others

No need for any concatenation to build the SQL. If you don't like a long(ish) SQL statement in one line then use a multiline String literal, which VB has supported for a while now (since 2017 or maybe 2015, I think). Before that, XML literals were the best option.

[clausowitz]

I changed my code an used the Parameters. Only question remains why the use of parameters is better?!

[si_the_geek]

Using Parameters helps avoid many problems, including the kind of thing you experienced here, data type issues, security issues, and more.

For further explanation, see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).

[techgnome]

Of course the most famous reason to use parameters is of course the story of Little Bobby Drop Tables;

-tg

[jmcilhinney]

Only question remains why the use of parameters is better?!

Why would that question remain when I specifically explain the answer in the blog post I directed you to multiple times? It's almost like you were too lazy to look at it all to begin with and then, when you did look, you were too lazy top read it properly. If you ask for help and people provide it, maybe use it.