how to correctly build a formview dynamically/programmatically/code behind (did i do this

What i've written works(it does what it's being asked to do) but i was wondering if this is the correct way to programmatically bind and alter the formview
Reason: We have various needs that will need to be achieved like, various SQL statements, variables & rules... so i was needing more control over what the wizard provides...

So i was wondering if this way is the "Best practice" and etc..

**Also i gotta figure out how to keep it form inserting a row on "refresh button" & I couldn't get itemUpdated to work?

For brevity of code I'll post the ItemTemplate, which is replaced by EditItemTemplate or InsertItemTemplate with <%#Bind()%> on the Textboxes if necessary.

<asp:LabelID="lblError"runat="server"></asp:Label><br/>

<br/>

<asp:FormViewID="FormView1"runat="server"OnModeChanging="FormView1_ModeChanging"OnItemUpdating="FormView1_ItemUpdating"DataKeyNames="RowID"OnItemInserting="FormView1_ItemInserting">

<ItemTemplate>
<tablestyle="width: 338px; height: 264px">

<tr>
<tdstyle="width: 100px">Employee Id :</td>|
<tdstyle="width: 100px"><asp:LabelID="Label1"runat="server"Text='<%# Eval("RowID") %>'></asp:Label></td>
</tr>

<tr>
<tdstyle="width: 100px">First Name:</td>
<tdstyle="width: 100px"><asp:LabelID="Label2"runat="server"Text='<%# Eval("FirstName") %>'></asp:Label></td>
</tr>

<tr>
<tdstyle="width: 100px">Last Name:</td>
<tdstyle="width: 100px"><asp:LabelID="Label6"runat="server"Text='<%# Eval("LastName") %>'></asp:Label></td>
</tr>

<tr>
<tdstyle="width: 100px">Title:</td>
<tdstyle="width: 100px"><asp:LabelID="Label3"runat="server"Text='<%# Eval("Title") %>'></asp:Label></td>
</tr>

<tr>
<tdstyle="width: 100px">Phone #:</td>
<tdstyle="width: 100px"><asp:LabelID="Label4"runat="server"Text='<%# Eval("PhoneNumber") %>'></asp:Label></td>
</tr>

<tr>
<tdstyle="width: 100px">Notes</td>
<tdstyle="width: 100px"><asp:LabelID="Label5"runat="server"Height="85px"Text='<%# Eval("Notes") %>'Width="216px"></asp:Label></td>
</tr>

</table>

//These values change based on the template (Update CommandName="Update" ) or (Insert CommandName="Insert")
<asp:LinkButtonID="editButton"runat="server"CommandName="Edit">Edit</asp:LinkButton>|<asp:LinkButtonID="createButton"runat="server"CommandName="New">Create New</asp:LinkButton><br/>

<br/>

</ItemTemplate>

Code behind:

1public partialclass _Default : System.Web.UI.Page2{3//global variables45int myRowID;678protected void Page_Load(object sender, EventArgs e)9 {10//COMING IN FRESH START AT 1 ELSE USE WHATEVER VALUE myRowID is passed1112if (myRowID ==0)13 {14 myRowID = 1001;15 }1617if (!Page.IsPostBack)18 {19 BindData();20 }2122 }2324//BIND DATA25protected void BindData()26 {27//Create Connection28string strConnectionString = ConfigurationManager.ConnectionStrings["pubsConnectionString"].ConnectionString;29 SqlConnection myConnection =new SqlConnection(strConnectionString);3031//Create the Command32string strCommandText ="SELECT * FROM [Fishbowl] WHERE ([RowID] = '"+myRowID+"')";33 SqlCommand myCommand =new SqlCommand(strCommandText, myConnection);3435//open the connection36 myConnection.Open();3738 FormView1.DataSource = myCommand.ExecuteReader();39 FormView1.DataBind();4041//Close Connection42 myConnection.Close();4344 }454647CREATE A NEW KEY/ROWID48public int getLastID()49 {50//Create Connection51string strConnectionString = ConfigurationManager.ConnectionStrings["pubsConnectionString"].ConnectionString;52 SqlConnection myConnection =new SqlConnection(strConnectionString);5354//Create the Command55string strCommandText ="SELECT max(RowID) FROM [Fishbowl] ";56 SqlCommand myCommand =new SqlCommand(strCommandText, myConnection);5758//open the database connection59 myConnection.Open();6061//convert the value into something we can use62int lastID = (int)myCommand.ExecuteScalar()+1;6364//close the connection65 myConnection.Close();66return lastID;67 }686970//SWITCH THE FORMVIEW MODES71protected void FormView1_ModeChanging(object sender, FormViewModeEventArgs e)72 {73switch (e.NewMode)74 {75case FormViewMode.Edit:76 FormView1.ChangeMode(FormViewMode.Edit);77break;78case FormViewMode.Insert:79 FormView1.ChangeMode(FormViewMode.Insert);80break;81default:82 FormView1.ChangeMode(FormViewMode.ReadOnly);83break;84 }85 BindData();86 }8788//UPDATE THE CONTENT89protected void FormView1_ItemUpdating(object sender, FormViewUpdateEventArgs e)90 {91//Create Connection92string strConnectionString = ConfigurationManager.ConnectionStrings["pubsConnectionString"].ConnectionString;93 SqlConnection myConnection =new SqlConnection(strConnectionString);9495try96 {97//Query to execute98string strQuery ="UPDATE [Fishbowl] SET [FirstName] = @dotnet.itags.org.FirstName, [LastName] = @dotnet.itags.org.LastName, [Title] = @dotnet.itags.org.Title, [PhoneNumber] = @dotnet.itags.org.PhoneNumber, [Notes] = @dotnet.itags.org.Notes WHERE [RowID] = @dotnet.itags.org.RowID";99100//Create the Command101 SqlCommand myCommand =new SqlCommand(strQuery, myConnection);102103// FormViewRow row = FormView1.Row;104 //Set up parameters105 TextBox txtFirstName = (TextBox)FormView1.FindControl("txtFirstName");106 TextBox txtLastName = (TextBox)FormView1.FindControl("txtLastName");107 TextBox txtTitle = (TextBox)FormView1.FindControl("txtTitle");108 TextBox txtPhoneNumber = (TextBox)FormView1.FindControl("txtPhoneNumber");109 TextBox txtNotes = (TextBox)FormView1.FindControl("txtNotes");110111 myCommand.Parameters.AddWithValue("@dotnet.itags.org.FirstName", txtFirstName.Text );112 myCommand.Parameters.AddWithValue("@dotnet.itags.org.LastName", txtLastName.Text);113 myCommand.Parameters.AddWithValue("@dotnet.itags.org.Title", txtTitle.Text);114 myCommand.Parameters.AddWithValue("@dotnet.itags.org.PhoneNumber", txtPhoneNumber.Text);115 myCommand.Parameters.AddWithValue("@dotnet.itags.org.Notes", txtNotes.Text);116 myCommand.Parameters.AddWithValue("@dotnet.itags.org.RowID", myRowID);117118//open the database connection119 myConnection.Open();120 myCommand.ExecuteNonQuery();121122 }123catch (Exception ex)124 {125//Houston We have problems126127 lblError.Text = ex.Message;128 }129finally130 {131//close the database connection132 myConnection.Close();133 }134135 FormView1.ChangeMode(FormViewMode.ReadOnly);136 BindData();137 }138139//i guess if we can get this work, then we can validate the updated row140141void FormView1_ItemUpdated(object sender, FormViewUpdatedEventArgs e)142 {143// for some strange reason this won't fire.. really don't know why144 }145146//Insert ROW147148149protected void FormView1_ItemInserting(object sender, FormViewInsertEventArgs e)150 {151152//Create Connection153string strConnectionString = ConfigurationManager.ConnectionStrings["pubsConnectionString"].ConnectionString;154 SqlConnection myConnection =new SqlConnection(strConnectionString);155156try157 {158//Query to execute159string strQuery ="INSERT [Fishbowl] ([FirstName], [LastName], [Title], [PhoneNumber] , [Notes], [RowID]) VALUES ( @dotnet.itags.org.FirstName, @dotnet.itags.org.LastName,@dotnet.itags.org.Title, @dotnet.itags.org.PhoneNumber, @dotnet.itags.org.Notes, @dotnet.itags.org.RowID)";160161//Create the Command162 SqlCommand myCommand =new SqlCommand(strQuery, myConnection);163164//Set up parameters165 TextBox txtFirstName = (TextBox)FormView1.FindControl("txtFirstName");166 TextBox txtLastName = (TextBox)FormView1.FindControl("txtLastName");167 TextBox txtTitle = (TextBox)FormView1.FindControl("txtTitle");168 TextBox txtPhoneNumber = (TextBox)FormView1.FindControl("txtPhoneNumber");169 TextBox txtNotes = (TextBox)FormView1.FindControl("txtNotes");170171 myCommand.Parameters.AddWithValue("@dotnet.itags.org.FirstName", txtFirstName.Text);172 myCommand.Parameters.AddWithValue("@dotnet.itags.org.LastName", txtLastName.Text);173 myCommand.Parameters.AddWithValue("@dotnet.itags.org.Title", txtTitle.Text);174 myCommand.Parameters.AddWithValue("@dotnet.itags.org.PhoneNumber", txtPhoneNumber.Text);175 myCommand.Parameters.AddWithValue("@dotnet.itags.org.Notes", txtNotes.Text);176 myCommand.Parameters.AddWithValue("@dotnet.itags.org.RowID", getLastID());177178//open the database connection179 myConnection.Open();180 myCommand.ExecuteNonQuery();181182 }183catch (Exception ex)184 {185//Houston We have problems186 lblError.Text = ex.Message;187 }188finally189 {190//close the database connection191 myConnection.Close();192 }193194 FormView1.ChangeMode(FormViewMode.ReadOnly);195 myRowID = getLastID() - 1;196 BindData();197 lblError.Text ="Row Added";198 }199}200201

any help suggestions will be greatly appriecated thx in adv

well maybe this is a way to do it... so I'll go with this way until something breaks

another way is just to hi-jack the sqlDataSource Commands in the code behind..

if (condition 1) {

SqlDataSource1.SelectCommand ="SELECT * FROM [Fishbowl] WHERE ([RowID] = '"+myRowID+"')";

}else{

//condition2

SqlDataSource1.SelectCommand ="SELECT * FROM [Fishbowl] WHERE ([RowID] = '"+myRowID+"')";

}

Duh!!!!

This way i'll total control over what i want to query :)