Solved

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

Posted on 2006-11-14
14
251 Views
Last Modified: 2010-04-16
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
Comment
Question by:mousemat24
  • 8
  • 6
14 Comments
 
LVL 5

Expert Comment

by:rameedev
ID: 17944909
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
 

Author Comment

by:mousemat24
ID: 17945789
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
 
LVL 5

Expert Comment

by:rameedev
ID: 17946044
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
 

Author Comment

by:mousemat24
ID: 17946087
rameedev

My knowledge of .NET is very small, do you think you can provide the code?
Thanks
0
 
LVL 5

Accepted Solution

by:
rameedev earned 500 total points
ID: 17946319
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
 

Author Comment

by:mousemat24
ID: 17951600
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
 
LVL 5

Expert Comment

by:rameedev
ID: 17953492
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:mousemat24
ID: 17954539
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
 
LVL 5

Expert Comment

by:rameedev
ID: 17954632
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
 

Author Comment

by:mousemat24
ID: 17954849
TOP QUALITY CODE!!! rameedev. THANKS FOR YOUR HARD WORK!!!
0
 

Author Comment

by:mousemat24
ID: 17954879
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
 
LVL 5

Expert Comment

by:rameedev
ID: 17954981
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
 

Author Comment

by:mousemat24
ID: 17955048
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
 

Author Comment

by:mousemat24
ID: 17955139
I've just created the question rameedev

Title: Pass DataSet values to a webservice
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now