"Dave Griffiths" <davegino***@hotmail.com.nospam> schreef in bericht
news:xn0epotmz92rd0000@news.online.no...
> 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.
> --
>
> DaveG
> Norway - Oslo - Skøyen

> 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" <davegino***@hotmail.com.nospam> schreef in bericht
> news:xn0epotmz92rd0000@news.online.no...
> > 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.
> > --
> > DaveG
> > Norway - Oslo - Skxyen

"Dave Griffiths" <davegino***@hotmail.com.nospam> schreef in bericht
news:xn0epov22az5t5000@news.online.no...
>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.
> --------------------------------
>
> If I do the above against an SQL 2000 server all works fine.
>
> Thanks for your time Cor
> --
>
> DaveG
> Norway - Oslo - Skoyen
>
>
> Cor Ligthert [MVP] wrote:
>
>> 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" <davegino***@hotmail.com.nospam> schreef in bericht
>> news:xn0epotmz92rd0000@news.online.no...
>> > 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.
>> > --
>> > DaveG
>> > Norway - Oslo - Skxyen

> 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" <davegino***@hotmail.com.nospam> schreef in bericht
> news:xn0epov22az5t5000@news.online.no...
> > 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.
> > --------------------------------
> >
> > If I do the above against an SQL 2000 server all works fine.
> >
> > Thanks for your time Cor
> > --
> > DaveG
> > Norway - Oslo - Skoyen
> >
> >
> > Cor Ligthert [MVP] wrote:
> >
> > > 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" <davegino***@hotmail.com.nospam> schreef in bericht
> > > news:xn0epotmz92rd0000@news.online.no...
> >>> 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.
> >>> --
> >>> DaveG
> >>> Norway - Oslo - Skxyen

> 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" <davegino***@hotmail.com.nospam> schreef in bericht
news:xn0epq4wr35uzs000@news.online.no...
> 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
> --
>
> DaveG
> Norway - Oslo - Skoyen
>
>
> Dave Griffiths wrote:
>
>> 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.