• Top Members
    Reps
    Posts
  • 834 Replies
    2585 Replies
  • 716 Replies
    2025 Replies
  • 331 Replies
    1928 Replies
Our minions are trying to make the site more secure by encasing the server in a cage made of solid SSL-ium which may throw up some issues when using the site for the next few hours. Bear with us while we weld this thing together and we are sorry for any inconvenience this may cause.

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 ):
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 "SQLEdit.aspx"
Image
( 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:
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'
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:
Image
Image
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”