Codenstuff.com: ASPX & Database: SQL Edit Items

ASPX & Database: SQL Edit Items

3 posts Page 1 of 1

Post Reply

Contributors

Kieken72
VIP - Donator

Posts: 231

Joined: Sun May 02, 2010 10:38 am

ASPX & Database: SQL Edit Items
Kieken72
Mon Mar 07, 2011 4:55 pm

Hello because all good reavtions on previous tutorial here another

First of all make a database: Named dbPersons ( I used acces 2010 ):

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 ):

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:

U can also see I deleted some files. But that isn't necesairy. Then we are going to add a page named "SQLEdit.aspx"

( 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:

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:

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 ;)