• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

Code not working, its almost 99% done - inserting values to a SQL db, based on an array

Wonder if you can help me? I'm new to C#/ASP.NET. I'm using VS2005. My problem is I have a string thats stored in a textbox (ID=codeArea):

[['test','menu1','test1','_self','test11'],['test1','menu2','test3313','_self','test144',['test1','dsdsf','sdfsf','_self','dsfdsf'],
['test1','ssss','sss','_self','sss',['test1','sssssadsa','asdasd','_self','sss']],['test1','ffff','fff','_self','ffff']],
['test3','menusdd','test3313e','_self','ddsd',['test3','ssss','ddd','_self','ddsd']],['eeee','wee','test3313e','_self','ddsd']];

I need all of that some how converted into seperate SQL rows (table menu), with each item inserted into seperate columns, so to give you an idea, each one of the lines below to be inserted as seperate rows in SQL

['test','menu1','test1','_self','test11']
['test1','menu2','test3313','_self','test144']
['test1','dsdsf','sdfsf','_self','dsfdsf']
['test1','ssss','sss','_self','sss']
['test1','sssssadsa','asdasd','_self','sss']
['test1','ffff','fff','_self','ffff']
['test3','menusdd','test3313e','_self','ddsd']
['test3','ssss','ddd','_self','ddsd']
['eeee','wee','test3313e','_self','ddsd']

and I have 5 columns in the SQL table, so its going to be inserted like:

menu_Icon          menu_Title          menu_URL          menu_Target          menu_description  << SQL columns (varchar)
-------------          -------------         -------------         ----------------          --------------------          
test                     menu1                test1                     _self                        test11                  << new SQL row
test1                    menu2                test3312               _self                        test1444               << new SQL row
test1                    dsdsf                  sdfsf                     _self                        dsfdsf                  << new SQL row
test1                    ssss                    sss                     _self                          sss                       << new SQL row
test1                    sssssadsa            asdasd                _self                         sss                       << new SQL row
test1                    ffff                      fff                       _self                         ffff                        << new SQL row
test3                    menusdd             test3313e            _self                         ddsd                     << new SQL row
test3                    ssss                    ddd                     _self                         ddsd                     << new SQL row
test3                    wee                    test3313e            _self                         ddsd                     << new SQL row


=============================================================================================
I've got the code that coverts it, but it dosnt work, here is the code:

string allEntries = "[['test','menu1','test1','_self','test11'],['test1','menu2','test3313','_self','test144',['test1','dsdsf','sdfsf','_self','dsfdsf'], ['test1','ssss','sss','_self','sss',['test1','sssssadsa','asdasd','_self','sss']],['test1','ffff','fff','_self','ffff']], ['test3','menusdd','test3313e','_self','ddsd',['test3','ssss','ddd','_self','ddsd']],['eeee','wee','test3313e','_self','ddsd']];";
 
int index = allEntries.IndexOf("[");  // detect start of data
if (index>=0)
{
    allEntries = allEntries.Remove(0,index+1);
    index = allEntries.IndexOf("]]"); // detect end of data
    if (index>=0)
    {
         allEntries.Remove(index+1);
         string[] records = allEntries.Split(",".ToCharArray());

         string sqlstring = "Data Source=.;Initial Catalog=menu1;Integrated Security=True";
         SqlConnection conn = new SqlConnection(sqlstring);
         SqlCommand myCommand = new SqlCommand();

         foreach(string record in records)
         {
              string values = record.Replace('[','(').Replace(']',')'); // HERE IS VALUE
              string query = "INSERT INTO yourTableHere (menu_Icon,menu_Title,menu_URL,menu_Target ,menu_description) VALUES"+values;
              myCommand = new SqlCommand(query, conn);
              conn.Open();
              myCommand.ExecuteNonQuery();
         }
    }
}

When I run it:

value is equal to   (('test'
query is equal to   INSERT INTO mitesh (menu_Icon, menu_Title, menu_URL, menu_Target , menu_description) VALUES (('test'

So it dosnt get the rest of the value.

Can you please help me get this sorted out ASAP.

Many thanks and kind regards
Mousemat24
0
mousemat24
Asked:
mousemat24
  • 8
  • 6
1 Solution
 
rameedevCommented:
Hi just check this below piece of code

string allEntries = "[['test','menu1','test1','_self','test11'],['test1','menu2','test3313','_self','test144'],['test1','dsdsf','sdfsf','_self','dsfdsf'], ['test1','ssss','sss','_self','sss'],['test1','sssssadsa','asdasd','_self','sss']],['test1','ffff','fff','_self','ffff']], ['test3','menusdd','test3313e','_self','ddsd'],['test3','ssss','ddd','_self','ddsd']],['eeee','wee','test3313e','_self','ddsd']];";

allEntries =  allEntries.Replace("[[","[");
allEntries =  allEntries.Replace("]]","]");

string[] Test = allEntries.Split(',');
string strValues="";

foreach(string strTmp in Test)
{
    if(strTmp.IndexOf("]") < 0)
    {
      strValues += strTmp + ",";
    }
    else
    {
      strValues += strTmp;
      strValues = strValues.Replace("[","(").Replace("]",")");
                //Use this strValues as Values for ur Sql Query and add ur Datbaase piece of code where it inserts into the DB
      strValues ="";
   }
}

One more thing ur code will not compile..as it will give a Compile error at line "allEntries.Remove(index+1);" it will need one more arguement to be passed...One more question in the datastring for some of the values the "]" is missing...i am assuming it was just a typo error what i meant is

....['test1','ffff','fff','_self','ffff']], ['test3','menusdd','test3313e','_self','ddsd',['test3','ssss','ddd','_self','ddsd']].....
for the 2nd set of values there is no "]"

i have added them myself worked on the above code...Do chk it out if it is useful
0
 
mousemat24Author Commented:
rameedev


"One more question in the datastring for some of the values the "]" is missing" - the array is acually correct, I'm using
http://jscook.yuanheng.org/JSCookMenu/MenuBuilder.html

to create the menu, this creates the arrray. So If I use

string allEntries = "[ ['1','1','1','1','1'], ['2','2','2','2','2', ['6','6','6','6','6'], ['7','7','7','7','7', ['11','11','11','11','11'], ['22','22','22','22','22'] ], ['8','8','8','8','8'] ], ['3','3','3','3','3'], ['12','12','12','12','12', ['121','121','121','121','121'], ['1212','1212','1212','1212','1212'], ['12123','12123','12123','12123','12123', ['qw','qw','qw','qw','qw'], ['qweqwe','qwewqe','wqeqwe','qweqwe','qweqwe']] ], ['5','5','5','5','5'] ]; ";

the output of this is:

INSERT INTO yourTableHere (menu_Icon,menu_Title,menu_URL,menu_Target ,menu_description) VALUES('1','1','1','1','1')
INSERT INTO yourTableHere (menu_Icon,menu_Title,menu_URL,menu_Target ,menu_description) VALUES ('2','2','2','2','2', ('6','6','6','6','6')
INSERT INTO yourTableHere (menu_Icon,menu_Title,menu_URL,menu_Target ,menu_description) VALUES ('7','7','7','7','7', ('11','11','11','11','11')
INSERT INTO yourTableHere (menu_Icon,menu_Title,menu_URL,menu_Target ,menu_description) VALUES ('22','22','22','22','22') )
INSERT INTO yourTableHere (menu_Icon,menu_Title,menu_URL,menu_Target ,menu_description) VALUES ('8','8','8','8','8') )
INSERT INTO yourTableHere (menu_Icon,menu_Title,menu_URL,menu_Target ,menu_description) VALUES ('3','3','3','3','3')
INSERT INTO yourTableHere (menu_Icon,menu_Title,menu_URL,menu_Target ,menu_description) VALUES ('12','12','12','12','12', ('121','121','121','121','121')
INSERT INTO yourTableHere (menu_Icon,menu_Title,menu_URL,menu_Target ,menu_description) VALUES ('1212','1212','1212','1212','1212')
INSERT INTO yourTableHere (menu_Icon,menu_Title,menu_URL,menu_Target ,menu_description) VALUES ('12123','12123','12123','12123','12123', ('qw','qw','qw','qw','qw')
INSERT INTO yourTableHere (menu_Icon,menu_Title,menu_URL,menu_Target ,menu_description) VALUES ('qweqwe','qwewqe','wqeqwe','qweqwe','qweqwe') )
INSERT INTO yourTableHere (menu_Icon,menu_Title,menu_URL,menu_Target ,menu_description) VALUES ('5','5','5','5','5') );


Unfortunately this is wrong, do you think you can help me in getting this corrected please?
Thanks
ousemat24
0
 
rameedevCommented:
If ['2','2','2','2','2', ['6','6','6','6','6'],  are separtae sets of data then why is ']' missing from the first set of data.?

If this cannot be controlled then my suggestion is Split the entire thing get the entire array loop through it and for every 5th counter create an insert statement since you have 5 columns in your sql statement
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mousemat24Author Commented:
rameedev

My knowledge of .NET is very small, do you think you can provide the code?
Thanks
0
 
rameedevCommented:
string allEntries = "[['1','1','1','1','1'], ['2','2','2','2','2', ['6','6','6','6','6'], ['7','7','7','7','7', ['11','11','11','11','11'], ['22','22','22','22','22'] ], ['8','8','8','8','8'] ], ['3','3','3','3','3'], ['12','12','12','12','12', ['121','121','121','121','121'], ['1212','1212','1212','1212','1212'], ['12123','12123','12123','12123','12123', ['qw','qw','qw','qw','qw'], ['qweqwe','qwewqe','wqeqwe','qweqwe','qweqwe']] ], ['5','5','5','5','5']]";

allEntries.Trim();
allEntries =  allEntries.Replace("[[","[");
allEntries =  allEntries.Replace("]]","]");
                                    
string[] Test = allEntries.Split(',');
string strValues="";
                                    
int iCtrArr=0;
foreach(string strTmp in Test)
{
                        
     if(strTmp.IndexOf("]") < 0)
     {
           strValues += strTmp + ",";
           iCtrArr++;
           if(iCtrArr==5)
          {
      strValues = strValues.Replace("[","(").Replace("]",")");
      strValues = strValues.Remove(strValues.LastIndexOf(','),1) + ")";

                //add ur Database piece of code where it inserts into the DB

      strValues ="";
      iCtrArr=0;
          }
      }
      else
      {
             strValues += strTmp;
             strValues = strValues.Replace("[","(").Replace("]",")");
             iCtrArr++;
             if(iCtrArr==5)
             {
      //add ur Database piece of code where it inserts into the DB

      strValues ="";
      iCtrArr=0;
             }
      }
}
0
 
mousemat24Author Commented:
Hi rameedev

Thanks for your help!!! that worked like a charm.

Do you think you can help me with the same code, but slightly change it so that it results get written to a dataset, then once I have the values, I can then pass that to a WEB service to insert the values. I know how to write the web service, but I dont know how to write the value to a dataset.

I can open up a new question called Write array to a dataset.

Would really like your help, if its possible?
Thanks
0
 
rameedevCommented:
U mean the split values  ('1','1','1','1','1'), ('2','2','2','2','2'), ('6','6','6','6','6') has to be written to a dataset...as individual rows in the dataset
0
 
mousemat24Author Commented:
thats correct, all the values written to individual rows so it would be

'1','1','1','1','1'

'2','2','2','2','2'

'6','6','6','6','6'

etc

As I said I can create a new question if you can help me out.
Thanks

0
 
rameedevCommented:
string allEntries = "[['1','1','1','1','1'], ['2','2','2','2','2', ['6','6','6','6','6'], ['7','7','7','7','7', ['11','11','11','11','11'], ['22','22','22','22','22'] ], ['8','8','8','8','8'] ], ['3','3','3','3','3'], ['12','12','12','12','12', ['121','121','121','121','121'], ['1212','1212','1212','1212','1212'], ['12123','12123','12123','12123','12123', ['qw','qw','qw','qw','qw'], ['qweqwe','qwewqe','wqeqwe','qweqwe','qweqwe']] ], ['5','5','5','5','5']]";

                  allEntries.Trim();
                  allEntries =  allEntries.Replace("[[","[");
                  //Console.WriteLine(allEntries);
                  allEntries =  allEntries.Replace("]]","]");
                  //Console.Write(allEntries);
                  
                  string[] Test = allEntries.Split(',');
                  string strValues="";
                  string[] strArrForDS =  new string[5];
                  
                  DataSet dsSplitRows =  new DataSet();
                  DataTable dtSplitRows =  new DataTable();
                  DataRow drRow;

                  DataColumn dc1 = new DataColumn("menu_Icon");
                  DataColumn dc2 = new DataColumn("menu_Title");
                  DataColumn dc3 = new DataColumn("menu_URL");
                  DataColumn dc4 = new DataColumn("menu_Target");
                  DataColumn dc5 = new DataColumn("menu_description");

                  dtSplitRows.Columns.Add(dc1);
                  dtSplitRows.Columns.Add(dc2);
                  dtSplitRows.Columns.Add(dc3);
                  dtSplitRows.Columns.Add(dc4);
                  dtSplitRows.Columns.Add(dc5);

                  dsSplitRows.Tables.Add(dtSplitRows);

                  int iCtrArr=0;
                  foreach(string strTmp in Test)
                  {
                        
                        if(strTmp.IndexOf("]") < 0)
                        {
                              strValues += strTmp + ",";
                              iCtrArr++;
                              if(iCtrArr==5)
                              {
                                    strValues = strValues.Replace("[","(").Replace("]",")");
                                    strValues = strValues.Remove(strValues.LastIndexOf(','),1) + ")";
                                    Console.WriteLine(strValues);
                                    
                                    strArrForDS = strValues.Replace("(","").Replace(")","").Split(',');
                                    
                                                                                                // Add your DB insert Code here      

                                    drRow = dsSplitRows.Tables[0].NewRow();
                                    drRow["menu_Icon"] = strArrForDS[0].Replace("'","").Trim();
                                    drRow["menu_Title"] = strArrForDS[1].Replace("'","").Trim();
                                    drRow["menu_URL"] = strArrForDS[2].Replace("'","").Trim();
                                    drRow["menu_Target"] = strArrForDS[3].Replace("'","").Trim();
                                    drRow["menu_description"] = strArrForDS[4].Replace("'","").Trim();
                                    dsSplitRows.Tables[0].Rows.Add(drRow);
                                    
                                    strValues ="";
                                    iCtrArr=0;
                              }
                        }
                        else
                        {
                              strValues += strTmp;
                              strValues = strValues.Replace("[","(").Replace("]",")");
                              iCtrArr++;
                              if(iCtrArr==5)
                              {
                                    Console.WriteLine(strValues);

                                    strArrForDS = strValues.Replace("(","").Replace(")","").Split(',');
                                                                                                 
                                                                                                // Add your DB insert Code here      

                                    drRow = dsSplitRows.Tables[0].NewRow();
                                    drRow["menu_Icon"] = strArrForDS[0].Replace("'","").Trim();
                                    drRow["menu_Title"] = strArrForDS[1].Replace("'","").Trim();
                                    drRow["menu_URL"] = strArrForDS[2].Replace("'","").Trim();
                                    drRow["menu_Target"] = strArrForDS[3].Replace("'","").Trim();
                                    drRow["menu_description"] = strArrForDS[4].Replace("'","").Trim();
                                    dsSplitRows.Tables[0].Rows.Add(drRow);

                                    strValues ="";
                                    iCtrArr=0;
                              }
                        }
                  }
0
 
mousemat24Author Commented:
TOP QUALITY CODE!!! rameedev. THANKS FOR YOUR HARD WORK!!!
0
 
mousemat24Author Commented:
rameedev, quick question, know that its in a dataset, how do I pass it to a WEB Service? My understanding is, that I know have a dataset, with the correct column name + data. Given that, I need to pass that info to a WEB service, so that it can read it, and use a Stored Procedure to loop through the dataset and insert the value.

What whould you do, being an expert .NET developer? Do you think I'm doing this correctly? Would really like your expert advice.

PS Wish you let me create a new question for the dataset, you've really earned that.

Thanks rameedev
0
 
rameedevCommented:
Glad i was able to help....

Do you have the Web Service ready...or Do you have to Develop it....

My feeling if you are developing the webservice method then it's as simple as creating a web method which would take  a Datset as parameter and the method should be able to do the rest...i mean looping through dataset and inserting it into the DB by calling a procedure.
0
 
mousemat24Author Commented:
Thanks for replying to my quetion rameedev
I really want to create a new question, The question will be called

Pass DataSet values to a webservice

Would really appricate your help with this rameedev.

Thanks again for helping me out!!
0
 
mousemat24Author Commented:
I've just created the question rameedev

Title: Pass DataSet values to a webservice
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now