what tables should I create

Danish Habib

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

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Danish Habib11y ago
Dear Joginder Banger Thansk for you time and consideration , I have used checkboxlist for multi select option mean that a user can select multiple Districts,Multiple Tehsils,Multiple Ucs,Multiple 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 UC,Villages
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 district,tehsil,uc,villages with previous one any example please share
0
Joginder Banger11y ago
sorry boss for late reply.i check your picture, why are used checkbox list. I prefer dropdownlist instead checkbox list.
0
Danish Habib11y ago
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 ,UC,Village 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
0
Joginder Banger11y ago
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.
0
Danish Habib11y ago
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
0
Danish Habib11y ago
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.UserId,UT.TehsilId,UC.UCID ,UC.UCName,UV.VillageId ,UV.VillageName ,UT.TehsilName,U.DistrictId,UD.DistrictName ,UD.DistrictId ,UD.UserId,U.FullName,U.IPAddress,U.IsActive,U.IsAdmin,U.IsPasswordReset,U.IsReportable,U.IsVisible,U.CreateDate ,U.Mobile,U.Password,U.ReporintPerson,U.SecurityAnswer,U.SecurityQuestion ,U.UserId,U.Email,U.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
0
Joginder Banger11y ago
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 (id,name,email,password,fk_District,Fk_Tehsil,fk_UC,Fk_village ) Table Name Customer
second table (id,District) table_name District
thrid table (id,Thisil,Fk_district) table_name Thisil
fourth Table (id,UC) table Name UC...I have no idea what is UC.
fifth table (id,village,fk_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.

0
Danish Habib11y ago
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 ?
0
Riddhi Valecha11y ago
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)

0

ID	int	PK	Notnull
Username	varchar(100)	PK	not null
FName	Varchar(100)		not null
LName	varchar(100)