Solved

Quotation marks inside the  inserting value

Posted on 2002-05-21
12
266 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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

825 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