INSERT statement problem

[CharmCityRob]

I have a form where users can add file links to supporting documentation. I am storing the SiteID, along with the file description and file path of the file they browse to.

The problem is the path contains \ in the UNC path and I am getting an error on the INSERT statement. Can someone please provide some guidance?


Current INSERT Statement

'Build INSERT INTO statement.
strSQL = "INSERT INTO tblSiteFileAttachments " _
& "(siteID, faFileDescription, faFileName) " _
& "VALUES (" _
& [sID] & "," _
& "'" & txtFileDesc & "'," _
& "'" & txtFileName & " ')

 

[Andrzejek]

What do you have when you Print your SQL?

[green]'Build INSERT INTO statement.[/green]
strSQL = "INSERT INTO tblSiteFileAttachments " _
    & "(siteID, faFileDescription, faFileName) " _
    & "VALUES (" _
    & [sID] & "," _
    & "'" & txtFileDesc & "'," _
    & "'" & txtFileName & " ')" 
[blue]
Debug.Print strSQL[/blue]   [green]'????[/green]

And what error are you getting?

Have fun.

---- Andy

 

[CharmCityRob]

Debug Output of strSQL:
INSERT INTO tblSiteFileAttachments(siteID, faFileDescription, faFileName) VALUES (1006, '4-13 Error Log', 'C:\Error.log


and the Error is:
Error Number: 3075
Error Description: Syntax error in string in query expression ''C:\Error.log'.

 

[Andrzejek]

INSERT INTO tblSiteFileAttachments(siteID, faFileDescription, faFileName) 
VALUES (1006, '4-13 Error Log', 'C:\Error.log[red]')[/red]

Looks like the last quote and the ) is missing from your INSERT statement for some reason...

Have fun.

---- Andy

 

[CharmCityRob]

Yup, thats the problem. Any ideas on how to get it to include that in the SQL statement? As you can see my INSERT statement has that but is not being added. I believe it has to do with the slashes in the path... \ is an escape character is it not?

 

[Andrzejek]

I see....

If I do that (in VB 6):

Dim txtFileDesc  As String
Dim txtFileName  As String
Dim strSQL As String

txtFileDesc = "4-13 Error Log"
txtFileName = "C:\Error.log"

'Build INSERT INTO statement.
    strSQL = "INSERT INTO tblSiteFileAttachments " & vbNewLine _
       & "(siteID, faFileDescription, faFileName) " & vbNewLine _
       & "VALUES (1006," & vbNewLine _
       & "'" & txtFileDesc & "'," & vbNewLine _
       & "'" & txtFileName & "')"
       
Debug.Print strSQL

I get:[tt]

INSERT INTO tblSiteFileAttachments
(siteID, faFileDescription, faFileName)
VALUES (1006,
'4-13 Error Log',
'C:\Error.log')[/tt]

So, what's the difference?

Have fun.

---- Andy

 

[Andrzejek]

Try also:

Debug.Print "***" & txtFileDesc & "***" 
Debug.Print "***" & txtFileName & "***"

strSQL = .....

Let's see what you *really* have in txtFileDesc and txtFileName and how long they are. They may have a lot of Spaces at the end (I'm talking about txtFileName)

Have fun.

---- Andy

 

[CharmCityRob]

Ok, I think i have isolated the problem but not sure how to resolve it. For the OnClick event for the command button which allows the user to browse, i am using this function which is from an O'Reilly access book. The code is doing something to the textbox, just not sure what because if I just manually type in a path in the textbox it works fine.

Here is the code for the command button and the function called

Private Sub cmdFileBrowse_Click()

'show the open dialog and load
'selected file name in text box
txtFileName = GetFileNameBrowse

End Sub



Function GetFileNameBrowse() As String
On Error GoTo HandleError
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Forms("frmSiteView").hwnd
'OpenFile.hInstance = hInstance
sFilter = "All Files (*.*)" & Chr(0) & "*.*" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:\"
OpenFile.lpstrTitle = "Browse for an attachment"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
GetFileNameBrowse = ""
Else
'return the selected filename
GetFileNameBrowse = Trim(OpenFile.lpstrFile)
End If

Exit Function

HandleError:
GeneralErrorHandler Err.Number, Err.Description, BUS_LOGIC, _
"GetFileNameBrowse"
Exit Function

End Function

 

[CharmCityRob]

And here is the debug lines that you asked for


***Rob Test 9***
***C:\Error.log

 

[CharmCityRob]

I did a Len(txtFileName) and it is returning 257. It is not doing the Trim in the function properly and I can't see to trim it properly using any method.

Ideas?

 

[Andrzejek]

Is the txtFileName the name of the TextBox? If so, I would suggest to avoid using default properties and have it code:

Private Sub cmdFileBrowse_Click()
[green]
'show the open dialog and load
'selected file name in text box[/green]
txtFileName[blue].Text[/blue] = GetFileNameBrowse

End Sub

It halps to know that the txtFileName is not a variable, this way I can see that it is a text box. Just IMHO
[tt]
***Rob Test 9***
***C:\Error.log [/tt]
We are missing the last *** They have to be somewhere....

Try:

[green]'Build INSERT INTO statement.[/green]
strSQL = "INSERT INTO tblSiteFileAttachments " _
    & "(siteID, faFileDescription, faFileName) " _
    & "VALUES (" _
    & [sID] & "," _
    & "'" & txtFileDesc & "'," _
    & "'" & [blue]Trim([/blue]txtFileName[blue])[/blue] & "')" 

Debug.Print strSQL   [green]'????[/green]

Have fun.

---- Andy

 

[CharmCityRob]

I was able to fix it in the function by changing


GetFileNameBrowse = Trim(OpenFile.lpstrFile)

To

GetFileNameBrowse = left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1)

 

[Andrzejek]

The question is:
Does your INSERT statement work now?

Have fun.

---- Andy

 

[CharmCityRob]

Works like a champ