ASPX & Database: SQL Add new Items

Tutorials and code examples using ASP and ASP.Net
6 posts Page 1 of 1
Contributors
User avatar
Kieken72
VIP - Donator
VIP - Donator
Posts: 231
Joined: Sun May 02, 2010 10:38 am

ASPX & Database: SQL Add new Items
Kieken72
Hello because all good reavtions on previous tutorial here another :D


Original:
First of all make a database: Named dbPersons ( I used acces 2010 ):
Image
Edit: Don't use "-" or spaces only"_" and also when the first colum is "Id" chance it to " ID" because web developer sometimes may give an error.

Then save the table as "tblPersons". Then don't just save the db but publish ( if using acces 2007 or a later version ):
Image

Then go to you're microsoft Visual Web Developer and Create a new aspx project. I named it "20110304-Codenstuff-aspxdatareader"
then u search you're db file " dbPersons" and drag it in App_Data:
Image
U can also see I deleted some files. But that isn't necesairy. Then we are going to add a page named "SQLadd.aspx"
Image

So now the coding can begin !
Lets start with getting the messy stuff out of the page! Delete the first sententce and replace it with:
Code: Select all
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 End Sub
</script>
So u'll gain this:
Image

Now we are first going some textboxes. It's the amount of tables in youre db in mine I've got 4:"Name, Prename, E-Mail and Country'
So I need to add 4 Textboxes:
Code: Select all
<div>
    <p><asp:Label ID="Label2" runat="server" Text="Name: "></asp:Label><asp:TextBox ID="Name" runat="server" CssClass="Textboxes"></asp:TextBox></p>
    <p><asp:Label ID="Label1" runat="server" Text="Prename: "></asp:Label><asp:TextBox ID="Prename" runat="server" CssClass="Textboxes"></asp:TextBox></p>
    <p><asp:Label ID="Label3" runat="server" Text="E-Mail: "></asp:Label><asp:TextBox ID="EMail" runat="server" CssClass="Textboxes"></asp:TextBox></p>
    <p><asp:Label ID="Label4" runat="server" Text="Country: "></asp:Label><asp:TextBox ID="Country" runat="server" CssClass="Textboxes"></asp:TextBox></p>
     </div>
U see I made a css class beacuse its more professional ;) Here is the css :)
Code: Select all
<style type="text/css">
        .Textboxes {position:absolute; left:75px; }
    </style>
Add this in the <head> tag ;)

Now also I've made 2 Submit buttons one to add and then a confirm one ;) :
Code: Select all
<asp:button runat="server" text="Add" ID="Button2" onclick="Unnamed2_Click" />
        <asp:Button ID="Button1" runat="server" Text="Confirm" onclick="Unnamed1_Click" Visible="False" />
Now It will look like this:
Image
Image
Now we will start coding :) :
Code: Select all
Protected Sub Unnamed1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                 Server.MapPath("App_Data\dbPersons.mdb")
        'Specify path of Db and Db Type
        Dim cn As New OleDbConnection(strConn)
        'Make CN ( Not open )
        Dim strSQL As String = "INSERT INTO tblPersons (Name, Prename, E-Mail, Country) VALUES (@Name,@Prename,@E-Mail,@Country);"
        'Specify SQL Here we will use "INSERT INTO "
        Dim cm As New OleDbCommand(strSQL, cn)
        'Create the command to do 
        cn.Open()
        'open conncetion
        cm.Parameters.AddWithValue("@Name", Name.Text)
        cm.Parameters.AddWithValue("@Prename", Prename.Text)
        cm.Parameters.AddWithValue("@E-Mail", EMail.Text)
        cm.Parameters.AddWithValue("@Country", Country.Text)
        ' Set parameters we specifyd in the sql


        cm.ExecuteNonQuery()
        'excute the command
        
        cn.Close()
        'close connection
        
        Name.Text = ""
        Prename.Text = ""
        EMail.Text = ""
        Country.Text = ""
        'Empty the textboxes
        Button1.Visible = False
        Button2.Visible = True
        'make add buttons visible and hide the other
    End Sub
    Protected Sub Unnamed2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Button1.Visible = True
        Button2.Visible = False
        'make confirm buttons visible and hide the other
    End Sub
Or
Code: Select all
Protected Sub Unnamed1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                 Server.MapPath("App_Data\dbPersons.mdb")
        'Specify path of Db and Db Type
        Dim cn As New OleDbConnection(strConn)
        'Make CN ( Not open )
        Dim strSQL As String = "INSERT INTO tblPersons (Name, Prename, E-Mail, Country) VALUES ('" & Name.Text & "','" & Pename.text & "','" & EMail.text & "','" & Country.text & '");"
        'Specify SQL Here we will use "INSERT INTO "
        Dim cm As New OleDbCommand(strSQL, cn)
        'Create the command to do 
        cn.Open()
        'open connection
        cm.ExecuteNonQuery()
        'excute the command
        
        cn.Close()
        'close connection
        
        Name.Text = ""
        Prename.Text = ""
        EMail.Text = ""
        Country.Text = ""
        'Empty the textboxes
        Button1.Visible = False
        Button2.Visible = True
        'make add buttons visible and hide the other
    End Sub
    Protected Sub Unnamed2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Button1.Visible = True
        Button2.Visible = False
        'make confirm buttons visible and hide the other
    End Sub
Here we are enter the parameters directly into the sql ( for string = use ' ' for integer just put it in.
Then u can try it ;)
:D

New Database:
dbPersons.zip
Zip file:
20110305-Codenstuff-SQLAdd.zip
Rar file:
20110305-Codenstuff-SQLAdd.rar
Credits:
My teacher who taught me and my class.
You do not have the required permissions to view the files attached to this post.
Last edited by Kieken72 on Mon Mar 07, 2011 9:09 am, edited 2 times in total.
User avatar
Sh4d0w009
Just Registered
Just Registered
Posts: 2
Joined: Sat Mar 05, 2011 1:01 pm

Nice tutorial!! :)

Sh4d0w009
User avatar
Scottie1972
Codenstuff Elite
Codenstuff Elite
Posts: 953
Joined: Thu Jan 14, 2010 5:48 am

Nice tutorial.
I play around with ASPX and VWD put i always seem to have trouble using the database options.
Your tutorial did help me figure something out.

Although, I like using the Visual series of application "SDK's VWD is difficult to use. So seeing something like this is very helpful.

Scottie1972
Image
User avatar
Kieken72
VIP - Donator
VIP - Donator
Posts: 231
Joined: Sun May 02, 2010 10:38 am

Scottie1972 wrote:
Nice tutorial.
I play around with ASPX and VWD put i always seem to have trouble using the database options.
Your tutorial did help me figure something out.

Although, I like using the Visual series of application "SDK's VWD is difficult to use. So seeing something like this is very helpful.

Scottie1972
Thx for the comment,
and I'm working on the other SQL Features to "Edit" and to " Delete" Maybe i can finish them by tomorrow :)
User avatar
Sh4d0w009
Just Registered
Just Registered
Posts: 2
Joined: Sat Mar 05, 2011 1:01 pm

Hi Kieken72

I've some troubles with the database. I put your code in my project but when i am debugging, I get always this problem:
Image

This is my source code:
Code: Select all
Protected Sub Unnamed1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                  Server.MapPath("App_Data\bib.mdb")
        Dim cn As New OleDbConnection(strConn)
        Dim strSQL As String = "INSERT INTO tblLeden (Voornaam, Naam, Adres, Gemeente, Postcode, Username, Password) VALUES (@Voornaam,@Naam,@Adres,@Gemeente,@Postcode,@Username,@Password);"
        Dim cm As New OleDbCommand(strSQL, cn)
       
        cn.Open()
        cm.Parameters.AddWithValue("@Voornaam", txtvoornaam.Text)
        cm.Parameters.AddWithValue("@Naam", txtnaam.Text)
        cm.Parameters.AddWithValue("@Adres", txtadres.Text)
        cm.Parameters.AddWithValue("@Postcode", txtgemeente.Text)
        cm.Parameters.AddWithValue("@Gemeente", txtpostcode.Text)
        cm.Parameters.AddWithValue("@Username", txtusername.Text)
        cm.Parameters.AddWithValue("@Password", txtpassword1.Text)


        cm.ExecuteNonQuery()
        'Naam werd toegevoegd
        cn.Close()
        txtvoornaam.Text = ""
        txtnaam.Text = ""
       
        txtadres.Text = ""
        txtgemeente.Text = ""
        txtpostcode.Text = ""
        txtusername.Text = ""
        txtpassword1.Text = ""
        txtpassword2.Text = ""
        Button1.Visible = False
        bevestig.Visible = True
        
    End Sub
    Protected Sub Unnamed2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Button1.Visible = True
        bevestig.Visible = False
        
    End Sub
Can plz some1 help me out?

Thx
Sh4d0w009
User avatar
Kieken72
VIP - Donator
VIP - Donator
Posts: 231
Joined: Sun May 02, 2010 10:38 am

Edit:
For the one who download also download the new database and put the db into "App_Data"
6 posts Page 1 of 1
Return to “Tutorials”