Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3
This is the forum to discuss the Wrox book Beginning ASP.NET 3.5: In C# and VB by Imar Spaanjaars; ISBN: 9780470187593
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 13th, 2008, 09:55 PM
Registered User
 
Join Date: Sep 2004
Location: , , USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to TheNinthPlayer Send a message via MSN to TheNinthPlayer
Default ASP.Net, Access DB, and a webform?

I recently purchased the book and I've read a majority of it, but I have a project that I still feel overwhelmed with and I'm hoping to get some helpful tips from the forums.

I have an Inventory DB of PCs in an Access DB. I want to create a web form that allows people to add/modify PCs listed in that database.

The layout of the form is:
Asset Tag: [ ]
Serial Num: [ ]
User: [ ]

When a visitor enters an Asset Tag in the first field I want the website to check the DB for an existing entry. If it exist it should then postback all the missing fields so they can be modified and submitted. If it doesn't exist the remaining fields will be left empty and a new entry will be entered into the DB upon submission of the form.

So far I have created the form (does nothing, just contains the asp fields) and I have setup a connect to the Access Database using the DB Connector. And I'm not so much worried about submitting the entry as I think I can handle that part.

I am having trouble with the Asset Tag check/retrieval. How do I go about this?

Reply With Quote
  #2 (permalink)  
Old May 14th, 2008, 01:44 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi there,

You can set up the Asset text box to AutoPostBack. This way, when a user enters some text and tabs away, the page posts back. In the TextChanged handler of the text box you can access the asset tag the user entered, go in the database and see if the item exists. If it does, you populate the other controls and store the ID of the asset somewhere (in ViewState for example). Then in a Button's click event you retrieve the ID from ViewState. If it's null, you insert a new item using the text controls; otherwise you update an existing item. Below you find a quick example that demonstrates the idea. It used C# and a Code Behind file. It shouldn't be too difficult to convert if you're using VB instead:

[Default.aspx]
Code:
<table class="style1">
  <tr>
    <td>
      Asset Tag
    </td>
    <td>
      <asp:TextBox ID="txtAsset" runat="server" AutoPostBack="True" 
          OnTextChanged="txtAsset_TextChanged"></asp:TextBox>
    </td>
  </tr>
  <tr>
    <td>
      Serial
    </td>
    <td>
      <asp:TextBox ID="txtSerial" runat="server"></asp:TextBox>
    </td>
  </tr>
  <tr>
    <td>
       
    </td>
    <td>
      <asp:Button ID="btnSave" runat="server" OnClick="btnSave_Click" Text="Save" />
    </td>
  </tr>
  <tr>
    <td colspan="2">
      <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    </td>
  </tr>
</table>
[Default.aspx.cs]
Code:
protected void txtAsset_TextChanged(object sender, EventArgs e)
{
  // Replace code below with data access code to find the asset
  if (txtAsset.Text.StartsWith("a", StringComparison.InvariantCultureIgnoreCase))
  {
    txtSerial.Text = "Found";
    ViewState["ItemId"] = Guid.NewGuid();
  }
  else
  {
    txtSerial.Text = "NOT Found";
    ViewState["ItemId"] = null;
  }
}
protected void btnSave_Click(object sender, EventArgs e)
{
  if (ViewState["ItemId"] != null)
  {
    Label1.Text = string.Format(@"Updating item {0} with Serial number {1} and 
              Asset Tag {2}", new Guid(ViewState["ItemId"].ToString()), 
              txtSerial.Text, txtAsset.Text);
  }
  else
  {
    Label1.Text = string.Format(@"Inserting new item with Serial number {0} and 
              Asset Tag {1}", txtSerial.Text, txtAsset.Text);
  }
}
I am using Guid.NewGuid() to fake an item ID. Also, an Asset will be "found" when the key starts with the letter a. Obviously, you'll want to replace that code with your data access code to access the access database.

Do you have to use Access? If you'd be using SQL Server 2005 (Express even) you could use LINQ and searching for the Asset would be as simple as:
Code:
using (AssetDataContext myDb = new AssetDataContext())
{
  Asset myAsset = (from a in myDb.Assets
                   where a.AssetTag == txtAsset.Text
                   select a).SingleOrDefault();
  if (myAsset != null)
  {
    // Found
  }
}
Just an example, but hopefully you get the idea.....

Hope this helps; if not and you need specific help with the data access code, please let me know.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.
Reply With Quote
  #3 (permalink)  
Old May 15th, 2008, 12:19 PM
Registered User
 
Join Date: Sep 2004
Location: , , USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to TheNinthPlayer Send a message via MSN to TheNinthPlayer
Default

Imar,

Thanks for your speedy reply. I've been reading your book and feel its well written like many wrox books. I hope to be able to move onto the professional version in the next month or two, once I feel more comfortable working in ASP.Net.

I am having some trouble understand your reply. For the sake of my amatuer skills I'm just gonna focus on the Postback aspect of this problem. I understand the first part of your reply suggesting to use the postback feature and the first chuch of code, but the second chunk has me somewhat confused. I put comments in your code where I am lost with some pesudo code.

[Default.aspx.cs]
Code:
protected void txtAsset_TextChanged(object sender, EventArgs e)
{
  // Shouldn't this IF be ( txtAsset.Text = "SELECT assettag 
  // FROM computers" from DB).
  //
  if (txtAsset.Text.StartsWith("a", StringComparison.InvariantCultureIgnoreCase))
  {
// Shouldn't this part be : txtSerial.Text = SELECT serial 
//  FROM computers WHERE assettag = "xxxxxx" ?
//
    txtSerial.Text = "Found";
// Do I need to post it back to the viewstate? In truth I want the form to reply with a lot more fields (15 or so), I only mentioned 3 in the post for simplicity. In your book you mention not to go overboard with the viewstate cause of bandwidth issues.

    ViewState["ItemId"] = Guid.NewGuid();
  }
  else
  {
    txtSerial.Text = "NOT Found";
    ViewState["ItemId"] = null;
  }
}
I can use an SQL Server database. So I created a new DB based of my access one and populated it with sample data.

To reiterate:

How can I compare txtAsset.Text to find a matching entry in the database?
Reply With Quote
  #4 (permalink)  
Old May 16th, 2008, 03:57 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

OK, I quickly threw an example together that accesses a SQL Server database. You need to replace SqlConnection etc with OleDbConnection if you are using an Access database.
Code:
protected void txtAsset_TextChanged(object sender, EventArgs e)
{
  string connectionString = ConfigurationManager.ConnectionStrings["Assets"].ConnectionString;
  using (SqlConnection myConnection = new SqlConnection(connectionString))
  {
    string sql = "SELECT AssetTag, SerialNumber FROM Assets WHERE AssetTag = @assetTag";
    SqlCommand myCommand = new SqlCommand(sql, myConnection);
    myCommand.Parameters.AddWithValue("@assetTag", txtAsset.Text);
    myConnection.Open();

    SqlDataReader myReader = myCommand.ExecuteReader();

    if (myReader.Read())
    {
      txtSerial.Text = myReader.GetString(1);
      ViewState["AssetTag"] = txtAsset.Text;
    }
    else
    {
        // Clear text boxes
      txtSerial.Text = String.Empty;
      ViewState["AssetTag"] = null;

    }
    myReader.Close();
  }
}

protected void btnSave_Click(object sender, EventArgs e)
{
  string sql = String.Empty;
  if (ViewState["AssetTag"] != null)
  {
    sql = "UPDATE Assets SET SerialNumber = @serialNumber WHERE AssetTag = @assetTag";
  }
  else
  {
    sql = "INSERT INTO Assets (AssetTag, SerialNumber) VALUES (@assetTag, @serialNumber)";
  }

  string connectionString = ConfigurationManager.ConnectionStrings["Assets"].ConnectionString;
  using (SqlConnection myConnection = new SqlConnection(connectionString))
  {
    SqlCommand myCommand = new SqlCommand(sql, myConnection);
    myCommand.Parameters.AddWithValue("@assetTag", txtAsset.Text);
    myCommand.Parameters.AddWithValue("@serialNumber", txtSerial.Text);
    myConnection.Open();
    myCommand.ExecuteNonQuery();
  }
}
The code assumes there's a connection string called Assets in the web.config file that points to a SQL Server with a database called Assets that in turn has AssetTag and SerialNumber columns.

Basically, the code executes a SqlDataReader to find the requested asset. If it's there, it sets txtSerialNumber. Otherwise, the item is not found and the box is cleared.

The code also stores AssetTag in ViewState. Your worries about ViewState are correct: you shouldn't store too much in it. However, in this case, all you need to store is the AssetTag as that is the primary key of the Assets table. You need this key to determine in the Button's Click handler whether to Insert a new item (no AssetTag in ViewState) or Update an existing item (ViewState contains the last request AssetTag). All the other fields (SerialNumber in this case) can be retrieved from their respective controls.

Now that you are using SQL Server, things become a little easier. As I showed you in my earlier post, using LINQ to find the Assets is a lot easier and requires less coding. The manual solution I posted now uses SqlConnection, SqlCommand and SqlDataReader objects which are not always very intuitive to use. With LINQ, all you need to do is drag and drop a data diagram and write a few queries.

If you want to proceed with the manual coding, you may want to take a look here:

http://quickstarts.asp.net/Quickstar...wcontents.aspx

Additionally, Chapter 8 of the book Professional ASP.NET 3.5 is dedicated to data management with ADO.NET (the underlying data access technology) and shows you a lot more about connections, readers, command and parameters.

I can imagine all of this is a bit overwhelming at first. My suggestion is to try out my code and see if you can make it work. That you can post follow up questions here for clarification, or refer to the resources I linked to.

Good luck....

P.s. When you post code here, can you add a few line breaks here and there? The page is very wide now, which makes it hard to read....

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.
Reply With Quote
  #5 (permalink)  
Old May 20th, 2008, 01:59 PM
Registered User
 
Join Date: Sep 2004
Location: , , USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to TheNinthPlayer Send a message via MSN to TheNinthPlayer
Default

I definitly understand this a lot better. I was able to use your example and make a few modifications to fit my needs. Question though, what does this command do?

Code:
myCommand.Parameters.AddWithValue("@pcAssetTag", txtPCAsset.Text);
My next step is to add a Drop Down List for PC Models. I added it to the SQL query and I am able to display the current Model based on a specific AssetTag in the txtPCDescription TextBox.

Code:
string sql = "SELECT pcAssets.pcAssetTag, pcAssets.pcSerialNumber, pcModels.pcModelName FROM pcAssets INNER JOIN pcModels ON pcAssets.pcModelID = pcModels.pcModelID WHERE pcAssetTag = @pcAssetTag";
I went ahead and created a DropDownList which sucessfuly querys the Dataase for a list of Models. Now I just need to set the default value in the drop down list equal to what Model is returend in the AssetTag query.

Here is what I have so far:
Code:
<tr>
  <td>PC Description</td>
  <td>
    <asp:TextBox ID="txtPCDescription" runat="server"></asp:TextBox>
    <asp:DropDownList ID="ddlPCDescription" runat="server" 
      DataSourceID="SqlDataSource1" DataTextField="pcModelName" 
      DataValueField="pcModelName"></asp:DropDownList>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
      ConnectionString="<%$ ConnectionStrings:PC Assets DatabaseConnectionString1 %>" 
      SelectCommand="SELECT [pcModelName] FROM [pcModels]">
    </asp:SqlDataSource>
  </td>
</tr>
How can I set the default value from the dropdown list based on its currently listed model name in the database?

Reply With Quote
  #6 (permalink)  
Old May 21st, 2008, 12:51 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Take a look at the SQL statement. It contains keywords prefixed by an @ symbol. These placeholders get their value from a Parameter, which is what this code creates:

myCommand.Parameters.AddWithValue("@pcAssetTag", txtPCAsset.Text);

Basically it says: take @pcAssetTag in the SQL statement and replace it with the value of txtPCAsset.Text.

You can preselect an item by its value with this code:

if (ddlPCDescription.Items.FindByValue("SomeValue") != null)
{
  ddlPCDescription.Items.FindByValue("SomeValue").Se lected = true;
}

If you don't have the value but only the text of an Item you can also use FindByText.

Can you please add enters to your code to split up these really long line? Makes it easier to read these posts and write replies.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.
Reply With Quote
  #7 (permalink)  
Old May 21st, 2008, 03:27 PM
Registered User
 
Join Date: Sep 2004
Location: , , USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to TheNinthPlayer Send a message via MSN to TheNinthPlayer
Default

Thanks for the explination and sorry about the long lines.

I combined your solution with something I came across while googleing.
Code:
ddlPCModel.SelectedIndex = ddlPCModel.Items.IndexOf
       (ddlPCModel.Items.FindByText(myReader.GetString(2)));
This soltuion seems to be working for me. Thanks for the help!

Reply With Quote
  #8 (permalink)  
Old June 3rd, 2008, 08:53 AM
Registered User
 
Join Date: Sep 2004
Location: , , USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to TheNinthPlayer Send a message via MSN to TheNinthPlayer
Default

I've made all kinds of progress with the webapp I'be been desining but I've ran into another problem. I am attempting to add a validator the the txtAsset text box but the post-back keeps removing the validation error. I want to use a regular expression to make sure the asset tag is X numbers long. I have already created and tested the regular expression. I just need the error to stay present after the postback.

Reply With Quote
  #9 (permalink)  
Old June 3rd, 2008, 10:28 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

If this is a question outside of the scope of the book, could you please post it in one of the non-book forums for ASP.NET (http://p2p.wrox.com/asp-net-asp-12). We'd be happy to answer it there, plus it will be seen by more people.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Embed MsWord in asp.net webform rajatake ASP.NET 2.0 Professional 1 December 5th, 2006 11:29 AM
Error in Asp,net and MS Access DB shankha ADO.NET 1 January 4th, 2006 12:00 PM
OLEDB with ASP.NET and MS Access DB krishanu Classic ASP Databases 0 June 8th, 2005 12:52 PM
how to edit and update my access db using asp.net method ASP.NET 1.0 and 1.1 Basics 4 April 25th, 2005 03:03 PM



All times are GMT -4. The time now is 10:51 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.