?
Solved

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

Posted on 2006-11-14
14
Medium Priority
?
269 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

765 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