kinton
asked on
[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
AdoCommand.CommandTimeout = 0
AdoCommand.ActiveConnectio
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?
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
AdoCommand.CommandTimeout = 0
AdoCommand.ActiveConnectio
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?
Hmmmm.
Do you have any examples of the Sql statement that generated this error??
Maybe try including the command type:
...
AdoCommand.ActiveConnectio
AdoCommand.CommandText = sql
AdoCommand.CommandType = adCmdText
...
Do you have any examples of the Sql statement that generated this error??
Maybe try including the command type:
...
AdoCommand.ActiveConnectio
AdoCommand.CommandText = sql
AdoCommand.CommandType = adCmdText
...
ASKER
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~|~DRA
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~|~DRA
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!
Here is the whole error message..
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string '202452~|~0999911880~|~DRA
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~|~DRA
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!
ASKER
ps, paste that into query analyser and it runs fine!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Surely that will end up putting a single quote into the database row?
>>Surely that will end up putting a single quote into the database row?<<
Of course.
Of course.
ASKER