Solved

Quotation marks inside the  inserting value

Posted on 2002-05-21
12
263 Views
Last Modified: 2010-05-02
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
Comment
Question by:lalithaw
  • 4
  • 2
  • 2
  • +3
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7023744
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
 
LVL 142

Expert Comment

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

CHeers
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7023776
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
 
LVL 18

Expert Comment

by:mdougan
ID: 7024063
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
 
LVL 4

Expert Comment

by:trkcorp
ID: 7024296
mdougan,  That is nice to know.
0
 
LVL 2

Accepted Solution

by:
johnny6 earned 75 total points
ID: 7025074
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:lalithaw
ID: 7026546

Thanks John.I appreciate ur help.Thank  you very much
0
 
LVL 1

Author Comment

by:lalithaw
ID: 7026557

Thanks John.I appreciate ur help.Thank  you very much
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7026733
trkcorp, yea, it works really well.  It's all I use now.
0
 
LVL 2

Expert Comment

by:johnny6
ID: 7026962
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
 
LVL 1

Author Comment

by:lalithaw
ID: 7028735
John.,

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

Lalitha
0
 
LVL 1

Author Comment

by:lalithaw
ID: 7028754

Hi Angellll/mdougan.,

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

Lalitha.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now