CODEWITHSUNDEEP
sqlasp.netinsert
user1953051
user1953051

Reputation: 611

Inserting Item Collection in Asp.net

I am trying to insert some words that i keep as an item .My project is asp.net web project.I would like to generate id for each word and keep 4 field null.There are 6 fields in my word table in database.id word length fr Boolean weight.

When I run the project I get this error.

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

Here the code,

 using (con){
    con.Open();

    foreach (var item in results) {//here trying to insert word its id and some other informations but for now they can stay null(yes,null allowed for them)
        id++;
        SqlCommand cmd= con.CreateCommand();
        cmd.CommandText= "insert into word values('"+id+"','"+item.Word+"','"+0+"','"+0+"','"+0+"','"+0+"')";

        cmd.ExecuteNonQuery();
        con.Close();
    }

Edit:

I have moved the con.Close out of iteration loop but here now I got this error;

Server Error in '/' Application.

Incorrect syntax near 's'. Unclosed quotation mark after the character string ')'.

 using (con){
        con.Open();

        foreach (var item in results) {//here trying to insert word its id and some other informations but for now they can stay null(yes,null allowed for them)
            id++;
            SqlCommand cmd= con.CreateCommand();
            cmd.CommandText= "insert into word values('"+id+"','"+item.Word+"','"+0+"','"+0+"','"+0+"','"+0+"')";

            cmd.ExecuteNonQuery();

        }  con.Close();
}   }

Upvotes: 0

Views: 70

Answers (4)

ilan danielov
ilan danielov

Reputation: 21

on first iteration your'e closing the SQL Connection.

move the line: "con.Close();" outside of the loop.

For your second problem, make sure you escape quatation mark in word property and try using string format to create insert string

string word = String.Replace(item.Word,"'","''")
cmd.CommandText = string.Format("insert into word values('{0}','{1}','{2}','{3}','{4}','{5}')", id, word, 0, 0, 0, 0);

Upvotes: 2

M.K. Wierzba
M.K. Wierzba

Reputation: 146

You have to be sure that every word (item.Word) has no quotation char, maybe

String.Replace(item.Word,"'","''")

resolve your problem.

Upvotes: 1

Serg
Serg

Reputation: 22811

Move con.Close(); outside foreach (){}

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15389

In your code:

using (con){
    con.Open();

    foreach (var item in results) {
        //here trying to insert word its id and some other informations but for now they can stay null(yes,null allowed for them)
        id++;
        SqlCommand cmd= con.CreateCommand();
        cmd.CommandText= "insert into word values('"+id+"','"+item.Word+"','"+0+"','"+0+"','"+0+"','"+0+"')";

        cmd.ExecuteNonQuery();
        con.Close();
    }

You have written con.Close() in the foreach, so at the end of the first loop you close your connection.

So move the con.Close() after the close bracket of your foreach like this:

using (con){
con.Open();

foreach (var item in results) {//here trying to insert word its id and some other informations but for now they can stay null(yes,null allowed for them)
    id++;
    SqlCommand cmd= con.CreateCommand();
    cmd.CommandText= "insert into word values('"+id+"','"+item.Word+"','"+0+"','"+0+"','"+0+"','"+0+"')";

    cmd.ExecuteNonQuery();
}
con.Close();

Upvotes: 3

Related Questions