insert array in database

[sdhawan]

Hi Guys ,
can any one tell me how to insert array in database using insert statement.
thanks

[finito]

What database are you using?

[mono_jit23]

if u r using sqlserver, it should be easy.
Before sending it to database traverse the array and make a single string with array values in a "," or ":" then save the string as varchar into database. When u need to fetch the data u just have to break it into substrings..

[sknake]

Saving arrays in a database as any kind of delimited field is usually not a good idea. The point of databases is normalization . Why don't you save the array as individual rows in a detail table and join it to the primary table?

The implementation of that depends on which rdbms you are using.

[sdhawan]

thanks for the reply guys .I got that part working but when i inserting values in databse i get this exception error Incorrect syntax near '/'.Can anyone help me with this.I am sure that i dont have this sysmbol anywhere in my string or text.Thanks

[finito]

can you post your code so we may analyse and tell you.

[sdhawan]

sorry i knew what the problem was the problem now is i am getting error Index was outside the bounds of the array.my code is as follows

private void saveDataInDatabase()
        {
            string[] key = null;
            for (int i = 0; i < allData.Count; i++)
            {
                
                TextProcItem item = (TextProcItem)allData[i];
                string text1 = item.text.ToString();
                text1 = text1.Replace("'", "''");
                string sent2 = item.sentences.ToString();
                sent2 = sent2.Replace("'", "''");
                if (item.keywords.Length > item.sentences.Length && item.keywords.Length > item.nouns.Length && item.keywords.Length > item.adjectives.Length && item.keywords.Length > item.verbs.Length)
                {
                    for (int j = 0; j < item.keywords.Length; j++)
                    {


                        //insert query procedure here
                        string myString;
                        SqlConnection cn = new SqlConnection(@"Server=208.124.179.198;Database=Content_text;UID=sa;Password=November17th;");
                        cn.Open();
                        myString = @"INSERT INTO contentTbl(Author,Title,text,badsent,all_sent,sentences,key_word,noun,verbs,adjectives)  Values('" + "me" + "','" + item.article + "','" + text1 + "','" + item.badSent + "','" + item.allSentNumber + "','" + sent2 + "','" + item.keywords[j].ToString() + "','" + item.nouns[j].ToString() + "','" + item.verbs[j].ToString() + "','" + item.adjectives[j].ToString() + "')";
                        SqlCommand myCmd = new SqlCommand(myString, cn);
                        myCmd.ExecuteNonQuery();

                        cn.Close();


                    }
                }
                else if (item.sentences.Length > item.keywords.Length && item.sentences.Length > item.nouns.Length && item.sentences.Length > item.adjectives.Length && item.sentences.Length > item.verbs.Length)
                {
                    string text2 = item.text.ToString();
                    text2 = text2.Replace("'", "''");
                    string sent3 = item.sentences.ToString();
                    sent3 = sent3.Replace("'", "''");
                    for (int j = 0; j < item.sentences.Length; j++)
                    {


                        //insert query procedure here
                        string myString;
                        SqlConnection cn = new SqlConnection(@"Server=208.124.179.198;Database=Content_text;UID=sa;Password=November17th;");
                        cn.Open();
                        myString = @"INSERT INTO contentTbl(Author,Title,text,badsent,all_sent,sentences,key_word,noun,verbs,adjectives)  Values('" + "me" + "','" + item.article + "','" + text2 + "','" + item.badSent + "','" + item.allSentNumber + "','" + sent3 + "','" + item.keywords[j].ToString() + "','" + item.nouns[j].ToString() + "','" + item.verbs[j].ToString() + "','" + item.adjectives[j].ToString() + "')";
                        SqlCommand myCmd = new SqlCommand(myString, cn);
                        myCmd.ExecuteNonQuery();

                        cn.Close();


                    }


                }
                else if (item.nouns.Length > item.keywords.Length && item.nouns.Length > item.sentences.Length && item.nouns.Length > item.adjectives.Length && item.nouns.Length > item.verbs.Length)
                {
                    string text3 = item.text.ToString();
                    text3 = text3.Replace("'", "''");
                    string art = item.article.ToString();
                    string sent = item.sentences.ToString();
                    sent = sent.Replace("'", "''");
                    
                    for (int j = 0; j < item.nouns.Length; j++)
                    {


                        //insert query procedure here
                        string myString;
                        SqlConnection cn = new SqlConnection(@"Server=208.124.179.198;Database=Content_text;UID=sa;Password=November17th;");
                        cn.Open();
                        myString = @"INSERT INTO contentTbl(Author,Title,text,badsent,all_sent,sentences,key_word,noun,verbs,adjectives)Values('" + "me" + "','" + art + "','" + text3 + "','" + item.badSent + "','" + item.allSentNumber + "','" + sent + "','" + item.keywords[j].ToString() + "','" + item.nouns[j].ToString() + "','" + item.verbs[j].ToString() + "','" + item.adjectives[j].ToString() + "')";
                        SqlCommand myCmd = new SqlCommand(myString, cn);
                        myCmd.ExecuteNonQuery();

                        cn.Close();


                    }


                }
                else if (item.adjectives.Length > item.keywords.Length && item.adjectives.Length > item.sentences.Length && item.adjectives.Length > item.nouns.Length && item.adjectives.Length > item.verbs.Length)
                {
                    string text4 = item.text.ToString();
                    text4 = text4.Replace("'", "''");
                    string sent4 = item.sentences.ToString();
                    sent4 = sent4.Replace("'", "''");
                    for (int j = 0; j < item.adjectives.Length; j++)
                    {


                        //insert query procedure here
                        string myString;
                        SqlConnection cn = new SqlConnection(@"Server=208.124.179.198;Database=Content_text;UID=sa;Password=November17th;");
                        cn.Open();
                        myString = @"INSERT INTO contentTbl(Author,Title,text,badsent,all_sent,sentences,key_word,noun,verbs,adjectives)  Values('" + "me" + "','" + item.article + "','" + text4 + "','" + item.badSent + "','" + item.allSentNumber + "','" + sent4 + "','" + item.keywords[j].ToString() + "','" + item.nouns[j].ToString() + "','" + item.verbs[j].ToString() + "','" + item.adjectives[j].ToString() + "')";
                        SqlCommand myCmd = new SqlCommand(myString, cn);
                        myCmd.ExecuteNonQuery();

                        cn.Close();


                    }


                }
                else if (item.verbs.Length > item.keywords.Length && item.verbs.Length > item.sentences.Length && item.verbs.Length > item.nouns.Length && item.verbs.Length > item.adjectives.Length)
                {
                    string text5 = item.text.ToString();
                    text5 = text5.Replace("'", "''");
                    string sent5 = item.sentences.ToString();
                    sent5 = sent5.Replace("'", "''");
                    for (int j = 0; j < item.verbs.Length; j++)
                    {


                        //insert query procedure here
                        string myString;
                        SqlConnection cn = new SqlConnection(@"Server=208.124.179.198;Database=Content_text;UID=sa;Password=November17th;");
                        cn.Open();
                        myString = @"INSERT INTO contentTbl(Author,Title,text,badsent,all_sent,sentences,key_word,noun,verbs,adjectives)  Values('" + "me" + "','" + item.article + "','" + text5 + "','" + item.badSent + "','" + item.allSentNumber + "','" + sent5 + "','" + item.keywords[j].ToString() + "','" + item.nouns[j].ToString() + "','" + item.verbs[j].ToString() + "','" + item.adjectives[j].ToString() + "')";
                        SqlCommand myCmd = new SqlCommand(myString, cn);
                        myCmd.ExecuteNonQuery();

                        cn.Close();


                    }


                }

            }
        }

i would really appriciate if someone can help me with this

[sdhawan]

i got in in the insert statement i need to replace [j] with [i].thanks

[finito]

always nice to see when someone solves their problem. Please mark as solved if solved.

[sdhawan]

Sorry finito it is suppose to be j so i am back to square one, i am still getting error array index out of bound.Plz help :(

[sdhawan]

do anyone knw how can i store an array in a string and insert that string in databse.So supose if i have string with values a,b,c ,d.... etc i dont want each value in different row of databse table, i want them ina single row.Can anyone help me with this

[finito]

you never told me which Sql server you are using?

[__avd]

do anyone knw how can i store an array in a string and insert that string in databse.So supose if i have string with values a,b,c ,d.... etc i dont want each value in different row of databse table, i want them ina single row.Can anyone help me with this

My answer is absolutely not. These are two completely different data structures. There are work around like putting it in a blob (serialization) or comma separating a text column but you don't to access the the individual values in your queries.NOTE: Use parametrized query.