Solved

Quotation marks inside the  inserting value

Posted on 2002-05-21
12
268 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +3
12 Comments
 
LVL 143

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 143

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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
 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

726 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