carbonice
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.Ole Db" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Web.UI" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Collecti ons" %>
<%@ Import Namespace="System.Componen tModel" %>
<%@ Import Namespace="System.Drawing" %>
<%@ Import Namespace="System.Web.Sess ionState" %>
<%@ Import Namespace="System.Web.UI.W ebControls " %>
<%@ Import Namespace="System.Web.UI.H tmlControl s" %>
<%@ Import Namespace="System.Globaliz ation" %>
<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.Con cat("Provi der=Micros oft.Jet.Ol eDb.4.0; Data Source=",Server.MapPath("D atabase.md b"),";"));
OleDbCommand DBquery = new OleDbCommand("SELECT * FROM Events WHERE ID = @id", DBconn);
DBquery.Parameters.Add("@i d",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_Nam e"]);
tmpdata[2] = Convert.ToString(DBreader[ "Event_Loc ation"]);
tmpdata[3] = Convert.ToString(DBreader[ "Event_Des c_Long"]);
tmpdata[4] = Convert.ToString(DBreader[ "Event_Des c_Short"]) ;
tmpdata[5] = Convert.ToString(DBreader[ "Event_Dat e"]);
tmpdata[6] = Convert.ToString(DBreader[ "Event_Sta rtTime"]);
tmpdata[7] = Convert.ToString(DBreader[ "Event_End Time"]);
tmpdata[8] = Convert.ToString(DBreader[ "Event_Lin k"]);
}
DBconn.Close();
return tmpdata;
}
public void UpdateRecord(Object s, EventArgs e)
{
//do the update work
OleDbDataReader DBreader;
OleDbConnection DBconn = new OleDbConnection(String.Con cat("Provi der=Micros oft.Jet.Ol eDb.4.0; Data Source=",Server.MapPath(". ./db/calen dar.mdb"), ";"));
OleDbCommand DBquery = new OleDbCommand("UPDATE Events SET Event_Name='@name' WHERE ID = @id", DBconn);
//DBquery.Parameters.Add(" @id",txtid .Text);
DBquery.Parameters.Add("@n ame",txtna me.Text);
DBquery.Parameters.Add("@l ocation",t xtlocation .Text);
DBquery.Parameters.Add("@d esclong",t xtdesclong .Text);
DBquery.Parameters.Add("@d escshort", txtdescsho rt.Text);
DBquery.Parameters.Add("@d ate",txtda te.Text);
DBquery.Parameters.Add("@s tarttime", txtstartti me.Text);
DBquery.Parameters.Add("@e ndtime",tx tendtime.T ext);
DBquery.Parameters.Add("@l ink",txtli nk.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(EventDa ta[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(EventDa ta[5]).ToS hortDateSt ring();
txtstarttime.Text = Convert.ToDateTime(EventDa ta[6]).ToL ongTimeStr ing();
txtendtime.Text = Convert.ToDateTime(EventDa ta[7]).ToL ongTimeStr ing();
txtlink.Text = EventData[8];
}
}
/*
private void Page_Load(object sender, System.EventArgs e)
{
if( !this.IsPostBack )
{
}
}*/
</script>
<%
if(!Page.IsPostBack)
PopulateFields(Convert.ToI nt32(Event ID));
%>
<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%"> </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> </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> </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> </td>
<td><div align="right"><asp:Button ID="btnupdate" Text="UpdateRecord" runat="server" OnClick="UpdateRecord"/>
</div></td>
<td> </td>
<td><div align="left"><asp:Button ID="btndelete" Text="DeleteRecord" CommandArgument="idnum" CommandName="Delete" runat="server" />
</div></td>
<td> </td>
</tr>
</table>
<br>
<asp:ValidationSummary id="vsbumit" runat="server" />
<asp:RequiredFieldValidato r ID="reqname" ControlToValidate="txtname " Display="None" ErrorMessage="Name: This field is required." runat="server" />
<asp:RequiredFieldValidato r ID="reqlocation" ControlToValidate="txtloca tion" Display="None" ErrorMessage="Location: This field is required." runat="server" />
<asp:RequiredFieldValidato r ID="reqdate" ControlToValidate="txtdate " Display="None" ErrorMessage="Date: This field is required." runat="server" />
<asp:RegularExpressionVali dator 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:RequiredFieldValidato r ID="reqstarttime" ControlToValidate="txtstar ttime" Display="None" ErrorMessage="Start Time: This field is required." runat="server" />
<asp:RegularExpressionVali dator ID="valtime" ControlToValidate="txtstar ttime" ValidationExpression="^((0 ?[1-9]|1[0 12])(:[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:RequiredFieldValidato r ID="reqendtime" ControlToValidate="txtendt ime" Display="None" ErrorMessage="End Time: This field is required." runat="server" />
<asp:RegularExpressionVali dator ID="valtime2" ControlToValidate="txtendt ime" ValidationExpression="^((0 ?[1-9]|1[0 12])(:[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:RequiredFieldValidato r ID="reqdesclong" ControlToValidate="txtdesc long" Display="None" ErrorMessage="Long Description: This field is required." runat="server" />
</form>
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.Ole
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Web.UI" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Collecti
<%@ Import Namespace="System.Componen
<%@ Import Namespace="System.Drawing"
<%@ Import Namespace="System.Web.Sess
<%@ Import Namespace="System.Web.UI.W
<%@ Import Namespace="System.Web.UI.H
<%@ Import Namespace="System.Globaliz
<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.Con
OleDbCommand DBquery = new OleDbCommand("SELECT * FROM Events WHERE ID = @id", DBconn);
DBquery.Parameters.Add("@i
DBconn.Open();
DBreader = DBquery.ExecuteReader();
//{"name", "date", "ID", "LockStatus"}
if(DBreader.Read())
{
tmpdata[0] = Convert.ToString(DBreader[
tmpdata[1] = Convert.ToString(DBreader[
tmpdata[2] = Convert.ToString(DBreader[
tmpdata[3] = Convert.ToString(DBreader[
tmpdata[4] = Convert.ToString(DBreader[
tmpdata[5] = Convert.ToString(DBreader[
tmpdata[6] = Convert.ToString(DBreader[
tmpdata[7] = Convert.ToString(DBreader[
tmpdata[8] = Convert.ToString(DBreader[
}
DBconn.Close();
return tmpdata;
}
public void UpdateRecord(Object s, EventArgs e)
{
//do the update work
OleDbDataReader DBreader;
OleDbConnection DBconn = new OleDbConnection(String.Con
OleDbCommand DBquery = new OleDbCommand("UPDATE Events SET Event_Name='@name' WHERE ID = @id", DBconn);
//DBquery.Parameters.Add("
DBquery.Parameters.Add("@n
DBquery.Parameters.Add("@l
DBquery.Parameters.Add("@d
DBquery.Parameters.Add("@d
DBquery.Parameters.Add("@d
DBquery.Parameters.Add("@s
DBquery.Parameters.Add("@e
DBquery.Parameters.Add("@l
DBconn.Open();
DBquery.ExecuteNonQuery();
DBconn.Close();
//Response.Redirect("this.
}
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(EventDa
{
txtid.Text = EventData[0];
txtname.Text = EventData[1];
txtlocation.Text = EventData[2];
txtdesclong.Text = EventData[3];
txtdescshort.Text = EventData[4];
txtdate.Text = Convert.ToDateTime(EventDa
txtstarttime.Text = Convert.ToDateTime(EventDa
txtendtime.Text = Convert.ToDateTime(EventDa
txtlink.Text = EventData[8];
}
}
/*
private void Page_Load(object sender, System.EventArgs e)
{
if( !this.IsPostBack )
{
}
}*/
</script>
<%
if(!Page.IsPostBack)
PopulateFields(Convert.ToI
%>
<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%"> </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> </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> </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> </td>
<td><div align="right"><asp:Button ID="btnupdate" Text="UpdateRecord" runat="server" OnClick="UpdateRecord"/>
</div></td>
<td> </td>
<td><div align="left"><asp:Button ID="btndelete" Text="DeleteRecord" CommandArgument="idnum" CommandName="Delete" runat="server" />
</div></td>
<td> </td>
</tr>
</table>
<br>
<asp:ValidationSummary id="vsbumit" runat="server" />
<asp:RequiredFieldValidato
<asp:RequiredFieldValidato
<asp:RequiredFieldValidato
<asp:RegularExpressionVali
<asp:RequiredFieldValidato
<asp:RegularExpressionVali
<asp:RequiredFieldValidato
<asp:RegularExpressionVali
<asp:RequiredFieldValidato
</form>
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);
//DBquery.Parameters.Add("
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
Regards,
Aeros
ASKER
ID is an autonumber field, unique Identifier. What exactly are you refering to that I'm setting it again?
ASKER
Your not talking about:
OleDbCommand DBquery = new OleDbCommand("UPDATE Events SET Event_Name='@name' WHERE ID = @id", DBconn);
DBquery.Parameters.Add("@i d",txtid.T ext); <--------------------
DBquery.Parameters.Add("@n ame",txtna me.Text);
DBquery.Parameters.Add("@l ocation",t xtlocation .Text);
that are you???
OleDbCommand DBquery = new OleDbCommand("UPDATE Events SET Event_Name='@name' WHERE ID = @id", DBconn);
DBquery.Parameters.Add("@i
DBquery.Parameters.Add("@n
DBquery.Parameters.Add("@l
that are you???
yes the one with the comments.
ASKER
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
Regards,
Aeros
So when you update just put the id in the WHERE Clause.
ASKER
It's a dynamic value
Like so:
UPDATE Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'
UPDATE Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'
ASKER
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")
Where = Me.mylabel.text
or
Where = MyVariable
or
Where = Session("MyVariable")
ASKER
It runs through all the code but doesn't update the values
ASKER
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...
ASKER
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.Con cat("Provi der=Micros oft.Jet.Ol eDb.4.0; Data Source=",Server.MapPath(". ./db/calen dar.mdb"), ";"));
OleDbCommand DBquery = new OleDbCommand("DELETE FROM Events WHERE ID=@id", DBconn);
DBquery.Parameters.Add("@i d",txtid.T ext);
DBconn.Open();
DBquery.ExecuteNonQuery();
DBconn.Close();
}
public void DeleteRecord(Object s, EventArgs e)
{
//do the delete work
OleDbDataReader DBreader;
OleDbConnection DBconn = new OleDbConnection(String.Con
OleDbCommand DBquery = new OleDbCommand("DELETE FROM Events WHERE ID=@id", DBconn);
DBquery.Parameters.Add("@i
DBconn.Open();
DBquery.ExecuteNonQuery();
DBconn.Close();
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Heres my connection string as well:
<appSettings>
<add key="SiteDB" value="Provider=Microsoft. Jet.OleDb. 4.0;Data Source=C:\Inetpub\wwwroot\ DOTNETShop pingCart\D atabase\Si te.mdb;">< /add>
</appSettings>
<appSettings>
<add key="SiteDB" value="Provider=Microsoft.
</appSettings>
ASKER
I'll give it a shot
ASKER
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.
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
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.Co unt == 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
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.Co
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
Regards,
Aeros
ASKER
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.
ASKER
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?
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?
yes
ASKER
heh...yes to the ID, or yes to the other?!
only the ID
ASKER
ok thanks!
ASKER
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.Con cat("Provi der=Micros oft.Jet.Ol eDb.4.0; Data Source=",Server.MapPath(". ./db/calen dar.mdb"), ";"));
OleDbCommand DBquery = new OleDbCommand("DELETE FROM Events WHERE ID=" + txtid.Text, DBconn);
DBconn.Open();
DBquery.ExecuteNonQuery();
DBconn.Close();
}
public void DeleteRecord(Object s, EventArgs e)
{
//do the delete work
OleDbDataReader DBreader;
OleDbConnection DBconn = new OleDbConnection(String.Con
OleDbCommand DBquery = new OleDbCommand("DELETE FROM Events WHERE ID=" + txtid.Text, DBconn);
DBconn.Open();
DBquery.ExecuteNonQuery();
DBconn.Close();
}
what happens?
ASKER
Nothing, just doesn't delete the file =\
ASKER
er....record
what happens if you paste the command into access?
ASKER
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\ SavoyFurni ture\Inclu des\Site.m db;"></add >
</appSettings>
And then something like this:
#Region " Products DataGrid "
Private Sub Products_ItemDataBound(ByV al sender As System.Object, ByVal e As System.Web.UI.WebControls. DataGridIt emEventArg s) Handles Products.ItemCreated
Dim DeleteItem As LinkButton
Dim ProductName As Label
Dim ImageSrc As HtmlInputHidden
If e.Item.ItemType = ListItemType.AlternatingIt em Or e.Item.ItemType = ListItemType.Item Then
DeleteItem = DirectCast(e.Item.FindCont rol("Delet eItem"), LinkButton)
DeleteItem.Attributes.Add( "onclick", "return confirm('Are you sure you want to delete this " & _
"product?');")
ProductName = DirectCast(e.Item.FindCont rol("Produ ctInfo"), Label)
ProductName.Text = CStr(DataBinder.Eval(e.Ite m.DataItem , "ProductName"))
ProductName.Text &= "<BR> &nb sp; & #187; " & _
CStr(DataBinder.Eval(e.Ite m.DataItem , "Tier1Name"))
If Not DataBinder.Eval(e.Item.Dat aItem, "Tier2Name") Is DBNull.Value Then
ProductName.Text &= " » " & CStr(DataBinder.Eval(e.Ite m.DataItem , "Tier2Name"))
End If
If Not DataBinder.Eval(e.Item.Dat aItem, "Tier3Name") Is DBNull.Value Then
ProductName.Text &= " » " & CStr(DataBinder.Eval(e.Ite m.DataItem , "Tier3Name"))
End If
'ImageSrc = DirectCast(e.Item.FindCont rol("ItemI mageSrc"), HtmlInputHidden)
'ImageSrc.Value = CStr(DataBinder.Eval(e.Ite m.DataItem , "ImageSrc"))
End If
End Sub
Private Sub Products_ItemCommand(ByVal source As System.Object, ByVal e As System.Web.UI.WebControls. DataGridCo mmandEvent Args) Handles Products.ItemCommand
If e.CommandName = "Delete" Then
If IsRefresh Then Return
Dim cnn As New OleDb.OleDbConnection(Conf igurationS ettings.Ap pSettings( "SiteDB"))
Dim cmd As New OleDb.OleDbCommand
Dim ImageSrc As String = DirectCast(e.Item.FindCont rol("ItemI mageSrc"), HtmlInputHidden).Value
Dim ProductImage As IO.FileInfo
cmd.CommandType = CommandType.Text
cmd.CommandText = "DELETE FROM Products WHERE ProductID = " & _
CStr(Products.DataKeys(e.I tem.ItemIn dex))
cmd.Connection = cnn
cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()
cmd.Dispose()
cnn.Dispose()
Try
ProductImage = New IO.FileInfo(Server.MapPath ("~/Pictur es/Product s/" & ImageSrc))
If ProductImage.Exists Then ProductImage.Delete()
ProductImage = New IO.FileInfo(Server.MapPath ("~/Pictur es/Product s/Thumbnai ls/" & ImageSrc))
If ProductImage.Exists Then ProductImage.Delete()
Catch ex As Exception
End Try
LoadProductData()
ElseIf e.CommandName = "Edit" Then
Response.Redirect("EditPro duct.aspx? pid=" & CStr(Products.DataKeys(e.I tem.ItemIn dex)))
End If
End Sub
Private Sub LoadProductData(Optional ByVal Query As String = "")
Dim cnn As New OleDb.OleDbConnection(Conf igurationS ettings.Ap pSettings( "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
<appSettings>
<add key="SiteDB" value="Provider=Microsoft.
</appSettings>
And then something like this:
#Region " Products DataGrid "
Private Sub Products_ItemDataBound(ByV
Dim DeleteItem As LinkButton
Dim ProductName As Label
Dim ImageSrc As HtmlInputHidden
If e.Item.ItemType = ListItemType.AlternatingIt
DeleteItem = DirectCast(e.Item.FindCont
DeleteItem.Attributes.Add(
"product?');")
ProductName = DirectCast(e.Item.FindCont
ProductName.Text = CStr(DataBinder.Eval(e.Ite
ProductName.Text &= "<BR> &nb
CStr(DataBinder.Eval(e.Ite
If Not DataBinder.Eval(e.Item.Dat
ProductName.Text &= " » " & CStr(DataBinder.Eval(e.Ite
End If
If Not DataBinder.Eval(e.Item.Dat
ProductName.Text &= " » " & CStr(DataBinder.Eval(e.Ite
End If
'ImageSrc = DirectCast(e.Item.FindCont
'ImageSrc.Value = CStr(DataBinder.Eval(e.Ite
End If
End Sub
Private Sub Products_ItemCommand(ByVal
If e.CommandName = "Delete" Then
If IsRefresh Then Return
Dim cnn As New OleDb.OleDbConnection(Conf
Dim cmd As New OleDb.OleDbCommand
Dim ImageSrc As String = DirectCast(e.Item.FindCont
Dim ProductImage As IO.FileInfo
cmd.CommandType = CommandType.Text
cmd.CommandText = "DELETE FROM Products WHERE ProductID = " & _
CStr(Products.DataKeys(e.I
cmd.Connection = cnn
cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()
cmd.Dispose()
cnn.Dispose()
Try
ProductImage = New IO.FileInfo(Server.MapPath
If ProductImage.Exists Then ProductImage.Delete()
ProductImage = New IO.FileInfo(Server.MapPath
If ProductImage.Exists Then ProductImage.Delete()
Catch ex As Exception
End Try
LoadProductData()
ElseIf e.CommandName = "Edit" Then
Response.Redirect("EditPro
End If
End Sub
Private Sub LoadProductData(Optional ByVal Query As String = "")
Dim cnn As New OleDb.OleDbConnection(Conf
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
ASKER
Thanks! Stupid mistake on my part, guess that's what happens when your rushed! =)
Thanks Again!
Thanks Again!
np, please leave positive feedback as I have done my best to answer your questions throughly
Regards,
Aeros
Regards,
Aeros
ASKER
Already have =) Thanks again!
np have a good one I appreciate it
Regards,
Aeros
Regards,
Aeros
Regards,
Aeros