Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 991
  • Last Modified:

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string in visual basic

Hi,

I am using the following function to insert a short string into a text field on a sql server 2000 database

Public Function ExecuteSql(sql As String, Src As ADODB.Connection) As String

Dim AdoCommand As ADODB.Command
Set AdoCommand = New ADODB.Command
Dim Attempts As Double

On Error GoTo err:

Attempts = 0

'OpenConn SQL, False, AdoCommand
'AdoCommand.CursorLocation = adUseClient

AdoCommand.CommandTimeout = 0
AdoCommand.ActiveConnection = Src
AdoCommand.CommandText = sql
AdoCommand.Execute

Set AdoCommand = Nothing

ExecuteSql = "SUCCESS"

Set AdoCommand = Nothing

Exit Function

Usually the string works fine, however, quite frequently I receive the error message

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string

If I paste the statement into Query Analyser then it runs fine, which is frustrating.  There is clearly no unlcosed quotation mark!  There is no consistency to strings that fail.  Has anyone seen this before, any ideas on how to fix it?






0
kinton
Asked:
kinton
  • 4
  • 2
1 Solution
 
kintonAuthor Commented:
ps  I am using VB6
0
 
leclairmCommented:
Hmmmm.

Do you have any examples of the Sql statement that generated this error??

Maybe try including the command type:

...
AdoCommand.ActiveConnection = Src
AdoCommand.CommandText = sql
AdoCommand.CommandType = adCmdText
...
0
 
kintonAuthor Commented:
unfortuently the command text option did not work..

Here is the whole error message..

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string '202452~|~0999911880~|~DRAGON NEWS PACKAGE~|~DRAGON NEWS PACKAGE~|~28/Jan/2003 06:45:12~|~01~|~01.99~|~01.99.099~|~01.99.099.09~|~3133~|~Null~|~Null~|~Null~|~Null~|~Null~|~Null~|~0901'.

and here is the SQL string

 declare @RepRow1 as varchar(8000)  declare @RepRow2 as varchar(8000) declare @RepRow3 as varchar(8000) declare @RepRow4 as varchar(8000)   set @RepRow1 = '202452~|~0999911880~|~DRAGON NEWS PACKAGE~|~DRAGON NEWS PACKAGE~|~28/Jan/2003 06:45:12~|~01~|~01.99~|~01.99.099~|~01.99.099.09~|~3133~|~Null~|~Null~|~Null~|~Null~|~Null~|~Null~|~0901  ~|~Null~|~Null~|~Null~|~S~|~Null~|~'
 set @RepRow2 = ''
 set @RepRow3 = ''
 set @RepRow4 = ''
   insert into tblofflineReplicationRows (TableName, PrimaryKeyValue, PrimaryKeyName, DateTimeAvailable, SpiritDateStamp, ReplicationRow,  EducationNumber, OfflineUserID, AuditID,  SourceID) select 'tblProducts' , '202452' , 'ISBNId' , '08/Jun/2006 15:01:34' , '28/Jan/2003 06:45:12' ,   + @RepRow1 + @RepRow2  + @RepRow3 + @RepRow4  + '_EOR_' , 0, 0 , 21372312 , 3

thank you!


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
kintonAuthor Commented:
ps, paste that into query analyser and it runs fine!
0
 
Anthony PerkinsCommented:
You need to escape all single quotes.  This is done by replacing all single quotes with two single quotes. So change:
AdoCommand.CommandText = sql

To:
AdoCommand.CommandText = Replace(sql, "'", "''")

0
 
kintonAuthor Commented:
Surely that will end up putting a single quote into the database row?
0
 
Anthony PerkinsCommented:
>>Surely that will end up putting a single quote into the database row?<<
Of course.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now