How to auto-populate data to textbox from DB

Question

I created a program and one of its function is when you select an item from the dropdownlist, the data from database will automatically populate to the textbox. I have 16 texboxes and problem was, when I click the a selection, only 1 data from the DB will populate to the 16 textboxes. To help me explain clearly, please see the attached picture of my codes and DB. By the way, I am only a novice in programming. Thanks you in advance.

Nitin Sontakke · Answer

@Mithun, Purely from the aesthetics point of view it would be better to write as follows: while(sdr.Read()) { String value = sdr.IsDBNull(sdr.GetOrdinal("Amount") ? String.Empty : sdr["Amount"].ToString(); switch(i) { case 1: txtCoverageBenefit1.Text = value; break; // and so on... } }

Jisny Av · Answer

Hi, Try below code protected void ddlHPACoverage_SelectedIndexChanged(object sender, EventArgs e) { using (SqlConnection SqlCn = DBConnection.GetDbCon()) { String strQuery = "select HPAPlan, Amount from tblHPACoverageBenefits where HPAPlan = @HPAPlan"; SqlCommand cmd = new SqlCommand(); cmd.Parameters.AddWithValue("@HPAPlan", ddlHPACoverage.SelectedItem.Text); cmd.CommandType = CommandType.Text; cmd.CommandText = strQuery; cmd.Connection = SqlCn; try { SqlCn.Open(); // SqlDataReader sdr = cmd.ExecuteReader(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet ds = null; adapter.Fill(ds); for (int i = 0; i { TextBox txtCoverageBenefit = (TextBox)Page.FindControl("txtCoverageBenefit" + i); txtCoverageBenefit.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); } } catch (Exception ex) { throw ex; } finally { SqlCn.Close(); SqlCn.Dispose(); } } }

Hold On · Answer

Here's my table script below @Esakki Muthu USE [DPYInsurance] GO /****** Object: Table [dbo].[tblHPACoverageBenefits] Script Date: 9/21/2016 8:06:35 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblHPACoverageBenefits]( [ID] [int] IDENTITY(1,1) NOT NULL, [CoverageBenefits] [nvarchar](max) NULL, [HPAPlan] [nvarchar](50) NULL, [Amount] [decimal](18, 2) NULL, CONSTRAINT [PK_tblHPACoverageBenefits$] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO And yes, it should bind the corresponding amount from "tblHPACoverageBenefits" table.

Esakki Muthu · Answer

Hold On, Can you share the actual table script "tblHPACoverageBenefits" , in the attachment you have shared different table script that is "tblHPAInsurance". Is my understanding correct, Based on the HPAPlan you have selected based on that you need to bind the corresponding amount from "tblHPACoverageBenefits" table? Did u thought of using entity framework?

Hold On · Answer

Below code is what I did: protected void cbPlan1_CheckedChanged(object sender, EventArgs e) { string CoverageBenefit1 = "250,000"; string CoverageBenefit2 = "500,000"; string CoverageBenefit3 = "3,750,000"; string CoverageBenefit4 = "125,000"; string CoverageBenefit5 = "625,000"; string CoverageBenefit6 = "625,000"; string CoverageBenefit7 = "6,250,000"; string CoverageBenefit8 = "750,000"; string CoverageBenefit9 = "250,000"; string CoverageBenefit10 = "1,000,000"; string CoverageBenefit11 = "25,000,000"; string CoverageBenefit11a = "15,000,000"; string CoverageBenefit11b = "25,000,000"; string CoverageBenefit12 = "200,000"; string CoverageBenefit13 = "0.00"; string CoverageBenefit14 = "0.00"; if (cbPlan1.Checked) { txtCoverageBenefit1.Text = CoverageBenefit1.ToString(); txtCoverageBenefit2.Text = CoverageBenefit2.ToString(); txtCoverageBenefit3.Text = CoverageBenefit3.ToString(); txtCoverageBenefit4.Text = CoverageBenefit4.ToString(); txtCoverageBenefit5.Text = CoverageBenefit5.ToString(); txtCoverageBenefit6.Text = CoverageBenefit6.ToString(); txtCoverageBenefit7.Text = CoverageBenefit7.ToString(); txtCoverageBenefit8.Text = CoverageBenefit8.ToString(); txtCoverageBenefit9.Text = CoverageBenefit9.ToString(); txtCoverageBenefit10.Text = CoverageBenefit10.ToString(); txtCoverageBenefit11.Text = CoverageBenefit11.ToString(); txtCoverageBenefit11a.Text = CoverageBenefit11a.ToString(); txtCoverageBenefit11b.Text = CoverageBenefit11b.ToString(); txtCoverageBenefit12.Text = CoverageBenefit12.ToString(); txtCoverageBenefit13.Text = CoverageBenefit13.ToString(); txtCoverageBenefit14.Text = CoverageBenefit14.ToString(); } else { txtCoverageBenefit1.Text = ""; txtCoverageBenefit2.Text = ""; txtCoverageBenefit3.Text = ""; txtCoverageBenefit4.Text = ""; txtCoverageBenefit5.Text = ""; txtCoverageBenefit6.Text = ""; txtCoverageBenefit7.Text = ""; txtCoverageBenefit8.Text = ""; txtCoverageBenefit9.Text = ""; txtCoverageBenefit10.Text = ""; txtCoverageBenefit11.Text = ""; txtCoverageBenefit11a.Text = ""; txtCoverageBenefit11b.Text = ""; txtCoverageBenefit12.Text = ""; txtCoverageBenefit13.Text = ""; txtCoverageBenefit14.Text = ""; } } protected void cbPlan2_CheckedChanged(object sender, EventArgs e) { string CoverageBenefit1 = "375000"; string CoverageBenefit2 = "750000"; string CoverageBenefit3 = "5000000"; string CoverageBenefit4 = "175000"; string CoverageBenefit5 = "625000"; string CoverageBenefit6 = "625000"; string CoverageBenefit7 = "8750000"; string CoverageBenefit8 = "1000000"; string CoverageBenefit9 = "250000"; string CoverageBenefit10 = "1250000"; string CoverageBenefit11 = "50000000"; string CoverageBenefit11a = "30000000"; string CoverageBenefit11b = "50000000"; string CoverageBenefit12 = "250000"; string CoverageBenefit13 = "125000"; string CoverageBenefit14 = "50000"; if (cbPlan2.Checked) { txtCoverageBenefit1.Text = CoverageBenefit1.ToString(); txtCoverageBenefit2.Text = CoverageBenefit2.ToString(); txtCoverageBenefit3.Text = CoverageBenefit3.ToString(); txtCoverageBenefit4.Text = CoverageBenefit4.ToString(); txtCoverageBenefit5.Text = CoverageBenefit5.ToString(); txtCoverageBenefit6.Text = CoverageBenefit6.ToString(); txtCoverageBenefit7.Text = CoverageBenefit7.ToString(); txtCoverageBenefit8.Text = CoverageBenefit8.ToString(); txtCoverageBenefit9.Text = CoverageBenefit9.ToString(); txtCoverageBenefit10.Text = CoverageBenefit10.ToString(); txtCoverageBenefit11.Text = CoverageBenefit11.ToString(); txtCoverageBenefit11a.Text = CoverageBenefit11a.ToString(); txtCoverageBenefit11b.Text = CoverageBenefit11b.ToString(); txtCoverageBenefit12.Text = CoverageBenefit12.ToString(); txtCoverageBenefit13.Text = CoverageBenefit13.ToString(); txtCoverageBenefit14.Text = CoverageBenefit14.ToString(); } else { txtCoverageBenefit1.Text = ""; txtCoverageBenefit2.Text = ""; txtCoverageBenefit3.Text = ""; txtCoverageBenefit4.Text = ""; txtCoverageBenefit5.Text = ""; txtCoverageBenefit6.Text = ""; txtCoverageBenefit7.Text = ""; txtCoverageBenefit8.Text = ""; txtCoverageBenefit9.Text = ""; txtCoverageBenefit10.Text = ""; txtCoverageBenefit11.Text = ""; txtCoverageBenefit11a.Text = ""; txtCoverageBenefit11b.Text = ""; txtCoverageBenefit12.Text = ""; txtCoverageBenefit13.Text = ""; txtCoverageBenefit14.Text = ""; } } As well as for the Plan 3 to 5. This helps me attain my objective though is it very tedious. Can anyone help me to make this simple?

Hold On · Answer

Nitin Sontakke if you were able to read the question I posted, I emphasize that I am novice in C# programming. I learned only by tutorials and forums like this. A detailed instruction will help me a lot. Anyway, thanks for the advice though.

Delpin Susai Raj · Answer

http://stackoverflow.com/questions/21852192/autofill-textboxes-from-database-based-on-one-textbox-value

Nitin Sontakke · Answer

Hello, Looks like you are not reading the replies with extreme care and attention, which is an absolute must. My comment was to Mithun and not to you. So basically, you will need to use Mithun's code and integrate the improvement i suggested. The code given by me IS NOT going work as it is. Hope you understand.

Hold On · Answer

Hi Nitin Sontakke I tried it again but I got an error with the (i) in the switch.

Jisny Av · Answer

Hi Hold On, First you debug and check whether dataset contains result of query. Give ids of textboxes as follows txtCoverageBenefit1 txtCoverageBenefit2 txtCoverageBenefit3 No need to assigning values to each textboxes in the for loop instead of that give single step TextBox txtCoverageBenefit = (TextBox)Page.FindControl("txtCoverageBenefit" + i); txtCoverageBenefit.Text = ds.Tables[0].Rows[i]["Amount"].ToString();

Nitin Sontakke · Answer

Furthermore, I just took Mithun's code and wanted to improve on it. You haven't declared the variable i. Notice all red squiggly lines below the code and hover cursor over them to read errors. I don't know the entire context of the code. I just picked a piece of it and showed alternate way to write it.

Nitin Sontakke · Answer

The brackets are mismatching...Try the one below. I am surprised that you are able to run the code despite compilation errors. Did you choose to continue despite of it? You should first resolve syntactical errors before continuing... String value = sdr.IsDBNull(sdr.GetOrdinal("Amount")) ? String.Empty : sdr["Amount"].ToString();

Bikesh Srivastava · Answer

Hi, Visit here you will find proper example . http://www.c-sharpcorner.com/article/autocomplete-textbox-in-C-Sharp/

Hold On · Answer

Hi Nitin Sontakke , thanks for your suggestion. When I tried your code, it gives me an error (please see picture). So sorry for being novice in programming.

Hold On · Answer

Midhun T P I tried your suggestion but I got only this result

Hold On · Answer

@ Jisny Av , I tried your suggestion and did this: protected void ddlHPACoverage_SelectedIndexChanged(object sender, EventArgs e) { using (SqlConnection SqlCn = DBConnection.GetDbCon()) { String strQuery = "select HPAPlan, Amount from tblHPACoverageBenefits where HPAPlan = @HPAPlan"; SqlCommand cmd = new SqlCommand(); cmd.Parameters.AddWithValue("@HPAPlan", ddlHPACoverage.SelectedItem.Text); cmd.CommandType = CommandType.Text; cmd.CommandText = strQuery; cmd.Connection = SqlCn; try { SqlCn.Open(); //SqlDataReader sdr = cmd.ExecuteReader(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet ds = null; adapter.Fill(ds); for (int i = 0; i { TextBox txtCoverageBenefit1 = (TextBox)Page.FindControl("txtCoverageBenefit1" + i); txtCoverageBenefit1.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); TextBox txtCoverageBenefit2 = (TextBox)Page.FindControl("txtCoverageBenefit2" + i); txtCoverageBenefit2.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); TextBox txtCoverageBenefit3 = (TextBox)Page.FindControl("txtCoverageBenefit3" + i); txtCoverageBenefit3.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); TextBox txtCoverageBenefit4 = (TextBox)Page.FindControl("txtCoverageBenefit4" + i); txtCoverageBenefit4.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); TextBox txtCoverageBenefit5 = (TextBox)Page.FindControl("txtCoverageBenefit5" + i); txtCoverageBenefit5.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); TextBox txtCoverageBenefit6 = (TextBox)Page.FindControl("txtCoverageBenefit6" + i); txtCoverageBenefit6.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); TextBox txtCoverageBenefit7 = (TextBox)Page.FindControl("txtCoverageBenefit7" + i); txtCoverageBenefit7.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); TextBox txtCoverageBenefit8 = (TextBox)Page.FindControl("txtCoverageBenefit8" + i); txtCoverageBenefit8.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); TextBox txtCoverageBenefit9 = (TextBox)Page.FindControl("txtCoverageBenefit9" + i); txtCoverageBenefit9.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); TextBox txtCoverageBenefit10 = (TextBox)Page.FindControl("txtCoverageBenefit10" + i); txtCoverageBenefit10.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); TextBox txtCoverageBenefit11a = (TextBox)Page.FindControl("txtCoverageBenefit11a" + i); txtCoverageBenefit11a.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); TextBox txtCoverageBenefit11b = (TextBox)Page.FindControl("txtCoverageBenefit11b" + i); txtCoverageBenefit11b.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); TextBox txtCoverageBenefit12 = (TextBox)Page.FindControl("txtCoverageBenefit12" + i); txtCoverageBenefit12.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); TextBox txtCoverageBenefit13 = (TextBox)Page.FindControl("txtCoverageBenefit13" + i); txtCoverageBenefit13.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); TextBox txtCoverageBenefit14 = (TextBox)Page.FindControl("txtCoverageBenefit14" + i); txtCoverageBenefit14.Text = ds.Tables[0].Rows[i]["Amount"].ToString(); } } catch (Exception ex) { throw ex; } finally { SqlCn.Close(); SqlCn.Dispose(); } } } but I got this error Server Error in '/' Application. Value cannot be null. Parameter name: dataSet

Hold On · Answer

I am very sorry, I attached the wrong DB. Please find below DB Structure. I have 5 Types of Plan. And each plan have different figures.

Vaibhav Deshmukh · Answer

Hi Hold on, I have seen your Db structure and Code but you given this table [ tblHPAInsurance ] and your fetching from this table tblHPACoverageBenefits in sql command. If you dont mind can you please, provide this table structure of tblHPACoverageBenefits .. So that I can help you out.

Midhun T P · Answer

Hi, You may need to assign value of textboxes like below - try { SqlCn.Open(); SqlDataReader sdr = cmd.ExecuteReader(); int i=0; while (sdr.Read()) { if(i==0) txtCoverageBenefit1.Text = sdr["Amount"].ToString(); else if(i==1) txtCoverageBenefit2.Text = sdr["Amount"].ToString(); else if(i==2) txtCoverageBenefit3.Text = sdr["Amount"].ToString(); //Remaining code with condition i++; } }

How to auto-populate data to textbox from DB

Answers (19)