VB 6.0 - unclosed quotation mark after tthe character string ".

Asked By madhavi
09-Feb-12 08:24 PM

Hi,

Iam getting this error:

run time error '-2147217900(80040e14)' unclosed quotation mark after tthe character string ".

my code is below..
please help me its very urgent


Option Explicit
Private sdate As Date
Private sql$
   
Public Sub Run()
    sdate = Now
    cvfrm.PrgBar.Value = 0
    cvfrm.PrgBar.Min = 0
    cvfrm.PrgBar.Visible = False
    cvfrm.lbl_3.Caption = ""

    cvfrm.lbl_1.Caption = "Please wait, Transfer Unitholder file in progress..."
    cvfrm.lbl_2.Caption = "Start at:" & sdate
    DoEvents

    ' clear record in UH database
    Call ClearRecord
   
    cvfrm.lbl_1.Caption = "Transfer Unitholder file completed."
    cvfrm.lbl_2.Caption = "Start at:" & sdate & Space(4) & "Ended at: " & Now
    DoEvents

End Sub

Private Sub ClearRecord()
    Dim rs As New ADODB.Recordset
    sql = "delete from uuhm"
    cnCMT.Execute (sql)
   
    sql = " SELECT UUHMID, UUHMCusNo, UUHMIDTyp, UUHMUHTyp, UUHMCSts, UUHMUHSts, "
    sql = sql & " UUHMOID, UUHMUNm, UUHMTitle, UUHMDOB, UUHMSex, UUHMNat, "
    sql = sql & " UUHMRace, UUHMRelg, UUHMOccp, UUHMIndus, UUHMUMTyp, UUHMUHCatg, "
    sql = sql & " UUHMUSTyp, UUHMTelO, UUHMTelH, UUHMTelHP, UUHMFaxNo,"
    sql = sql & " UUHMEMail, UUHMCStm, UUHMPer1Nm, UUHMPer1Ds, UUHMPer1TelO, "
    sql = sql & " UUHMPer1TelHP, UUHMPer1EMail, UUHMPer2Nm, UUHMPer2Ds,"
    sql = sql & " UUHMPer2TelO, UUHMPer2TelHP, UUHMPer2EMail, UUHMEPFNo, "
    sql = sql & " UUHMEPFIn, UUHMEPFBr, UUHMEPFInd, UUHMZakat, UUHMEdu, UUHMInc,"
    sql = sql & " UUHMLgge, UUHMPasswd, UUHMStf, UUHMStaff, UUHMMStats, UUHMSInc, "
    sql = sql & " UUHMMMaidenNm, UUHMQuesCd, UUHMAnswer, UUHMSMSNm,"
    sql = sql & " UUHMSCCat, UUHMUsr, UUHMSyDt, UUHMUsrU, UUHMSyDtU, UUHMSts, "
    sql = sql & " UUHMEpfSts, UUHMATyp, UUHMENm, UUHMEBN, UUHMEA1, UUHMEA2,"
    sql = sql & " UUHMEA3, UUHMEA4, UUHMPIN, UUHMPINDT, UUHMLNO, UUHMPOB, UUHMPWD, "
    sql = sql & " UUHMURL, UUHMPRS, UUHMESolFlg, UUHMPBESolFlg,"
    sql = sql & " UUHMEPFPDt from uuhm"
    Set rs = cnCOM.Execute(sql)
   
    While Not rs.EOF
     
      sql = "INSERT INTO UUHM (UUHMID, UUHMCusNo, UUHMIDTyp, UUHMUHTyp, UUHMCSts, UUHMUHSts, "
      sql = sql & " UUHMOID, UUHMUNm, UUHMTitle, UUHMDOB, UUHMSex, UUHMNat, "
      sql = sql & " UUHMRace, UUHMRelg, UUHMOccp, UUHMIndus, UUHMUMTyp, UUHMUHCatg, "
      sql = sql & " UUHMUSTyp, UUHMTelO, UUHMTelH, UUHMTelHP, UUHMFaxNo,"
      sql = sql & " UUHMEMail, UUHMCStm, UUHMPer1Nm, UUHMPer1Ds, UUHMPer1TelO, "
      sql = sql & " UUHMPer1TelHP, UUHMPer1EMail, UUHMPer2Nm, UUHMPer2Ds,"
      sql = sql & " UUHMPer2TelO, UUHMPer2TelHP, UUHMPer2EMail, UUHMEPFNo, "
      sql = sql & " UUHMEPFIn, UUHMEPFBr, UUHMEPFInd, UUHMZakat, UUHMEdu, UUHMInc,"
      sql = sql & " UUHMLgge, UUHMPasswd, UUHMStf, UUHMStaff, UUHMMStats, UUHMSInc, "
      sql = sql & " UUHMMMaidenNm, UUHMQuesCd, UUHMAnswer, UUHMSMSNm,"
      sql = sql & " UUHMSCCat, UUHMUsr, UUHMSyDt, UUHMUsrU, UUHMSyDtU, UUHMSts, "
      sql = sql & " UUHMEpfSts, UUHMATyp, UUHMENm, UUHMEBN, UUHMEA1, UUHMEA2,"
      sql = sql & " UUHMEA3, UUHMEA4, UUHMPIN, UUHMPINDT, UUHMLNO, UUHMPOB, UUHMPWD, "
      sql = sql & " UUHMURL, UUHMPRS, UUHMESolFlg, UUHMPBESolFlg,"
      sql = sql & " UUHMEPFPDt)"
      sql = sql & " VALUES ('" & Trim(rs!UUHMID) & "', '" & Trim(rs!UUHMCusNo) & "', '" & rs!UUHMIDTyp & "', "
      sql = sql & " '" & Trim(rs!UUHMUHTyp) & "', '" & Trim(rs!UUHMCSts) & "', '" & Trim(rs!UUHMUHSts) & "', "
      sql = sql & " '" & Trim(rs!UUHMOID) & "', '" & Trim(rs!UUHMUNm) & "', '" & Trim(rs!UUHMTitle) & "', "
      sql = sql & " '" & Format(Trim(rs!UUHMDOB), "yyyy/mm/dd") & "', '" & Trim(rs!UUHMSex) & "', '" & Trim(rs!UUHMNat) & "', "
      sql = sql & " '" & Trim(rs!UUHMRace) & "', '" & Trim(rs!UUHMRelg) & "', '" & Trim(rs!UUHMOccp) & "', '" & Trim(rs!UUHMIndus) & "', "
      sql = sql & " '" & Trim(rs!UUHMUMTyp) & "', '" & Trim(rs!UUHMUHCatg) & "', "
      sql = sql & " '" & Trim(rs!UUHMUSTyp) & "', '" & Trim(rs!UUHMTelO) & "', '" & Trim(rs!UUHMTelH) & "', '" & Trim(rs!UUHMTelHP) & "', "
      sql = sql & " '" & Trim(rs!UUHMFaxNo) & "',"
      sql = sql & " '" & Trim(rs!UUHMEMail) & "', '" & Trim(rs!UUHMCStm) & "', '" & Replace(Trim(rs!UUHMPer1Nm), "'", "`") & "', "
      sql = sql & " '" & Trim(rs!UUHMPer1Ds) & "', '" & Trim(rs!UUHMPer1TelO) & "', "
      sql = sql & " '" & Trim(rs!UUHMPer1TelHP) & "', '" & Trim(rs!UUHMPer1EMail) & "', '" & Trim(rs!UUHMPer2Nm) & "',"
      sql = sql & " '" & Trim(rs!UUHMPer2Ds) & "',"
      sql = sql & " '" & Trim(rs!UUHMPer2TelO) & "', '" & Trim(rs!UUHMPer2TelHP) & "', '" & Trim(rs!UUHMPer2EMail) & "',"
      sql = sql & " '" & Trim(rs!UUHMEPFNo) & "', "
      sql = sql & " '" & Trim(rs!UUHMEPFIn) & "', '" & Trim(rs!UUHMEPFBr) & "', '" & Trim(rs!UUHMEPFInd) & "', "
      sql = sql & " '" & Trim(rs!UUHMZakat) & "', '" & Trim(rs!UUHMEdu) & "', '" & Trim(rs!UUHMInc) & "',"
      sql = sql & " '" & Trim(rs!UUHMLgge) & "', '" & Trim(rs!UUHMPasswd) & "', '" & Trim(rs!UUHMStf) & "', "
      sql = sql & " '" & Trim(rs!UUHMStaff) & "', '" & Trim(rs!UUHMMStats) & "', '" & Trim(rs!UUHMSInc) & "', "
      sql = sql & " '" & Trim(rs!UUHMMMaidenNm) & "', '" & Trim(rs!UUHMQuesCd) & "', '" & Trim(rs!UUHMAnswer) & "', '" & Trim(rs!UUHMSMSNm) & "',"
      sql = sql & " '" & Trim(rs!UUHMSCCat) & "', '" & Trim(rs!UUHMUsr) & "', '" & Format(Trim(rs!UUHMSyDt), "yyyy/mm/dd") & "', '" & Trim(rs!UUHMUsrU) & "', "
      sql = sql & " '" & Format(Trim(rs!UUHMSyDtU), "yyyy/mm/dd") & "', '" & Trim(rs!UUHMSts) & "', "
      sql = sql & " '" & Trim(rs!UUHMEpfSts) & "', '" & Trim(rs!UUHMATyp) & "', '" & Trim(rs!UUHMENm) & "', '" & Trim(rs!UUHMEBN) & "',"
      sql = sql & " '" & Trim(rs!UUHMEA1) & "', '" & Trim(rs!UUHMEA2) & "',"
      sql = sql & " '" & Trim(rs!UUHMEA3) & "', '" & Trim(rs!UUHMEA4) & "', '" & Trim(rs!UUHMPIN) & "', '" & Trim(rs!UUHMPINDT) & "', "
      sql = sql & " '" & Trim(rs!UUHMLNO) & "', '" & Trim(rs!UUHMPOB) & "', '" & Trim(rs!UUHMPWD) & "', "
      sql = sql & " '" & Trim(rs!UUHMURL) & "', '" & Trim(rs!UUHMPRS) & "', '" & Trim(rs!UUHMESolFlg) & "', '" & Trim(rs!UUHMPBESolFlg) & "',"
      sql = sql & " '" & Format(Trim(rs!UUHMEPFPDt), "yyyy/mm/dd") & "')"
      cnCMT.Execute (sql)
      rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing

End Sub
 

 

  Danasegarane Arunachalam replied to madhavi
09-Feb-12 11:52 PM
This means that the query is not properly ended. You are missing some "" chars in the query. Check the query


Do one thing

Use the

Messagebox.show(sql)

in your application and see the exact query which is executed . Then run the query in the sql and correct the error
  Venkat K replied to madhavi
10-Feb-12 12:47 AM
It is very difficult to debug this long query through you c# rather copy the string inside the SQL in debug mode and then try to execute the query through SSMS, you can easily trace the error.

Thanks
  Sandeep Mittal replied to madhavi
11-Feb-12 01:55 AM
your query seems to be fine.
I think there could be single quote in the your recordset values that would cause issue. Please check.
If this is the case that handle this by using replace function. Replace single quote "'" with 2 single quotes "''"
Create New Account
help
the vaue of an autonumber (identity) column after adding and updating a record in ADO-VB? C++ / VB I am trying to retrieve the value of the autonumber field after adding and updating is what I use (ID is the identity field of the table): Set rstE = New ADODB.Recordset Set rstE.ActiveConnection = DB1 rstE.CursorLocation = adUseServer rstE.Open "SELECT ID, name FROM tblEmployee such a basic and important functionality doesn't work in SQL Server. Please help. Oscar VB DataBase ADO Discussions ADODB (1) SQL Server (1) ID (1) AdOpenKeyset (1) ActiveConnection (1) AdLockOptimistic (1) CursorLocation (1) SCOPE_IDENTITY rstE.Requery to update the recordset. You may also need to precede it with a DoEvents to allow some time for the db to actually update. and / Henning Use the adOpenKeyset the, vaue, of, an, autonumber, (identity), column, after, adding, and, updating, a, record, in, ADO-VB? description: I am trying to retrieve the value of the autonumber field after adding and
werden kann, ist mir leider im Moment noch nicht gelungen. Vielen Dank im voraus. - - Andreas VB DataBase - German Discussions ADODB.Connection (1) ADODB.Command (1) ADODB.EventStatusEnum (1) ADODB.Recordset (1) ADODB.Error (1) ADODB.Field (1) Oracle (1) Console.WriteLine (1) Salut Andreas, Schick doch mal das PL / SQL hier einen Alert aufzufangen. In Internet waren einige Beispiele die sich aber nicht einfach auf VB übertragen ließen. Das Ziel der Übung ist, dass mein Programm auf den INSERT eines anderen
VB 6.0 need some help on figuring out an annoying issue At my company we have an existing VB 6.0 project that used Monarch Replort Explorer for creating invoices. Discovered that Monarch will database for a server name every time we send a message. Dim cmd As New ADODB.Command Dim Con As New ADODB.Connection Con.ConnectionString = DE.cnWITEDI.ConnectionString Dim param As New ADODB.Parameter Con.Open param.Name = "@SMTPServer" param.Direction = adParamOutput param.Type = adChar param.Size = 200 WILL RESULT IN ALOT OF PHONE / EMAIL ETC '* ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** Private Sub GeneratePDFInvoices() Dim rs As New ADODB.Recordset, rsDetail As New ADODB.Recordset, rsControl As New ADODB.Recordset, rsSAC As New ADODB.Recordset Dim cntRecords As Integer, cntPage As Integer, iPage As
ADODB / Class / Events .NET Framework Hallo zusammen, bin gerade etwas gestrandet. Versuche den Fortschritt einer Abfrage Initial Catalog = xxxxxx;Data Source = xxxxxxx" Private Const strDefaultSQL = "SELECT * FROM test" Public cn As ADODB.Connection Public WithEvents rs As ADODB.Recordset Public Function refresh() Dim strSQL As String Set cn = New ADODB.Connection cn.Open strConn Set rs = New ADODB.Recordset With rs .CursorLocation = adUseClient .Properties("Initial Fetch Size") = 2 .Properties("Background Fetch Size") = 4 End Function Public Sub rs_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset) Debug.Print Progress Form1.Label1.Caption = Progress DoEvents End Sub Hat jemand eine Idee. Ado-Version ist eine Reference auf 2.8 Danke
save complete html page as i.e. do page.hm and pge_files 'dir' with webbrowserControl vb.net without displayng any dialog box as we did with in vb WebBrowser1. ExecWB OLECMDID_SAVEAS, OLECMDEXECOPT_DODEFAULT thanks for any help. ) You can directly save a web page 2) You can save complete web page s as a .mht file using CDO and ADODB components. First Add References to the “Microsoft CDO For Windows 2000 Library” and “Microsoft ActiveX Data Objects 2.5 Library”. Project menu -> Add Reference. . . -> COM tab Imports ADODB Imports CDO Public Class Form1 Private Sub Button1_Click( ByVal sender As System.Object, _ ByVal CDO.Message = New CDO.Message iMessage.CreateMHTMLBody(Url, _ CDO.CdoMHTMLFlags.cdoSuppressNone, "" , "" ) Dim adodbstream As ADODB.Stream = New ADODB.Stream adodbstream.Type = ADODB.StreamTypeEnum.adTypeText adodbstream.Charset = "US-ASCII" adodbstream.Open() iMessage.DataSource.SaveToObject(adodbstream, "_Stream" ) adodbstream.SaveToFile(FilePath, _ ADODB.SaveOptionsEnum.adSaveCreateOverWrite) End Sub End Class Related thread: http: / / social.msdn.microsoft.com / forums / en