?
Solved

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

Posted on 2007-07-21
6
Medium Priority
?
179 Views
Last Modified: 2013-11-26
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
0
Comment
Question by:Dovberman
  • 3
  • 3
6 Comments
 
LVL 27

Accepted Solution

by:
Dabas earned 2000 total points
ID: 19541740
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
 

Author Comment

by:Dovberman
ID: 19541990
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
 
LVL 27

Expert Comment

by:Dabas
ID: 19542254
Try daStockHist.UpdateCommand = New sqlCommand("Update...")
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:Dovberman
ID: 19542467
That helped.

The only error remaining is

Must declare the scalar variable "@PriceChange".

From line:

daStockHist.Update(dstStockHist.Tables("StockHist"))
0
 

Author Comment

by:Dovberman
ID: 19542585
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
 
LVL 27

Expert Comment

by:Dabas
ID: 19543670
Good job!
Glad I could point you in the right direction

Dabas
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand column That will then direct you to their download page. From that p…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question