Problem with updating database from a dataset (row recently deleted)

  • Thread starter Thread starter Alex Shirley
  • Start date Start date
A

Alex Shirley

Hi

I'm using ASP.Net (VS2003). I think this is more an ADO Question. I've
just deleted a row from a dataset and I want to update the database
from the recently altered dataset. It's not working.... Can you skim
down the code, look at the comments below please?

Many thanks

Alex

'
'SqlDeleteProducts
'
Me.SqlDeleteProducts.CommandText = "DELETE FROM [Products Assigned to
Contacts] WHERE (ProductContactID = @ProductContactID)"

Me.SqlDeleteProducts.Connection = Me.SqlConnection1
Me.SqlDeleteProducts.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ProductContactID",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "ProductContactID",
System.Data.DataRowVersion.Original, Nothing))
CType(Me.DsContactsX1,
System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.DsContactIndex1,
System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.DsProductsAssignedToContacts1,
System.ComponentModel.ISupportInitialize).EndInit()


''''''''''''''''''
'Find position in dataset
Dim rowDelete As DSProductsAssignedToContacts.Products_Assigned_to_ContactsRowrowDelete
= DsProductsAssignedToContacts1.Products_Assigned_to_Contacts.FindByProduct_ProductContactID(e.Item.Cells(2).Text)
' Find position in dataset

rowDelete.Delete()
DGProducts.DataBind() ' This works so far, row has now visibliy
deleted from my datagrid DGProducts

'From here downwards it doesn't work, I am merely trying to update the
' database from the dataset
'I get error "Prepared statement '(@ProductContactID int)DELETE FROM
'[Products Assigned to Contact' expects parameter @ProductContactID,
'which was not supplied."
'It seems to ignore the very next line,
'I've tried chopping it out and it doesn't work either.
'I'm supplying the correct parameter aren't I?

SqlDeleteProducts.Parameters("@ProductContactID").Value =
CInt(e.Item.Cells(2).Text.ToString)
DAProductsAssignedToContacts.Update(DsProductsAssignedToContacts1)

Please help!

Thanks

Alex
 
'From here downwards it doesn't work, I am merely trying to update the
' database from the dataset
'I get error "Prepared statement '(@ProductContactID int)DELETE FROM
'[Products Assigned to Contact' expects parameter @ProductContactID,
'which was not supplied."
'It seems to ignore the very next line,
'I've tried chopping it out and it doesn't work either.
'I'm supplying the correct parameter aren't I?

SqlDeleteProducts.Parameters("@ProductContactID").Value =
CInt(e.Item.Cells(2).Text.ToString)
DAProductsAssignedToContacts.Update(DsProductsAssignedToContacts1)
Click to expand...

Remove the .Value line and pass datatable instead of dataset to Update
(adapter should get data from DataTable and not directly)
 
Hi Miha - many thanks

Not having much joy I'm afraid.

Tried...

'blagh

DGProducts.DataBind()
DAProductsAssignedToContacts.Update(DsProductsAssignedToContacts1.Tables
("Products Assigned to Contacts"))

I get the same error. I know this is passing the dataset in again, but I
can't find anything syntactically that will work, I've tried loads of
things. Would you mind if you can give an example in this case?

Sorry I guess I'm being dumb here. I have the concept of the
relationship with data sets, data adapters and the database itself (I
think). But passing a value back from a a "datatable" is confusing me. I
thought the database and dataset contains datatables?

Alex


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Hi Alex,

Just for fun, check that your table has "ProductContactID" column.
 
Hi there Miha

Yes both the database and the .xsd has the ProductContactID column and
both are them are set as the primary key.

Thanks

Alex




*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Hi Miha

I got it working, but I don't think it's really the best way of doing
things. I would have prefered to use .parameters or as you say Miha,
abandon the parameter completely.

Instead of this (which does not work):
---------->

SqlDeleteProducts.CommandText = "DELETE FROM [Products Assigned to
Contacts] WHERE (ProductContactID = @ProductContactID)"
'Parameters declaired here etc, see original source code

SqlDeleteProducts.Parameters("@ProductContactID").Value =
CInt(e.Item.Cells(2).Text.ToString)

I've used this (which does work, but not particularly elegant):
---------->

SqlDeleteProducts.CommandText = "DELETE FROM [Products Assigned to
Contacts] WHERE (ProductContactID = " &
CInt(e.Item.Cells(2).Text.ToString) & ")"

DAProductsAssignedToContacts.Update(DsProductsAssignedToContacts1,
"Products Assigned to Contacts") '.Tables("Products Assigned to
Contacts"))

<-------------------------

If this gives a further clue as to what is wrong (refer to the origial
source code in my first post), then please let me know. I'd really like
to get to the bottom of this.

Cheers!

Alex

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Back
Top