Solved

Error generating the Update, Insert and Delete command

Posted on 2006-11-21
8
225 Views
Last Modified: 2010-04-23
Hi All

I am using SQL Server 2005 Express Edition on my home computer to develop some applications for my work. When ever i create a new SQLDataAdapter i get an error saying "Error Generating the Insert, Update and Delete Commands". However, when I use my office SQL Server (which is SQL Server 2000) via vpn I don't get this error.

I know the problem it not with the permissions, as when I change the ConnectionString for the SQLConnection, to use my home SQL Server 2005 Express Edition it works. I have also tried creating a new CommandBuilder and using the same DataAdapter for adding, deleteing and/or changing records in a table, hence the same data adapter works. But when I create a new one visually, it does not generate insert, delete and update commands - which is very tiring as I have to make a CommandBuilder object everytime i want to add, remove or modify any data in a table.

Any help is much appreciated!
0
Comment
Question by:shkhurram
8 Comments
 
LVL 7

Expert Comment

by:Mohamed Zedan
ID: 17990772
What is the select command  that is generated visually?
0
 
LVL 7

Expert Comment

by:Vaxman2
ID: 17991081
I believe you get that error if the select statement you have set for the DataAdapter doesn't have a field selected that is the primary key for the table.  I would check the table you are creating the adapter for to verify it has a primary key.
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17994346
0
 
LVL 1

Author Comment

by:shkhurram
ID: 17998301
the select statement do have the primary key and all the fields which are set "not null" the sql server. If there is a problem with the dataadapter, then it should'nt work when i change the connection string from my VPN server to my local server. I believe something is wrong with the connection, i.e. SQL Server 2000 drivers and SQL Server 2005... ??? do i need some special drivers. Although I have updated MDAC 2.8 SP1 drivers.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 7

Expert Comment

by:Mohamed Zedan
ID: 17999065
Try using oledbDataAdapter it should be more compatible.
0
 
LVL 7

Accepted Solution

by:
Mohamed Zedan earned 500 total points
ID: 17999069
with an oledbconnection to SQL Server
0
 
LVL 1

Author Comment

by:shkhurram
ID: 17999626
I tried the OLEdbDataAdapter and it worked.

Also when I downloaded the SQL Server 2005 Native Client, it makes a connection using this adapter. Can you tell me the difference between using OLEDB and SQL DataAdapters?
0
 
LVL 7

Expert Comment

by:Mohamed Zedan
ID: 17999711

SqlConnection class was made to connect to the SQL server only and was made before SQLServer2005 .NET 1.1 maybe that's the problem ... as .NET 2.0 has builtin support for SQL2005 ...

Oledb is a generic data access library which uses data providers to connect to different data source...

SQLClient library is specifically desgined for SQL Server  on and for that is quite faster than oledb...

so SqlClient is faster but oledb is safer ...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now