what tables should I create

Question

I have user into my system having information like(name,email,password,id,District,Tehsil,UC,Village)

ONE User can have one or many Districts
One user can have NO or many TEhsil
One user can have NO or many UCS
One user can have no or many Villages

These are cascade in nature District----------Populates-------->Tehsils

Tehsils----------Populates---------UC

UC-------------Populates-------Villages

I have create table from where my checkbox list getting values like this cascade nature but for save what i should do how many table should i have to create to save these records.
please share some example if any one have

Danish Habib · Answer

Dear Joginder Banger Thansk for you time and consideration  I have used checkboxlist for multi select option mean that a user can select multiple DistrictsMultiple TehsilsMultiple UcsMultiple Villages IF i used dropdown then i also have to use some client side javascript for multi select option and meanwhile What ever the control I will use I have to populates it again and again based on the Value comming with Previious Database structure While I go into the edit mode i get 70 Rows in datareader and my reader loops row by row and then if the district id is one then it populatest just those tehsils where the foreign key district id is one and then loop through and check the comming value from database and the actual value if they match it makes it checked same for UCVillages As they are cascade like that District---->tehsil---->uc---->Villages If i use dropdown then i also have to bind other dropdown like I have to bind tehsil dropdown based on the district dropdown value .....(because I want to show not all villages just the villages where the Foreign key UCID present ... R u getting me? just let me know with same database i want to give the previleges to the user to edit and make changes mean can add another districttehsilucvillages with previous one any example please share

Joginder Banger · Answer

sorry boss for late reply.i check your picture why are used checkbox list. I prefer dropdownlist instead checkbox list.

Danish Habib · Answer

Dear Joginder I have a Form Placed on my aspx page and This form has some labels and textboxes i.e Name (TEXTBOX) Email (TEXTBOX) For District TEhsil UCVillage I have checkbox and this form is placed in a view and on edit i am calling my view by active view index of multiview and show the form in edit mode same form with values from database  Now If a user has been assigned 200 Villages first time and on edit he want to assign more villages that in mind i place cascade checkbox so if he/she want to add another then he can  i also attached a image of my form

Joginder Banger · Answer

Tell me where you want show data like grid view datalist and any where. if you are using gird view why are you used check box or any control just your village data on any label. NO need a loop. only edit time you need the check box list.

Danish Habib · Answer

that is my code of row_editing Private Sub grdUsers_RowEditing(ByVal sender As Object ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles grdUsers.RowEditing ' Pflag = True ViewState(Pflag) = True count = count + 1 MakeTrue(Pflag) ' btnUpdateDistricts.Visible = True Dim Tehsilid As String = Nothing Dim DistrictId As String = Nothing Dim UCId As String = Nothing Dim villageID As String = Nothing Dim cnnUser As New SqlConnection(AppSettings(DbSqlPortal)) Dim cmdUser As New SqlCommand(spAdminGetUsersByIdNEW cnnUser) Dim drUser As SqlDataReader = Nothing cmdUser.CommandType = CommandType.StoredProcedure cmdUser.Parameters.Add(@UserId SqlDbType.BigInt).Value = CInt(grdUsers.DataKeys(e.NewEditIndex).Value) ' Try cnnUser.Open() drUser = cmdUser.ExecuteReader() If (Not (drUser) Is Nothing) Then If drUser.HasRows Then While (drUser.Read()) DisplayInitialForm() litUserId.Text = CInt(drUser(UserId)) txtFullName.Text = drUser(FullName) txtEmail.Text = drUser(Email) txtPassword.Text = drUser(Password) txtAnswer.Text = drUser(SecurityAnswer) ddlSecurityQuestions.SelectedValue = drUser(SecurityQuestion) chkAdmin.Checked = drUser(IsAdmin) chkActive.Checked = drUser(IsActive) ddlDistricts.SelectedValue = drUser(DistrictId) Dim i As Int32 Dim j As Int32 For i = 0 To CheckBoxList1.Items.Count - 1 Dim strChkID As String = drUser(DistrictID) If CheckBoxList1.Items(i).Value = strChkID Then CheckBoxList1.Items.Item(i).Selected = True End If Next GetListBoxSelStringInComma(CheckBoxList1) PopulateTehsil() For j = 0 To CheckBoxList2.Items.Count - 1 Dim strChkIDs As String = drUser(TehsilId).ToString() If CheckBoxList2.Items(j).Value = strChkIDs Then CheckBoxList2.Items.Item(j).Selected = True End If Next Tehsilid &= drUser(TehsilId).ToString() &  Session(savedTehsil) = Tehsilid Dim word As String() = Tehsilid.Split() For Each li In word For j = 0 To CheckBoxList2.Items.Count - 1 If li = CheckBoxList2.Items.Item(j).Value Then CheckBoxList2.Items.Item(j).Selected = True End If Next Next GetListBoxSelStringInCommaTehsil(CheckBoxList2) PopulateUcs() For j = 0 To CheckBoxList3.Items.Count - 1 Dim strChkIDss As String = drUser(UCID).ToString() If CheckBoxList3.Items(j).Value = strChkIDss Then CheckBoxList3.Items.Item(j).Selected = True End If Next UCId &= drUser(UCID).ToString() &  Session(seceltedUcs) = UCId Dim wordss As String() = UCId.Split() For Each li In wordss For j = 0 To CheckBoxList3.Items.Count - 1 If li = CheckBoxList3.Items.Item(j).Value Then CheckBoxList3.Items.Item(j).Selected = True End If Next Next 'NOE ' BindCheckBoxList() GetListBoxSelStringInCommaUCs(CheckBoxList3) PopulateVillages() For j = 0 To CheckBoxList4.Items.Count - 1 Dim strChkIDsss As String = drUser(VillageId).ToString() If CheckBoxList4.Items(j).Value = strChkIDsss Then CheckBoxList4.Items.Item(j).Selected = True End If Next villageID &= drUser(VillageId).ToString() &  Session(VillagesID) = villageID Dim wordsss As String() = villageID.Split() For Each li In wordsss For j = 0 To CheckBoxList4.Items.Count - 1 If li = CheckBoxList4.Items.Item(j).Value Then CheckBoxList4.Items.Item(j).Selected = True End If Next Next ChkIsReportAble.Checked = CBool(drUser(IsReportable)) ddlUserTypes.SelectedValue = drUser(UserTypes) If drUser(UserTypes) = 4 Then fillReportablePeopleListForPartner() Else fillReportablePeopleList() End If If ChkIsReportAble.Checked Then ddlReporintUser.SelectedValue = Convert.ToString(drUser(ReporintPerson)) End If End While drUser.NextResult() While (drUser.Read()) 'do something DisplayInitialForm() litUserId.Text = CInt(drUser(UserId)) txtFullName.Text = drUser(FullName) txtEmail.Text = drUser(Email) txtPassword.Text = drUser(Password) txtAnswer.Text = drUser(SecurityAnswer) ddlSecurityQuestions.SelectedValue = drUser(SecurityQuestion) chkAdmin.Checked = drUser(IsAdmin) chkActive.Checked = drUser(IsActive) ddlDistricts.SelectedValue = drUser(DistrictId) ChkIsReportAble.Checked = CBool(drUser(IsReportable)) ddlUserTypes.SelectedValue = drUser(UserTypes) If ChkIsReportAble.Checked Then ddlReporintUser.SelectedValue = Convert.ToString(drUser(ReporintPerson)) End If End While End If End If End Sub

Danish Habib · Answer

Dear Joginder Banger I Know i Always have different Types of Question to keep myself Unique :) I have the same Table Structure As you told me the problem with me is that I have a user having 200 Villages Assigned to him so my using the below query SELECT U.UserIdUT.TehsilIdUC.UCID UC.UCNameUV.VillageId UV.VillageName UT.TehsilNameU.DistrictIdUD.DistrictName UD.DistrictId UD.UserIdU.FullNameU.IPAddressU.IsActiveU.IsAdminU.IsPasswordResetU.IsReportableU.IsVisibleU.CreateDate U.MobileU.PasswordU.ReporintPersonU.SecurityAnswerU.SecurityQuestion U.UserIdU.EmailU.UserTypes FROM Users U INNER JOIN UserDistricts UD ON UD.UserId=U.UserId AND UD.DistrictId=U.DistrictId INNER JOIN UserTehsils UT ON UT.UserID=U.UserId AND UT.DistrictId=UD.DistrictId INNER JOIN UserUcs UC ON UC.UserID=U.UserId AND UC.TehsilId=UT.TehsilId INNER JOIN UserVillages UV ON UV.UserId=U.UserId AND UV.UcID=UC.UCID WHERE U.UserId =26 I get 200 Rows so how to make each checkboxlist or any other control checked if its value comming from the database and my data reader when have 200 rows it loops one by one and take more than 2 minute to fetch 100 records and for 200 it ends without completion please assist me that how to make checkboxlist or any other control checked if its value comming and not checked if its value not commming and you must consider that if I am getting Dim strChkID As String = drUser(DistrictID) district id 1 then my tehsil checkbox should only list those tehsil where the foreign key district id is one and then select those whose value are present in database

Joginder Banger · Answer

HI Danish Habib you have different type of problem always  m kidding man. i am consider only your first problem about the table. this is my point of view not a universal true Ok I think you need a five table like this type first table column Name (idnameemailpasswordfk_ DistrictFk_Tehsilfk_UCFk_village ) Table Name Customer second table (idDistrict) table_name District thrid table (idThisilFk_district) table_name Thisil fourth Table (idUC) table Name UC...I have no idea what is UC. fifth table (idvillagefk_thisil_id) table Name village. Second problem. why are used the checkbox you are used the list box or any tool are available in asp.net toolbox.

Danish Habib · Answer

Dear I have Used the same Structure as you told the Problem with me is That I have to save the user with 200 Villages So My save process first goes into the first checkboxlist of District Find the Values and save the selected on and then loop to the next TEhsil Loop then to the UC loop and then to the Village Loop My Saving process is slow  Secondly the major problem comes in Gridview Row_Editing aS I have 200 Rows so my Datareader has 200 Rows and it first goes from first row iterate through the District find the District value from the database match this with the checkboxlist and make it checked if its id match and then pass this district value to TEhsilsMethod So that the Associated Tehsil Should be populated and then loop thorugh the Tehsil and Then pass this tehsil Value to UC MEthod so that the associated UC should come and loop through and then pass UC ID to Village Method to find the associated villages so i have 200 Rows and my datareader never ends up with loop What should i do ?

Riddhi Valecha · Answer

Hi.. Create table as follows - User_Master ID int PK Notnull Username varchar(100) PK not null FName Varchar(100) not null LName varchar(100) Transaction_Table ID int pk not null Username varchar(100) foreign key not null Email varchar(100) Password varchar(100) District varchar(100) Village varchar(100) UGC varchar(100) Tehsil varchar(100)