I was wondering if there was a way to copy the exact contents of a datagrid into a SQL server 2000 table. What makes it tricky is that initially the datagrid is populated by an array, sans any kind of database. Also the datagrid has thirty columns, so to do an insert or something I'd have to have thirty different parameters and that isn't a direction I'd like to go. Plus I'd like it to be more dynamic anyway...
But it's very likely there's some easier way to do this that I dont know about, so if anyone happens to know a way, any help would be great.
thanksOK I'm getting closer. Here's what I've got so far...
Private Sub AddData(ByVal m_sa As System.Collections.ArrayList)
'this first part populates the first dataset from an array
Dim oTable As New DataTable
Dim i As Integer
For i = 0 To m_Sal.Count - 1
oTable.Columns.Add(m_Sal.Item(i).FieldName)
Next
Dim x As Integer
For x = 0 To m_sa.Count - 1
Dim oRow As DataRow
oRow = oTable.NewRow
For i = 0 To m_Sal.Count - 1
oRow.Item(i) = m_sa.Item(x)(i)
Next
oTable.Rows.Add(oRow)
Next
Dim ds As New DataSet
Dim da As New SqlDataAdapter
ds.Tables.Add(oTable)
dgFile.DataSource = ds
'And here's where I need to connect to the DB and copy it to a table
Dim SQLCOnn As New SqlConnection("stuff")
Dim cmd As SqlCommand = SQLCOnn.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * FROM tbl"
Dim daCopy As New SqlDataAdapter
daCopy.SelectCommand = cmd
Dim dsCopy As New DataSet
daCopy.Fill(dsCopy)
dsCopy = ds.Copy
dsCopy.AcceptChanges()
'I know I'm missing something around here. A method call? Do I need to use a insert or update statement?
End Sub
I know the ds's are copying corrently, so it's just a matter of figuring out how to get dsCopy to write the changes back to the table...to save back to db, you need to retrieve information from database, stick it into a dataset, and then do your changes to the dataset, and then accept changes, then the changes will reflect at yoru db.
this is what you are doing, creating new table, and then inserting new rows, and then sticking it into a dataset, and try to accept changes...
-Tak
But it's very likely there's some easier way to do this that I dont know about, so if anyone happens to know a way, any help would be great.
thanksOK I'm getting closer. Here's what I've got so far...
Private Sub AddData(ByVal m_sa As System.Collections.ArrayList)
'this first part populates the first dataset from an array
Dim oTable As New DataTable
Dim i As Integer
For i = 0 To m_Sal.Count - 1
oTable.Columns.Add(m_Sal.Item(i).FieldName)
Next
Dim x As Integer
For x = 0 To m_sa.Count - 1
Dim oRow As DataRow
oRow = oTable.NewRow
For i = 0 To m_Sal.Count - 1
oRow.Item(i) = m_sa.Item(x)(i)
Next
oTable.Rows.Add(oRow)
Next
Dim ds As New DataSet
Dim da As New SqlDataAdapter
ds.Tables.Add(oTable)
dgFile.DataSource = ds
'And here's where I need to connect to the DB and copy it to a table
Dim SQLCOnn As New SqlConnection("stuff")
Dim cmd As SqlCommand = SQLCOnn.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * FROM tbl"
Dim daCopy As New SqlDataAdapter
daCopy.SelectCommand = cmd
Dim dsCopy As New DataSet
daCopy.Fill(dsCopy)
dsCopy = ds.Copy
dsCopy.AcceptChanges()
'I know I'm missing something around here. A method call? Do I need to use a insert or update statement?
End Sub
I know the ds's are copying corrently, so it's just a matter of figuring out how to get dsCopy to write the changes back to the table...to save back to db, you need to retrieve information from database, stick it into a dataset, and then do your changes to the dataset, and then accept changes, then the changes will reflect at yoru db.
this is what you are doing, creating new table, and then inserting new rows, and then sticking it into a dataset, and try to accept changes...
-Tak