Solved

Update access database issues using c# asp.net

Posted on 2004-08-02
40
1,628 Views
Last Modified: 2007-12-19
Ok, I've searched through the site here without any of the suggestions helping.  Here's the problem.  The database will not update, yet there is no error.  I have noticed though, when it does run the update method, it hides the id on post back.  Not sure if this helps.  It will run through the update method, but won't update the database, I've simplified the update method to only update the name for testing purposes.  Any Ideas?  This is an ascx file.

Also, why can I place references to contols in <%%> section and have it work, but not in the code section within a method? Secondary Question, first more important.

----- CODE -----

<%@ Control Language="c#" AutoEventWireup="false" TargetSchema="http://schemas.microsoft.com/intellisense/ie5" Debug="true" ClassName="Editor"%>

<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Web.UI" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.ComponentModel" %>
<%@ Import Namespace="System.Drawing" %>
<%@ Import Namespace="System.Web.SessionState" %>
<%@ Import Namespace="System.Web.UI.WebControls" %>
<%@ Import Namespace="System.Web.UI.HtmlControls" %>
<%@ Import Namespace="System.Globalization" %>

<script language="c#" runat="server">
public String EventID = "0";

public String[] GetEvent(Int32 eventid)
{
      String[] tmpdata = new String[9];
      tmpdata[0] = "0"; //default value if there is no match
      OleDbDataReader DBreader;
      OleDbConnection DBconn = new OleDbConnection(String.Concat("Provider=Microsoft.Jet.OleDb.4.0; Data Source=",Server.MapPath("Database.mdb"),";"));
      OleDbCommand DBquery = new OleDbCommand("SELECT * FROM Events WHERE ID = @id", DBconn);
      DBquery.Parameters.Add("@id",Convert.ToString(eventid));
      DBconn.Open();
      DBreader = DBquery.ExecuteReader();
      //{"name", "date", "ID", "LockStatus"}
      if(DBreader.Read())
      {            
            tmpdata[0] = Convert.ToString(DBreader["ID"]);
            tmpdata[1] = Convert.ToString(DBreader["Event_Name"]);
            tmpdata[2] = Convert.ToString(DBreader["Event_Location"]);
            tmpdata[3] = Convert.ToString(DBreader["Event_Desc_Long"]);
            tmpdata[4] = Convert.ToString(DBreader["Event_Desc_Short"]);
            tmpdata[5] = Convert.ToString(DBreader["Event_Date"]);
            tmpdata[6] = Convert.ToString(DBreader["Event_StartTime"]);
            tmpdata[7] = Convert.ToString(DBreader["Event_EndTime"]);
            tmpdata[8] = Convert.ToString(DBreader["Event_Link"]);
      }
      DBconn.Close();
      return tmpdata;
}
public void UpdateRecord(Object s, EventArgs e)
{
//do the update work
      OleDbDataReader DBreader;
      OleDbConnection DBconn = new OleDbConnection(String.Concat("Provider=Microsoft.Jet.OleDb.4.0; Data Source=",Server.MapPath("../db/calendar.mdb"),";"));
      OleDbCommand DBquery = new OleDbCommand("UPDATE Events SET Event_Name='@name' WHERE ID = @id", DBconn);
      //DBquery.Parameters.Add("@id",txtid.Text);
      DBquery.Parameters.Add("@name",txtname.Text);
      DBquery.Parameters.Add("@location",txtlocation.Text);
      DBquery.Parameters.Add("@desclong",txtdesclong.Text);
      DBquery.Parameters.Add("@descshort",txtdescshort.Text);
      DBquery.Parameters.Add("@date",txtdate.Text);
      DBquery.Parameters.Add("@starttime",txtstarttime.Text);
      DBquery.Parameters.Add("@endtime",txtendtime.Text);
      DBquery.Parameters.Add("@link",txtlink.Text);
      DBconn.Open();
      DBquery.ExecuteNonQuery();
      DBconn.Close();
      //Response.Redirect("this.aspx");
}

public void DeleteRecord(Object s, EventArgs e)
{
//do the delete work
}
public void PopulateFields(Int32 ID)
{
//populate the fields from the db to the page
      String[] EventData;
      EventData = GetEvent(ID);
      if(Convert.ToInt32(EventData[0]) != 0)
      {
            txtid.Text = EventData[0];
            txtname.Text = EventData[1];
            txtlocation.Text = EventData[2];
            txtdesclong.Text = EventData[3];
            txtdescshort.Text = EventData[4];
            txtdate.Text = Convert.ToDateTime(EventData[5]).ToShortDateString();
            txtstarttime.Text = Convert.ToDateTime(EventData[6]).ToLongTimeString();
            txtendtime.Text = Convert.ToDateTime(EventData[7]).ToLongTimeString();
            txtlink.Text = EventData[8];
      }
}
/*
private void Page_Load(object sender, System.EventArgs e)
{

  if( !this.IsPostBack )
  {

  }
}*/
</script>
<%
if(!Page.IsPostBack)
      PopulateFields(Convert.ToInt32(EventID));
%>
<form runat="server">

<table width="600" border="0">
      <tr>
            <td><div align="left">Event ID:</div></td>
            <td colspan="4"><asp:TextBox ID="txtid" MaxLength="15" TextMode="SingleLine" runat="server"  Enabled="false" Width="50"/></td>
      </tr>
      <tr>
            <td width="25%">Name:</td>
            <td width="23%">
          <asp:TextBox ID="txtname" MaxLength="50" TextMode="SingleLine" runat="server" />    
            </td>
            <td width="5%">&nbsp;</td>
            <td width="23%">Location:</td>
            <td width="23%">
          <asp:TextBox ID="txtlocation" MaxLength="50" TextMode="SingleLine" runat="server" />    
            </td>
      </tr>
      <tr>
            <td>Date:</td>
            <td><asp:TextBox ID="txtdate" MaxLength="50" TextMode="SingleLine" runat="server" /></td>
            <td>&nbsp;</td>
            <td>Start Time: </td>
            <td><asp:TextBox ID="txtstarttime" MaxLength="50" TextMode="SingleLine" runat="server" /></td>
      </tr>
      <tr>
            <td>Link:</td>
            <td><asp:TextBox ID="txtlink" MaxLength="50" TextMode="SingleLine" runat="server" /></td>
            <td>&nbsp;</td>
            <td>End Time: </td>
            <td><asp:TextBox ID="txtendtime" MaxLength="50" TextMode="SingleLine" runat="server" /></td>
      </tr>
      <tr>
            <td>Short Description: </td>
            <td colspan="4"><asp:TextBox ID="txtdescshort" MaxLength="100" TextMode="SingleLine" runat="server" Width="450"/></td>
            </tr>
      <tr>
            <td>Long Description: </td>
            <td colspan="4"><asp:TextBox ID="txtdesclong" TextMode="MultiLine" runat="server" Width="450" Rows="5"/></td>
            </tr>
      <tr>
            <td>&nbsp;</td>
            <td><div align="right"><asp:Button ID="btnupdate" Text="UpdateRecord" runat="server" OnClick="UpdateRecord"/>
            </div></td>
            <td>&nbsp;</td>
            <td><div align="left"><asp:Button ID="btndelete" Text="DeleteRecord" CommandArgument="idnum" CommandName="Delete" runat="server" />
                  </div></td>
            <td>&nbsp;</td>
      </tr>
</table>

<br>
<asp:ValidationSummary id="vsbumit" runat="server" />

<asp:RequiredFieldValidator ID="reqname" ControlToValidate="txtname" Display="None" ErrorMessage="Name: This field is required." runat="server" />

<asp:RequiredFieldValidator ID="reqlocation" ControlToValidate="txtlocation" Display="None" ErrorMessage="Location: This field is required." runat="server" />

<asp:RequiredFieldValidator ID="reqdate" ControlToValidate="txtdate" Display="None" ErrorMessage="Date: This field is required." runat="server" />
<asp:RegularExpressionValidator ID="valdate" ControlToValidate="txtdate" ValidationExpression="^\d{1,2}/\d{1,2}/(\d{4}|\d{2})$" ErrorMessage="Date: Invalid Format - mm/dd/yyyy" Display="None" runat="server" />

<asp:RequiredFieldValidator ID="reqstarttime" ControlToValidate="txtstarttime" Display="None" ErrorMessage="Start Time: This field is required." runat="server" />
<asp:RegularExpressionValidator ID="valtime" ControlToValidate="txtstarttime" ValidationExpression="^((0?[1-9]|1[012])(:[0-5]\d){0,2}(\ [AP]M))$|^((0?[1-9]|1[012])(:[0-5]\d){0,2}(\ [ap]m))$|^([01]\d|2[0-3])(:[0-5]\d){1,2}$" ErrorMessage="Start Time: Invalid Format - use hh:mm"  Display="None" runat="server" />

<asp:RequiredFieldValidator ID="reqendtime" ControlToValidate="txtendtime" Display="None" ErrorMessage="End Time: This field is required." runat="server" />
<asp:RegularExpressionValidator ID="valtime2" ControlToValidate="txtendtime" ValidationExpression="^((0?[1-9]|1[012])(:[0-5]\d){0,2}(\ [AP]M))$|^((0?[1-9]|1[012])(:[0-5]\d){0,2}(\ [ap]m))$|^([01]\d|2[0-3])(:[0-5]\d){1,2}$" ErrorMessage="End Time: Invalid Format - use hh:mm" Display="None" runat="server" />

<asp:RequiredFieldValidator ID="reqdesclong" ControlToValidate="txtdesclong" Display="None" ErrorMessage="Long Description: This field is required." runat="server" />

</form>
0
Comment
Question by:carbonice
  • 21
  • 18
40 Comments
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11698927
try posting your generated sql statement into an Access Query Using the Acces Query Builder.  Change to SQL View, and delete the stuff thats there. Paste your generated query from VS.NET.  Then switch to the datasheet view.  If all the field headers are correct and everything looks good there then you know its in code.  Since you got no error and no update I'm guessin something in your SQL is not jiving with Access.

Regards,

Aeros
0
 
LVL 1

Author Comment

by:carbonice
ID: 11698952
The query is fine updates like it should.  Also note the following line in my code although commented here, it not commented in the actual file.

//DBquery.Parameters.Add("@id",txtid.Text);
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11698980
Why are you setting the ID again after using it as a constraint in your WHERE Clause?  What type of field is it in your access database?

Regards,

Aeros
0
 
LVL 1

Author Comment

by:carbonice
ID: 11699080
ID is an autonumber field, unique Identifier.  What exactly are you refering to that I'm setting it again?
0
 
LVL 1

Author Comment

by:carbonice
ID: 11699088
Your not talking about:

     OleDbCommand DBquery = new OleDbCommand("UPDATE Events SET Event_Name='@name' WHERE ID = @id", DBconn);
     DBquery.Parameters.Add("@id",txtid.Text);  <--------------------
     DBquery.Parameters.Add("@name",txtname.Text);
     DBquery.Parameters.Add("@location",txtlocation.Text);

that are you???
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11699112
yes the one with the comments.

0
 
LVL 1

Author Comment

by:carbonice
ID: 11699126
I haven't set it yet the line that is commented out, that is the first time I'm setting it, if I don't assign it as a paramater there, in the Command Statement, it won't know where to go for the ID = part.  I guess I don't understand what your driving at....
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11699136
you don't set the id, Access creates it for you!!  Insert all the other fields normally and Access will insert the next Autonumber.

Regards,

Aeros
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11699140
So when you update just put the id in the WHERE Clause.
0
 
LVL 1

Author Comment

by:carbonice
ID: 11699142
It's a dynamic value
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11699145
Like so:

UPDATE Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'
0
 
LVL 1

Author Comment

by:carbonice
ID: 11699149
And I've tried that anyway, just for testing and it still doesn't work
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11699161
well then use something like one of the following:

Where = Me.mylabel.text

or

Where = MyVariable

or

Where = Session("MyVariable")

0
 
LVL 1

Author Comment

by:carbonice
ID: 11699167
It runs through all the code but doesn't update the values
0
 
LVL 1

Author Comment

by:carbonice
ID: 11699185
Regaurdless of the way to input the Where statement is irrelivant, unless this statement accually doesn't work, which I believe it should, as it's how my reference matterial is displaying it.  It must be another problem.  And like I said the query is fine in access...
0
 
LVL 1

Author Comment

by:carbonice
ID: 11699198
I also added a delete method and it does the same thing runs through without error but doesn't work:

public void DeleteRecord(Object s, EventArgs e)
{
//do the delete work
      OleDbDataReader DBreader;
      OleDbConnection DBconn = new OleDbConnection(String.Concat("Provider=Microsoft.Jet.OleDb.4.0; Data Source=",Server.MapPath("../db/calendar.mdb"),";"));
      OleDbCommand DBquery = new OleDbCommand("DELETE FROM Events WHERE ID=@id", DBconn);
      DBquery.Parameters.Add("@id",txtid.Text);
      DBconn.Open();
      DBquery.ExecuteNonQuery();
      DBconn.Close();
}
0
 
LVL 17

Accepted Solution

by:
AerosSaga earned 500 total points
ID: 11699205
Try doing it this way instead of the way you are doing it.

Private Sub WriteCustomerInfo()
        Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New OleDb.OleDbCommand
        Dim strCustomerID As String
        strCustomerID = CStr(Session("CustomerID"))
        Me.lblCustomerID.Text = strCustomerID
        cmd.CommandType = CommandType.Text
        If Me.rdoCC.Checked Then
            cmd.CommandText = "UPDATE Customers SET SessionID = " & CInt(Session("SessionID")) & ", " & "EmailAddress = '" & Me.txtEmailAddress.Text.ToString & "', FirstName = '" & Me.txtFirstName.Text & "', LastName = '" & Me.txtLastName.Text.ToString & "', Address = '" & _
            Me.txtAddress.Text.ToString & "', Location = '" & Me.txtLocation.Text.ToString & "', City = '" & Me.txtCity.Text.ToString & "', State = '" & _
            Me.txtState.Text.ToString & "', ZipCode = '" & Me.txtZipCode.Text.ToString & "', Country = '" & Me.lblCountry.Text.ToString & "', PhoneNumber = '" & Me.txtPhoneNumber.Text.ToString & _
            "', CCName = '" & Me.txtCCName.Text.ToString & "', CCNumber = '" & Me.txtCCNumber.Text.ToString & _
            "', CCExpDate = '" & Me.txtCCExpDate.Text.ToString & "' WHERE CustomerID = " & Me.lblCustomerID.Text.ToString
            cmd.Connection = cnn
            cnn.Open()
            cmd.ExecuteNonQuery()
        Else
            cmd.CommandText = "UPDATE Customers SET SessionID = " & CInt(Session("SessionID")) & ", " & "EmailAddress = '" & Me.txtEmailAddress.Text.ToString & "', FirstName = '" & Me.txtFirstName.Text & "', LastName = '" & Me.txtLastName.Text.ToString & "', Address = '" & _
               Me.txtAddress.Text.ToString & "', Location = '" & Me.txtLocation.Text.ToString & "', City = '" & Me.txtCity.Text.ToString & "', State = '" & _
               Me.txtState.Text.ToString & "', ZipCode = '" & Me.txtZipCode.Text.ToString & "', Country = '" & Me.lblCountry.Text.ToString & "', PhoneNumber = '" & Me.txtPhoneNumber.Text.ToString & _
               "', EFTName = '" & Me.txtAccountName.Text.ToString & "', EFTType = '" & Me.txtAccountType.SelectedValue.ToString & _
               "', EFTAccountNumber = '" & Me.txtAccountNumber.Text.ToString & "', EFTBankName = '" & Me.txtBankName.Text.ToString & "', EFTRoutingNumber = '" & Me.txtRoutingNumber.Text.ToString & "' WHERE CustomerID = " & Me.lblCustomerID.Text.ToString
            cmd.Connection = cnn
            cnn.Open()
            cmd.ExecuteNonQuery()
        End If
        cnn.Close()
        cmd.Dispose()
        cnn.Dispose()
    End Sub

I have no issues with this:

0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11699220
Heres my connection string as well:

<appSettings>
            <add key="SiteDB" value="Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Inetpub\wwwroot\DOTNETShoppingCart\Database\Site.mdb;"></add>
      </appSettings>
0
 
LVL 1

Author Comment

by:carbonice
ID: 11699224
I'll give it a shot
0
 
LVL 1

Author Comment

by:carbonice
ID: 11699362
Ok, I did as you suggested and here's the results:

OleDbCommand DBquery = new OleDbCommand("UPDATE Events SET Event_Name='" + txtname.Text + "', Event_Location=@location WHERE ID=" + txtid.Text, DBconn);

The Event_name does update but the other way with Event_location does not.  So my next Question is WHY????

For updating any of the matterial I have seen has said to do it the way I was doing it.  Yet it doesn't work.  Is there something I am missing here?  The way you showed me worked, but either way should.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 17

Expert Comment

by:AerosSaga
ID: 11699679
I personally am an ASP.NET/VB.NET coder, and the way I showed you is how microsoft show you how to do it.  As for why yours didn't work, I am not sure.  Either way it should have given you an error if it was unsuccessful, so I'm not sure.  The point I was making about the way you were doing it is that you were using whateverID field as an updated value, and as a criteria in your where statement.  Did you change the connection string at all?  I am not a C# programmer nor do I make any claims to be, but the prementioned logic was incorrect no matter what the frontend coding language.

Regards,

Aeros
0
 
LVL 10

Expert Comment

by:jnhorst
ID: 11699728
I don't want to make your frustration even worse, but I am thinking there is a much easier way to manage this sort of thing:

Instead of using a string array to populate your text boxes, create a DataTable in a Dataset with the fields you want to show.  Then bind the Text property of each textbox to the corresponding field in the DataTable.  Create a select command (with a parameter for the EventID) and an update command (with parameters for all the fields).  Then create a DataAdapter and assign the SelectCommand and UpdateCommand properties to these commands.

Once you have your DataAdapter completed, on the Page_Load event fire the Fill() method of the DataAdapter, passing it the DataTable.  This will call the select command and populate your DataTable with the one row you are looking for.  After calling Fill(), test for IsPostback [if (!this.IsPostback) {... code}]  Inside the if block test to see if you have your one row [if (Dataset.DataTable.Rows.Count == 1) **change Dataset.DataTable to the names you are using, of course**].  If you ahve the one row you are expecting, call DataBind on all of your textboxes.  This will cause them to display the field to which they have been bound.

To update you changes, have a routine that first gets the DataRow from the DataTable like this:

DataRow row = DataSet.DataTable.Rows[0];

Then reassign the fields in the row to whatever values the textboxes have:

row["Event_Name"] = txtEventName.Text
...

You should not have to reset the EventID (in fact you should disable that textbox if you are showing it).  After you make sure to assign any changes, call the Update() method of the DataAdapter you created, passing it the DataTable.  The DataAdapter will recognize that changes have been made to the underlying DataTable and will call the Update() method.

The key here is to execute the Fill() method **outside** the if(!this.IsPostback) block.  This way the DataAdpter will see the changes made when you click the Update button.

Good luck!

John
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11699816
I agree I just didn't want to confuse you further.

Regards,

Aeros
0
 
LVL 1

Author Comment

by:carbonice
ID: 11700889
NO, I appritiate the suggestion.  I'm a seasoned programmer, but new to the .NET framework.  Therefore, I'm still trying to understand how it all works together.  I'm not a "web programmer" per say either.  Therefore, I don't know the best approach to WEB solutions.  Thank you for you help in this problem and explaining it.  I'm not one that is content just taking a reason, I like to know why it works.  That's how one learns after all.  I guess the part that irritated me the most, is all the sources I found told me to do it one way and that way didn't work!  Well, thanks again.
0
 
LVL 1

Author Comment

by:carbonice
ID: 11700908
And just a note, I never was updating the ID, as that this is assigned when the Database Entry is created and is never changed, it's the point of reference.  As with the other values,

OleDbCommand DBquery = new OleDbCommand("UPDATE Events SET Event_Name='" + txtname.Text + "', Event_Location=@location WHERE ID=" + txtid.Text, DBconn);

for example the @location, you were saying that it's incorrect to use this field if it's updating as criteria or were you only talking about the ID?
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11703314
yes
0
 
LVL 1

Author Comment

by:carbonice
ID: 11704478
heh...yes to the ID, or yes to the other?!
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11704625
only the ID
0
 
LVL 1

Author Comment

by:carbonice
ID: 11705058
ok thanks!
0
 
LVL 1

Author Comment

by:carbonice
ID: 11708532
Ok, now the delete method isn't working:

public void DeleteRecord(Object s, EventArgs e)
{
//do the delete work
      OleDbDataReader DBreader;
      OleDbConnection DBconn = new OleDbConnection(String.Concat("Provider=Microsoft.Jet.OleDb.4.0; Data Source=",Server.MapPath("../db/calendar.mdb"),";"));
      OleDbCommand DBquery = new OleDbCommand("DELETE FROM Events WHERE ID=" + txtid.Text, DBconn);
      DBconn.Open();
      DBquery.ExecuteNonQuery();
      DBconn.Close();
}
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11708572
what happens?
0
 
LVL 1

Author Comment

by:carbonice
ID: 11708615
Nothing, just doesn't delete the file =\
0
 
LVL 1

Author Comment

by:carbonice
ID: 11708622
er....record
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11708650
what happens if you paste the command into access?
0
 
LVL 1

Author Comment

by:carbonice
ID: 11708676
It works fine....It's just like the other one, everything should work, but it doesn't.  Is this how you would do it?
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11708702
No I set my connection string in my web.config like this:

<appSettings>
            <add key="SiteDB" value="Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Inetpub\wwwroot\SavoyFurniture\Includes\Site.mdb;"></add>
      </appSettings>

And then something like this:

#Region " Products DataGrid "
    Private Sub Products_ItemDataBound(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles Products.ItemCreated
        Dim DeleteItem As LinkButton
        Dim ProductName As Label
        Dim ImageSrc As HtmlInputHidden
        If e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item Then

            DeleteItem = DirectCast(e.Item.FindControl("DeleteItem"), LinkButton)
            DeleteItem.Attributes.Add("onclick", "return confirm('Are you sure you want to delete this " & _
                "product?');")

            ProductName = DirectCast(e.Item.FindControl("ProductInfo"), Label)
            ProductName.Text = CStr(DataBinder.Eval(e.Item.DataItem, "ProductName"))
            ProductName.Text &= "<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&#187; " & _
                CStr(DataBinder.Eval(e.Item.DataItem, "Tier1Name"))
            If Not DataBinder.Eval(e.Item.DataItem, "Tier2Name") Is DBNull.Value Then
                ProductName.Text &= "&nbsp;&#187;&nbsp;" & CStr(DataBinder.Eval(e.Item.DataItem, "Tier2Name"))
            End If
            If Not DataBinder.Eval(e.Item.DataItem, "Tier3Name") Is DBNull.Value Then
                ProductName.Text &= "&nbsp;&#187;&nbsp;" & CStr(DataBinder.Eval(e.Item.DataItem, "Tier3Name"))
            End If

            'ImageSrc = DirectCast(e.Item.FindControl("ItemImageSrc"), HtmlInputHidden)
            'ImageSrc.Value = CStr(DataBinder.Eval(e.Item.DataItem, "ImageSrc"))
        End If
    End Sub
    Private Sub Products_ItemCommand(ByVal source As System.Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles Products.ItemCommand
        If e.CommandName = "Delete" Then
            If IsRefresh Then Return

            Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
            Dim cmd As New OleDb.OleDbCommand
            Dim ImageSrc As String = DirectCast(e.Item.FindControl("ItemImageSrc"), HtmlInputHidden).Value
            Dim ProductImage As IO.FileInfo

            cmd.CommandType = CommandType.Text
            cmd.CommandText = "DELETE FROM Products WHERE ProductID = " & _
                CStr(Products.DataKeys(e.Item.ItemIndex))
            cmd.Connection = cnn

            cnn.Open()
            cmd.ExecuteNonQuery()
            cnn.Close()

            cmd.Dispose()
            cnn.Dispose()
            Try
                ProductImage = New IO.FileInfo(Server.MapPath("~/Pictures/Products/" & ImageSrc))
                If ProductImage.Exists Then ProductImage.Delete()
                ProductImage = New IO.FileInfo(Server.MapPath("~/Pictures/Products/Thumbnails/" & ImageSrc))
                If ProductImage.Exists Then ProductImage.Delete()
            Catch ex As Exception
            End Try
            LoadProductData()
        ElseIf e.CommandName = "Edit" Then
            Response.Redirect("EditProduct.aspx?pid=" & CStr(Products.DataKeys(e.Item.ItemIndex)))
        End If
    End Sub
    Private Sub LoadProductData(Optional ByVal Query As String = "")
        Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New OleDb.OleDbCommand
        Dim da As New OleDb.OleDbDataAdapter(cmd)
        Dim dt As New DataTable

        'If the query is not passed, check to see if a previous query has been executed and stored
        If Query = "" Then Query = FullQuery.Value

        'If it's still empty, use the default query
        If Query = "" Then
            Query = "SELECT Tier1.Name AS [Tier1Name], Tier2.Name AS [Tier2Name], Tier3.Name AS [Tier3Name], " & _
                "Products.Name AS [ProductName], Products.ProductID, Products.ImageSrc FROM Tier3 RIGHT " & _
                "JOIN (Tier2 RIGHT JOIN (Tier1 INNER JOIN Products ON Tier1.Tier1ID = Products.Tier1ID) " & _
                "ON Tier2.Tier2ID = Products.Tier2ID) ON Tier3.Tier3ID = Products.Tier3ID ORDER BY " & _
                "Products.Name"
        End If

        cmd.CommandType = CommandType.Text
        cmd.CommandText = Query
        cmd.Connection = cnn

        da.Fill(dt)

        Products.DataKeyField = "ProductID"
        Products.DataSource = dt
        Products.DataBind()

        cmd.Dispose()
        cnn.Dispose()

        If Products.Items.Count = 0 Then
            Products.Visible = False
        Else
            Products.Visible = True
        End If
    End Sub
#End Region

Regards,

Aeros
0
 
LVL 1

Author Comment

by:carbonice
ID: 11708741
Thanks!  Stupid mistake on my part, guess that's what happens when your rushed! =)

Thanks Again!
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11708765
np, please leave positive feedback as I have done my best to answer your questions throughly

Regards,

Aeros
0
 
LVL 1

Author Comment

by:carbonice
ID: 11708876
Already have =)  Thanks again!
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11708911
np have a good one I appreciate it

Regards,

Aeros
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now