Solved

Telerik Grid append all rows to SQL

Posted on 2010-11-11
5
611 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
  • 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now