Solved

Form for inserting new record

Posted on 2008-10-14
8
534 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
ID: 22714915
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
ID: 22715261
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
ID: 22715486
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
ID: 22732760
>>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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 31

Author Comment

by:Frosty555
ID: 22745758
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
ID: 22748335
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
ID: 22748603
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
ID: 22750598
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
A short film showing how OnPage and Connectwise integration works.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

947 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

19 Experts available now in Live!

Get 1:1 Help Now