How to save multiple values from various controls in sql server database?

Question

0.00/5 (No votes)

See more:

VS2012

I am designing windows application using 3-tier architecture. One of my forms contain dynamically generated controls. My question is that How can I save multiple values from controls in my database. I have used return statement but since it returns only one value my database stored only one value from each of the control. Basically I am creating BILL FORM.

I have created 8 methods one for each control and pass their values in business logic layer's function, but instead of saving multiple values it saved only one value of each control as mentioned above. I tried to use Tuples also but then it gave me "CONVERSION EXCEPTION"

One of my 8 methods is:

C#

public string combo_box_1()
            {
                foreach (Control ctrl in this.Controls)
                {
                    if (ctrl is ComboBox)
                    {
                        if (ctrl.Name.Equals("combo_box_1"))
                        {
                            string main_category = ctrl.Text;
                            string[] arr1 = { main_category };
                            foreach (string alph in arr1)
                            {
                                MessageBox.Show(alph);
                                return alph;
                            }
                            return main_category;
                            
                        }
                    }
                }
                return null;

}

It's Tuple version:

C#

public Tuple<string> combo_box_1()
           {
               foreach (Control ctrl in this.Controls)
               {
                   if (ctrl is ComboBox)
                   {
                       if (ctrl.Name.Equals("combo_box_1"))
                       {
                           string main_category = ctrl.Text;
                           Tuple<string> txt2 = new Tuple<string>(main_category);
                           return txt2;
                       }
                   }
               }
               return null;
           }

My business layer function(Tuple Version):

C#

public bool save_bill(Tuple<string> CB1, Tuple<string> CB2, Tuple<string> CB3, Tuple<string> CB4, Tuple<string> NUD1, Tuple<string> CB5, Tuple<string> TB1, Tuple<string> TB2)
            {
                try
                {
                    DAL obj = new DAL();
                    obj.OpenConnection();
                    obj.LoadSpParameters("save_bill", CB1, CB2, CB3, CB4, NUD1, CB5, TB1, TB2);
                    obj.ExecuteQuery();
                    obj.UnLoadSpParameters();
                    obj.CloseConnection();
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }

Programming behind my save button(Tuple Version):

C#

private void save_button_Click(object sender, EventArgs e)
           {
               BLL obj = new BLL();
               bool obj2 = obj.save_bill(combo_box_1(), combo_box_2(), combo_box_3(), combo_box_4(), numeric_up_down_1(), combo_box_5(), txt_box_1(), txt_box_2());
               if (obj2 == true)
               {
                   MessageBox.Show("bill saved");
               }

               else
               {
                   MessageBox.Show("bill cannot be saved");
               }

           }

Posted 30-Jul-14 22:53pm

Anus Kaleem

Add a Solution

Comments

Kumarbs 31-Jul-14 5:48am

Question to you, how you are saving in the database? Each value in one column or all values related to one control in one column with comma separated?

Anus Kaleem 31-Jul-14 5:49am

each value in one column. I have total 8 columns for 8 controls

Kumarbs 31-Jul-14 5:59am

well, in the question, as mentioned that you have many values for a single control and you want to save those values. Isn't it? If so how you can save it? all the values of a control in one column?

Anus Kaleem 31-Jul-14 6:16am

yes, like I am creating bill form for supermarket and as customers bought many items so there would be many dynamically controls, for this I have used 5 Combo Boxes, 2 TextBoxes and 1 Numeric UpDown. What I wished is that no matter how many controls are generated each control's text should be saved accordingly in my database in its respective column.

Kumarbs 31-Jul-14 7:14am

You have written 8 methods and each returning one value. So for each control you are getting one value. As per your above comment this is fine. Then what is your problem?

Anus Kaleem 31-Jul-14 7:21am

my problem is that i dont want to return 1 value, for example a customer bought 20 items then there will be 20 series each containing 8 controls and each control would have its separate value. Now comes my actual problem when i hit save button it only saves first series values rest of the series are neglected, this problem is becoming very frustrating for me. I have been searching solutions but all in vain :(

Kumarbs 31-Jul-14 7:44am

Last question don't mind :). In your first code block you written "combo_box_1". And you are assigning this control value to an string array. Is this value a single string or array of strings?

Anus Kaleem 31-Jul-14 8:15am

array of strings.

Kumarbs 31-Jul-14 8:47am

public string combo_box_1()
{
foreach (Control ctrl in this.Controls)
{
if (ctrl is ComboBox)
{
if (ctrl.Name.Equals("combo_box_1"))
{
string main_category = ctrl.Text;
string[] arr1 = { main_category };
foreach (string alph in arr1)
{
MessageBox.Show(alph);
return alph;
}
return main_category;

}
}
}
return null
In this code you just concatenate all the values in the arr1 so that you will get only one string with all the values separated with "," (As per our discussion 20 values with comma separated).
You pass this string to the db and there you can split it and perform insert.

Anus Kaleem 31-Jul-14 9:04am

Thanks for your timely response :). How can I split it please explain me in detail as I am not expert in database.

Kumarbs 1-Aug-14 0:14am

I provide the query in the solution.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kumarbs · Accepted Answer · 2014-07-31T18:13:00

You Can create user function as below.

create FUNCTION fn_split(@MYSTR VARCHAR(500), @DELIMITER CHAR(1))
RETURNS @MYTBL  TABLE (idx smallint, value varchar(8000))
AS 
BEGIN
 DECLARE @RET VARCHAR(500)
 DECLARE @INDEX INT
 DECLARE @COUNTER smallint
 
 --Get the first position of delimiter in the main string
 SET @INDEX = CHARINDEX(@DELIMITER,@MYSTR)
 SET @COUNTER = 0
 
 --Loop if delimiter exists in the main string
 WHILE @INDEX > 0
 BEGIN
  --extract the result substring before the delimiter found
  SET @RET = SUBSTRING(@MYSTR,1, @INDEX-1 )
  --set mainstring right part after the delimiter found
  SET @MYSTR = SUBSTRING(@MYSTR,@INDEX+1 , LEN(@MYSTR) - @INDEX )
  --increase the counter
  SET @COUNTER = @COUNTER  + 1 
  --add the result substring to the table
  INSERT INTO @MYTBL (idx, value)
  VALUES (@COUNTER, @RET)
  --Get the next position of delimiter in the main string
  SET @INDEX = CHARINDEX(@DELIMITER,@MYSTR)
 END
 
 --if no delimiter is found then simply add the mainstring to the table
 IF @INDEX = 0 
 BEGIN
  SET @COUNTER = @COUNTER  + 1
  INSERT INTO @MYTBL (idx, value)
  VALUES (@COUNTER, @MYSTR)
 END 
 RETURN   
END

And call the function to get the values like

select * from fn_split('test1,test2,test3',',')

In your code you can specify your delimiter in place of ','.