Click here to Skip to main content
15,885,435 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:
MIDL
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()


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
Posted
Updated 22-Nov-10 19:52pm
v3
Comments
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.

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
 
Share this answer
 
Comments
saradhiAD 23-Nov-10 3:48am    
ok thanq.How can i write the query for this command..Plz help me in this..
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
 
Share this answer
 
Comments
saradhiAD 24-Nov-10 7:24am    
ok.thank u so much.but i have used this one..
Cmd1.connection=con
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, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900