asked on

Insert Multi Row Values From Data Grid into FoxPro table

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">
<HTML>
      <HEAD>

            <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="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
      </HEAD>
      <body MS_POSITIONING="GridLayout">
            <form runat="server">
                  <table cellSpacing="0" cellPadding="0" align="center" border="0">
                        <tr>
                              <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>
                        </tr>
                        <tr>
                              <td style="HEIGHT: 5px"> 
                              </td>
                        </tr>
                        <tr>
                              <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>
                                          <Columns>
                                                <asp:TemplateColumn HeaderText="Client">
                                                      <ItemTemplate>
                                                            <asp:Label id="lblClient" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.acc_code")%>'>
                                                            </asp:Label>
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
                                                <asp:TemplateColumn HeaderText="Run">
                                                      <ItemTemplate>
                                                            <asp:Label id="lblRoute" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.route")%>'>
                                                            </asp:Label>
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
                                                <asp:TemplateColumn HeaderText="Deal Code">
                                                      <ItemTemplate>
                                                            <asp:Label id="lblDealCode" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.dealercode")%>'>
                                                            </asp:Label>
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
                                                <asp:TemplateColumn HeaderText="Dealership Name">
                                                      <ItemTemplate>
                                                            <asp:Label id="lblName" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.name")%>'>
                                                            </asp:Label>
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
                                                <asp:TemplateColumn HeaderText="City">
                                                      <ItemTemplate>
                                                            <asp:Label id="lblCity" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.city")%>'>
                                                            </asp:Label>
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
                                                <asp:TemplateColumn HeaderText="State">
                                                      <ItemTemplate>
                                                            <asp:Label id="lblState" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.state")%>'>
                                                            </asp:Label>
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
                                                <asp:TemplateColumn HeaderText="Pieces">
                                                      <ItemTemplate>
                                                            <asp:TextBox id="txtPieces" style="Z-INDEX: 101" runat="server" Width="40px"></asp:TextBox>
                                                            <%'# Container.DataItem("stor_id") %>
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
                                          </Columns>
                                    </asp:datagrid></td>
                        </tr>
                        <tr>
                              <td align="right">
                                    <asp:Button CommandName="DoItemUpdate" id="cmdSubmit" text="Submit" runat="server" />
                              </td>
                        </tr>
                  </table>
            </form>
      </body>
</HTML>

------------------------------------------------------DailEnt.aspx.Vb-----------------------------------------------------------------------------

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.
InitializeComponent()
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
UserVal()
End If
End Sub

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

Trim(RqUser)
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
BindDataGrid()
End Sub

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

' Create a Command object with select statement
Dim strSelect As [String] = "SELECT runs.acc_code,runs.route,all_dealers.dealercode,all_dealers.name,all_dealers.city,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
DailGrid.DataBind()
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
VFP7Connection.Open()

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

' Execute the command
VFP7UpdateCommand.ExecuteNonQuery()

' Close the connection
VFP7Connection.Close()

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

UserVal()
End Sub 'DoItemUpdate

End Class

sachiek

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?

Sachi

slip9

ASKER

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.

sachiek

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
http://www.asp101.com/articles/lee/multiupdate/default.asp
http://www.tridenttech.edu/project/updatemultiplerecords.html

ASKER CERTIFIED SOLUTION

sachiek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial