Solved

Quotation marks inside the  inserting value

Posted on 2002-05-21
12
262 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

705 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

12 Experts available now in Live!

Get 1:1 Help Now