• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Error generating the Update, Insert and Delete command

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
shkhurram
Asked:
shkhurram
1 Solution
 
Mohamed ZedanCommented:
What is the select command  that is generated visually?
0
 
Vaxman2Commented:
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
 
vbturboCommented:
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
shkhurramAuthor Commented:
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
 
Mohamed ZedanCommented:
Try using oledbDataAdapter it should be more compatible.
0
 
Mohamed ZedanCommented:
with an oledbconnection to SQL Server
0
 
shkhurramAuthor Commented:
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
 
Mohamed ZedanCommented:

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now