Hi Community,
As the Title indicates, I'm having trouble trying to bulk insert records using a GridiView. I have searched for tutorials for a about a few weeks and the simplest one I could find was this: http://weblogs.asp.net/manojkdotnet/archive/2010/01/26/bulk-insert-using-gridview-and-sql-server-xml.aspx.
What I'm trying to achieve is that for every Site Name that the GridView displays, the user should be able to input information such as their Electricity, Gas, and wastage. This is a mock screen I made to help understand my situation: http://img3.imageshack.us/img3/8651/bulkinsertss.jpg
These are the tables that I'm working with:
Table: Site
SITE_ID
ORGANISATION_ID
SITE_NAME
ADDRESS_ID
CREATE_DATETIME
STATUS
Table: Site_Details
SITE_DETAILS_ID
SITE_ID
ELEC_PERIOD
ELEC_CONSUMED
ELEC_RENEWABLE
GAS_PERIOD
GAS_CONSUMED
CREATE_DATETIME
STATUS
Table: Site_Waste
SITE_WASTE_ID
SITE_ID
WASTE_TYPE
WASTE_PERIOD
WASTE_QUANTITY
WASTE_RECYCLED
WASTE_UNIT
CREATE_DATETIME
STATUSI was trying to keep it simple to get it to work so I only tried to make the "ELEC_PERIOD,
ELEC_CONSUMED, ELEC_RENEWABLE" columns to work. This is my code:
BulkInsertTest.aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="BulkInsertTest.aspx.vb" Inherits="Default2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Bulk Insert Test</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server"/>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:GridView ID="GridView1" runat="server" BackColor="#CCCCCC" Visible="true"
BorderColor="#999999" BorderStyle="Solid" BorderWidth="3px" CellPadding="4"
CellSpacing="2" EnableModelValidation="True" ForeColor="Black" AutoGenerateColumns="False">
<Columns>
<asp:TemplateField HeaderText="Site">
<ItemTemplate>
<asp:Label ID="lblSiteName" runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Period">
<ItemTemplate>
<asp:TextBox ID="txtPeriod" runat="server"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Amount in KW/h (Average)">
<ItemTemplate>
<asp:TextBox ID="txtAmount" runat="server"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Renewable Energy (%)">
<ItemTemplate>
<asp:TextBox ID="txtRenewable" runat="server"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#CCCCCC" />
<HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />
<RowStyle BackColor="White" />
<SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
</asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>
<br />
<br />
<asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="btnInsert_Click" />
<br />
<br />
<asp:Label ID="lblMsg" runat="server"></asp:Label>
</div>
</form>
</body>
</html>I know the GridView here looks nothing like the Screenshot I provided but at this stage I would rather get the functionality working rather than getting the design done.
This is the VB code I have. I Googled a C# to VB converter to try and understand the syntax of the tutorial. This is what I came up with:
Imports System.Configuration
Imports System.Data
Imports System.Linq
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.HtmlControls
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Xml.Linq
Imports System.Text
Imports System.Data.SqlClient
Imports System.Web.Configuration
Partial Class Default2
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
If Not Page.IsPostBack Then
InsertEmptyRow()
End If
End Sub
Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As EventArgs)
InsertCustomer()
End Sub
Private Sub InsertEmptyRow()
Dim dt As New DataTable()
Dim dr As DataRow = Nothing
dt.Columns.Add(New DataColumn("SITE_NAME", GetType(String)))
dt.Columns.Add(New DataColumn("ELEC_PERIOD", GetType(String)))
dt.Columns.Add(New DataColumn("ELEC_CONSUMED", GetType(String)))
dt.Columns.Add(New DataColumn("ELEC_RENEWABLE", GetType(String)))
For i As Integer = 0 To GridView1.Rows.Count - 1
dr = dt.NewRow()
dr("SITE_NAME") = String.Empty
dr("ELEC_PERIOD") = String.Empty
dr("ELEC_CONSUMED") = String.Empty
dr("ELEC_RENEWABLE") = String.Empty
dt.Rows.Add(dr)
Next
GridView1.DataSource = dt
GridView1.DataBind()
End Sub
Private Sub InsertCustomer()
Dim sb As New StringBuilder()
sb.Append("<root>")
For i As Integer = 0 To GridView1.Rows.Count - 1
Dim lblSiteName As Label = TryCast(GridView1.Rows(i).FindControl("lblSiteName"), Label)
Dim txtPeriod As TextBox = TryCast(GridView1.Rows(i).FindControl("txtPeriod"), TextBox)
Dim txtAmount As TextBox = TryCast(GridView1.Rows(i).FindControl("txtAmount"), TextBox)
Dim txtRenewable As TextBox = TryCast(GridView1.Rows(i).FindControl("txtRenewable"), TextBox)
If lblSiteName.Text.Length <> 0 Then
sb.Append("<row Name='" & lblSiteName.Text.Trim() & "' Position='" & txtPeriod.Text.Trim() & "' City='" & txtAmount.Text.Trim() & "' State='" & txtRenewable.Text.Trim() & "'/>")
End If
Next
sb.Append("</root>")
Dim conStr As String = WebConfigurationManager.ConnectionStrings("CarbonEmissionsConnectionString").ConnectionString
Dim con As New SqlConnection(conStr)
Dim sql As String
sql = "SELECT SITE.SITE_NAME, SITE_DETAILS.ELEC_PERIOD, SITE_DETAILS.ELEC_CONSUMED, SITE_DETAILS.ELEC_RENEWABLE FROM SITE, SITE_DETAILS WHERE SITE_NAME = @SITE_NAME"
Dim cmd As New SqlCommand("InsertSiteDetails", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@XMLSiteDetails", sb.ToString())
Try
Using con
con.Open()
cmd.ExecuteNonQuery()
End Using
lblMsg.Text = "Record(s) Inserted successfully"
lblMsg.ForeColor = System.Drawing.Color.Green
Catch ex As Exception
lblMsg.Text = "Error Occured"
lblMsg.ForeColor = System.Drawing.Color.Red
End Try
End Sub
End ClassI've never used Procedures before but I thought it was worth a shot and it looked simple on the tutorial. This is what I have:
InsertSiteDetails.txt
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[InsertSiteDetails]
(
@XMLSiteDetails XML
)
AS
BEGIN
INSERT INTO SITE_DETAILS (ELEC_PERIOD, ELEC_CONSUMED, ELEC_RENEWABLE)
SELECT TempSiteDetails.Item.value('@ELEC_PERIOD', 'VARCHAR(50)'),
TempSiteDetails.Item.value('@ELEC_CONSUMED', 'NUMERIC(18, 2)'),
TempSiteDetails.Item.value('@ELEC_RENEWABLE', 'NUMERIC(18, 2)'),
FROM @XMLSiteDetails.nodes('/root/row') AS TempSiteDetails(Item)
RETURN 0
ENDAny assistance with this problem will be greatly appreciated. Also don't hesitate to ask any questions as I would be more than happy to answer them to the best of my ability.
Thank you for your time.