Insert Multi Row Values From Data Grid into FoxPro table

Posted on 2005-04-05
Last Modified: 2008-01-09
Let me first start off with the objective I am trying for. There are two tables I am displaying data from and the onload BindDataGrid() works great, I need the user to be able to see multiple rows of data and a few text boxes next to it to enter piece counts and things like that so when there finished they click one submit button and it will take all of the data displayed in the grid plus the user input and do a insert into a totaly new table and data stamp. I can problay figure out the date stamp part but what im having trouble with is getting a button to do a multi-row submit. PLEASE HELP! thanks in advance.

----------------------- This is DailEnt.aspx--------------------------------------------------------------------------------------------

<%@ Page Language="vb" Codebehind="DailEnt.aspx.vb" Inherits="MenuProg.DailEnt"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

            <META http-equiv="Content-Type" content="text/html; charset=windows-1252">
            <meta content="Microsoft Visual Studio.NET 7.0" name="GENERATOR">
            <meta content="Visual Basic 7.0" name="CODE_LANGUAGE">
            <meta content="JavaScript" name="vs_defaultClientScript">
            <meta content="" name="vs_targetSchema">
      <body MS_POSITIONING="GridLayout">
            <form runat="server">
                  <table cellSpacing="0" cellPadding="0" align="center" border="0">
                              <td style="BORDER-TOP: #b6b6b6 thin solid; FONT-SIZE: 20px; COLOR: #ce0000; BORDER-BOTTOM: #3d3c3c thin solid; FONT-FAMILY: Concielian; TEXT-ALIGN: left"><asp:label id="lblTitle" runat="server"></asp:label></td>
                              <td style="HEIGHT: 5px">&nbsp;
                              <td><asp:datagrid id="DailGrid" style="Z-INDEX: 101" runat="server" AutoGenerateColumns="False" OnUpdateCommand="DoItemUpdate">
                                          <HeaderStyle Font-Bold="True" HorizontalAlign="Center" ForeColor="WhiteSmoke" BorderColor="Black" BackColor="#CE0000"></HeaderStyle>
                                                <asp:TemplateColumn HeaderText="Client">
                                                            <asp:Label id="lblClient" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.acc_code")%>'>
                                                <asp:TemplateColumn HeaderText="Run">
                                                            <asp:Label id="lblRoute" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.route")%>'>
                                                <asp:TemplateColumn HeaderText="Deal Code">
                                                            <asp:Label id="lblDealCode" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.dealercode")%>'>
                                                <asp:TemplateColumn HeaderText="Dealership Name">
                                                            <asp:Label id="lblName" runat="server" Text='<%# DataBinder.Eval(Container, "")%>'>
                                                <asp:TemplateColumn HeaderText="City">
                                                            <asp:Label id="lblCity" runat="server" Text='<%# DataBinder.Eval(Container, "")%>'>
                                                <asp:TemplateColumn HeaderText="State">
                                                            <asp:Label id="lblState" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.state")%>'>
                                                <asp:TemplateColumn HeaderText="Pieces">
                                                            <asp:TextBox id="txtPieces" style="Z-INDEX: 101" runat="server" Width="40px"></asp:TextBox>
                                                            <%'# Container.DataItem("stor_id") %>
                              <td align="right">
                                    <asp:Button CommandName="DoItemUpdate" id="cmdSubmit" text="Submit" runat="server"  />


Imports System
Imports System.Collections
Imports System.ComponentModel
Imports System.Data
Imports System.Data.Common
Imports System.Data.OleDb
Imports System.Drawing
Imports System.Web
Imports System.Web.SessionState
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls

Public Class DailEnt
    Inherits System.Web.UI.Page
    Protected WithEvents lblTitle As System.Web.UI.WebControls.Label
    Protected WithEvents DailGrid As System.Web.UI.WebControls.DataGrid
    Protected VFP7DataAdapter As System.Data.OleDb.OleDbDataAdapter
    Protected VFP7SelectCommand As System.Data.OleDb.OleDbCommand
    Protected VFP7UpdateCommand As System.Data.OleDb.OleDbCommand
    Protected VFP7Connection As System.Data.OleDb.OleDbConnection
    Protected VFP7DataView As System.Data.DataView
    Protected strUpdateSelect As String
    Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
    Protected strConnection As String = "provider=VFPOLEDB.1 ;data source='C:\Inetpub\wwwroot\MenuProg\DATA\dealers'"
    Protected WithEvents cmdSubmit As System.Web.UI.WebControls.Button
    Public SlAcc As String

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Page.IsPostBack = False Then
        End If
    End Sub

    Public Sub UserVal()
        Dim RqUser As String
        Dim UsName As String
        RqUser = User.Identity.Name

        Select Case RqUser
            Case "admin"
                'response.write("Admin has been selected")
            Case "fordnj"
                SlAcc = "FC"
                lblTitle.Text = "Ford NJ Daily Entry"
            Case "nissanmd"
                '  SlUser = 10 no number
            Case "benznj"
                SlAcc = "EC"
                lblTitle.Text = "Benz NJ Daily Entry"
            Case "toyotanj"
                SlAcc = "TC"
                lblTitle.Text = "Toyota NJ Daily Entry"
            Case "chryslerny"
                SlAcc = "CN"
                lblTitle.Text = "Chrysler NY Daily Entry"
            Case "nissanfl"
                SlAcc = "NF"
                lblTitle.Text = "Nissan FL Daily Entry"
            Case "nissansc"
                SlAcc = "NG"
                lblTitle.Text = "Nissan SC Daily Entry"
            Case "bmwnj"
                SlAcc = "BC"
                lblTitle.Text = "BMW NJ Daily Entry"
            Case "benzfl"
                SlAcc = "EF"
                lblTitle.Text = "Benz FL Daily Entry"
            Case "nissannj"
                SlAcc = "NC"
                lblTitle.Text = "Nissan NJ Daily Entry"
            Case "nissantn"
                SlAcc = "NT"
                lblTitle.Text = "Nissan TN Daily Entry"
        End Select
    End Sub

    Public Sub BindDataGrid()
        ' Make connection to VFP database
        Dim VFP7Connection As New OleDbConnection()
        VFP7Connection.ConnectionString = strConnection

        ' Create a Command object with select statement
        Dim strSelect As [String] = "SELECT runs.acc_code,runs.route,all_dealers.dealercode,,,all_dealers.state FROM runs INNER JOIN all_dealers ON runs.route = LEFT(all_dealers.route,2) WHERE runs.acc_code = '" & (SlAcc) & "' AND all_dealers.masteracct = '" & (SlAcc) & "' ORDER BY runs.route, all_dealers.sequence"
        Dim VFP7SelectCommand As New OleDbCommand(strSelect, VFP7Connection)

        ' Create a DataAdapter
        Dim VFP7DataAdapter As New OleDbDataAdapter()
        VFP7DataAdapter.SelectCommand = VFP7SelectCommand

        ' Create a DataSet
        Dim VFP7DataSet As New DataSet()

        ' Fill the DataSet with table information
        VFP7DataAdapter.Fill(VFP7DataSet, "RUNS, ALL_DEALERS")

        ' Bind to DataGrid
        Dim VFP7DataView As New DataView(VFP7DataSet.Tables("RUNS, ALL_DEALERS"))
        DailGrid.DataSource = VFP7DataView
    End Sub 'BindDataGrid

    '  Public Sub DoItemEdit(ByVal objSource As Object, ByVal objArgs As DataGridCommandEventArgs)
    ' Sets the Edit index of the DataGrid to the selected row
    ' Starts editing mode
    '  dgrCustomers.EditItemIndex = objArgs.Item.ItemIndex

    '   BindDataGrid()
    'End Sub 'DoItemEdit

    '  Public Sub DoItemCancel(ByVal objSource As Object, ByVal objArgs As DataGridCommandEventArgs)
    ' Reset the Edit index of the DataGrid to -1 which exits out of editing mode
    '     dgrCustomers.EditItemIndex = -1

    '    BindDataGrid()
    'End Sub 'DoItemCancel

    Public Sub DoItemUpdate(ByVal objSource As Object, ByVal objArgs As DataGridCommandEventArgs)
        ' Create objects to hold references to the edited row
        Response.Write(" Test ")
        Dim objClient As Label
        Dim objRoute As Label
        Dim objDealCode As Label
        Dim objName As Label
        Dim objCity As Label
        Dim objState As Label
        Dim objPieces As TextBox

        ' Assign the objects to each object in the edited row
        objClient = CType(objArgs.Item.FindControl("lblClient"), Label)
        objRoute = CType(objArgs.Item.FindControl("lblRoute"), Label)
        objDealCode = CType(objArgs.Item.FindControl("lblDealCode"), Label)
        objName = CType(objArgs.Item.FindControl("lblName"), Label)
        objCity = CType(objArgs.Item.FindControl("lblCity"), Label)
        objState = CType(objArgs.Item.FindControl("lblState"), Label)
        objPieces = CType(objArgs.Item.FindControl("txtPieces"), TextBox)

        ' Create update select statement
        strUpdateSelect = "INSERT INTO DailyProc (client,route,dealercode,name,city,state,pieces,cages,pallets,totes) VALUES ('" & objClient.Text & "','" & objRoute.Text & "','" & objDealCode.Text & "','" & objName.Text & "','" & objCity.Text & "','" & objState.Text & "','" & objPieces.Text & "')"
        'SET Company='" + objCompanyCtrl.Text + "', " + "Contact='" + objContactCtrl.Text + "' " + "WHERE Cust_ID='" '+ objCustIDCtrl.Text + "'" OLD Syntax

        ' Create a new connection
        Dim VFP7Connection As New OleDbConnection()
        VFP7Connection.ConnectionString = strConnection

        ' Create a Command object with select statement
        Dim VFP7UpdateCommand As New OleDbCommand(strUpdateSelect, VFP7Connection)

        ' Execute the command

        ' Close the connection

        ' Reset the Edit index of the DataGrid to -1 which exits out of editing mode
        DailGrid.EditItemIndex = -1

    End Sub 'DoItemUpdate

End Class
Question by:slip9
    LVL 11

    Expert Comment

    What do you mean by multiple row submit? I could'nt get you.
    Is that mean you want to update more than one row at one point into foxpro table?


    Author Comment

    Yes exactly, so say there are 20 rows in a datagrid and there is a blank text box next to each row and a user entered 1 2 3 4 going down all of the data shown would be entered. Thanks.
    LVL 11

    Expert Comment

    Below are the examples which you can take as a guideline to create your datagrid or repeater or datalist

    Update Multiple Records with the DataGrid, DataList and Repeater

    LVL 11

    Accepted Solution

    Further more there are few other samples too

    Multi-row Editing in the ASP.NET DataGrid...

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    732 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

    18 Experts available now in Live!

    Get 1:1 Help Now