Want to use and Update as an Insert... :-(

[dzr]

Ok, I'm trying to actually code some stuff by hand since I can't seem to get simple enough directions on how to make a form that loads up all the current data from one table (as in an update) but instead of updating, it inserts the edits into another table (or at least a new row into the same table).

I'm pretty close, I think... If someone could point me to an example or post one for me of this I would be so grateful!!

what I want is simple - list out active users in from the user table as links to an edit page with the ID on the querystring. With that - it fills up the form that, on submit - inserts the record.


It seems that I can only get the URL variable in GridView but then I can use the <asp:TextBox ... or do I need the <asp:BoundField... ??

Seems like this should be so simple!! in php I would just do something like
<input type="text" name="username" value="<? echo $recordset['username']; ?>">

 

[Isadore]

dzr: Here is simple example of an Update event:

Dim DBUpdate As New OleDbCommand
Dim cnnA As New OleDbConnection( _
 "Provider=Microsoft.Jet.OLEDB.4.0; " & _
 "Data Source=" & Server.MapPath("fpdb\Activity.mdb;"))
 DBUpdate.CommandText = "UPDATE tblActivity SET MeetingPlace = '" & txtPlace.Text & "', MeetingDate = '" & txtDate.Text & "' WHERE ID =" & Request.QueryString("ID")
Try 
 DBUpdate.Connection = cnnA
 DBUpdate.Connection.Open
 DBUpdate.ExecuteNonQuery()
 cnnA.Close()
Finally
 If (Not cnnA Is Nothing) Then
   cnnA.Close()
 End If
End Try

 

[dzr]

Isador - thank you for your reply!

Unfortunately, it's not really what I'm having a hard time with.

It's the actual form that is the problem. I have been able to do the code behind for all sorts of things.

I really neeed to figure out how to populate a form w/ a record's current values from the database and then, if the user clicks 'update' the changes are saved in a temp table until approved.

and also, as a php programmer I'm using c# - it's been yeeaaaarrrrs since I did any VB coding and it's harder for me to translate.

thanks!

 

[Isadore]

dzr: I hear ya. You'll probably want to use a DataReader to populate your textboxes and then once the "Update" button is clicked you'll want to save all values to a temporary table (when you say temporary do you mean "DataSet" temporary or "actual Table" temporary? Using javascript, e.g., to detect those textboxes that were changed is too much time and trouble to mess with.

Seems like simple binding is all you need to populate the Form (or if the Form opens with no values then you simply read them into the SQL Update statement).

 

[dzr]

The below works very close to what I want to do. I did this "updatecommand="insert into..." and it works great!

Currently it lists one program at a time and you click 'edit' to get the form fields.

I want to
1) have the ID passed in via querystring so the user reaches a page with the editable form fields
2) after clicking 'update' i want it to go to a new page, not back to the non-editable version of the program.

If I change the datasource to have the @tbl1_id coming in, set parameter as querystring and querystringfield as @tbl1_id, go to the page with edit.aspx?tbl1_1d=32 (which i know is in the DB) -- i just get no employees found.

I had the last part of the above working but I broke that code somehow and cannot get it back...


Please help!!!

<asp:FormView ID="FormView1"
DataSourceID="SqlDataSource1"
DataKeyNames="tbl1_id"
RunAt="server"
allowpaging="true"
emptydatatext="No employees found."
BorderStyle="None"
CellPadding="2"
Width="250px" EnableTheming="False">


<rowstyle backcolor="LightGreen"
wrap="False"/>
<editrowstyle backcolor="LightBlue"
wrap="False"/>

<itemtemplate>
<table>
<tr>
<td>
<b>Name:</b>
</td>
<td>
<%# Eval("tbl1_contact_name")%>
</td>
</tr>
<tr>
<td colspan="2">
<asp:linkbutton id="Edit"
text="Edit"
commandname="Edit"
runat="server"/>
</td>
</tr>
</table>
</itemtemplate>

<edititemtemplate>
<table>
<tr>
<td>
<b>Name:</b>
</td>
<td>
<asp:textbox id="LastNameUpdateTextBox"
text='<%# Bind("tbl1_contact_name") %>'
runat="server"/>
</td>
</tr>
<tr>
<td colspan="2">
<asp:linkbutton id="UpdateButton"
text="Update"
commandname="Update"
runat="server"/>
<asp:linkbutton id="CancelButton"
text="Cancel"
commandname="Cancel"
runat="server"/>
</td>
</tr>
</table>
</edititemtemplate>

</asp:formview>

<asp:sqldatasource id="SqlDataSource1"
selectcommand="Select * From [contact_main]"
updatecommand="insert into contact_main_temp (tbl1_contact_name_temp) VALUES (@tbl1_contact_name)"
connectionstring="<%$ ConnectionStrings:myDBConnectionString %>"
runat="server"/>

 

[dzr]

Yes that's it Isadore! My insert form works fine, I just want to take that form and fill it up with a record's data to make the edit form but I need more control so, on submit - i can do things ie. send email...

 

[dzr]

my insert looks like this:
<asp:TextBox ID="contact_name" runat="server" Width="250px"></asp:TextBox>

how can I combine the 2 ways?
does it need to be within formview tags?

could I do something like this?

<asp:TextBox ID="contact_name" runat="server" Width="250px" ><%# Eval("tbl1_contact_name")%></asp:TextBox>

 

[Isadore]

dzr: I like this new style, using the asp:FormView, etc. Is this specific to SQL -- I'm not technically a trained programmer, and do not keep up with this as much as I should, etc. But I like your approach, very cutting edge stuff.

My only response, and perhaps ca8msm or Jim could drop by, or others, to help (they are more knowlegeable on SQL matters), is that there are two basic ways to carry out simple binding, and one is your proposed method; which should work just fine.

The other is to read it in with a reader - can't see creating a DataSet, etc for this.

This formview business is new to me; I'd like to hear an argument on this, i.e., why <asp:FormView..> rather than the standard <Form..> tags.

 

[dzr]

I Got It! I don't know whether to celebrate or hang my head in shame.

I keep the same textbox code as on my input form.
<asp:TextBox ID="contact_name" runat="server" Width="250px"></asp:TextBox>

On page load, do my select, get results... and then set the variable value... with a data reader, just like you said!!!

form:
<asp:TextBox ID="contact_name" runat="server" Width="250px"></asp:TextBox>

code behind:
contact_name.Text = dbDataReader["contact_name"].ToString()



---

Re. FormView -- it's an asp.net thing, not SQL. I'm very new to asp.net so I don't know much. For me, it was the only way I could get the fields out of the database and customize the layout of the page (as opposed to the 2 column detailsview).

But I still need control over what happened on submit.
There may have been ways for me to do w/ details, grid or formview but really, my biggest problem with asp.net is that there are too many options,factors,styles,nuances.... makes my head spin. Seems like tutorials should be split up by coding styles -C# vs VB, wsywig vs coder etc. (and yes, I'm just fussy cause I could have done this project 4x over in php

Thanks for all your help!!!!

 

[Isadore]

dzr: Remember there are C# to VB transformation pages out there on the web that go between the two; take advantage of those (I've seen 2 C# to VB, so assume there must be a VB to C# somewhere as well).

 

[0ddball]

This is the C# equivelent of Isadore's VB (give or take a load of parameters).

I wrote it in notepad and havn't debugged or spellchecked it in any way shape of form.

Just because it was mentioned and I had the time.

// Set up your connection and command
OleDbConnection dCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("fpdb\Activity.mdb;"));

// Create your command
OleDbCommand dUpdate = dCon.CreateCommand();

// Configure it
dUpdate.CommandText = "UPDATE tblActivity SET MeetingPlace = ?, MeetingDate = ? WHERE ID = ?";
dUpdate.Parameters.Add(new OleDbParameter("place", OleDbType.VarChar));
dUpdate.Parameters.Add(new OleDbParameter("date", OleDbType.VarChar));
dUpdate.Parameters.Add(new OleDbParameter("id", OleDbType.VarChar));

// Update the values
dUpdate.Parameters["place"].Value = txtPlace.Text;
dUpdate.Parameters["date"].Value = txtDate.Text;
dUpdate.Parameters["id"].Value = Request.QueryString["ID"];

try
{
	dCon.Open();
	dCon.ExecureNonQuery();
}
catch (Exception e)
{
	// Handle your connection problems here or leave this section out
	// If you leave it out your program will fall over
	// This is sometimes preferable behavior if the error is critical
}
finally
{
	dUpdate = null;

	if (dCon.State != ConnectionState.Closed)
		dCon.Close();
	
	dCon = null;
}

Yet another unchecked rambling brought to you by:
Oddball

 

[Isadore]

Thanks oddball

 

[dzr]

ok, i think i get this. i'll give it a shot...! i really would prefer to use the tools that asp offers... (too bad it won't let me code in php. then i would feel smart again!)