将数据插入带有掩码列的MS Access数据库 [英] Inserting Data into MS Access Database with Masked Column

问题描述

我正在尝试为现有数据库开发改进的UI.我需要编辑一个表格，该表格的电话号码要以(123)456-7899格式显示.我认为这是导致"INSERT INTO语句中的语法错误"的原因.错误，因为我不确定是否需要在尝试更新数据适配器之前将数据条目格式化为这种格式，否则表将进行必要的转换吗?我将电话号码输入为字符串(请注意:电话号码的字段数据类型被列为文字").另外，第一列是自动编号列，我在上一篇文章中读过，我不需要提供任何信息.

当前，我正在尝试简单地读取一行并将数据传递到另一行，以确保该方法有效.

语法是正确的，因为使用相同的信息，我可以将数据传递到只有一个数据字段的另一张表中.

I am trying to develop an improved UI for an existing database. I need to edit a table which has a masked field for phone numbers to show in [phone] format. I think this is what is causing the "Syntax error in INSERT INTO statement." error as I am not sure if I need to format my data entries as this format before trying to update the dataadapter or will the table do the necessary conversion of I input the phone number as a string (note: field data types for the phone numbers are listed as Text). Also, the first column is autonumber column which I read in a previous post that I do not need to provide any information to.

Currently, I am trying to simply read one row and pass the data to another row to ensure that the method works.

The syntax is correct as with the same information I am able to pass data into another table with only one datafield.

dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
        dbSource = "Data Source = \\usykgw\ycaus\Newnan\service\Share\1_T_&_m\T&m service\Service20000TM.accde"
        If (con.State = ConnectionState.Open) Then
        Else
            con.ConnectionString = dbProvider & dbSource
            con.Open()
        End If


        'Load customer information'
        sql = "SELECT * FROM Customers"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "RRCustomers")
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        dsNewRow = ds.Tables("RRCustomers").NewRow()
        'dsNewRow.Item("CustomerID") =  This is the autonumber field, not necessary to input
        dsNewRow.Item("Company Name") = ds.Tables("RRCustomers").Rows(0).Item(1).ToString & "test"
        dsNewRow.Item("Customer Phone") = ds.Tables("RRCustomers").Rows(0).Item(2)
        dsNewRow.Item("Customer FAX") = ds.Tables("RRCustomers").Rows(0).Item(3)
        dsNewRow.Item("Def Bill To") = ds.Tables("RRCustomers").Rows(0).Item(4).ToString
        dsNewRow.Item("Def Bill Address1") = ds.Tables("RRCustomers").Rows(0).Item(5).ToString
        dsNewRow.Item("Def Bill Address2") = ds.Tables("RRCustomers").Rows(0).Item(6).ToString
        dsNewRow.Item("Def Bill City") = ds.Tables("RRCustomers").Rows(0).Item(7).ToString
        dsNewRow.Item("Def Bill State") = ds.Tables("RRCustomers").Rows(0).Item(8).ToString
        dsNewRow.Item("Def Bill Zip") = ds.Tables("RRCustomers").Rows(0).Item(9).ToString
        dsNewRow.Item("Def Ship To") = ds.Tables("RRCustomers").Rows(0).Item(10).ToString
        dsNewRow.Item("Def Ship Address1") = ds.Tables("RRCustomers").Rows(0).Item(11).ToString
        dsNewRow.Item("Def Ship Address2") = ds.Tables("RRCustomers").Rows(0).Item(12).ToString
        dsNewRow.Item("Def Ship City") = ds.Tables("RRCustomers").Rows(0).Item(13).ToString
        dsNewRow.Item("Def Ship State") = ds.Tables("RRCustomers").Rows(0).Item(14).ToString
        dsNewRow.Item("Def Ship Zip") = ds.Tables("RRCustomers").Rows(0).Item(15).ToString
        ds.Tables("RRCustomers").Rows.Add(dsNewRow)
        da.Update(ds, "RRCustomers")

推荐答案

我怀疑您正在尝试将字符串[phone]添加到数字字段中.如何解决呢?

I suspect that you''re trying to add string: [phone] into numeric field. How to go around it?

1. 仅添加数字数据1234567899(不带方括号，空格等)
或

2. 将该列的数据类型更改为CHAR并以准确的形式插入数据:[phone]

1. add only numeric data 1234567899 (without any brackets, spaces, etc.)
or

2. change data type for that column to CHAR and insert data in exact form: [phone]

选择是您自己的；)

The choice is yours ;)

我放弃了此过程，并使用带有executeNonQuery选项的直接SQL命令:

I gave up on this process and went with straight SQL command with executeNonQuery option:

Dim Str As String = "insert into Customers ([Company Name], [Customer Phone], [Customer FAX], [Def Bill To], [Def Bill Address1], [Def Bill Address2], [Def Bill City], [Def Bill State], [Def Bill Zip], [Def Ship To], [Def Ship Address1], [Def Ship Address2], [Def Ship City], [Def Ship State], [Def Ship Zip]) values ('a','1231234567','1231234567','a','a','a','a','a','a','a','a','a','a','a','a')"
   Dim cmd As New OleDbCommand(Str, con)
   cmd.ExecuteNonQuery()

做工精美.

Worked beautifully.

这篇关于将数据插入带有掩码列的MS Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！