shkhurram
asked on
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!
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!
Mohamed Zedan
What is the select command that is generated visually?
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.
Checkout this link
It covers your problem description
https://www.experts-exchange.com/questions/22049559/Having-Data-Fill-Problem.html
It covers your problem description
https://www.experts-exchange.com/questions/22049559/Having-Data-Fill-Problem.html
ASKER
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.
Try using oledbDataAdapter it should be more compatible.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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 ...