Link to home
Start Free TrialLog in
Avatar of carbonice
carboniceFlag for United States of America

asked on

Update access database issues using c# asp.net

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>
Avatar of AerosSaga
AerosSaga

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
Avatar of carbonice

ASKER

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);
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
ID is an autonumber field, unique Identifier.  What exactly are you refering to that I'm setting it again?
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???
yes the one with the comments.

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....
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
So when you update just put the id in the WHERE Clause.
It's a dynamic value
Like so:

UPDATE Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'
And I've tried that anyway, just for testing and it still doesn't work
well then use something like one of the following:

Where = Me.mylabel.text

or

Where = MyVariable

or

Where = Session("MyVariable")

It runs through all the code but doesn't update the values
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...
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();
}
ASKER CERTIFIED SOLUTION
Avatar of AerosSaga
AerosSaga

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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>
I'll give it a shot
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.
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
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
I agree I just didn't want to confuse you further.

Regards,

Aeros
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.
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?
heh...yes to the ID, or yes to the other?!
only the ID
ok thanks!
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();
}
what happens?
Nothing, just doesn't delete the file =\
er....record
what happens if you paste the command into access?
It works fine....It's just like the other one, everything should work, but it doesn't.  Is this how you would do it?
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
Thanks!  Stupid mistake on my part, guess that's what happens when your rushed! =)

Thanks Again!
np, please leave positive feedback as I have done my best to answer your questions throughly

Regards,

Aeros
Already have =)  Thanks again!
np have a good one I appreciate it

Regards,

Aeros