I need a clear example of code to update a SQL Table from an edited DataSet

I need a clear example of code to update a SQL Table from an edited DataSet

I am able to create the DataSet and loop through the rows.
I am not sure how to edit a value in each row during the loop.
The SQL Table has a primary key
I am not sure how to update the SQL table with the value from the edited
DataSet row.

      'Write deltaprice to the stock hist table
        Dim intRow As Integer
        Dim strConnection As String = "Data Source=BURCEL05;Initial Catalog=StockSelectSQL;" _
        & "Integrated Security=SSPI;Connection Timeout=10"
        Dim conStockSelector As SqlConnection
        conStockSelector = New SqlConnection(strConnection)
        Dim strUpdateSQL As String = ""
        Dim strSelectSQL As String = _
        "SELECT TOP 100 StockHistID,SymbolID, QuoteDate,ClosePrice,PriceChange " _
        & "FROM StockHist " _
        & "WHERE QuoteDate>'06/25/2007' AND QuoteDate <'06/28/2007' " _
        & "AND SymbolID<24000 " _
        & "ORDER BY SymbolID,QuoteDate "

        Dim daStockHist As SqlDataAdapter = New SqlDataAdapter( _
          strSelectSQL, conStockSelector)

        Dim dstStockHist As DataSet = New DataSet
        daStockHist.Fill(dstStockHist, "StockHist")
        Dim rowStockHist As DataRow
        Dim cmdUpdate As SqlCommand
        Dim cmdSelect As SqlCommand

        strUpdateSQL = "UPDATE StockHist SET PriceChange=12.57 " _
        & "WHERE StockHistID=@StockHistID "
        ' Create the SelectCommand.
        cmdSelect = New SqlCommand(strSelectSQL, conStockSelector)
        ' Create the UpdateCommand.
        cmdUpdate = New SqlCommand(strUpdateSQL, conStockSelector)

        ' Add the parameters for the UpdateCommand.
        'Following SqlDBType reference is wrong.
       I seem to need a number. ie . SqlDbType.Int,Some number
        cmdUpdate.Parameters.Add("@StockHistID", SqlDbType.Int, "StockHistID")
        cmdUpdate.Parameters.Add("@PriceChange", SqlDbType.SmallMoney, "PriceChange")

        daStockHist.UpdateCommand.CommandText = _
        "UPDATE StockHist SET PriceChange=@PriceChange " _
        & "WHERE StockHistID=@StockHistID "

        Dim prmStockHist As SqlParameter = cmdUpdate.Parameters.Add( _
        "@oldPriceChange", SqlDbType.SmallMoney, "PriceChange")
        prmStockHist.SourceVersion = DataRowVersion.Original

        For Each rowStockHist In dstStockHist.Tables("StockHist").Rows
            intRow = intRow + 1
           'Rows(0) is not correct. How do I reference the current row?
            'dstStockHist.Tables("StockHist").Rows(0)("PriceChange") = 12.57
            daStockHist.Update(dstStockHist, "StockHist")
            If intRow = 12 Then
                Exit For
            End If
        Next
        txtPriceChange.Text = intRow
    End Sub
DovbermanAsked:
Who is Participating?
 
DabasConnect With a Mentor Commented:
Hello Dovberman,

I think you are trying to use two separate techniques to do the same thing at the same time.
A problem like this one can be resolved using a dataAdapter, or using sqlCommand objects. You are using both!
I suggest you give using a DataAdapter a go, and remove the cmdSelect and cmdUpdate objects all together.

When using the DataAdapter, the usual code goes like this:
1) Create a connection
2) Create a SqlDataAdapter using a select statement string and the connection
3) Add an UpdateCommand (and an Insert and/or Delete commands if needed)
3) Fill your dataset
4) Change whatever needs to be changed
5) Update your dataset

In your case, it probably will go something like this:

        Dim strConnection As String = "Data Source=BURCEL05;Initial Catalog=StockSelectSQL;" _
        & "Integrated Security=SSPI;Connection Timeout=10"
        Dim conStockSelector As SqlConnection
        conStockSelector = New SqlConnection(strConnection)
        Dim strSelectSQL As String = _
        "SELECT TOP 100 StockHistID,SymbolID, QuoteDate,ClosePrice,PriceChange " _
        & "FROM StockHist " _
        & "WHERE QuoteDate>'06/25/2007' AND QuoteDate <'06/28/2007' " _
        & "AND SymbolID<24000 " _
        & "ORDER BY SymbolID,QuoteDate "

          Dim daStockHist As SqlDataAdapter = New SqlDataAdapter( _
          strSelectSQL, conStockSelector)

        Dim dstStockHist As DataSet = New DataSet
        daStockHist.Fill(dstStockHist, "StockHist")
        Dim rowStockHist As DataRow

        daStockHist.UpdateCommand.CommandText = _
        "UPDATE StockHist SET PriceChange=@PriceChange " _
        & "WHERE StockHistID=@StockHistID "

        For Each rowStockHist In dstStockHist.Tables("StockHist").Rows
                rowStockHist.Item("PriceChange") = 12.57
        Next

       daStockHist.Update



Regards,

Dabas
0
 
DovbermanAuthor Commented:
I thought I might be confusing two different approaches.
Thanks,
This almost works now.

Object reference not set to an instance of an object.
daStockHist.UpdateCommand.CommandText = _
Line 366:        "UPDATE StockHist SET PriceChange=@PriceChange " _
Line 367:        & "WHERE StockHistID=@StockHistID "

I also changed this daStockHist.Update()
to this:daStockHist.Update(dstStockHist.Tables("StockHist"))

Any ideas?

to avoid a compilation error.
0
 
DabasCommented:
Try daStockHist.UpdateCommand = New sqlCommand("Update...")
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
DovbermanAuthor Commented:
That helped.

The only error remaining is

Must declare the scalar variable "@PriceChange".

From line:

daStockHist.Update(dstStockHist.Tables("StockHist"))
0
 
DovbermanAuthor Commented:
I defined the update parameters as in:

Parameters.Add("@PriceChange", SqlDbType.SmallMoney, 4, "PriceChange")
Parameters.Add("@StockHistID", SqlDbType.Int, 5 "StockHistID")

Now it works well.

Thanks for all the help
0
 
DabasCommented:
Good job!
Glad I could point you in the right direction

Dabas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.