Solved

Form for inserting new record

Posted on 2008-10-14
8
530 Views
Last Modified: 2013-11-07
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.
0
Comment
Question by:Frosty555
  • 4
  • 3
8 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
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
 
LVL 11

Expert Comment

by:fanopoe
Comment Utility
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
 
LVL 31

Author Comment

by:Frosty555
Comment Utility
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
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
Comment Utility
>>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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 31

Author Comment

by:Frosty555
Comment Utility
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
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 500 total points
Comment Utility
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
 
LVL 31

Author Comment

by:Frosty555
Comment Utility
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
 
LVL 31

Author Comment

by:Frosty555
Comment Utility
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now