Inserting records in the database using Grid control(Appending new Item

Welcome Guest

Search | Active Topics | Sign In | Register

Essential Objects Product Support Forum » All Products » Support » Inserting records in the database using Grid control(Appending new Item

Options

Previous Topic · Next Topic

Teju

Posted: Friday, January 23, 2009 5:47:54 AM

Rank: Member
Groups: Member

Joined: 1/23/2009
Posts: 12

I have just downloaded the controls and started to work on it , but realised is there any way for inserting the values in the database using the grid control and sql data source
If you post some example or link me to that example, it would be great help.
Many Thanks,
Teju

eo_support

Posted: Friday, January 23, 2009 6:03:40 AM

Rank: Administration
Groups: Administration

Joined: 5/27/2007
Posts: 24,089

Hi,

This is a two step process. The first step is to allow user to insert new items into the Grid. The second step is to save the newly inserted items into your DB.

The first step can be easily done by setting the Grid's AllowNewItem to true. Note in order for user to edit, you must have an editable column. The following sample assumes you have a Grid with two column: The first column is a RowNumberColumn (which is not editable), the second column is a TextBoxColumn, which is editable.

Once you run the page, you can add new items into the Grid. You would then post back the page (by clicking a button, for example). Once the page is posted back, you will need to save the newly added items into your DB.

The key for saving the records is AddedItems property. You would basically loop through AddedItems and call whatever your database code to do the work. For example:

Code: C#

Copy

//Open the DB connection
SqlConnection cn = new SqlConnection("your connection string here...");
cn.Open();

//User can add more than items, so we loop through each of them
foreach (EO.Web.GridItem item in grid1.AddedItems)
{
    //Get the cell value in the second column
    string someText = item.Cells[1].Value;

    //Now insert the record
    string sql = string.Format(
        "insert into your_table values ('{0}')", someText);
    SqlCommand cmd = new SqlCommand(sql, cn);
    cmd.ExecuteNonQuery();
}

//Close the connection
cn.Close();

You can put this code inside your "Save" Button's Click handler. Note the SQL related code (open connection, call insert statement) is just for demonstration purpose. It's almost certainly that you will need to change that part so that it works with your own database.

Hope this helps.

Thanks!

Teju

Posted: Friday, January 23, 2009 7:42:44 AM

Rank: Member
Groups: Member

Joined: 1/23/2009
Posts: 12

Hi,
Thanks very much for your reply. I did try to use it and seemed to work but not to solve the whole purpose.
I am trying to loop through all the elements of the gridview ie. the textbox as i won't know how many rows willl be appended. But i just get the value of the first row . I don't know what wrong Iam doing.
If you could help me that would be great. In mean time i will dig further.
Many Thanks,
Teju

eo_support

Posted: Friday, January 23, 2009 7:48:55 AM

Rank: Administration
Groups: Administration

Joined: 5/27/2007
Posts: 24,089

Hi,

I am not sure why you want to to loop through all the elements of the Grid (not GridView! Make sure you are using our product, not somebody else.:P). You need to loop AddedItems for added items, ChangedItems for changed items, DeletedItems for deleted items. Those property contains all information you will need. For example, if user has appended two items, then you will see AddedItems property contains two GridItem object. If user has modified three items, you will ChangedItems contains three GridItem object. Even if the Grid contains 100 items, you will only care about what user modified/changed/deleted when it comes to update your DB.

You can find more information about handling Grid editing here:

http://www.essentialobjects.com/ViewDoc.aspx?t=Grid%2fediting.html

Hope this helps.

Thanks

Teju

Posted: Friday, January 23, 2009 8:00:26 AM

Rank: Member
Groups: Member

Joined: 1/23/2009
Posts: 12

I am definitely using the eo grid below is my code . What i am doing is trying to print all the newly added items .

Code: Visual Basic.NET

Copy

Dim ansgrid As EO.Web.Grid = dvQuestions.FindControl("grdAnswers")
			For Each item As EO.Web.GridItem In ansgrid.AddedItems
			'Get the cell value in the second column
			Dim someText As String = item.Cells(0).Value

			Response.Write(someText)
			Response.End()
		Next

Could you tell me what wrong AM i doing.
Many Thanks,
Teju

eo_support

Posted: Friday, January 23, 2009 8:08:32 AM

Rank: Administration
Groups: Administration

Joined: 5/27/2007
Posts: 24,089

You are very wrong by using Response.Write and Response.End. :) You should not use that. In fact if user added two items, your For Each would loop twice and you would have ended your Response twice (by calling Response.End twice).

The easiest way to see what has been added is use a Label. You would put a Label in the page and then do something like this:

Code: Visual Basic.NET

Copy

Dim s as String = ""

For Each item As EO.Web.GridItem in ansgrid.AddedItems

    'Get the value
    Dim someText As String = item.Cells(0).Value

    'Append it to s
    s = s & someText
Next

'Now display it
Label1.Text = s

Obviously this are just for you to see what has been added. Eventually you will need to change the code to save the information into your database.

Hope this helps.

Thanks

Teju

Posted: Friday, January 23, 2009 8:17:11 AM

Rank: Member
Groups: Member

Joined: 1/23/2009
Posts: 12

Oh silly me its because I am using response .end . I will follow what you say and change my code to reflect the added items in the database.
Many Thanks,
Teju

Teju

Posted: Friday, February 6, 2009 9:40:50 AM

Rank: Member
Groups: Member

Joined: 1/23/2009
Posts: 12

Hi
Sorry for getting so late back to you.
I have used exactly the same way but i am unable to retrieve the data for second column.
For e.g I have four column as below
Item
StartDate
EndDate
DueDate
I can get all the values for Item but not for start date End date etc.
Could you please help.
Many Thanks,
Teju

eo_support

Posted: Friday, February 6, 2009 9:46:48 AM

Rank: Administration
Groups: Administration

Joined: 5/27/2007
Posts: 24,089

Hi,

Please create a complete test page and post both the .aspx and .vb code so that we can take a look. Make sure the code runs independently.

Thanks!

Teju

Posted: Friday, February 6, 2009 9:54:11 AM

Rank: Member
Groups: Member

Joined: 1/23/2009
Posts: 12

Hi Sorry for giving you such a brief.
Below is my aspx

Code: Visual Basic.NET

Copy

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim ansgrid As EO.Web.Grid = dvCourseWork.FindControl("grdCourseItem")
	 Dim s As String = ""
	 Dim s2 As String = ""
	For Each item As EO.Web.GridItem In ansgrid.AddedItems
		'Get the value
		Dim txtanswers As String = item.Cells(0).Value
		Dim txtcorrect As String = item.Cells(1).Value

		'Append it to s
		s = s & txtanswers
		s2 = s2 & txtcorrect
	Next
'Now display it
Label1.Text = s & "<br>" & s2
End Sub

Code: HTML/ASPX

Copy

<eo:Grid  id="grdCourseItem" Width="99%" Height="200px" runat="server" ColumnHeaderDescImage="00050105"
								BorderWidth="1px" IsCallbackByMe="False" BorderColor="#7F9DB9" Font-Size="8.75pt" Font-Names="Tahoma"
								ColumnHeaderAscImage="00050104" GoToBoxVisible="True" GridLines="Both" FixedColumnCount="1"
								ColumnHeaderDividerImage="00050103" GridLineColor="220, 223, 228" AllowNewItem="True"
								FullRowMode="False">
					<FooterStyle CssText="padding-bottom:4px;padding-left:4px;padding-right:4px;padding-top:4px;"></FooterStyle>
					<ItemStyles>
						<eo:GridItemStyleSet>
							<ItemHoverStyle CssText="background-color: whitesmoke"></ItemHoverStyle>
							<CellStyle CssText="padding-left:8px;padding-top:2px;"></CellStyle>
							<ItemStyle CssText="background-color: white"></ItemStyle>
							<FixedColumnCellStyle CssText="border-right: #d6d2c2 1px solid; padding-right: 10px; border-top: #faf9f4 1px solid; border-left: #faf9f4 1px solid; border-bottom: #d6d2c2 1px solid; background-color: #ebeadb; text-align: right"></FixedColumnCellStyle>
						</eo:GridItemStyleSet>
					</ItemStyles>
					<ContentPaneStyle CssText="border-bottom-color:#7f9db9;border-bottom-style:solid;border-bottom-width:1px;border-left-color:#7f9db9;border-left-style:solid;border-left-width:1px;border-right-color:#7f9db9;border-right-style:solid;border-right-width:1px;border-top-color:#7f9db9;border-top-style:solid;border-top-width:1px;"></ContentPaneStyle>
					<ColumnTemplates>
						<eo:TextBoxColumn>
							<TextBoxStyle CssText="BORDER-RIGHT: #7f9db9 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #7f9db9 1px solid; PADDING-LEFT: 2px; FONT-SIZE: 8.75pt; PADDING-BOTTOM: 1px; MARGIN: 0px; BORDER-LEFT: #7f9db9 1px solid; PADDING-TOP: 2px; BORDER-BOTTOM: #7f9db9 1px solid; FONT-FAMILY: Tahoma"></TextBoxStyle>
						</eo:TextBoxColumn>
						<eo:DateTimeColumn>
				<DatePicker DayHeaderFormat="FirstLetter" DayCellHeight="16" DisabledDates="" TitleLeftArrowImageUrl="DefaultSubMenuIconRTL"
					OtherMonthDayVisible="True" DayCellWidth="19" TitleRightArrowImageUrl="DefaultSubMenuIcon"
					ControlSkinID="None" SelectedDates="" PickerFormat="dd/MM/yyyy" PickerHint="dd/mm/yyyy">
					<DayHoverStyle CssText="font-family: tahoma; font-size: 12px; border-right: #fbe694 1px solid; border-top: #fbe694 1px solid; border-left: #fbe694 1px solid; border-bottom: #fbe694 1px solid"></DayHoverStyle>
					<TitleStyle CssText="background-color:#9ebef5;font-family:Tahoma;font-size:12px;padding-bottom:2px;padding-left:6px;padding-right:6px;padding-top:2px;"></TitleStyle>
					<DayHeaderStyle CssText="font-family: tahoma; font-size: 12px; border-bottom: #aca899 1px solid"></DayHeaderStyle>
					<DayStyle CssText="font-family: tahoma; font-size: 12px; border-right: white 1px solid; border-top: white 1px solid; border-left: white 1px solid; border-bottom: white 1px solid"></DayStyle>
					<SelectedDayStyle CssText="font-family: tahoma; font-size: 12px; background-color: #fbe694; border-right: white 1px solid; border-top: white 1px solid; border-left: white 1px solid; border-bottom: white 1px solid"></SelectedDayStyle>
					<TitleArrowStyle CssText="cursor:hand"></TitleArrowStyle>
					<TodayStyle CssText="font-family: tahoma; font-size: 12px; border-right: #bb5503 1px solid; border-top: #bb5503 1px solid; border-left: #bb5503 1px solid; border-bottom: #bb5503 1px solid"></TodayStyle>
					<PickerStyle CssText="border-bottom-color:#7f9db9;border-bottom-style:solid;border-bottom-width:1px;border-left-color:#7f9db9;border-left-style:solid;border-left-width:1px;border-right-color:#7f9db9;border-right-style:solid;border-right-width:1px;border-top-color:#7f9db9;border-top-style:solid;border-top-width:1px;font-family:Courier New;font-size:8pt;margin-bottom:0px;margin-left:0px;margin-right:0px;margin-top:0px;padding-bottom:1px;padding-left:2px;padding-right:2px;padding-top:2px;"></PickerStyle>
					<OtherMonthDayStyle CssText="font-family: tahoma; font-size: 12px; color: gray; border-right: white 1px solid; border-top: white 1px solid; border-left: white 1px solid; border-bottom: white 1px solid"></OtherMonthDayStyle>
					<CalendarStyle CssText="background-color: white; border-right: #7f9db9 1px solid; padding-right: 4px; border-top: #7f9db9 1px solid; padding-left: 4px; font-size: 9px; padding-bottom: 4px; border-left: #7f9db9 1px solid; padding-top: 4px; border-bottom: #7f9db9 1px solid; font-family: tahoma"></CalendarStyle>
					<DisabledDayStyle CssText="font-family: tahoma; font-size: 12px; color: gray; border-right: white 1px solid; border-top: white 1px solid; border-left: white 1px solid; border-bottom: white 1px solid"></DisabledDayStyle>
					<MonthStyle CssText="font-family: tahoma; font-size: 12px; margin-left: 14px; cursor: hand; margin-right: 14px"></MonthStyle>
				</DatePicker>
						</eo:DateTimeColumn>
						<eo:DateTimeColumn>
				<DatePicker DayHeaderFormat="FirstLetter" DayCellHeight="16" DisabledDates="" TitleLeftArrowImageUrl="DefaultSubMenuIconRTL"
					OtherMonthDayVisible="True" DayCellWidth="19" TitleRightArrowImageUrl="DefaultSubMenuIcon"
					ControlSkinID="None" SelectedDates="" PickerFormat="dd/MM/yyyy">
					<DayHoverStyle CssText="font-family: tahoma; font-size: 12px; border-right: #fbe694 1px solid; border-top: #fbe694 1px solid; border-left: #fbe694 1px solid; border-bottom: #fbe694 1px solid"></DayHoverStyle>
					<TitleStyle CssText="background-color:#9ebef5;font-family:Tahoma;font-size:12px;padding-bottom:2px;padding-left:6px;padding-right:6px;padding-top:2px;"></TitleStyle>
					<DayHeaderStyle CssText="font-family: tahoma; font-size: 12px; border-bottom: #aca899 1px solid"></DayHeaderStyle>
					<DayStyle CssText="font-family: tahoma; font-size: 12px; border-right: white 1px solid; border-top: white 1px solid; border-left: white 1px solid; border-bottom: white 1px solid"></DayStyle>
					<SelectedDayStyle CssText="font-family: tahoma; font-size: 12px; background-color: #fbe694; border-right: white 1px solid; border-top: white 1px solid; border-left: white 1px solid; border-bottom: white 1px solid"></SelectedDayStyle>
					<TitleArrowStyle CssText="cursor:hand"></TitleArrowStyle>
					<TodayStyle CssText="font-family: tahoma; font-size: 12px; border-right: #bb5503 1px solid; border-top: #bb5503 1px solid; border-left: #bb5503 1px solid; border-bottom: #bb5503 1px solid"></TodayStyle>
					<PickerStyle CssText="border-bottom-color:#7f9db9;border-bottom-style:solid;border-bottom-width:1px;border-left-color:#7f9db9;border-left-style:solid;border-left-width:1px;border-right-color:#7f9db9;border-right-style:solid;border-right-width:1px;border-top-color:#7f9db9;border-top-style:solid;border-top-width:1px;font-family:Courier New;font-size:8pt;margin-bottom:0px;margin-left:0px;margin-right:0px;margin-top:0px;padding-bottom:1px;padding-left:2px;padding-right:2px;padding-top:2px;"></PickerStyle>
					<OtherMonthDayStyle CssText="font-family: tahoma; font-size: 12px; color: gray; border-right: white 1px solid; border-top: white 1px solid; border-left: white 1px solid; border-bottom: white 1px solid"></OtherMonthDayStyle>
					<CalendarStyle CssText="background-color: white; border-right: #7f9db9 1px solid; padding-right: 4px; border-top: #7f9db9 1px solid; padding-left: 4px; font-size: 9px; padding-bottom: 4px; border-left: #7f9db9 1px solid; padding-top: 4px; border-bottom: #7f9db9 1px solid; font-family: tahoma"></CalendarStyle>
					<DisabledDayStyle CssText="font-family: tahoma; font-size: 12px; color: gray; border-right: white 1px solid; border-top: white 1px solid; border-left: white 1px solid; border-bottom: white 1px solid"></DisabledDayStyle>
					<MonthStyle CssText="font-family: tahoma; font-size: 12px; margin-left: 14px; cursor: hand; margin-right: 14px"></MonthStyle>
				</DatePicker>
			</eo:DateTimeColumn>
						<eo:DateTimeColumn>
				<DatePicker DayHeaderFormat="FirstLetter" DayCellHeight="16" DisabledDates="" TitleLeftArrowImageUrl="DefaultSubMenuIconRTL"
					OtherMonthDayVisible="True" DayCellWidth="19" TitleRightArrowImageUrl="DefaultSubMenuIcon"
					ControlSkinID="None" SelectedDates="" PickerFormat="dd/MM/yyyy">
					<DayHoverStyle CssText="font-family: tahoma; font-size: 12px; border-right: #fbe694 1px solid; border-top: #fbe694 1px solid; border-left: #fbe694 1px solid; border-bottom: #fbe694 1px solid"></DayHoverStyle>
					<TitleStyle CssText="background-color:#9ebef5;font-family:Tahoma;font-size:12px;padding-bottom:2px;padding-left:6px;padding-right:6px;padding-top:2px;"></TitleStyle>
					<DayHeaderStyle CssText="font-family: tahoma; font-size: 12px; border-bottom: #aca899 1px solid"></DayHeaderStyle>
					<DayStyle CssText="font-family: tahoma; font-size: 12px; border-right: white 1px solid; border-top: white 1px solid; border-left: white 1px solid; border-bottom: white 1px solid"></DayStyle>
					<SelectedDayStyle CssText="font-family: tahoma; font-size: 12px; background-color: #fbe694; border-right: white 1px solid; border-top: white 1px solid; border-left: white 1px solid; border-bottom: white 1px solid"></SelectedDayStyle>
					<TitleArrowStyle CssText="cursor:hand"></TitleArrowStyle>
					<TodayStyle CssText="font-family: tahoma; font-size: 12px; border-right: #bb5503 1px solid; border-top: #bb5503 1px solid; border-left: #bb5503 1px solid; border-bottom: #bb5503 1px solid"></TodayStyle>
					<PickerStyle CssText="border-bottom-color:#7f9db9;border-bottom-style:solid;border-bottom-width:1px;border-left-color:#7f9db9;border-left-style:solid;border-left-width:1px;border-right-color:#7f9db9;border-right-style:solid;border-right-width:1px;border-top-color:#7f9db9;border-top-style:solid;border-top-width:1px;font-family:Courier New;font-size:8pt;margin-bottom:0px;margin-left:0px;margin-right:0px;margin-top:0px;padding-bottom:1px;padding-left:2px;padding-right:2px;padding-top:2px;"></PickerStyle>
					<OtherMonthDayStyle CssText="font-family: tahoma; font-size: 12px; color: gray; border-right: white 1px solid; border-top: white 1px solid; border-left: white 1px solid; border-bottom: white 1px solid"></OtherMonthDayStyle>
					<CalendarStyle CssText="background-color: white; border-right: #7f9db9 1px solid; padding-right: 4px; border-top: #7f9db9 1px solid; padding-left: 4px; font-size: 9px; padding-bottom: 4px; border-left: #7f9db9 1px solid; padding-top: 4px; border-bottom: #7f9db9 1px solid; font-family: tahoma"></CalendarStyle>
					<DisabledDayStyle CssText="font-family: tahoma; font-size: 12px; color: gray; border-right: white 1px solid; border-top: white 1px solid; border-left: white 1px solid; border-bottom: white 1px solid"></DisabledDayStyle>
					<MonthStyle CssText="font-family: tahoma; font-size: 12px; margin-left: 14px; cursor: hand; margin-right: 14px"></MonthStyle>
				</DatePicker>
			</eo:DateTimeColumn>
						<eo:MaskedEditColumn>
							<MaskedEdit ControlSkinID="None" TextBoxStyle-CssText="BORDER-RIGHT: #7f9db9 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #7f9db9 1px solid; PADDING-LEFT: 2px; PADDING-BOTTOM: 1px; MARGIN: 0px; BORDER-LEFT: #7f9db9 1px solid; PADDING-TOP: 2px; BORDER-BOTTOM: #7f9db9 1px solid; font-family:Courier New;font-size:8pt;"></MaskedEdit>
						</eo:MaskedEditColumn>
					</ColumnTemplates>
					<Columns>
						<eo:RowNumberColumn Width="40"></eo:RowNumberColumn>
						<eo:TextBoxColumn Width="110" HeaderText="Item"></eo:TextBoxColumn>
						<eo:DateTimeColumn Width="80" HeaderText="StartDate"></eo:DateTimeColumn>
						<eo:DateTimeColumn Width="80" HeaderText="EndDate"></eo:DateTimeColumn>
						<eo:DateTimeColumn Width="80" HeaderText="DueDate"></eo:DateTimeColumn>
					</Columns>
					<ColumnHeaderStyle CssText="background-image:url('00050101');padding-left:8px;padding-top:3px;"></ColumnHeaderStyle>
				</eo:Grid>

	<asp:Label ID="Label1" runat="server" ></asp:Label>
	<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />

I will create test page in a short while to put on our staging server.
Many Thanks,
Teju

eo_support

Posted: Friday, February 6, 2009 10:05:42 AM

Rank: Administration
Groups: Administration

Joined: 5/27/2007
Posts: 24,089

I see your code getting "txtanswers" and "txtcorrect" from the first and second column. So my questions to you:

1. Why do you get txtanswers from your first column? The first column is a RowNumberColumn. The contents is always row number.

2. Your third column, not second column is a DateTime column. Where is the code that retrieves value from that column?

Thanks

Teju

Posted: Tuesday, February 10, 2009 11:05:14 AM

Rank: Member
Groups: Member

Joined: 1/23/2009
Posts: 12

Thanks, very much !!!. It works great.
My next question is . Is there any way of validating I mean if some one appends a row but doesn't put any information.

Sorry for any trouble.
Many Thanks,
Teju

eo_support

Posted: Tuesday, February 10, 2009 11:09:09 AM

Rank: Administration
Groups: Administration

Joined: 5/27/2007
Posts: 24,089

That will be up to you. The Grid does not do any validation for you.

Thanks

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Message