Fetch and put a data table

[dcwm]

I have been trying to split up working code so as to have it in two bits [ GetTheInfo and PutTheInfo ] in app_code, and so that I can call up the data from any aspx.vb page with the minimum code writing on the aspx.vb page.

I've managed to get the datatable out of the database, but I can't figure out how to get it back there. I seem to have a bit of a blind spot about how it all works. I should be grateful for some advice.

In App_code, I have two methods, one of which works and another one which is obviously rot.

<code>


Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO
Imports System.Web.UI.HtmlControls
Imports System.Web.UI.WebControls


Public Class GetAndPut

Public Function GetTheInfo() As DataTable
'
' GET MATERIAL FROM SQL ON REMOTE SERVER
'
Dim conS As String
conS = ConfigurationManager.ConnectionStrings("TieKnot").ConnectionString
'
Dim RemoteTableName As String
RemoteTableName = "BulkPurchase"
'
Dim qS As String = "SELECT * FROM " & RemoteTableName
'
Dim sqlCon As SqlConnection = New SqlConnection(conS)
sqlCon.Open()
'
Dim daX As SqlDataAdapter = New SqlDataAdapter(qS, sqlCon)
Dim dsKor As DataSet = New DataSet
daX.FillSchema(dsKor, SchemaType.Source, RemoteTableName)
daX.Fill(dsKor, RemoteTableName)
'
Dim LT As DataTable
LT = dsKor.Tables(RemoteTableName)
'
sqlCon.Close()
'
Return LT
'
End Function

Public Sub PutTheInfo(ByVal LT As DataTable)
'
' SEND MATERIAL BACK TO SQL ON REMOTE SERVER
'
Dim conS As String
conS = ConfigurationManager.ConnectionStrings("TieKnot").ConnectionString
Dim RemoteTableName As String
RemoteTableName = "BulkPurchase"
Dim qS As String = "SELECT * FROM " & RemoteTableName
Dim sqlCon As SqlConnection = New SqlConnection(conS)
Dim daX As SqlDataAdapter = New SqlDataAdapter(qS, sqlCon)
Dim dsKor As DataSet = New DataSet
'
Dim objcommandbuilder As New SqlCommandBuilder(daX)
daX.Update(dsKor, RemoteTableName)
'
sqlCon.Close()
'
End Sub

</code>

On a Page20.aspx.vb I called up the information as follows and this works OK

<code>

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
'
' Get the existing table
'
Dim LocalTable As DataTable
Dim z As New GetAndPut()
LocalTable = z.GetTheInfo()
'
Dim MyRow As DataRow ' *******
Dim MyCol As DataColumn
Dim aja As String, ajb As String
'
MyRow = LocalTable.Rows.Item(2)
MyCol = LocalTable.Columns.Item(2)
aja = MyRow(MyCol)
Label2.Text = aja ' OK TO HERE
'
ajb = "roffle"
MyRow(MyCol) = ajb
'
z.PutTheInfo(LocalTable) ' LAST THREE LINES AND/OR PutTheInfo are NBG
'
End Sub

</code>

[SouthwaterDave]

If you bind your DataList, GridView or ListView to an ObjectDataSource in your .aspx file then you may not need any code at all in the corresponding .aspx.vb file.

Here are a few code snippets to show you what I mean.

In your .aspx file:


<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
TypeName="MyDataSource" SelectMethod="GetPeople" UpdateMethod="UpdatePerson" />

<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1" DataKeyNames="Name">
<Columns><asp:CommandField ShowEditButton="True" ButtonType="Button" /></Columns>
</asp:GridView>


In your .vb file in the App_Code folder:


Imports System.Data

Public Class MyDataSource
Public Function GetPeople() As DataTable
Dim dt As New DataTable

dt.Columns.Add("Name", System.Type.GetType("System.String"))
dt.Columns.Add("Age", System.Type.GetType("System.Int32"))

Dim r As DataRow = dt.NewRow
r("Name") = "Burt"
r("Age") = 42
dt.Rows.Add(r)

Return dt
End Function

Public Sub UpdatePerson(ByVal Name As String, ByVal Age As Integer)
'Build and execute the SQL update statement here...
End Sub
End Class