Click here to Skip to main content
14,359,091 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
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        

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

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

So please help me for this. urgent
Updated 22-Nov-10 20:52pm
JF2015 23-Nov-10 1:53am
Please, never use the word "urgent" since it's only urgent to you and nobody else in this forum.
Rate this:
Please Sign up or sign in to vote.

Solution 1

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
saradhiAD 23-Nov-10 3:48am
ok thanq.How can i write the query for this command..Plz help me in this..
Rate this:
Please Sign up or sign in to vote.

Solution 2

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
saradhiAD 24-Nov-10 7:24am
ok.thank u so much.but i have used this one..
Again i am getting the same error like

Invalid Statement error:expected 'insert,delete,select,update,procedure'
Kschuler 29-Nov-10 9:18am
I'm not sure if you are just taking a different approach than I've used before, but I'm noticing a lot of things with your code that do not make sense. I think you need to set the CommandText property of the command object instead of that CommandType stuff. If you are performing an Insert you do not want to use the ExecuteReader you want to do a ExecuteNonQuery.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100