Pipe character in query in VB6 app


S

Saga

Hello. I am maintaining an application in VB6 that uses DAO 3.51. The app uses an Access MDB to hold data while it is processing.

One of the records contains a name that has a pipe character, for example "500S|Sports World". A syntax error occurs when this record is inserted intothe MDB database. I found that the pipe has special meaning, but was unable to find any useful information on how to insert and query data that has aliteral pipe embedded in it.

Can anyone help with this issue? Thank you all, Saga
 
Ad

Advertisements

R

Ron Weiner

Saga wrote on 2/7/2014 :
Hello. I am maintaining an application in VB6 that uses DAO 3.51. The app
uses an Access MDB to hold data while it is processing.

One of the records contains a name that has a pipe character, for example
"500S|Sports World". A syntax error occurs when this record is inserted into
the MDB database. I found that the pipe has special meaning, but was unable
to find any useful information on how to insert and query data that has a
literal pipe embedded in it.

Can anyone help with this issue? Thank you all, Saga
Taking a Wild Ass Guess here that the name of the Column has a Pipe
character in it. If that's the case wrap the Column name in Square
Brackets [].

Ex: insert into YourTable([Colum|Name|With|Pipe]) values('dsa|fdsa')

Rdub
 
S

Saga

Saga wrote on 2/7/2014 :
Hello. I am maintaining an application in VB6 that uses DAO 3.51. The app
uses an Access MDB to hold data while it is processing.

One of the records contains a name that has a pipe character, for example
"500S|Sports World". A syntax error occurs when this record is insertedinto
the MDB database. I found that the pipe has special meaning, but was unable
to find any useful information on how to insert and query data that hasa
literal pipe embedded in it.

Can anyone help with this issue? Thank you all, Saga


Taking a Wild Ass Guess here that the name of the Column has a Pipe

character in it. If that's the case wrap the Column name in Square

Brackets [].



Ex: insert into YourTable([Colum|Name|With|Pipe]) values('dsa|fdsa')



Rdub
Actually it is the data that contains the pipe character ("500S|Sports World" given in mu OP). Column names are ok. I can just replace the pipe with aspace, but this would be my last recourse. Before that I would like t solve the issue without having to alter the data.

Thank you for your reply. Saga
 
B

Bob Barrows

Saga said:
Hello. I am maintaining an application in VB6 that uses DAO 3.51. The
app uses an Access MDB to hold data while it is processing.

One of the records contains a name that has a pipe character, for
example "500S|Sports World". A syntax error occurs when this record
is inserted into the MDB database.
Using SQL or a recordset? Let's see some code.
I found that the pipe has special
meaning,
.... which is ... ?
but was unable to find any useful information on how to
insert and query data that has a literal pipe embedded in it.

Can anyone help with this issue? Thank you all, Saga
In sql and in vb-related languages, the common way of escaping special
characters so that they are treated as literals is to double them. I was
unaware of a special meaning for the pipe character, but using Replace to
replace a pipe with two pipes should get around that.
 
B

Bob Barrows

Saga said:
Hello. I am maintaining an application in VB6 that uses DAO 3.51. The
app uses an Access MDB to hold data while it is processing.

One of the records contains a name that has a pipe character, for
example "500S|Sports World". A syntax error occurs when this record
is inserted into the MDB database. I found that the pipe has special
meaning, but was unable to find any useful information on how to
insert and query data that has a literal pipe embedded in it.

Can anyone help with this issue? Thank you all, Saga
https://support.microsoft.com/kb/178070
 
R

Ron Weiner

Saga wrote on 2/8/2014 :
Saga wrote on 2/7/2014 :
Hello. I am maintaining an application in VB6 that uses DAO 3.51. The app
uses an Access MDB to hold data while it is processing.
One of the records contains a name that has a pipe character, for example
"500S|Sports World". A syntax error occurs when this record is inserted
into the MDB database. I found that the pipe has special meaning, but was
unable to find any useful information on how to insert and query data
that has a literal pipe embedded in it.

Can anyone help with this issue? Thank you all, Saga


Taking a Wild Ass Guess here that the name of the Column has a Pipe

character in it. If that's the case wrap the Column name in Square

Brackets [].



Ex: insert into YourTable([Colum|Name|With|Pipe]) values('dsa|fdsa')



Rdub
Actually it is the data that contains the pipe character ("500S|Sports World"
given in mu OP). Column names are ok. I can just replace the pipe with a
space, but this would be my last recourse. Before that I would like t solve
the issue without having to alter the data.

Thank you for your reply. Saga
We are gonn'a need to see your code. I don't remember there being any
problems with the Pipe character in the data. I just tried the insert
statement in my sample and it inserted one record.

Rdub
 
Ad

Advertisements

S

Saga

Using SQL or a recordset? Let's see some code.







... which is ... ?







In sql and in vb-related languages, the common way of escaping special

characters so that they are treated as literals is to double them. I was

unaware of a special meaning for the pipe character, but using Replace to

replace a pipe with two pipes should get around that.
No. I already tried that and it gives me an error indicating bad use of thevertical character. I should mention that before this incident I too did not know that the pipe had special significance. Also, the fellows at the vbgeneral discussion group found that upgrading DAO from 3.51 to 3.6 fixed the problem. I will be looking into this on Monday when I get back to the office. Regards, Saga
 
S

Saga

Saga wrote on 2/8/2014 :
Saga wrote on 2/7/2014 :

Hello. I am maintaining an application in VB6 that uses DAO 3.51. The app
uses an Access MDB to hold data while it is processing.


One of the records contains a name that has a pipe character, for example
"500S|Sports World". A syntax error occurs when this record is inserted
into the MDB database. I found that the pipe has special meaning, but was
unable to find any useful information on how to insert and query data
that has a literal pipe embedded in it.



Can anyone help with this issue? Thank you all, Saga



Taking a Wild Ass Guess here that the name of the Column has a Pipe

character in it. If that's the case wrap the Column name in Square

Brackets [].



Ex: insert into YourTable([Colum|Name|With|Pipe]) values('dsa|fdsa')



Rdub
Actually it is the data that contains the pipe character ("500S|Sports World"
given in mu OP). Column names are ok. I can just replace the pipe with a
space, but this would be my last recourse. Before that I would like t solve
the issue without having to alter the data.

Thank you for your reply. Saga


We are gonn'a need to see your code. I don't remember there being any

problems with the Pipe character in the data. I just tried the insert

statement in my sample and it inserted one record.



Rdub
Thanks Ron, I'll follow up on Monday when I get back to the office. As noted in my other post, it seems that upgrading DAO from 3.51 to 3.6 fixes this. I will look further into this option. Regards, Saga
 
S

Saga

Thanks all for your help! Here is more info plus code.

Access MDB has been in operation since late 90s. The manager mentioned thatmost likely it was created using Access 97, but...

The code has provisions for creating the DB and the table in case it is notfound, so at this time it is being created by DAO 3.51. Querying the DAO dbcon.Version property in VB6 I found that 3.51 creates an MDB version 3.00.

I then changed the references to use DAO 3.6 and the code worked. Again I queried DAO dbcon.Version property in VB6 and got version 4.0.

Since the app is monstrous (to say the least) I was not able to include thecode. In its place I created a small (tiny) VB6 applet that uses the same code, is integrated in a small code snippet and produces the exact error that the big is application is having.

I should make note that the single quotes are being handled correctly, as there are records whose [Name] field contain these and this has never been aproblem. The only problematic rows are those that contain the pipe in thisfield.

Here is the code that I used for this test:

[New VB6 EXE project, added List1, Text1 and Command2]

Private Sub Command2_Click()

Dim sDBName As String
Dim sDBPath As String
Dim wrkDBEng As Workspace
Dim tdfNewTbl As TableDef
Dim dbCon As Database
Dim sSQL As String
Dim daors As DAO.Recordset

sDBName = "test01-v7.mdb"
sDBPath = App.Path

If Right$(sDBPath, 1) <> "\" Then
sDBPath = sDBPath & "\"
End If

'Create DB if not found.
If Len(Dir$(sDBPath & sDBName)) = 0 Then
'Not found, create
Set wrkDBEng = DBEngine.CreateWorkspace("ClientHold", "Admin", "")
Set dbCon = wrkDBEng.CreateDatabase(sDBPath & sDBName, dbLangGeneral)
Set dbCon = OpenDatabase(sDBPath & sDBName)
Set tdfNewTbl = dbCon.CreateTableDef("CustInfo")

With tdfNewTbl
.Fields.Append .CreateField("CustId", dbLong)
.Fields.Append .CreateField("CustName", dbText, 50)
.Fields.Append .CreateField("CustCode", dbText, 10)
End With

dbCon.TableDefs.Append tdfNewTbl

'Insert dummy data.
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values " & _
"(1,'Cozi Diner','COZD300')"
dbCon.Execute sSQL
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values " & _
"(2,'Mal''s Music','MALE122')"
dbCon.Execute sSQL
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values " & _
"(3,'3D4|Clockworx','CLOY429')"
dbCon.Execute sSQL
Else
'Found, open only.
Set dbCon = OpenDatabase(sDBPath & sDBName)
End If

'Data in table, query.
'Build query.
sSQL = "select * from CustInfo"

'Check if code is provided.
If Len(Trim$(Text1.Text)) > 0 Then
'Yes, include.
sSQL = sSQL & " where CustCode='" & Replace(Text1.Text, "'", "''") & "'"
End If

'Open dataset.
Set daors = dbCon.OpenRecordset(sSQL)

If Not daors.EOF Then
Do Until daors.EOF
List1.AddItem Format$(daors!CustId, "000") & " " & daors!CustCode & _
" " & daors!CustName
daors.MoveNext
Loop
End If

End Sub

Used DAO 3.51, deleted MDB file, got the following:

Err.Number: 3075
Err.Description: Syntax error in string in query expression ''3D4|Clockworx''.

The error, as expected, occurs on the third INSERT


Used DAO 3.6, deleted MDB file, everything worked as expected.

Further notes:

When I create the MDB using 3.51 I can open it using DAO 3.6, but when I create it using DAO 3.6 and try to open it using DAO 3.51 I get: Unrecognizeddatabase format 'E:\AccessPipe\test01-v7.mdb'. Just an interesting point.

The pipe character: **Jet uses pipe symbols to delimit field or parameter names embedded in a literal string, such as: SELECT "|LastName|, |FirstName|" FROM Employees ** Source: https://support.microsoft.com/kb/178070

The solution provided, to replace "|" with "'" & chr(124) & "'" is very particular to the Access environment.

Saga
 
U

Ulrich Möller

Am 10.02.2014 20:58, schrieb Saga:
Thanks all for your help! Here is more info plus code.

Access MDB has been in operation since late 90s. The manager mentioned that most likely it was created using Access 97, but...

The code has provisions for creating the DB and the table in case it is not found, so at this time it is being created by DAO 3.51. Querying the DAO dbcon.Version property in VB6 I found that 3.51 creates an MDB version 3.00.

I then changed the references to use DAO 3.6 and the code worked. Again I queried DAO dbcon.Version property in VB6 and got version 4.0.

Since the app is monstrous (to say the least) I was not able to include the code. In its place I created a small (tiny) VB6 applet that uses the same code, is integrated in a small code snippet and produces the exact error that the big is application is having.

I should make note that the single quotes are being handled correctly, as there are records whose [Name] field contain these and this has never been a problem. The only problematic rows are those that contain the pipe in this field.

Here is the code that I used for this test:

[New VB6 EXE project, added List1, Text1 and Command2]

Private Sub Command2_Click()

Dim sDBName As String
Dim sDBPath As String
Dim wrkDBEng As Workspace
Dim tdfNewTbl As TableDef
Dim dbCon As Database
Dim sSQL As String
Dim daors As DAO.Recordset

sDBName = "test01-v7.mdb"
sDBPath = App.Path

If Right$(sDBPath, 1) <> "\" Then
sDBPath = sDBPath & "\"
End If

'Create DB if not found.
If Len(Dir$(sDBPath & sDBName)) = 0 Then
'Not found, create
Set wrkDBEng = DBEngine.CreateWorkspace("ClientHold", "Admin", "")
Set dbCon = wrkDBEng.CreateDatabase(sDBPath & sDBName, dbLangGeneral)
Set dbCon = OpenDatabase(sDBPath & sDBName)
Set tdfNewTbl = dbCon.CreateTableDef("CustInfo")

With tdfNewTbl
.Fields.Append .CreateField("CustId", dbLong)
.Fields.Append .CreateField("CustName", dbText, 50)
.Fields.Append .CreateField("CustCode", dbText, 10)
End With

dbCon.TableDefs.Append tdfNewTbl

'Insert dummy data.
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values " & _
"(1,'Cozi Diner','COZD300')"
dbCon.Execute sSQL
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values " & _
"(2,'Mal''s Music','MALE122')"
dbCon.Execute sSQL
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values " & _
"(3,'3D4|Clockworx','CLOY429')"
dbCon.Execute sSQL
Else
'Found, open only.
Set dbCon = OpenDatabase(sDBPath & sDBName)
End If

'Data in table, query.
'Build query.
sSQL = "select * from CustInfo"

'Check if code is provided.
If Len(Trim$(Text1.Text)) > 0 Then
'Yes, include.
sSQL = sSQL & " where CustCode='" & Replace(Text1.Text, "'", "''") & "'"
End If

'Open dataset.
Set daors = dbCon.OpenRecordset(sSQL)

If Not daors.EOF Then
Do Until daors.EOF
List1.AddItem Format$(daors!CustId, "000") & " " & daors!CustCode & _
" " & daors!CustName
daors.MoveNext
Loop
End If

End Sub

Used DAO 3.51, deleted MDB file, got the following:

Err.Number: 3075
Err.Description: Syntax error in string in query expression ''3D4|Clockworx''.

The error, as expected, occurs on the third INSERT


Used DAO 3.6, deleted MDB file, everything worked as expected.

Further notes:

When I create the MDB using 3.51 I can open it using DAO 3.6, but when I create it using DAO 3.6 and try to open it using DAO 3.51 I get: Unrecognized database format 'E:\AccessPipe\test01-v7.mdb'. Just an interesting point.

The pipe character: **Jet uses pipe symbols to delimit field or parameter names embedded in a literal string, such as: SELECT "|LastName|, |FirstName|" FROM Employees ** Source: https://support.microsoft.com/kb/178070

The solution provided, to replace "|" with "'" & chr(124) & "'" is very particular to the Access environment.

Saga
Hi,

just a note:
dao.createdatabase has an optional third parameter to specify the
databaseformat ex. dbVersion30 or dbVersion40. So it should be possible
to create a DB 3.5x Format with DAO 3.6.

Ulrich
 
S

Saga

Am 10.02.2014 20:58, schrieb Saga:
Thanks all for your help! Here is more info plus code.
Access MDB has been in operation since late 90s. The manager mentioned that most likely it was created using Access 97, but...
The code has provisions for creating the DB and the table in case it isnot found, so at this time it is being created by DAO 3.51. Querying the DAO dbcon.Version property in VB6 I found that 3.51 creates an MDB version 3..00.
I then changed the references to use DAO 3.6 and the code worked. AgainI queried DAO dbcon.Version property in VB6 and got version 4.0.
Since the app is monstrous (to say the least) I was not able to includethe code. In its place I created a small (tiny) VB6 applet that uses the same code, is integrated in a small code snippet and produces the exact error that the big is application is having.
I should make note that the single quotes are being handled correctly, as there are records whose [Name] field contain these and this has never been a problem. The only problematic rows are those that contain the pipe in this field.
Here is the code that I used for this test:
[New VB6 EXE project, added List1, Text1 and Command2]
Private Sub Command2_Click()
Dim sDBName As String
Dim sDBPath As String
Dim wrkDBEng As Workspace
Dim tdfNewTbl As TableDef
Dim dbCon As Database
Dim sSQL As String
Dim daors As DAO.Recordset

sDBName = "test01-v7.mdb"
sDBPath = App.Path

If Right$(sDBPath, 1) <> "\" Then
sDBPath = sDBPath & "\"
End If

'Create DB if not found.
If Len(Dir$(sDBPath & sDBName)) = 0 Then
'Not found, create
Set wrkDBEng = DBEngine.CreateWorkspace("ClientHold", "Admin", "")
Set dbCon = wrkDBEng.CreateDatabase(sDBPath & sDBName, dbLangGeneral)
Set dbCon = OpenDatabase(sDBPath & sDBName)
Set tdfNewTbl = dbCon.CreateTableDef("CustInfo")

With tdfNewTbl
.Fields.Append .CreateField("CustId", dbLong)
.Fields.Append .CreateField("CustName", dbText, 50)
.Fields.Append .CreateField("CustCode", dbText, 10)
End With

dbCon.TableDefs.Append tdfNewTbl

'Insert dummy data.
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values "& _
"(1,'Cozi Diner','COZD300')"
dbCon.Execute sSQL
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values "& _
"(2,'Mal''s Music','MALE122')"
dbCon.Execute sSQL
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values "& _

dbCon.Execute sSQL

'Found, open only.
Set dbCon = OpenDatabase(sDBPath & sDBName)
End If

'Data in table, query.
'Build query.
sSQL = "select * from CustInfo"

'Check if code is provided.
If Len(Trim$(Text1.Text)) > 0 Then
'Yes, include.
sSQL = sSQL & " where CustCode='" & Replace(Text1.Text, "'", "''") & "'"
End If

'Open dataset.
Set daors = dbCon.OpenRecordset(sSQL)

If Not daors.EOF Then
Do Until daors.EOF
List1.AddItem Format$(daors!CustId, "000") & " " & daors!CustCode & _
" " & daors!CustName


End If

End Sub

Used DAO 3.51, deleted MDB file, got the following:

Err.Number: 3075
Err.Description: Syntax error in string in query expression ''3D4|Clockworx''.

The error, as expected, occurs on the third INSERT


Used DAO 3.6, deleted MDB file, everything worked as expected.

Further notes:

When I create the MDB using 3.51 I can open it using DAO 3.6, but when I create it using DAO 3.6 and try to open it using DAO 3.51 I get: Unrecognized database format 'E:\AccessPipe\test01-v7.mdb'. Just an interesting point.

The pipe character: **Jet uses pipe symbols to delimit field or parameter names embedded in a literal string, such as: SELECT "|LastName|, |FirstName|" FROM Employees ** Source: https://support.microsoft.com/kb/178070

The solution provided, to replace "|" with "'" & chr(124) & "'" is veryparticular to the Access environment.

Saga
Hi,



just a note:

dao.createdatabase has an optional third parameter to specify the

databaseformat ex. dbVersion30 or dbVersion40. So it should be possible

to create a DB 3.5x Format with DAO 3.6.



Ulrich
Thanks Ulrich. I will keep this feature in mind should it become necessary.Yesterday when I was compiling the different code fragments to build the example that I posted I noticed that the process creates the DB in cases when it does not exist, so luckily I do not need to worry about compatibility.Again, Danke, Saga
 
Ad

Advertisements

S

Saga

Am 10.02.2014 20:58, schrieb Saga:
Thanks all for your help! Here is more info plus code.
Access MDB has been in operation since late 90s. The manager mentioned that most likely it was created using Access 97, but...
The code has provisions for creating the DB and the table in case it isnot found, so at this time it is being created by DAO 3.51. Querying the DAO dbcon.Version property in VB6 I found that 3.51 creates an MDB version 3..00.
I then changed the references to use DAO 3.6 and the code worked. AgainI queried DAO dbcon.Version property in VB6 and got version 4.0.
Since the app is monstrous (to say the least) I was not able to includethe code. In its place I created a small (tiny) VB6 applet that uses the same code, is integrated in a small code snippet and produces the exact error that the big is application is having.
I should make note that the single quotes are being handled correctly, as there are records whose [Name] field contain these and this has never been a problem. The only problematic rows are those that contain the pipe in this field.
Here is the code that I used for this test:
[New VB6 EXE project, added List1, Text1 and Command2]
Private Sub Command2_Click()
Dim sDBName As String
Dim sDBPath As String
Dim wrkDBEng As Workspace
Dim tdfNewTbl As TableDef
Dim dbCon As Database
Dim sSQL As String
Dim daors As DAO.Recordset

sDBName = "test01-v7.mdb"
sDBPath = App.Path

If Right$(sDBPath, 1) <> "\" Then
sDBPath = sDBPath & "\"
End If

'Create DB if not found.
If Len(Dir$(sDBPath & sDBName)) = 0 Then
'Not found, create
Set wrkDBEng = DBEngine.CreateWorkspace("ClientHold", "Admin", "")
Set dbCon = wrkDBEng.CreateDatabase(sDBPath & sDBName, dbLangGeneral)
Set dbCon = OpenDatabase(sDBPath & sDBName)
Set tdfNewTbl = dbCon.CreateTableDef("CustInfo")

With tdfNewTbl
.Fields.Append .CreateField("CustId", dbLong)
.Fields.Append .CreateField("CustName", dbText, 50)
.Fields.Append .CreateField("CustCode", dbText, 10)
End With

dbCon.TableDefs.Append tdfNewTbl

'Insert dummy data.
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values "& _
"(1,'Cozi Diner','COZD300')"
dbCon.Execute sSQL
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values "& _
"(2,'Mal''s Music','MALE122')"
dbCon.Execute sSQL
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values "& _

dbCon.Execute sSQL

'Found, open only.
Set dbCon = OpenDatabase(sDBPath & sDBName)
End If

'Data in table, query.
'Build query.
sSQL = "select * from CustInfo"

'Check if code is provided.
If Len(Trim$(Text1.Text)) > 0 Then
'Yes, include.
sSQL = sSQL & " where CustCode='" & Replace(Text1.Text, "'", "''") & "'"
End If

'Open dataset.
Set daors = dbCon.OpenRecordset(sSQL)

If Not daors.EOF Then
Do Until daors.EOF
List1.AddItem Format$(daors!CustId, "000") & " " & daors!CustCode & _
" " & daors!CustName


End If

End Sub

Used DAO 3.51, deleted MDB file, got the following:

Err.Number: 3075
Err.Description: Syntax error in string in query expression ''3D4|Clockworx''.

The error, as expected, occurs on the third INSERT


Used DAO 3.6, deleted MDB file, everything worked as expected.

Further notes:

When I create the MDB using 3.51 I can open it using DAO 3.6, but when I create it using DAO 3.6 and try to open it using DAO 3.51 I get: Unrecognized database format 'E:\AccessPipe\test01-v7.mdb'. Just an interesting point.

The pipe character: **Jet uses pipe symbols to delimit field or parameter names embedded in a literal string, such as: SELECT "|LastName|, |FirstName|" FROM Employees ** Source: https://support.microsoft.com/kb/178070

The solution provided, to replace "|" with "'" & chr(124) & "'" is veryparticular to the Access environment.

Saga
Hi,



just a note:

dao.createdatabase has an optional third parameter to specify the

databaseformat ex. dbVersion30 or dbVersion40. So it should be possible

to create a DB 3.5x Format with DAO 3.6.



Ulrich
Incredible! I had some problems with compatibility and your information regarding dbVersion30 solved it. Thanks again! Saga
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top