当两个字段包含逗号时，如何使用ADO将多个字段插入表中 [英] How to insert several fields into a table using ADO when two of the fields contain commas

问题描述

我在Access 2010中创建了一个ado记录集，它从sql server 2008 r2上的存储过程返回9个不同的字段.

I have an ado created recordset in access 2010 it returns 9 different fields from a stored procedure on sql server 2008 r2.

我尝试使用此记录集(确实填充)将所有记录插入与输出匹配的表中.我的问题是其中两个字段是其中包含逗号的名称字段.例如，史密斯(Smith)，约瑟夫(Joseph)-我需要将该逗号插入相应的字段中.现在，由于字段中的逗号，它引发了一个错误.

I am trying to use this recordset (which does populate) to insert all of the records into a table that matches the output. My issue is that two of the fields are name fields that have commas in them. For example Smith, Joseph-- I need to insert that comma into the appropriate field. Right now it throws an error because of the comma in the field.

这是我正在使用的代码:

Here is the code that I am using:

Option Compare Database

'Executes the filtering routine
Private Sub cmdApplyFilter_Click()

'If txtStartDate.Value And txtEndDate.Value Is Not Null Then
'    QuickFilter
'Else
'    DefaultRun
'End If
QuickFilter
'********** Filter as you type **********

'Private Sub txtFilter_Change()
'   QuickFilter
'End Sub


End Sub

'Perform the actual filtering on the subform
Private Sub QuickFilter()

Dim Sql As String
Dim filter As String

If txtStartDate = vbNullString Then
    'Reset the filter if the textbox is empty
    'This will be the default sql statement to fill the subreport
    SubForm.Form.FilterOn = False
Else
    'Some common substitutions that users may have already inserted as wildchars
    filter = Replace(txtStartDate, "%", "*")
    filter = Replace("*" & filter & "*", "**", "*")
    'Construct the filter for the sql statement
    '/*********** GROUP BY GOES HERE ***********/

    'Assign the filter to the subform
    'SubForm.Form.filter = Sql
    'SubFomr.Form.FilterOn = True
End If
End Sub


Private Sub Form_Load()
   'Sets up the connection with the sql server database retrieves the stored procedure,       executes it and puts the result set into a table
   Dim Conn As ADODB.Connection
   Dim Cmd As ADODB.Command
   Dim Rs As ADODB.Recordset
   Dim rs1 As ADODB.Recordset
   Dim Connect As String
   Dim filter As String


   Connect = "Provider =SQLNCLI10; Data Source=10.50.50.140; Initial Catalog=CCVG; User Id = oe; Password = Orth03c0; "

   'Establish the connection with sql server
   Set Conn = New ADODB.Connection
   Conn.ConnectionString = Connect
  Conn.Open

   'Open the recorset
   Set Cmd = New ADODB.Command
   Cmd.ActiveConnection = Conn
   Cmd.CommandText = "dbo.cusGenNoNotesReport"
   Cmd.CommandType = adCmdStoredProc

   Set Rs = Cmd.Execute()








   Dim x As Integer

   If Not Rs.BOF And Not Rs.EOF Then
    If Not Rs.BOF Then Rs.MoveFirst
    Do Until Rs.EOF
        For x = 0 To Rs.Fields.Count - 1
            MsgBox Rs.Fields(x)
            'DoCmd.RunSQL "INSERT INTO tblNoNotes (Provider, Facility, TicketNumber,       Charges, FinancialClass, CPT, CPTDescription, PatientFullName, DateOfEntry) SELECT " & Rs.Fields(x).Value & ""
        Next x
            Rs.MoveNext
    Loop
End If


   'Process results from recordset, then close it.
   'DoCmd.RunSQL "INSERT INTO tblNoNotes (Provider, Facility, TicketNumber, Charges,     FinancialClass, CPT, CPTDescription, PatientFullName, DateOfEntry) VALUES (""" & Rs![Provider] & """,""" & Rs![Facility] & """ & Rs![TicketNumber] & """, """ & Rs![Charges] & """, """ & Rs![FinancialClass] & """, """ & Rs![CPT] & """, """ & Rs![CPTDescription] & """, """ & Rs![PatientFullName] & """, """ & Rs![DateOfEntry] & """ )"

   Rs.Open
   Rs.Close
   Conn.Close
   Set Rs = Nothing
   Set Cmd = Nothing
   Set Conn = Nothing


End Sub

推荐答案

您有一个ADO记录集Rs，其中包含要添加到Access表中的数据.与其尝试修复INSERT语句以添加每一行，不如为目标表打开DAO记录集并通过在DAO记录集中添加新行来存储每个ADO行中的值，将更加容易.尽管这仍然是 RBAR( 方法，它应该比为每行建立并执行INSERT语句快得多.

You have an ADO Recordset, Rs, which contains data you want to add to your Access table. Instead of trying to fix the INSERT statement to add each row, it should be easier to open a DAO Recordset for the destination table and store the values from each ADO row by adding a new row the the DAO Recordset. Although this is still a RBAR (row by agonizing row) approach, it should be significantly faster than building and executing an INSERT statement for each row.

首先，请确保将Option Explicit添加到模块的声明"部分.

First of all, make sure to add Option Explicit to your module's Declarations section.

Option Compare Database
Option Explicit

然后使用此代码将ADO Recordset数据追加到表中.

Then use this code to append the ADO Recordset data to your table.

Dim db As DAO.Database
Dim rsDao As DAO.Recordset
Set db = CurrentDb
Set rsDao = db.OpenRecordset("tblNoNotes", _
    dbOpenTable, dbAppendOnly + dbFailOnError)

Do While Not Rs.EOF
    rsDao.AddNew
    rsDao!Provider.Value = Rs!Provider.Value
    rsDao!Facility.Value = Rs!Facility.Value
    rsDao!TicketNumber.Value = Rs!TicketNumber.Value
    rsDao!Charges.Value = Rs!Charges.Value
    rsDao!FinancialClass.Value = Rs!FinancialClass.Value
    rsDao!CPT.Value = Rs!CPT.Value
    rsDao!CPTDescription.Value = Rs!CPTDescription.Value
    rsDao!PatientFullName.Value = Rs!PatientFullName.Value
    rsDao!DateOfEntry.Value = Rs!DateOfEntry.Value
    rsDao.Update
    Rs.MoveNext
Loop

rsDao.Close
Set rsDao = Nothing
Set db = Nothing

请注意，这种方法意味着您不必担心PatientFullName是否包含逗号或撇号...，也不必担心正确引用字段值以生成有效的INSERT语句.您只需将值从一个记录集字段存储到另一记录集的相应字段中即可.

Note this approach means you needn't worry about whether PatientFullName contains a comma, or apostrophe ... or struggle with properly quoting field values to produce a valid INSERT statement. You simply store the value from one recordset field to the appropriate field in another recordset.

这篇关于当两个字段包含逗号时，如何使用ADO将多个字段插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！