Larry Brister
asked on
Fill SQLDataAdaptor from IList
I am using the property attached as the datasource for a RadGrid.
...datasource for grid...
grdAvailableForSale.DataSo urce = PendingOrdersStore
I need to fill a SQLDataAdaptor with this data so I can insert all the records into a SQL Table
How do I fill the SQLDataAdaptor?
...datasource for grid...
grdAvailableForSale.DataSo
I need to fill a SQLDataAdaptor with this data so I can insert all the records into a SQL Table
How do I fill the SQLDataAdaptor?
Protected Property PendingOrdersStore() As IList(Of Order)
Get
Try
Dim obj As Object = Session("AFS_VB")
If obj Is Nothing Then
obj = GetOrders()
Session("AFS_VB") = obj
End If
Return DirectCast(obj, IList(Of Order))
Catch ex As Exception
Session("AFS_VB") = Nothing
End Try
Return New List(Of Order)
End Get
Set(ByVal value As IList(Of Order))
Session("AFS_VB") = value
End Set
ASKER
ajb2222:
Yeah..I had found that and a few other examples.
Right now I have a datatable being filled with the code that is attached and I verified that the rows are there in the Watch WIndow
However...whan I'm trying to "fill" my SQLDataAdapter with this code...
Dim da As New SqlDataAdapter
da.Fill(GetSearch())
I am getting this error
"The SelectCommand property has not been initialized before calling 'Fill'."
Can someone help me out here?
Yeah..I had found that and a few other examples.
Right now I have a datatable being filled with the code that is attached and I verified that the rows are there in the Watch WIndow
However...whan I'm trying to "fill" my SQLDataAdapter with this code...
Dim da As New SqlDataAdapter
da.Fill(GetSearch())
I am getting this error
"The SelectCommand property has not been initialized before calling 'Fill'."
Can someone help me out here?
Public Function GetSearch() As DataTable
Dim list As IList(Of Order) = ShippedOrdersStore()
Dim t As New DataTable()
t.Columns.Add("afsSource", GetType(String))
t.Columns.Add("afsSourceDetail", GetType(String))
t.Columns.Add("afsDate", GetType(Date))
t.Columns.Add("afsAmount", GetType(Double))
For Each s As Order In list
Dim row As DataRow = t.NewRow()
row("afsSource") = s.afsSource
row("afsSourceDetail") = s.afsSourceDetail
row("afsDate") = s.afsDate
row("afsAmount") = s.afsAmount
row("afsStatus") = s.afsStatus
t.Rows.Add(row)
Next
Return t
End Function
if you are trying to update the sql server with what is in your datatable you need to use da.update(GetSearch())
you will also need to set the da.insertcommand property.
I am assuming you created a connection to the database.
you will also need to set the da.insertcommand property.
I am assuming you created a connection to the database.
ASKER
ajb222,
man am I lost...
Attached is my insert stored procedure...
Here is my button click event...
What do I need to add/change/...fold spindle or mutilate to get this to work?
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection()
Dim da As SqlDataAdapter = New SqlDataAdapter
cn.ConnectionString = "Server=SQL02\DEV01;Databa se=northwi nd;UID=myi d;PWD=mypw d;"
cn.Open()
da.Update(GetSearch())
cn.Close()
cn.Dispose()
End Sub
man am I lost...
Attached is my insert stored procedure...
Here is my button click event...
What do I need to add/change/...fold spindle or mutilate to get this to work?
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection()
Dim da As SqlDataAdapter = New SqlDataAdapter
cn.ConnectionString = "Server=SQL02\DEV01;Databa
cn.Open()
da.Update(GetSearch())
cn.Close()
cn.Dispose()
End Sub
ALTER PROCEDURE [dbo].[sp_cfa_CollateralInsert] (
@afsSource nvarchar(16),
@afsSourceDetail nvarchar(3),
@afsDate smalldatetime,
@afsAmount money
)
AS
BEGIN
INSERT INTO [PROC_CFA].[dbo].[testCollateral]
([afsSource]
,[afsSourceDetail]
,[afsDate]
,[afsAmount])
VALUES
(@afsSource,
@afsSourceDetail,
@afsDate,
@afsAmount)
End
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great! Definately put me on the right track...I'll attach my final code set
ASKER
This is the final code set ...thanks
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim strSql As String
'This will return you all columns from the table but no rows so you can add the new ones
strSql = "Select * From proc_cfa.dbo.testCollateral where 1 = 2"
Dim dtDestination As New DataTable
Dim dtReadAdapter As New SqlClient.SqlDataAdapter(strSql, System.Configuration.ConfigurationManager.AppSettings("ConnPortal"))
dtReadAdapter.Fill(dtDestination)
dtReadAdapter.Dispose()
Dim gridTable As New DataTable
gridTable = GetSearch()
'Since you Grid was filled with a Store Procedure you need to import the rows to the Datatable manually
Dim drRow As DataRow
For Each drRow In gridTable.Rows
dtDestination.ImportRow(drRow)
Next
'This will save the Rows to your Table in SQL if there are no errors of primary/foreign key
Dim dtSaveAdapter As New SqlClient.SqlDataAdapter(strSql, System.Configuration.ConfigurationManager.AppSettings("ConnPortal"))
Dim objCommBuild As New SqlClient.SqlCommandBuilder(dtSaveAdapter)
dtSaveAdapter.Update(dtDestination)
dtSaveAdapter.Dispose()
End Sub
Public Function GetSearch() As DataTable
Dim list As IList(Of Order) = ShippedOrdersStore()
Dim t As New DataTable()
t.Columns.Add("afsSource", GetType(String))
t.Columns.Add("afsSourceDetail", GetType(String))
t.Columns.Add("afsDate", GetType(Date))
t.Columns.Add("afsAmount", GetType(Double))
For Each s As Order In list
Dim row As DataRow = t.NewRow()
row("afsSource") = s.afsSource
row("afsSourceDetail") = s.afsSourceDetail
row("afsDate") = s.afsDate
row("afsAmount") = s.afsAmount
t.Rows.Add(row)
Next
Return t
End Function
The datatable is what you would set as the datasource of the control.
Here is an example
http://support.microsoft.c