• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Updating a database Problem .. PLEASE HELP!!

I need to update a database from a datatable that i generate from a text file that i parsed.  The datatable is full of data, but i don't know how to get the update method of the dataadapter to work for this table, since no dataadapter was used to create the table.  please note i am trying to approach this from an untyped dataset.  Im still pretty novice to .VB compared to the rest of you so be patient with me a little at trying to expalain.

here is my code it may be way off:

Public Sub AddProducts(ByVal dt As DataTable)

        Dim sql As String = "select name, sku from product"
        Dim cmd As New OleDbCommand(sql, NewConnection()) 'This gets connection from other method ... this works
        Dim dap1 As New OleDbDataAdapter(cmd)
        Dim cb As New OleDbCommandBuilder(dap1)

        With dap1
            .UpdateCommand = cb.GetUpdateCommand
            .DeleteCommand = cb.GetDeleteCommand
            .InsertCommand = cb.GetInsertCommand
        End With

        dap1.Update(dt)
        dt.AcceptChanges()

    End Sub

Here is the code that i used to make the table

Private Sub BuildDataTable()

        dasProduct = New DataSet
        dtProduct = New DataTable("Product")

        DasProduct.Tables.Add(dtProduct)

        With dtProduct.Columns

            .Add("Name", GetType(String))
            .Add("SKU", GetType(String))
           
        End With

        Dim i As Integer
        Dim clsproduct As Product
        For i = 1 To m_cProducts.Count

            clsproduct = m_cProducts.Item(i)
            Dim oaFields(2) As Object

            With clsproduct

                oaFields(0) = .Name
                oaFields(1) = .SKU
               
            End With

            dtProduct.Rows.Add(oaFields)

        Next

..
End Sub
0
rodmjay
Asked:
rodmjay
  • 4
  • 4
1 Solution
 
darkpriestCommented:
Im not sure exactly what your trying to do from there, im not exactly a professional either.


But to use a Update Command its just as simple as defining it as a new object

( this is paraphrasing as im not near a compiler)

     Dim SQLCONNECTIONSTRING As String
        SQLCONNECTIONSTRING = "YOUR STRING"


        Dim Adapter As OleDb.OleDbDataAdapter
        Dim SelectCMD As OleDb.OleDbCommand
        Dim UpdateCMD As OleDb.OleDbCommand

        UpdateCMD.CommandText = "Your Update Query" ' Eg _ Update table set fieldname, fieldname where record = 1
        UpdateCMD.Connection = SQLCONNECTIONSTRING
        UpdateCMD.Connection.Open()
        UpdateCMD.ExecuteNonQuery()
        UpdateCMD.Connection.Close()


It depends on your query thats probably nto what your looking for but its a general run down i could be rung i am pretty tired.
But anyway hope that helps somehow
0
 
ptakjaCommented:
I think the problem is that you aren't actually adding rows to your table. Try this:

        Dim i As Integer
        Dim clsproduct As Product
        Dim NewRow As DataRow
        For i = 1 To m_cProducts.Count

            clsproduct = m_cProducts.Item(i)
            NewRow = dt.NewRow()

            With clsproduct
                NewRow("Name") = .Name
                NewRow("SKU") = .SKU
            End With

            dtProduct.Rows.Add(NewRow)

        Next
0
 
ptakjaCommented:
The other thing to try is naming your table:

dt.TableName  = "product"
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
rodmjayAuthor Commented:
The data table is loading fine, it is binding to a datagrid just fine but the problem is in updating to the database.  Also, i have 680 records and cannot use an update command for each row if that is what i am seeing from your answer darkpriest.  An update command would work if it would accept all 680 records, and that is why i am trying to use the dataset approach.  Any more suggestions?  i renamed the data table, that was not the problem.  I am still getting an error when i run the update command.
0
 
ptakjaCommented:
what error are you getting, exactly?
0
 
rodmjayAuthor Commented:
The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data.

Interesting... Any suggestions??
0
 
rodmjayAuthor Commented:
it appears that only the first 189 records are being updated and then throws this error, does anybody know a way to break the table into smaller chunks to send at a time, like 150 at a time
0
 
ptakjaCommented:
Is it possible that one of the data items is exceeding the field size in the database, say on the 190th item in your text file?
0
 
rodmjayAuthor Commented:
Yes, that was it.  Thank you very much.  I guess there was one stragler at position 190 that exceeded the limit i had set to my database.

Here is the code i inserted in my Product class

Property Name as string
...
Set(Value as string)
if value.length >50
value = value.substring(0, 50)
end if
sName = value


0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now