?
Solved

Form for inserting new record

Posted on 2008-10-14
8
Medium Priority
?
551 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 2000 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
 
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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

765 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