Solved

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

Posted on 2006-11-14
14
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

696 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