• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Quotation marks inside the inserting value

When inserting values to the database using VB code,is there any way that we can put quotation(') mark inside the inserting value.Ex. if we want to insert value like this ABC's Company limited,then it will give runtime error.That because normaly we use quotation marks to indicate the statement end or separate the statements.
0
lalithaw
Asked:
lalithaw
  • 4
  • 2
  • 2
  • +3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Yes, by duplicating it:

insert into yourtable values ( 'ABC''s' )

or, as your SQL is probably dynamically build:
strSQL = "insert into yourtable values ( '" &  replace(strCompany,"'", "''") & "' ) "

CHeers





0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Note:
the duplicate ' will only by used for the syntax, the database will only recieve a single ' for storage.

CHeers
0
 
inthedarkCommented:
Here's a tip; you get problems not only with strings but also with booleans detas, etc. each type of database could be different. So I created my class with all my handy tools. I called the class zADO so in a global module I just need Global ADO as New zADO and anywhere in code you can say:

SQL= "Select * From table1 where account='"+ADO.cSQL(AC$)+"'"


So here are some of the handy tools:

cSQLBoolean
cSQLDate
cSQLTime
cSQL (to handle strings)

Public Function cSQLBoolean(StringBoolean) As String

' Returns 1 or 0 for and SQL Update string
' Works with boolean or string values.

' examples:

'SQL = "Update Fred Set MyBool = " + ADO.cSQLBoolean(Request("MyCheckBox")) ' web page checkbox post
'SQL = "Update Fred Set MyBool = " + ADO.cSQLBoolean(RS("BooleanField")) ' Recordset
'SQL = "Update Fred Set MyBool = " + ADO.cSQLBoolean("Yes")) ' String ' Yes/No Oui/Non True/False
'SQL = "Update Fred Set MyBool = " + ADO.cSQLBoolean(True) ' Boolean values

If IsNull(StringBoolean) Then
    cSQLBoolean = "0"
    Exit Function
End If
If TypeOf StringBoolean Is CheckBox Then
    If StringBoolean.Value = 1 Then
        cSQLBoolean = "1"
    Else
        cSQLBoolean = "0"
    End If
Else
    ' Yes/No Oui/Non True/False
    If UCase$(Left(CStr(StringBoolean), 1)) = "T" Or UCase(Left(CStr(StringBoolean), 1)) = "Y" Or UCase(Left(CStr(StringBoolean), 1)) = "O" Then
        cSQLBoolean = "1"
    Else
        cSQLBoolean = "0"
    End If
End If
End Function

Function cSQLDate(DatePassed As Date)

' converts a string for use in an SQL

'example:
'strWhere = "MyDate = '" + ADO.cSQLDate(Now) + "'"

' Needs Dim mCurrentDateFormat as String ' in module decs.
If Len(mCurrentDateFormat) = 0 Then
    ' format of date depends on coutry of usage so this may need change
    mCurrentDateFormat = "YYYY-MM-DD HH:Nn:SS"
End If

cSQLDate = Format$(DatePassed, mCurrentDateFormat)

End Function

Public Function cSQLTime(TV As Date) As String

' Formats a time value for an SQL statement

cSQLTime = Format(TV, "HH:NN:SS")
End Function

Public Function cSQL(ByRef SQLData As String) As String

cSQL = Replace(SQLData, "'", "''")

End Function



 
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mdouganCommented:
I much prefer to use the ADO Command object with Parameter objects.  You put your string into the value of the parameter and append the parameter to the command object and then it doesn't matter what sort of embedded characters are in the string, ADO will handle it correctly.  Here is a sample:

Dim CN As ADODB.Connection
Dim CMD As ADODB.Command

Private Sub Command1_Click()
Dim sMsg As String
Dim i As Long
Dim Parm1 As ADODB.Parameter
Dim Parm2 As ADODB.Parameter

On Error GoTo ErrorRtn

Set Parm1 = New ADODB.Parameter
Parm1.Direction = adParamInput
Parm1.Type = adInteger
Parm1.Value = txtCompanyID.Text

Set Parm2 = New ADODB.Parameter
Parm2.Direction = adParamInput
Parm2.Type = adVarChar
Parm2.Size = 50
Parm2.Value = txtCompanyName.Text


Set CMD = New ADODB.Command
CMD.ActiveConnection = CN
CMD.CommandType = adCmdText
CMD.CommandText = "insert into mytable (company_id, company_name) values (?,?)"

CMD.Parameters.Append Parm1
CMD.Parameters.Append Parm2

CMD.Execute lRecCount

MsgBox lRecCount & " Records affected"

ExitRtn:
    Set Parm1 = Nothing
    Set Parm2 = Nothing
    Exit Sub

ErrorRtn:
   
    If CN.Errors.Count > 0 Then
        For i = 0 To CN.Errors.Count - 1
            sMsg = sMsg & CN.Errors(i).Number & " - " & CN.Errors(i).Description & " - " & CN.Errors(i).NativeError & vbCrLf
        Next i
        MsgBox sMsg
    Else
        MsgBox Err.Description
    End If
    GoTo ExitRtn
End Sub


Private Sub Form_Load()

Set CN = New ADODB.Connection
CN.CursorLocation = adUseClient
' happens to be SQL Server, could be any database
CN.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;DRIVER=SQL Server;SERVER=MyServer;DATABASE=MyDB;Trusted_Connection=Yes;Initial Catalog=MyDB"
CN.Open
If Not CN.State = adStateOpen Then
    MsgBox "Connection Failed"
    Exit Sub
End If

End Sub

Private Sub Form_Unload(Cancel As Integer)

Set CMD = Nothing

CN.Close
Set CN = Nothing


End Sub





0
 
trkcorpCommented:
mdougan,  That is nice to know.
0
 
johnny6Commented:
lalithaw:
           Here is function that I use to insert values with quotations in a database:

Public Function IgnoreApostrophe(text As String) As String
    Dim Asymbol, temp As String
    Dim I As Integer
    temp = ""
    For I = 1 To Len(text)
    Asymbol = Mid(text, I, 1)
    If Asymbol = Chr(34) Or Asymbol = "'" Then Asymbol = Asymbol & Asymbol
    temp = temp & Asymbol
    Next I
    IgnoreApostrophe = temp
    'In your SQL code:
    'strSQL = "INSERT INTO .... VALUES( '" & IgnoreApostrophe(Field1) & "')"
    'Your data will end up in the database as "William O'Neil" just like you want it. No conversion is required when retrieving data.
End Function




John
0
 
lalithawAuthor Commented:

Thanks John.I appreciate ur help.Thank  you very much
0
 
lalithawAuthor Commented:

Thanks John.I appreciate ur help.Thank  you very much
0
 
mdouganCommented:
trkcorp, yea, it works really well.  It's all I use now.
0
 
johnny6Commented:
lalithaw:
         Since my function does exactly what you asked, why not give me an "A" grade instead of a "B" grade?  An "A" grade does not cost you any more points than a "B" grade.



Regards,

John
0
 
lalithawAuthor Commented:
John.,

Sorry john I didnt know it.Very sorry about that.

Lalitha
0
 
lalithawAuthor Commented:

Hi Angellll/mdougan.,

I appreciate ur help also.I tested ur solutions.It also work.Thanks very much.

Lalitha.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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