Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread Update database with ODBC .Net Provider using UpdateCommand in VB .Net Dataset
Tue, Jun 20 2006 10:58 AMPermanent Link

dotnetaveragebear
I am trying to use Visual Studio 2003 dataset with the ODBC .Net Provider with a UpdateCommand CommandType of Stored Procedure because
MSDN says you can't update a database using OBDC .Net Provider with CommandType = Text.  I added parameters so that I can update the dataset
but when I check the database after running the update command, there are no updated rows.  Here is my code.  Can someone tell me what I need
to do to update the database with any changes made to the dataset using the datagrid?

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       OdbcConn.ConnectionString = "ODBC;DSN=DBISAM Tables;DRIVER=DBISAM 3 ODBC Driver;"
       OdbcConn.Open()

       adapter = CreateDataAdapter(OdbcConn)

       adapter.Fill(dsOfficers, "Officers")
       DataGrid1.DataSource = dsOfficers.Tables(0)
End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

       dsOfficers.AcceptChanges()
       dsOfficers.GetChanges()
       adapter.Update(dsOfficers, "Officers")

End Sub

Public Function CreateDataAdapter(ByVal connection As OdbcConnection) As OdbcDataAdapter

  Dim selectCommand As String = _
           "SELECT OF_ID, OF_FNAME + ', ' + OF_LNAME Officer, OF_Title Title, OF_PHONE 'Phone', OF_EMAIL 'Email Address', If  
(OF_EMAIL_LOAN_RPT IS NULL OR OF_EMAIL_LOAN_RPT = False THEN FALSE ELSE TRUE) 'OF_EMAIL_LOAN_RPT' FROM OFFICER;"

  Dim adapter As OdbcDataAdapter = New OdbcDataAdapter(selectCommand, OdbcConn)

  adapter.UpdateCommand = New OdbcCommand("UPDATE Officer SET OF_EMAIL_LOAN_RPT = ? " & _
                                                                      "WHERE OF_ID = ?")

  adapter.UpdateCommand.Parameters.Add("@OF_ID", OdbcType.Numeric, 2, "OF_ID").SourceVersion = DataRowVersion.Original

  adapter.UpdateCommand.Parameters.Add("@OF_EMAIL_LOAN_RPT", OdbcType.Int, 5).Value = DataRowVersion.Proposed

  Return adapter

End Function
Tue, Jun 20 2006 4:31 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

<<I am trying to use Visual Studio 2003 dataset with the ODBC .Net Provider
with a UpdateCommand CommandType of Stored Procedure because MSDN says you
can't update a database using OBDC .Net Provider with CommandType = Text.  I
added parameters so that I can update the dataset but when I check the
database after running the update command, there are no updated rows.  Here
is my code.  Can someone tell me what I need to do to update the database
with any changes made to the dataset using the datagrid? >>

To make this quicker, would it be possible for you to send me the VB.NET
form that you're using along with the table (empty is fine) ?  Otherwise, I
have to change all of the code to use a different typed dataset, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thu, Aug 10 2006 10:56 AMPermanent Link
 Cancelled Message Cancelled
Thu, Aug 10 2006 11:35 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Please only post attachments in the Binaries newsgroup.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thu, Aug 10 2006 1:55 PMPermanent Link
 Cancelled Message Cancelled
Fri, Aug 11 2006 3:17 PMPermanent Link
 Cancelled Message Cancelled
Tue, Sep 5 2006 4:29 PMPermanent Link
 Cancelled Message Cancelled
Image