检查重复值后将记录插入表中 [英] Insert records into table after checking the duplicate values

问题描述

我是萨拉迪.
我将数据网格视图中的记录(这些字段从一个excel文件加载)插入到Access数据表中，而无需插入重复的值.
这是我的代码:

Hi, this is saradhi.
I am inserting the records from datagrid view(these fields are loaded from one excel file) into Access data table without inserting duplicate values.
Here is my code:

Dim ConStr As String = "Provider=Microsoft.JET.OLEDB.4.0;Data Source= D:\DB.mdb"
Dim Con As New OleDbConnection(ConStr)
Dim cmd1 As New OleDbCommand
Con.Open()
cmd1.CommandType = CommandType.Text
"IF NOT EXISTS( SELECT * FROM [Table] where Col1 = '" & Col1 & "')" & _
        " begin insert into [Table](Col1,Col2,Col3,Col4,Col5) " & _
        "values('" & Col1 & "','" & Col2 & "','" & Col3e & "','" & Col4 & "','" & Col5 & "') End"
cmd1.Parameters.Add("@Col1", OleDbType.VarChar, 50).Value = Col1(These assigned(col1,col2,..etc.,) valus will be come frm another func)
cmd1.Parameters.Add("@Col2", OleDbType.VarChar, 50).Value = Col2
cmd1.Parameters.Add("@Col3", OleDbType.VarChar, 50).Value = Col3
cmd1.Parameters.Add("@Col4", OleDbType.VarChar, 50).Value = Col4
cmd1.Parameters.Add("@Col5", OleDbType.VarChar, 50).Value = Col5        
cmd1.ExecuteReader()
Con.Close()

当我运行代码时，错误是:
System.InvalidOperation异常
{"ExecuteReader:连接属性尚未初始化."}

然后有人告诉我，我必须使用对象来创建命令.
然后我使用如下代码:

When I run the code the error is:
System.InvalidOperation Exception
{"ExecuteReader: Connection property has not been initialized."}

Then somebody told me that I have to use object to create command.
Then I code used like below:

Dim cmd1 As New OleDbCommand  = Con.CreateCommand

现在错误是:
无效的语句错误:预期为插入，删除，选择，更新，过程"

所以请帮我. Emergency

Now the error is:
Invalid Statement error:expected ''insert,delete,select,update,procedure''

So please help me for this. urgent

推荐答案

您编写的Sql语句中似乎有一个错误.

这是你应该做的
1.通过代码进行调试
2.找到要执行的Sql语句
3.在MS Access上运行此命令，您将收到错误.

我认为并且我不太确定MS Access是否不允许IF Exists

There seems to be an error in the Sql statement you have written.

This is what you should do
1. Debug hrough the code
2. Find the Sql statement which is to be executed
3. Run this on MS access you will get the error.

I think and I am not too sure IF Exists is not allowed in MS Access

除非我只是没有正确阅读此权限，否则好像您忘了在命令对象...应该是这样的:
cmd1.Connection = con

Unless I''m just not reading this right, it looks like you forgot to set the connection property on the command object...should be something like this:
cmd1.Connection = con

这篇关于检查重复值后将记录插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！