Please help with "'"

Please help with "'"

Post by Sapna S. Sharm » Tue, 12 Jan 1999 04:00:00



hello all,

I have a stored Proc. in SQL server 6.5 and I call this thru
VB6 using ADO.
I have a lendername = "First Nat'l Bank"

I change the value of LenderName :
  If InStr(strLenderName, "'") > 0 Then
    strLenderName = Replace(strLenderName, "'", "''")
  End If

now  lendername = "First Nat''l Bank"

Now when i search my name  thru sql string this works fine..

      s = "Select * from LenderAlternateName Where MDSID =
'" & strMDSID & "' AND AlternateLenderName = '" &
strLenderName & "'"
      adoRs.Open s, gsConnectString, adOpenStatic,
adLockReadOnly

1) I want to insert this name in LenderAlternateName
table.    (not working)
        But when I pass strLendername which is now =  "First
Nat''l Bank", it add name as First Nat''l Bank instead of
First Nat'l Bank

2)  look for this name in other table and update more
column. if it finds this name ( not working)
        fails to locate the records in the table and returns
back with no error.

VB CODE:
1)
  Set ocmd = CreateObject("adodb.command")
    With ocmd
      .ActiveConnection = gsConnectString
      .CommandType = adCmdStoredProc
      .CommandTimeout = 180
     .CommandText = "Maint_AlternateNameTable"

adParamInput, Len(strMDSID), strMDSID)
     .Parameters.Append oparam

adVarChar, adParamInput, Len(strLenderName), strLenderName)
     .Parameters.Append oparam
     .Execute
  End With
Exit Function

STORED PROCEDURE:
Create Procedure Maint_AlternateNameTable


    INSERT INTO LenderAlternateName (AlternateLenderName,
MDSID )

2)
  Set ocmd = CreateObject("adodb.command")
    With ocmd
      .ActiveConnection = gsConnectString
      .CommandType = adCmdStoredProc
      .CommandTimeout = 180
     .CommandText = "Maint_MasterTable"

adParamInput, Len(strMDSID), strMDSID)
     .Parameters.Append oparam

adVarChar, adParamInput, Len(strLenderName), strLenderName)
     .Parameters.Append oparam
     .Execute
  End With
Exit Function

STORED PROCEDURE:
Create Procedure Maint_Master


    UPDATE Master


please if somebody can tell me whats going on here. I am
stuck with this.
I will also appreciate if you can reply to

Thanks in advance.

 
 
 

Please help with "'"

Post by Sapna S. Sharm » Tue, 12 Jan 1999 04:00:00


hello,
I got the answer ...here it is

If you are using a string to open a recordset then you have to
pass the value with 2 aphotophes.
so for First Nat'l Bank
should be First Nat''l Bank

But if you are calling the stored procedure which requires the
parameters you have to pass First Nat'l Bank with only single
code.
Thanks