arrays to sql server table

arrays to sql server table

• Bill Reiss

  Success!

  I added the following code after the for/next loop:

  Dim sqlCB As SqlCommandBuilder

  sqlCB = New SqlCommandBuilder(ResultAdapter)

  ResultAdapter.Update(ResultSet, FactorTableName)

  
  

• stevdes

  I agree, plain ADO in VB6 was far less verbose.  When I started using .NET I ran into the same problem you're describing and just decided to code the SQL statements manually (insert, update, etc.) and execute them through the SqlCommand object.  I've had far less trouble debugging and a much shallower learning curve.

  Is there some setting for 'copy database to output' that could be erasing the results between runs, or have you checked with SSMS after run/before next

  -Edit-  cross-post, nevermind :)

  
  

• fspilot2006

  If you're trying to modify the table you retrieved from SQL you need to reference the table in the dataset.

  Something like dim FactorTable As DataTable = ResultSet.Tables(0) or dim FactorTable As DataTable = ResultSet.Tables(factorTableName)

  That may, though, cause a problem with you setting the new column with the datetime to not allowing nulls, since any existing rows retrieved from SQL would have to have null as a default.

  Once you reference that table already in the dataset, add your new rows, then AcceptChanges(), any bound controls should see the new data.

  DJ

  
  
  

• elodie23

  Saw your nevermind. Just for the record: I agree, SQL statements are the way to go.

  I'm sure the development team has their reasons; but, I would have preferred a simpler way to handle simple data access activities. Perhaps, they will correct this in VB9.

  
  

• makoto117

  It looks like you are reading the array data correctly and putting it into the datatable correctly....

  DO NOT call AcceptChanges until you have called the tables update method!

  Table.Rows.Add(row)

  TableAdapter.Update()

  Table.AcceptChanges

  Remeber you always have the ability to utilize SQl command objects to do your updating

  
  
  

• kdee

  That's the point. The DataTable doesn't cause changes to occur in the database table. As I noted above, I am trying find out what needs to be used (DataAdapter, DataSet and/or DataTable ) and when. Obviously, what I'm doing is not working which is why I've asked for help.  

  Thanks for the info about using AcceptChanges after the for/next loop. Regarding DMan1's reply, as far as I can tell, I'm not using a TableAdapter; should I be

  This all goes back to my earlier comments: How does one determining when, where, how, why and in what combination one uses the various objects available in ADO.NET.

  BTW, that's why I didn't start initially with a code listing. I was hoping someone knew a generic answer to the question: how does one store array data in an existing database table

  
  

• Zadoras

  I've tried that but the database table (I'm checking it in SSMS) still doesn't get updated. Perhaps I should try a different approach.
  

• Whoisit

  It's no secret that I consider ADO far less code intensive than ADO.NET. I can do this with VB6 and ADO quickly and easily with about half a dozen lines of code.

  However, knowing when to use DataAdapters, DataSets and/or DataTables is what ruffles my feathers. I've read dozens of pages in BOL, assorted forums and numerous websites. Unfortunately, the normally very helpful walkthroughs concentrate on using designers and wizards; coding for dlls seems all but ignored. If there is a plain and simple way to programatically access data with ADO.NET, I've yet to find it.

  In the code below, I've added rows containing the array data to a datatable. However, it is not reflected in the database. Obviously, the datatable is in memory only; so I'm missing a step or steps. BTW, I've tried this with various combinations of DataAdapters, DataSets and DataTables.

  Dim ResultConn As New SqlConnection(DestConnStr)

  Dim ResultCmd As New SqlCommand

  sSQL = "SELECT * FROM " & FactorTableName

  ResultCmd.CommandType = CommandType.Text

  ResultCmd.CommandText = sSQL

  ResultCmd.Connection = ResultConn

  Dim ResultAdapter As New SqlDataAdapter

  ResultAdapter = New SqlDataAdapter(ResultCmd)

  Dim ResultSet As DataSet = New DataSet

  ResultAdapter.Fill(ResultSet)

  Dim FactorTable As DataTable = New DataTable

  Dim DTCol As DataColumn = FactorTable.Columns.Add( _

  "DateTime", Type.GetType("System.DateTime"))

  DTCol.AllowDBNull = False

  DTCol.Unique = True

  FactorTable.Columns.Add("TAM", Type.GetType("System.Double"))

  Dim n As Long

  Dim workRow As DataRow

  For n = 1 To NumRecords

  workRow = FactorTable.NewRow()

  workRow("DateTime") = CDate(DateTimeArray(n))

  workRow("TAM") = TAMArray( n)

  FactorTable.Rows.Add(workRow)

  FactorTable.AcceptChanges()

  Next n

  
  

• sam-pan

  I'm not sure where it is that you're looking for the changes to be reflected (you say you can't see the new rows).

  You've constructed a standalone table.  It's not part of the ResultSet dataset.

  Additionally, you only need to call FactorTable.AcceptChanges() once after you've completed adding all the rows.

   

   

  DJ

  
  
  

• Jehan Badshah

  Since the .Update() function provided the updates to the dataset, then it sounds like you were somehow still adding rows to a table outside the ds.
  
  Glad you found a solution.
  
  
  

• anglerdk

  Not to be sarcastic, but if you know how to get an array out of a database, why can't you just do that procedure in reverse to get it back into the database    If there is some part, specifically, that is confusing please post the relevant code for us to see.
  
• arrays to sql server table