Form for inserting new record

I'm building a website in ASP.NET, connecting to a local sql server 2005 database.

I'm working on the page for the "client" table.  The page has a formview inside it, with several gridviews that all get statistical information about the client. Next, there is a detailsview that is used to update the client. I set the updatequery, added the fields, added the CommandField for "update / cancel" etc. and it's working.

Now I need to be able to create a new client but I'm having trouble figuring out how to do that. I added the commandfield for "Insert / cancel" and set the insertquery, but some weird things happen and I'm confused.
 
       1) If I don't specify the clientid in my page's GET paremter, my sqldatasource returns zero records and no details view is shown at all, making it impossible to add a new client.

       2) If I open an existing client, and then click the "New" link, both the "Insert / Cancel" links and the "Update/Cancel" links show. Clicking Insert does not cause a new client to be created

       3) How do I figure out what the new clientID is after inserting? The clientid is an autoincrement primary key field in my sql database.

So... I'm pretty sure I'm missing something somewhere, and this is just my first stab at making simple ASP.NET applications. So maybe I did it wrong. What's the correct way to do it?

Would it be easier to create a brand new page called "client_add" that only inserts, and leave the updating/deleting up to another page? If so how do I do that? Because the page would have to display in "insert" mode automatically without the user having to click on the "New" link...

I don't know. Suggestions are welcome.
LVL 31
Frosty555Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
Every time I've either added records in conjunction with a GridView or seen examples of it, the add was done outside the grid.  So there was a separate set of text boxes (or other controls) and an Add button.  Under that Add button was the code to perform the Insert.
0
fanopoeCommented:
I handle adds in the item template footer row. the problem is you have to create the exact same set of fields for the empty data template to accommodate when there are no records. Then use you own command button to capture/save the record.

Attached is a sample GV that has two fields, First Name and Last Name. It demonstrates the use of the empty data template and the asp:template fields and their use of EditItem, Item, and Footer template fields. If you're not familiar with it, the '<%# Bind("LName") %>' and '<%# Eval("LName") %>' are scripting signatures that Print data values (Last Name in this example) using Eval, and allow editing using Bind.

You'll need to wire in your own data sources and code behind, but hopefully this will give you a good head start on another way to approach the problem.
<asp:GridView ID="gvExample" runat="server" AutoGenerateColumns="false" CellPadding="4"
	DataKeyNames="YourDataKeys" DataSourceID="YourDataSource">
	<EmptyDataTemplate>
		<table>
			<tr style="line-height: normal">
				<td>
					First Name
				</td>
				<td>
					Last Name
				</td>
				<td>
				</td>
			</tr>
			<tr valign="top">
				<td>
					<asp:TextBox ID="txtFName" runat="server"></asp:TextBox>
				</td>
				<td>
					<asp:TextBox ID="txtLName" runat="server"></asp:TextBox>
				</td>
				<td>
					<asp:Button ID="btnAddUser" runat="server" CommandName="Insert" CssClass="submit"
						Text="Insert" Visible="True" />
				</td>
			</tr>
		</table>
	</EmptyDataTemplate>
	<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
	<Columns>
		<asp:TemplateField FooterStyle-VerticalAlign="Top" HeaderText="First Name">
			<EditItemTemplate>
				<asp:TextBox ID="txtFName" runat="server" Text='<%# Bind("FName") %>'></asp:TextBox>
			</EditItemTemplate>
			<HeaderStyle ForeColor="White" HorizontalAlign="left" />
			<ItemTemplate>
				<asp:Label ID="lblFName" runat="server" Text='<%# Eval("FName") %>'></asp:Label>
			</ItemTemplate>
			<FooterTemplate>
				<asp:TextBox ID="txtFName" runat="server"></asp:TextBox>
			</FooterTemplate>
		</asp:TemplateField>
		<asp:TemplateField FooterStyle-VerticalAlign="Top" HeaderText="Last Name">
			<EditItemTemplate>
				<asp:TextBox ID="txtLName" runat="server" Text='<%# Bind("LName") %>'></asp:TextBox>
			</EditItemTemplate>
			<HeaderStyle ForeColor="White" HorizontalAlign="left" />
			<ItemTemplate>
				<asp:Label ID="lblLName" runat="server" Text='<%# Eval("FName") %>'></asp:Label>
			</ItemTemplate>
			<FooterTemplate>
				<asp:TextBox ID="txtLName" runat="server"></asp:TextBox>
			</FooterTemplate>
		</asp:TemplateField>
		<asp:TemplateField FooterStyle-VerticalAlign="Top" HeaderText="Actions" ItemStyle-Wrap="false">
			<EditItemTemplate>
				<asp:LinkButton ID="btnUpdate" runat="server" CommandName="UpdateRow" Text="Update"></asp:LinkButton>
				<asp:LinkButton ID="btnCancel" runat="server" CommandName="Cancel" Text="Cancel"></asp:LinkButton>
			</EditItemTemplate>
			<ItemTemplate>
				<asp:LinkButton ID="btnEdit" runat="server" CommandName="Edit" Text="Edit"></asp:LinkButton>
				<asp:LinkButton ID="btnDelete" runat="server" CommandArgument='<%# Eval("FName") %>'
					CommandName="DeleteUser" Text="Delete"></asp:LinkButton>
			</ItemTemplate>
			<FooterTemplate>
				<asp:LinkButton ID="btnInsert" runat="server" CommandName="Insert" ForeColor="white"
					Text="Add"></asp:LinkButton>
			</FooterTemplate>
		</asp:TemplateField>
	</Columns>
</asp:GridView>

Open in new window

0
Frosty555Author Commented:
I was doing this from a DetailsView... not a gridview.

But that's okay! I'm willing to do it from a gridview if that works. Does this mean I will have to convert *every* field in my gridview into a templatefield so that I can add a footer template?
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Daniel WilsonCommented:
>>Does this mean I will have to convert *every* field in my gridview into a templatefield so that I can add a footer template?

I think so.  Or you could do the separate ADD boxes at the bottom of the grid (or other reasonable place on the form).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frosty555Author Commented:
If I set up the add boxes somewhere else on the form... how do I tell it to use the Insert query that my gridview was using? I have a sqldatasource on the page called SqlDataSource1 that has the insert query in it. Do I add a details view?
0
Daniel WilsonCommented:
I would do it rather manually ... with a button.

Private Sub btnInsert_Click(sender as Object, e as EventArgs)
  dim cmd as SqlCommand = SqlDatSource1.InsertCommand

'Add your parameters to the command

  'then execute it
  cmd.ExecuteNonQuery
 
end sub
0
Frosty555Author Commented:
Hmm okay. So I now have a new page which has a detailsview on it with the fields I want, and I have the insert button inserting the record. I think it's working correctly, though it needs some tweaking.

Setting the detailsview's DefaultMode property to "Insert" helped tremendously, now I don't actually have to click the "New" link before I can fill out the fields.

In the Page_Load I am setting the default value of one textbox to be the default date:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim otxt As TextBox = DetailsView1.FindControl("txtDateCreated")
            otxt.Text = Now.ToString()
        End If
    End Sub

But.... I need to set another field's default value to be a value pulled from the databaase:

E.g.
       otxt2.Text = {{{result of SELECT somefield FROM dbo.defaults WHERE clause = 'something'}}}

I have a connection string defined in the web.config:;

 <connectionStrings>
  <add name="kcsportalConnectionString" connectionString="Data Source=localhost;Initial Catalog=kcsportal;Integrated Security=True"
   providerName="System.Data.SqlClient" />
 </connectionStrings>

How do I do that on the fly query of the database?
0
Frosty555Author Commented:
Figured it out after much digging.
            Dim oCnn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("kcsportalConnectionString").ConnectionString)
            oCnn.Open()
            Dim oCmd As System.Data.SqlClient.SqlCommand = oCnn.CreateCommand()
            oCmd.CommandText = "SELECT MAX(clientid) FROM clients"
            Dim cID As String = oCmd.ExecuteScalar()
            txtPK.Text = cID
            oCnn.Close()

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.