ASP.NET(vb.net) & Microsoft Access Multiple Add/Insert Rows Record

Author: admin | Posted: 13-06-2010 | Category: ASP.NET(vb.net) & Microsoft Access (System.Data.OleDb)

ASP.NET(vb.net) & Microsoft Access Multiple Add/Insert Rows Record - This is example scripts how to use ASP.NET add insert new multiple rows or record into Microsoft Access (.mdb) database.

ShotDev Focus:
- ASP.NET(vb.net) & Microsoft Access Multiple Add/Insert Rows Record

Example

AspNetAccessMultiAddInsert.aspx

<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Page Language="VB" %>
<script runat="server">
Dim objConn As OleDbConnection
Dim objCmd As OleDbCommand
Dim strSQL As String

Sub Page_Load(sender As Object, e As EventArgs)
IF Not Page.IsPostBack() Then
ShowGridViewRows()
End IF
End Sub

Sub ShowGridViewRows()
Dim myRows(4)
myGridView.DataSource = myRows
myGridView.DataBind()
End Sub

Sub Button1_Click(sender As Object, e As EventArgs)
Dim chkCusID As CheckBox
Dim i As Integer

Dim objConn As New OleDbConnection
Dim objCmd As New OleDbCommand
Dim strConnString,strSQL As String

Dim strCustomerID As TextBox
Dim strName As TextBox
Dim strEmail As TextBox
Dim strCountryCode As TextBox
Dim strBudget As TextBox
Dim strUsed As TextBox

strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("database/mydatabase.mdb")&";Jet OLEDB:Database Password=;"
objConn.ConnectionString = strConnString
objConn.Open()

Me.lblStatus.Text = ""

For i = 0 To myGridView.Rows.Count - 1
strCustomerID = CType(myGridView.Rows(i).FindControl("txtAddCustomerID"),TextBox)
strName = CType(myGridView.Rows(i).FindControl("txtAddName"),TextBox)
strEmail = CType(myGridView.Rows(i).FindControl("txtAddEmail"),TextBox)
strCountryCode = CType(myGridView.Rows(i).FindControl("txtAddCountryCode"),TextBox)
strBudget = CType(myGridView.Rows(i).FindControl("txtAddBudget"),TextBox)
strUsed = CType(myGridView.Rows(i).FindControl("txtAddUsed"),TextBox)

'*** IF Not Empty Value ***'
IF Trim(strCustomerID.Text) <> "" _
AND Trim(strName.Text) <> "" _
AND Trim(strEmail.Text) <> "" _
AND Trim(strCountryCode.Text) <> "" _
AND Trim(strBudget.Text) <> "" _
AND Trim(strUsed.Text) <> "" Then

'*** Insert Statement ***'
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
" VALUES " & _
" ('" & strCustomerID.Text & "','" & strName.Text & "','" & strEmail.Text & "', " & _
" '" & strCountryCode.Text & "','" & strBudget.Text & "','" & strUsed.Text & "')"

With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
End With

Me.pnlAdd.Visible = False

Try
objCmd.ExecuteNonQuery()
Me.lblStatus.Text = "Record Insert Sucessful."
Me.lblStatus.Visible = True
Catch ex As Exception
Me.lblStatus.Visible = True
Me.lblStatus.Text = "Record Cannot Insert : Error ("& ex.Message &")"
End Try

End IF
Next

objConn.Close()
objConn = Nothing

End Sub

</script>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Panel id="pnlAdd" runat="server">
<asp:GridView id="myGridView" runat="server" AutoGenerateColumns="False" >

<Columns>

<asp:TemplateField HeaderText="CustomerID">
<ItemTemplate>
<asp:TextBox id="txtAddCustomerID" size="5" runat="server"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:TextBox id="txtAddName" size="10" runat="server"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Email">
<ItemTemplate>
<asp:TextBox id="txtAddEmail" size="20" runat="server"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="CountryCode">
<ItemTemplate>
<asp:TextBox id="txtAddCountryCode" size="2" runat="server"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Budget">
<ItemTemplate>
<asp:TextBox id="txtAddBudget" size="6" runat="server"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Used">
<ItemTemplate>
<asp:TextBox id="txtAddUsed" size="6" runat="server"></asp:TextBox>
</ItemTemplate>

</asp:TemplateField>

</Columns>
</asp:GridView>
<br />
<asp:Button id="Button1" onclick="Button1_Click" runat="server" Text="Submit"></asp:Button>
<hr />
</asp:Panel>
<asp:Label id="lblStatus" runat="server" visible="False"></asp:Label>
</form>
</body>
</html>

Screenshot

.
.
.
Download this script.