Wrox Programmer Forums
|
BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8
This is the forum to discuss the Wrox book Beginning ASP.NET 2.0 by Chris Hart, John Kauffman, David Sussman, Chris Ullman; ISBN: 9780764588501
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 25th, 2009, 02:18 AM
Authorized User
 
Join Date: Sep 2009
Posts: 31
Thanks: 6
Thanked 0 Times in 0 Posts
Question How to auto update ID in SQL server?

Respected Imar sir,

I am designing a Gallery page for club Members, in this gallery they can upload any file ....now for this I have create database having following feilds:

1.FileID (int,primary key)
2.UploadedByMemberName(varchar (50))
3.Notes(text)
4.FileURL(varchar(50))

now I have added following code on .aspx file
Code:
<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Gallery.aspx.cs" Inherits="ClubMember_Gallery" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" Runat="Server">
    <table>
    <tr>
    <td align="right" style="width:200px">
    <asp:Label ID="lblFile" runat="server" Text="Enter the name of photo / file:"/> 
    </td>
    <td>
    <asp:FileUpload ID="FileUpload1" runat="server" />     
    </td> 
    </tr>
    <tr>
        <td align="right">File ID:</td>
        <td><asp:TextBox ID="txtFileID" runat="server" Enabled="false"/>
         </td>
        </tr>

    <tr>
    <td align="right">
       User Name:
      </td>
      <td>
       <asp:TextBox ID="txtMemberName" runat="server"/>
          </td>  
          </tr>
          <tr>
          <td align="right">
        Notes:
        </td>
        <td>
      <asp:TextBox ID="txtNotes" runat="server"></asp:TextBox>
       </td>
       </tr>
        <tr>
        <td></td>
        <td align="left">
         <br />
         <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="Button1_Click" Width="105px" /></td>
        </tr>
         <tr>
        <td align="left" colspan="2">
            <asp:Label ID="lblupload" runat="server" Text="Label"></asp:Label>
        </td>
        </tr>     
   
</table>
    &nbsp;&nbsp;<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:Roy %>"  InsertCommand="INSERT INTO [Gallery] ([FileID], [UploadedByMemberName], [Notes], [PictureURL]) VALUES (@FileID, @UploadedByMemberName, @Notes, @PictureURL)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [FileID], [UploadedByMemberName], [Notes], [PictureURL] FROM [Gallery]">
        
        <InsertParameters>
            <asp:ControlParameter Name="FileID"
            ControlID="txtFileID" PropertyName="Text"
             Type="String" />
            <asp:ControlParameter Name="UploadedByMemberName"
             ControlID="txtMemberName" PropertyName="Text"
             Type="String" />
            <asp:ControlParameter Name="Notes"
             ControlID="txtNotes" PropertyName="Text"
             Type="String" />
            <asp:ControlParameter Name="PictureURL"
             ControlID="FileUpload1"  PropertyName="FileName"
             Type="String" />
        </InsertParameters>
    </asp:SqlDataSource>
</asp:Content>
and .cs file has following code:
Code:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.IO;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class ClubMember_Gallery : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
       
        if (FileUpload1.HasFile)
        {
            try
            {
              FileUpload1.SaveAs("E:\\Marathi\\Royal\\images\\Gallery" + FileUpload1.FileName);
              lblupload.Text ="Thank You";
            }
            catch (Exception exUpload)
            {
                lblupload.Text = exUpload.Message;
            }

            SqlDataSource1.Insert();
        }
           

        else
        {
            lblupload.Text = "Please select File before click";
        }
    }



    
}
Now I got an error that cannot enter NULL in FileID....as I gave it as primary key...when I remove Primary key..Page runs perfectly,but inserting Null in FeildID,which makes no sense..but I am not understanding how to update FileID feild in database automatically whenever user upload the file/photo...?
I have tried with counter, i.e. increment counter display in txtFeildID and bound FeildID to txtFeildID, but gives more error...please help

Thank you in advance..

Last edited by bela_sush; September 25th, 2009 at 02:52 AM..
 
Old September 25th, 2009, 02:55 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hi there,

Besides the Primary Key, you also need to set it as an Indentity. When you're designing the table, click the FileID column. You then see a properties grid with settings for that column, including the Identity. Set it to Yes and SQL Server automatically inserts a new ID for each record for you.

Hope this helps,

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
The Following User Says Thank You to Imar For This Useful Post:
bela_sush (September 25th, 2009)
 
Old September 25th, 2009, 04:36 AM
Authorized User
 
Join Date: Sep 2009
Posts: 31
Thanks: 6
Thanked 0 Times in 0 Posts
Smile Thank you

Imar sir...

Hats off to you....Thanks a lot!!!
 
Old October 3rd, 2009, 03:37 AM
Authorized User
 
Join Date: Sep 2009
Posts: 31
Thanks: 6
Thanked 0 Times in 0 Posts
Default Updatind database

Respected Imar Sir,

I have completed my project of online hotel Reservation...but while final testing I got four runtime errors out of which two are serious...

First is about updating records in database...

I have created a page called ClubMembership..in which people suscribe membership of hotel, I have used two types of storage to store members data

1.Using Profile feature of .net
2.In SQL database

At both places data goes correctly when User click on Finish button.

Now i have developed anothe page called modify profile

.aspx code is as follows

Code:
<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="MyProfile.aspx.cs" Inherits="ClubMember_MyProfile" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" Runat="Server">

      <h1>
            Profile Settings for: <asp:LoginName ID="LoginName1" runat="server" />
        </h1>
        
        <table>
             <tr>
                <td>Your Date Of Join:</td>
                <td><asp:TextBox ID="txtJoin" BorderColor="black" Enabled="false" runat="server"></asp:TextBox> </td>
            </tr>
            <tr>
                <td>First Name:</td>
                <td><asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Last Name:</td>
                <td><asp:TextBox ID="txtLastName" runat="server"></asp:TextBox></td>
            </tr>
            
           
        
            <tr>
                <td>Address:</td>
                <td><asp:TextBox ID="txtAddress" runat="server"></asp:TextBox> </td>
            </tr>        
        <tr>
                <td>City:</td>
                <td><asp:TextBox ID="txtCity" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Country:</td>
                <td><asp:TextBox ID="txtCountry" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td style="height: 40px">Postal Code:</td>
                <td style="height: 40px"><asp:TextBox ID="txtPostalCode" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
          <td>E-mail:</td>
          <td>
              <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
              <asp:RegularExpressionValidator ID="rev1" runat="server" ControlToValidate="txtEmail"
                 ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
                   Text="*" ErrorMessage="Please Enter a valid E-mail address">
                   </asp:RegularExpressionValidator>
         </td>
         </tr>
            <tr>
                <td>Contact Number 1:</td>
                <td><asp:TextBox ID="txtContact1" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Contact Number 2:</td>
                <td><asp:TextBox ID="txtContact2" runat="server"></asp:TextBox></td>
            </tr>
             
        
        </table>
        <br />
    <asp:Button ID="btnSaveChanges" runat="server" Text="Save Changes" OnClick="btnSaveChanges_Click" />
    &nbsp;&nbsp;
     <asp:Button ID="btnCancelChanges" runat="server" Text="Cancel Changes" OnClick="btnCancelChanges_Click" />&nbsp;
    <br />
    <br />
    <asp:Button ID="btnExit" runat="server" Text="Exit" PostBackUrl="~/Default.aspx" OnClick="btnExit_Click" Width="125px" />

</asp:Content>
and .cs code is

Code:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class ClubMember_MyProfile : System.Web.UI.Page
{
   private void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)

            DisplayProfileProperties();
       
    }
  private void DisplayProfileProperties()
  {
      txtCountry.Text = Profile.Country;
      txtFirstName.Text = Profile.FirstName;
      txtLastName.Text = Profile.LastName;
      txtPostalCode.Text = Profile.PostalCode;
       txtContact1.Text = Profile.Contact1.ToString();
       txtContact2.Text = Profile.Contact2.ToString();
       txtAddress.Text = Profile.Address;
       txtCity.Text = Profile.City;
       txtEmail.Text = Profile.Email;
       txtJoin.Text = Profile.DateOfJoin;
                          
      
  }
    protected void btnSaveChanges_Click(object sender, EventArgs e)
    {
         Profile.Country = txtCountry.Text;
         Profile.FirstName = txtFirstName.Text;
         Profile.LastName = txtLastName.Text;
         Profile.PostalCode =  txtPostalCode.Text;
         Profile.Contact1 = Double.Parse(txtContact1.Text);
         Profile.Contact2 = Double.Parse(txtContact2.Text);
         Profile.Address = txtAddress.Text;
         Profile.City = txtCity.Text;
         Profile.Email = txtEmail.Text;


         SqlConnection conn = null;
         SqlTransaction trans = null;
         

         try
         {
             conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Roy"].ConnectionString);
             conn.Open();

             trans = conn.BeginTransaction();

             string sql = "SELECT * FROM ClubMembers";
             SqlDataAdapter adapt = new SqlDataAdapter(sql, conn);
             SqlCommandBuilder cmdbuild = new SqlCommandBuilder(adapt);
             DataSet ds = new DataSet("ClubMembers");
             adapt.Fill(ds, "ClubMembers");
           
          foreach (DataRow dr in ds.Tables["ClubMembers"].Rows)

          if (dr["MemberID"] == "@MemberID" )
             {   

 
             dr["@FirstName"] = txtFirstName.Text;
             dr["@LaststName"] = txtLastName.Text;
             dr["@Address"] = txtAddress.Text;
             dr["@Contact1"] = txtContact1.Text;
             dr["@City"] = txtCity.Text;
             dr["@Country"] = txtCountry.Text;
             dr["@PostalCode"] = txtPostalCode.Text;
             dr["@EmailID"] = txtEmail.Text;

             ds.Tables["ClubMembers"].AcceptChanges();
             ds.Tables["ClubMembers"].Rows.Add(dr);
             adapt.Update(ds, "ClubMembers");
            
          
          }
         
             // commit the transaction
             trans.Commit();

         }
         catch (SqlException SqlEx)
         {
             // some form of error - rollback the transaction 
             // and rethrow the exception
             if (trans != null)
                 trans.Rollback();



             // Log the exception
             // Tools.log("An error occurred while creating the order", SqlEx)
             throw new Exception("An error occurred while creating the order", SqlEx);
         }
         finally
         {
             if (conn != null)
                 conn.Close();
         }


              
    }


      
    
    protected void btnCancelChanges_Click(object sender, EventArgs e)
    {
        DisplayProfileProperties();
    }
    protected void btnExit_Click(object sender, EventArgs e)
    {
        
        
    }
}
now when I run the page, modification gets correctly updated in profile but i am failed while updating in SQL database..errors occure

I also try following code for SQL updation

Code:
         SqlConnection conn = null;
         SqlTransaction trans = null;
         SqlCommand cmd;

         try
         {
             conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Roy"].ConnectionString);
             conn.Open();

             trans = conn.BeginTransaction();

             cmd = new SqlCommand();
             cmd.Connection = conn;
             cmd.Transaction = trans;

             // set the order details
             cmd.CommandText = "UPDATE ClubMembers"
                               + "SET(FirstName=@FirstName,LastName=@LastName,Contact1=@Contact1,EmailID=@EmailID,Address=@Address,City=@City,PostalCode=@PostalCode,Country=@Country)"
                               +"WHERE MemberID=@MemberID";

            cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50);

            cmd.Parameters.Add("@DateOfJoin",SqlDbType.DateTime);
            cmd.Parameters.Add("@MembershipType",SqlDbType.NVarChar,50); 
            cmd.Parameters.Add("@PhotoIDNo", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@Age", SqlDbType.Int);
            cmd.Parameters.Add("@Contact1", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@Contact2", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@CardNo", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@EmailID", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@PhotoIDProof", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@CardType", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 255);
            cmd.Parameters.Add("@City", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@PostalCode", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@Country", SqlDbType.VarChar, 50);


            cmd.Parameters["@FirstName"].Value = txtFirstName.Text;
            cmd.Parameters["@LastName"].Value = txtLastName.Text;
            cmd.Parameters["@Contact1"].Value = txtContact1.Text;
            cmd.Parameters["@EmailID"].Value = txtEmail.Text;
            cmd.Parameters["@Address"].Value = txtAddress.Text;
            cmd.Parameters["@City"].Value = txtCity.Text;
            cmd.Parameters["@PostalCode"].Value = txtPostalCode.Text;
            cmd.Parameters["@Country"].Value = txtCountry.Text;

            cmd.ExecuteNonQuery();


             // commit the transaction
             trans.Commit();

         }
         catch (SqlException SqlEx)
         {
             // some form of error - rollback the transaction 
             // and rethrow the exception
             if (trans != null)
                 trans.Rollback();



             // Log the exception
             // Tools.log("An error occurred while creating the order", SqlEx)
             throw new Exception("An error occurred while creating the order", SqlEx);
         }
         finally
         {
             if (conn != null)
                 conn.Close();
         }
but still no updation in SQL database....

Please help me to write exact and correct code for updating data in SQL datatable

Thank You in advance
 
Old October 3rd, 2009, 04:37 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hi there,

This is too much code, and too little explanation for me to say something useful about it.

Provide more detail (error message, line number etc) and try stripping away irrelevent code...

Cheers,

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
 
Old October 3rd, 2009, 09:34 AM
Authorized User
 
Join Date: Sep 2009
Posts: 31
Thanks: 6
Thanked 0 Times in 0 Posts
Default

Respected Imar sir,

Actually, I am bit confused to use update code....I am not sure which code is use to update the database....thats why I tried two codes ...to update datatable should I go for dataadapter and fill method ? or should I go for just 'UPDATE and SET " method....

Actually while running both the code no errors are coming... build gets successful, but after updating profile data, if I check database, values do not get updated, but in aspnet_profile, values get updated correctly...so I think I am on wrong path for update database....means my method is wrong..like I want to go to London, but I caught New York plane...Plane is flying perfectly but I am not getting expected result..

So I just want to request you...please tell me way for London plane..i.e. proper code to update database table through C# code..

scenario is, when user clicks on modify profile, he gets textboxes with his profile data, now he want to update e-mail address, so he erase/clear e-mail address of E-mail textbox and enters new and clicks on Save Changes button...after this asp_profile E-mail ID field got updated correctly but my database field shows old value...I have
given code for update profile and update database, but I am sure even my update database code executes correctly, values not getting updated...

Please help..
Thank you..

Last edited by bela_sush; October 3rd, 2009 at 09:44 AM..
 
Old October 3rd, 2009, 10:04 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Quote:
please tell me way for London plane
I can only tell you how to take the plane to London if you tell me where you are right now.

Like I said, it's way too much code to wade through, especially since I cannot try it out because I don't have your database.

Cheers,

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
 
Old October 4th, 2009, 03:41 PM
Authorized User
 
Join Date: Sep 2009
Posts: 31
Thanks: 6
Thanked 0 Times in 0 Posts
Default about updating database through ADO.NET

Respected Imar sir,

This is the location where I stuck now...

I hava a database named, Royal, in that I have table named ClubMembers.
This ClubMember's fields are,

1.MemberID(primary)
2.FirstName
3.LastName
4.Address
5.City
6.PosalCode
7.Country
8.EmailID
9.CardType
10.CardN0

now I have a MyProfile.aspx page as
Code:
<table>
<tr>
                <td>First Name:</td>
                <td><asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox></td>
            </tr>
in same way I have a textbox for each of above said datafields..

now when any user fills Membership form first time(I have use create user wizard for that) he enter all his relevant details into respected textboxes...now this data gets stored perfectly in above database table...I have used ADO.NET code of INSERT(as used in beginning ASP.NET 2.0 checkout.aspx.cs)

Now I have created above MyProfile.aspx page where club member can update his relevent details (for ex- if member thrown out by his wife from home , thats why he is living at other place and thus wants to update address)...now when user enter new address in txtAddress, (in MyProfile.aspx) and click Save Changes button (which is there on MyProfile.aspx) this new address should get update in above ClubMember's Address datafield...I have used many ADO.NET code to do this....but all says..we are going to New York...and still I am in search of London plane...

At least now you can show me the way...I don't expect full code..but at least algo or something relevant....if you provide code it will be better for me as I am still a kid in ASP.net..

Sir I also want to say cheers...but after the solution

thank you very much in advance

Last edited by bela_sush; October 4th, 2009 at 03:46 PM..
 
Old October 5th, 2009, 03:52 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Take a look here:

http://www.aspfree.com/c/a/ASP.NET/U...ET-20-Pages/3/

It shows you how to use ExecuteNonQuery and parameters to update a table.

You may also want to take a look at this book: http://www.wrox.com/WileyCDA/WroxTit...764584375.html

It shows you, in depth, how to work woth data related objects like the SqlConnection, SqlCommand and so on.

Cheers,

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
The Following User Says Thank You to Imar For This Useful Post:
bela_sush (October 7th, 2009)
 
Old October 7th, 2009, 02:44 PM
Authorized User
 
Join Date: Sep 2009
Posts: 31
Thanks: 6
Thanked 0 Times in 0 Posts
Smile Thank you

Respected Imar Sir,

You have show me the perfect way for london plane...plane is flying smoothly and great news is, it is going to London only!!


Imar sir...Is there any word which values more than Thank You?...if you know plz tell me..

Till we find that...Thank You very much once again!!!





Similar Threads
Thread Thread Starter Forum Replies Last Post
datalist not displaying... sarah lee ASP.NET 1.0 and 1.1 Basics 1 October 3rd, 2006 10:42 AM
datalist not displaying.. help me please sarah lee ASP.NET 1.0 and 1.1 Basics 10 August 31st, 2006 07:48 AM
Displaying master/detail relationship in datalist cq_ted ASP.NET 1.x and 2.0 Application Design 0 May 10th, 2006 01:07 AM
Datalist does not display data sanjeet ADO.NET 1 August 8th, 2003 11:26 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.