Can't get last_insert_id() from table adapter please help

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

Unfortunately the autogenerated code does not refresh the data of the
inserted id column despite checking off "refresh data" and creating an
output parameter with mySQL. It does work with SQL Server though. In
this case I am working with MySQL though.

So I googled and googled and found (in theory) one could add an event
handler to the dataadapter that the tableadapter uses for the
rowupdated event and then fire off the subsequent query select
last_insert_id and update the identity value in the datarow.

No matter what I do however, I cannot seem to intercept the rowupdated
event. In some of the first examples these people were simply
creating a sub with the handles _adapter.RowUpdated and saying that
worked. This did not work, however for me. Searching even more I
found some people using the addhandler to add the event manually.

I also wanted to add the event handler into the instantiation of the
dataset object but I could not figure out where to put the code to
make that work. Instead I created a public function I could call from
my component class.

Can anyone throw me a bone here? Why does programming with MySQL
always have to be some damn difficult?

Here is the latest permutation of the code I am working with.

Namespace VTDSTableAdapters
Partial Public Class taCscart_products
Private IdentityQuery As New
MySql.Data.MySqlClient.MySqlCommand("SELECT LAST_INSERT_ID()",
Nothing)
Private Sub _adapter_RowUpdated(ByVal sender As Object, ByVal
e As MySql.Data.MySqlClient.MySqlRowUpdatedEventArgs) Handles
_adapter.RowUpdated
If e.StatementType = StatementType.Insert AndAlso _
e.Status = UpdateStatus.Continue Then
IdentityQuery.Connection = e.Command.Connection
e.Row("Product_Id") = IdentityQuery.ExecuteScalar
End If
End Sub

Public Sub AddHandlers()
InitConnection()
InitCommandCollection()
InitAdapter()
AddHandler _adapter.RowUpdated, AddressOf
_adapter_RowUpdated
End Sub
End Class
End Namespace
 
Unfortunately the autogenerated code does not refresh the data of the
inserted id column despite checking off "refresh data" and creating an
output parameter with mySQL.  It does work with SQL Server though.  In
this case I am working with MySQL though.

So I googled and googled and found (in theory) one could add an event
handler to the dataadapter that the tableadapter uses for the
rowupdated event and then fire off the subsequent query select
last_insert_id and update the identity value in the datarow.

No matter what I do however, I cannot seem to intercept the rowupdated
event.  In some of the first examples these people were simply
creating a sub with the handles _adapter.RowUpdated and saying that
worked.  This did not work, however for me.  Searching even more I
found some people using the addhandler to add the event manually.

I also wanted to add the event handler into the instantiation of the
dataset object but I could not figure out where to put the code to
make that work.  Instead I created a public function I could call from
my component class.

Can anyone throw me a bone here?  Why does programming with MySQL
always have to be some damn difficult?

Here is the latest permutation of the code I am working with.

Namespace VTDSTableAdapters
    Partial Public Class taCscart_products
        Private IdentityQuery As New
MySql.Data.MySqlClient.MySqlCommand("SELECT LAST_INSERT_ID()",
Nothing)
        Private Sub _adapter_RowUpdated(ByVal sender As Object, ByVal
e As MySql.Data.MySqlClient.MySqlRowUpdatedEventArgs) Handles
_adapter.RowUpdated
            If e.StatementType = StatementType.Insert AndAlso _
               e.Status = UpdateStatus.Continue Then
                IdentityQuery.Connection = e.Command.Connection
                e.Row("Product_Id") = IdentityQuery.ExecuteScalar
            End If
        End Sub

        Public Sub AddHandlers()
            InitConnection()
            InitCommandCollection()
            InitAdapter()
            AddHandler _adapter.RowUpdated, AddressOf
_adapter_RowUpdated
        End Sub
    End Class
End Namespace
Click to expand...

In case you want to know a better option than this, here is what I
did.

I went to my datatable's in the dataset designer and right-clicked
chose add query. I then created a query I named InsertQuery:

INSERT INTO cscart_products
(product_code, product_type, owner_id, avail,
manufacturer_id, list_price, amount, min_amount, weight, length,
width, height, shipping_freight,
low_avail_limit, `timestamp`, is_edp,
edp_shipping, tracking, free_shipping, feature_comparison,
zero_price_action, is_pbp, is_op, is_oper, supplier_id,
is_returnable, return_period, checksum)
VALUES (@product_code, @product_type, @owner_id, @avail,
@manufacturer_id, @list_price, @amount, @min_amount, @weight, @length,
@width, @height,
@shipping_freight, @low_avail_limit,
@timestamp, @is_edp, @edp_shipping, @tracking, @free_shipping,
@feature_comparison, @zero_price_action,
@is_pbp, @is_op, @is_oper, @supplier_id,
@is_returnable, @return_period, @checksum);
SELECT last_insert_id()

After doing this I changed the execute mode to scalar (return a single
value) and then in my code I did this:
cr.product_id = TaCscart_products1.InsertQuery
(cr.product_code, cr.product_type, cr.owner_id, cr.avail,
cr.manufacturer_id, cr.list_price, _
cr.amount, cr.min_amount, cr.weight, cr.length,
cr.width, cr.height, cr.shipping_freight, cr.low_avail_limit, _
cr.timestamp, cr.is_edp, cr.edp_shipping,
cr.tracking, cr.free_shipping, cr.feature_comparison,
cr.zero_price_action, _
cr.is_pbp, cr.is_op, cr.is_oper, cr.supplier_id,
cr.is_returnable, cr.return_period, cr.checksum)
dtCscart_Products.Addcscart_productsRow(cr)

This method is way easier and more stable than trying to bend the
tableadapter's wizard to work with mySQL.

Scott Emick
Euclid Friendly Computer Service
 
Back
Top