Error: Unclosed quotation mark after the character string..

[Aisya]

Hye,

I need help to overcome the problem mention above.

My application needs user to key in their details, but when it comes to name some names has an apostrophe, somehow it's okay to save the data with apostrophe because through my observation, the system read from the database not the one that was keyed in by the user. For example, in the database already has a name "Alex" ... The one that just keyed in (edit) by the user " Alex'is" ... 1st edit, save and its okay, because it read "Alex" .. when you try to edit 2nd time, system read "Alex'is" ... and there pop up the error msg.

Type of error : Error #_2147217900 was generated by Microsoft OLE DB Provider fpr ODBC Driver [Microsoft][ODBC SQL Server Driver][SQL Server] (Unclosed quotation mark after the character string 'ABC1'")

 
Private Sub CmdSave_Click()
    If fnintCheckFields = 1 Then Exit Sub
 
    If Trim(cboRelationship.Text) = Op_Spouse Then
        If gstrGender = Op_Female Then
            cboGender.Text = Op_Male
        Else
            cboGender.Text = Op_Female
        End If
    End If
    If Trim(cboRelationship.Text) = Op_Father Or Trim(cboRelationship.Text) = Op_Brother Then
        cboGender.Text = Op_Male
    End If
    If Trim(cboRelationship.Text) = Op_Mother Or Trim(cboRelationship.Text) = Op_Sister Then
        cboGender.Text = Op_Female
    End If
 
    Select Case strRecMode
        Case "ADD"
            On Error GoTo ParaAdd1:
 
            Set CurrRs = VSHc.Open_HcFamily(CnnLoc, 3, 1, gintComp_Cd, gstrEmp_no, fnstrRelationship(cboRelationship.Text), fnstrAppendSingleQuote(Trim(txtName)))
            If CurrRs.RecordCount > 0 Then
                Call DispError(Msg_Exist)
                txtRelationship.SetFocus
                Exit Sub
            End If
 
            CurrRs.AddNew
            Call MoveScreenToDb
            CurrRs.Update
 
            Call InitScreenFds
            Call FieldEnable
            txtRelationship.SetFocus
 
        Case "UPT"
            On Error GoTo ParaUpt1:
 
            intCfmMsg = DispMsg(Msg_Save)
            If intCfmMsg <> vbYes Then
                Call MoveDbToScreen
                Call FormatFields
            Else
                Set CurrRs = VSHc.Open_HcFamily(CnnLoc, 3, 1, gintComp_Cd, gstrEmp_no, fnstrRelationship(cboRelationship.Text), fnstrAppendSingleQuote(Trim(txtName)))
                If CurrRs.RecordCount > 0 And UCase(Trim(txtName)) <> UCase(Trim(txtName1)) Then
                    Call DispError(Msg_Exist)
                    txtName.SetFocus
                    Exit Sub
                End If
                CurrRs.Close
 
                '
                Set CurrRs = VSHc.Open_HcFamily(CnnLoc, 3, 1, gintComp_Cd, gstrEmp_no, fnstrRelationship(cboRelationship.Text), fnstrAppendSingleQuote(Trim(txtName1)))
                'Set CurrRs = VSHc.Open_HcFamily(CnnLoc, 3, 1, gintComp_Cd, gstrEmp_no, fnstrRelationship(cboRelationship.Text), fnstrAppendSingleQuote(Trim(txtName)))
 
                If CurrRs.RecordCount > 0 Then
                    Call MoveScreenToDb
                    CurrRs.Update
                    Call UpdRelvNames
                End If
            End If
            txtName1 = ""
            txtName.Enabled = True
            txtRelationship.Enabled = True
            cboRelationship.Enabled = True
            Call Rtn_Enable(1)
            txtEmp_No1.Enabled = True
            cmdFindEmp.Enabled = True
            CmdFind.SetFocus
    End Select
 
    Exit Sub
 
ParaAdd1:
    Me.MousePointer = 0
    If Str(Err.Number) = "-2147217900" Then
        Call DispError(Msg_Exist)
        txtRelationship.SetFocus
    Else
        strErrMsg = "Error # " & Str(Err.Number) & " was generated by " _
            & Err.Source & Chr(13) & Err.Description
        MsgBox strErrMsg, vbCritical, "Error"
    End If
 
    Set CurrRs = VSHc.Open_HcFamily(CnnLoc, 2, 1, gintComp_Cd, gstrEmp_no)
    Exit Sub
 
ParaUpt1:
    strErrMsg = "Error # " & Str(Err.Number) & " was generated by " _
            & Err.Source & Chr(13) & Err.Description
    Me.MousePointer = 0
    MsgBox strErrMsg, vbCritical, "Error"
 
End Sub
 
 

So, how would i solve this problem??

Thank you.

[VijaySofist]

Hi,

In the Coding, you have to replace Single Quotes with Double Single Quotes for that Sring and the resulting string only, should be stored in the DB

Example

strResult=Replace(txtName.Text, "'","''")

Regards
Vijay.R

[Aisya]

thanks for the response...

I have tried the code but the error still pop up. Where should I put these line?? Since I have tried to put it inside the code below;

Private Function fnintGetRecord(ByVal strRelation As String, ByVal strName As String) As Integer
    fnintGetRecord = 1
 
    Set CurrRs = VSHc.Open_HcFamily(CnnLoc, 3, 2, gintComp_Cd, gstrEmp_no, strRelation, Trim(strName))
    strName = Replace(txtName.Text, "'", "''")
    If CurrRs.RecordCount > 0 Then
        fnintGetRecord = 0   '   Selected Record Was Found
    Else
        Call DispError(Msg_RecNotFound, "Employee's Family Details Record", 0)
    End If
End Function
 

...and yet the error still pop up.

I replace the srtResult with strName since the system use strName to refer to the txtName. Am I on the right track?? :\

Should I put it somewhere in the previous code or in the code above??

p/s : i'm still new in VB6. Do help, thank you.

[Aisya]

I think I already found the right place to put the Replace statement...

Private Sub MoveScreenToDb()
 
    CurrRs("NAME") = Replace(txtName.Text, "'", "''")
 

The error already gone but the problem now is the one that been registered to the db is "''" .. So when you want to edit the data for example, the name will appear with double quotes. ex, Alex''is

I tried to use the same method to the MoveDbToScreen which is display record from the database...but when I do that, the new edit doesn't record in the database anymore. Why is that? Is there anything that i missed?