VS 2003 & SQL 2005 Data Adapter Error

[Dave Griffiths]

Hi All

Very new to ADO.Net & SQL

When I create a data adapter to SQL 2005 server I get the meassages.

Generated SELECT Command
Generated TABLE Mappings
Error creating INSERT command
Error creating UPDATE command
Error creating DELETE command

If I create a data adapter to a SQL 2000 server everything creates OK

Is this a known issue between the 2 products VS 2003 & SQL 2005.

Is there an answer to the problem apart from using sql 2000 server.

All help welcomed.

Thanks in advance.

 

[Cor Ligthert [MVP]]

Dave,

At least we need to know what kind of provider you use, SQLClient, OleDB,
ODBC.
Than we are interested in your Select command what is the base of the three
that goes wrong,

Cor

 

[Dave Griffiths]

I am attempting to connect with SQL Client.
When I create the Data adapter using the Wizard (as I said still early
days for me)
1. I choose the connection
2. I select "Use SQL Staements"
3. I use the "Query Builder" and select a single table and a few fields
4. The following Query is Generated.

SELECT
DisplayName,
CompanyName,
FirstName,
CustomerID
FROM
Customer

6. Click next and it creates all Statements but INSERT - DELETE -
UPDATE have errors. When I try and use the UPDATE in the program I get
an ERROR during UPDATE process.

Error Message
----------------------
An unhandled exception of type 'System.InvalidOperationException'
occurred in system.data.dll

Additional information: Update requires the UpdateCommand to have a
connection object. The Connection property of the UpdateCommand has not
been initialized.

 

[Cor Ligthert [MVP]]

Dave,

What is strange for me is that it works for the 2000 and not for 2005.

The error code you shows means that the connection for the dataadapter are
not set properly, but that is a part of the wizard generated code. Are you
sure you use the by the wizard generated datadapter. In other words, can you
show a piece of the code you use to update?

Cor

 

[Dave Griffiths]

Hi Cor

Your last reply has just got me thinking this may be a permissions
problem on the SQL 2005. I have just loaded it and wanted to start to
develop against it rather than SQL2000. The only difference was that
sql2k was on the 2k3 sever and sql2k5 is on my local machine.

I think some playing around with permissions might be in order here.

Thanks for your reply..... I no doubt will be back to you.

 

[Dave Griffiths]

Hi Cor

I have created 2 identical DB's one on the SQL2K server the other on
the local Machine sql2k5. Both with a single table and identical fields.

When I create the connection and then the data adapter using the wizard
in both cases. The data adapter to the 2k server creates OK no errors.
The data adapter to the 2k5 server creates with errors. When I look at
bthe properties vb2003 has failed to create the connection and command
text for INSERT UPDATE and DELETE.

I have since copied the relevant properties from the 2k data adapter
and the 2k3 data adapter then functions correctly for UPDATE (I have
not tried the DELETE & INSERT. One step at a time)

For some reason when I create a Data Adapter using VB2003 pro to
SQL2005 Dev Ed the INSERT UPDATE and DELETE are not created.

All code snippets are direct cut and paste.

Code used to Fill and UPDATE
-------------------------
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Me.Ds20001.Clear()
Me.da2kCustomer.Fill(Me.Ds20001.Customer)
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Me.Ds20031.Clear()
Me.da2k3Customer.Fill(Me.Ds20031.Customer)
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Me.da2kCustomer.Update(Me.Ds20001.Customer)
End Sub

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button4.Click
Me.da2k3Customer.Update(Me.Ds20031.Customer)
End Sub
-------------------------------


SELECT Created against SQL2000
---------------------------
SELECT CustomerID, FirstName, MidName, LastName
FROM Customer
-----------------------

SELECT created against SQL 2005
-----------------------
SELECT CustomerID, FirstName, MidName, LastName
FROM Customer
------------------------

Code created against SQL 2000 but not SQL 2005
------------------
DELETE FROM Customer
WHERE (CustomerID = @Original_CustomerID) AND (FirstName =
@Original_FirstName) AND (LastName = @Original_LastName OR
@Original_LastName IS NULL AND LastName IS NULL)
AND (MidName = @Original_MidName OR
@Original_MidName IS NULL AND MidName IS NULL)
-------------------
INSERT INTO Customer
(FirstName, MidName, LastName)
VALUES (@FirstName, @MidName, @LastName);
SELECT CustomerID, FirstName, MidName,
LastName
FROM Customer
WHERE (CustomerID = @@IDENTITY)
-----------------------
UPDATE Customer
SET FirstName = @FirstName, MidName = @MidName, LastName =
@LastName
WHERE (CustomerID = @Original_CustomerID) AND (FirstName =
@Original_FirstName) AND (LastName = @Original_LastName OR
@Original_LastName IS NULL AND LastName IS NULL)
AND (MidName = @Original_MidName OR
@Original_MidName IS NULL AND MidName IS NULL);
SELECT CustomerID, FirstName, MidName,
LastName
FROM Customer
WHERE (CustomerID = @CustomerID)
---------------------------

I hope this makes some sense to someone because it is killing me.

I have also tried this on my laptop running SQLEXPRESS and VB2003 pro
and I get the same errors

Thanks in advance for your time

 

[Cor Ligthert [MVP]]

Dave,

Sorry, I am all the time reading your question wrong.
(I was a little bit ill yesterday maybe was that the reason)

I was reading VS2005 asn SQL 2000 something you did not write.

To be sure, I tested your problem, and I have the same result as you and
AFAIK is that a known problem.

Again sorry.

Cor