• Top Members
    Reps
    Posts
  • 834 Replies
    2585 Replies
  • 713 Replies
    2021 Replies
  • 331 Replies
    1928 Replies
The site is still being updated (as of 20th Jan 2018 5PM GMT) but can be used in the meantime.

ASPX & Database: SQL Edit Items

User avatar
Kieken72
VIP - Donator
Posts: 231

ASPX & Database: SQL Edit Items

Mon Mar 07, 2011 5:55 pm

Hello because all good reavtions on previous tutorial here another



First of all make a database: Named dbPersons ( I used acces 2010 ):
[hsimg]http://img824.imageshack.us/img824/8876/sqlaccesnew.png[/hsimg]
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 ):
[hsimg]http://img135.imageshack.us/img135/9773/publish.png[/hsimg]

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:
[hsimg]http://img8.imageshack.us/img8/293/sqlbrowse.png[/hsimg]
U can also see I deleted some files. But that isn't necesairy. Then we are going to add a page named "SQLEdit.aspx"
[hsimg]http://img846.imageshack.us/img846/5218/sqladd.png[/hsimg]
( Picture form previous only name is different :) )

So now the coding can begin !
But first we will add the datareader we made in the first Tutorial we will add him here so u can see what contact u need to edit. ( If u use the one from the previous tutorial make sure there is in the While dr.read the dr("Id") is changed to dr("ID") and dr("E-Mail") to dr("EMail') dunnno;
( viewtopic.php?f=173&t=501

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:
[hsimg]http://img14.imageshack.us/img14/1852/page1r.png[/hsimg]

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'
Also the Textbox for entering the ID
So I need to add 5 Textboxes and 1 Button :

Code: Select all

<a href="reader.aspx" target="_blank">All Persons</a>
    <form id="form1" runat="server">
    <p><asp:Label ID="Label5" runat="server" Text="ID: "></asp:Label><asp:TextBox ID="ID" runat="server" CssClass="Textboxes"></asp:TextBox></p>
    <asp:Button ID="Button3" runat="server" Text="Load" onclick="Button3_Click" />
     <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>
   
    </form>

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="Edit" ID="Button2" onclick="Unnamed2_Click" />
        <asp:Button ID="Button1" runat="server" Text="Confirm" onclick="Unnamed1_Click" Visible="False" />

Now It will look like this:
[hsimg]http://img607.imageshack.us/img607/4543/codepre.png[/hsimg]
[hsimg]http://img194.imageshack.us/img194/728/designpre.png[/hsimg]
Now we will start coding =) :
On the first button for Loading the info :

Code: Select all

Protected Sub Button3_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")
        'Specifie the Database type and path
        Dim strSQL As String = "SELECT * FROM tblPersons WHERE ID=" & ID.Text
        'SQL Query
        Dim cn As New OleDbConnection(strConn)
        'Make CN ( Not open )
        Try
            cn.Open()
            'Open CN
            Dim cm As New OleDbCommand(strSQL, cn)
            'make command from SQL
            Dim dr As OleDbDataReader = cm.ExecuteReader()
            'make Reader
            If dr.HasRows() Then
                ' check if there are rows in the Db
                dr.Read()
                'He is reading the row
                Name.Text = dr("Name").ToString
                Prename.Text = dr("Prename").ToString
                EMail.Text = dr("EMail").ToString
                Country.Text = dr("Country").ToString
            Else
                'No rows are found. Add some to the DB
               
            End If
        Catch ex As Exception
            Trace.Warn(ex.Message)
            'Problem probably you have a wrong path or SQL
            Label1.Text = "Problem with database."
        Finally
            'Close connection ALWAYS
            cn.Close()
        End Try
    End Sub

Then the code for the Edit button :)

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 = "UPDATE tblPersons Set Name=@Name, Prename=@Prename, EMail=@EMail, Country=@Country WHERE ID=" & ID.Text & ";"
        'Specify SQL Here we will use "Update "
        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("@EMail", EMail.Text)
        cm.Parameters.AddWithValue("@Country", Country.Text)
       
        'set parameters


        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


Rar file:
20110307-CodenStudd-SQLEdit.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.

User avatar
LesserDemon
Just Registered
Posts: 7

Re: ASPX & Database: SQL Edit Items

Wed Mar 09, 2011 1:36 pm

Thx I was having troubles with this Sql but I found it :)
Thank You!

User avatar
hungryhounduk
VIP - Site Partner
Posts: 2870

Re: ASPX & Database: SQL Edit Items

Mon Jun 11, 2012 7:27 pm

Hey
Great stuff these tutorials

keep it up

Chris
Image

Post Reply

Return to “Tutorials”