Solved

Telerik Grid append all rows to SQL

Posted on 2010-11-11
5
616 Views
Last Modified: 2012-05-10
I have a Telerik TadGrid that is getting populated fine from SQL.

How can I take all the data in that grid and send it to a table in SQL?

There will be between 2 and 100 lines in the grids.
Public Sub getTestData()
        Dim objConn1 As SqlConnection
        objConn1 = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnPortal"))
        Dim oCom1 As SqlCommand
        oCom1 = New SqlCommand
        oCom1.Connection = objConn1
        oCom1.CommandText = "sp_cfa_TestCollateral"
        oCom1.CommandType = CommandType.StoredProcedure
        Dim dA As SqlDataAdapter = New SqlDataAdapter(oCom1)
        Dim myDataTable As DataTable = New DataTable()
        objConn1.Open()
        Try
            dA.Fill(myDataTable)
        Finally
            objConn1.Close()
        End Try
        grdCollateral.DataSource = myDataTable.DefaultView
        grdCollateral.GroupingSettings.CaseSensitive = False
       
        objConn1.Dispose()
        oCom1.Dispose()
        oCom1 = Nothing
    End Sub

Open in new window

0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 34113796
The destination table in SQL have the same columns that your Tadgrid? you are going to export all the columns from your Tadgrid?
0
 

Author Comment

by:lrbrister
ID: 34114354
gamarrojgq
Yes
0
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 500 total points
ID: 34114580
Ok, you can try this


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 YOUDESTINATIONTABLE WHERE 1=2 "

        Dim dtDestination As DataTable
        Dim dtReadAdapter As New SqlClient.SqlDataAdapter(strSql, System.Configuration.ConfigurationManager.AppSettings("ConnPortal"))
        dtReadAdapter.Fill(dtDestination)
        dtReadAdapter.Dispose()

        Dim gridTable As DataTable
        gridTable = CType(grdCollateral.DataSource, DataTable)

        '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()

Open in new window

0
 

Author Closing Comment

by:lrbrister
ID: 34115349
Wonderful...got me on the right track.  I'll add my final code
0
 

Author Comment

by:lrbrister
ID: 34115353
Final solution is attached...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

Open in new window

0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 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