What am I doing wrong with this query?

Question

Hello, I can't seem to find why this function doesn't insert records into the database. :( I get no error messages or whatsoever, just nothing in the database.

EDIT: this is how my query looks now .. still nothing ..

connection.Open();
XmlNodeList nodeItem = rssDoc.SelectNodes("/edno23/posts/post");

foreach (XmlNode xn in nodeItem)
{
 cmd.Parameters.Clear();
 msgText = xn["message"].InnerText;
 C = xn["user_from"].InnerText;
 avatar = xn["user_from_avatar"].InnerText;
 string   endhash =  GetMd5Sum(msgText.ToString());
 cmd.Parameters.Add("@endhash",endhash);
 cmd.CommandText  = "Select * FROM posts Where hash=@endhash";
 SqlCeDataReader reader = cmd.ExecuteReader();

 while (reader.Read())
 {
  string msgs = reader["hash"].ToString();

  if (msgs != endhash || msgs == null)
  {
   sql = "INSERT INTO posts([user],msg,avatar,[date],hash) VALUES(@username,@messige,@userpic,@thedate,@hash)";
   cmd.CommandText = sql;
   cmd.Parameters.Add("@username", C);
   cmd.Parameters.Add("@messige", msgText.ToString());
   cmd.Parameters.Add("@userpic", avatar.ToString());
   cmd.Parameters.Add("@thedate", dt);
   cmd.Parameters.Add("@hash", endhash);
   cmd.ExecuteNonQuery();// executes query
   adapter.Update(data);// saves the changes
  }
 }

 reader.Close();
}

connection.Close();

Answer 1

How to debug programs: http://www.drpaulcarter.com/cs/debug.php

Seriously, can you post some more information about where it's working? Does it work if you use SQL Server Express instead of SQL CE? If so, can you break out SQL Profiler and take a look at the SQL commands being executed?

Answer 2

I suspect your problem is that you're trying to reuse the same SqlCeCommand instances.

Try making a new SqlCeCommand within the while loop. Also, you can use the using statement to close your data objects.

---

Why are you calling adapter.Update(data) since you're not changing the DataSet at all? I suspect you want to call adapter.Fill(data). The Update method will save any changes in the DataSet to the database.

Answer 3

Lots of things going on here...

You are using the command 'cmd' to loop over records with a datareader, and then using the same 'cmd' command inside the while statement to execute an insert statement. You declared another command 'cmdAdd' before but don't seem to use it anywhere; is that what you intended to use for the insert statement?

You also close your data connection inside the while loop that iterates over your datareader. You are only going to read one record and then close the connection to your database that way; if your conditional for inserting is not met, you're not going to write anything to the database.

EDIT:

You really should open and close the connection to the database outside the foreach on the xmlnodes. If you have 10 nodes to loop over, the db connection is going to be opened and closed 10 times (well, connection pooling will probably prevent that, but still...)

You are also loading the entire 'posts' table into a dataset for seemingly no reason. You're not changing any of the values in the dataset yet you are calling an update on it repeatedly (at "save teh shanges"). If the 'posts' table is even remotely large, this is going to suck a lot of memory for no reason (on a handheld device, no less).

Answer 4

Why are you closing the Database Connection inside the while loop?
The code you posted should throw an exception when you try to call cmd.ExecuteNonQuery() with an unopen DB connection object.

SqlCeCommand.ExecuteNonQuery().aspx) method returns the number of rows affected.
Why don't you check whether it is returning 1 or not in the debugger as shown below?

int rowsAffectedCount = cmd.ExecuteNonQuery();

Hope it helps :-)

Answer 5

Does nodeItem actually have any items in it? If not, the contents of the foreach loop aren't being executed.

What's the adapter and data being used for? The queries and updates seem be done via other commands and readers.

What does 'hash' actually contain? If it's a hash, why are you hashing the content of the hash inside the while loop? If not, why is it being compared against a hash in the query SELECT * FROM posts WHERE hash = @endhash?

Won't closing the connection before the end of the while loop invalidate the reader used to control the loop?

Answer 6

Is anything returned from "Select * FROM posts Where hash=@endhash"?

If not, nothing inside the while loop matters....

Answer 7

You've got some issues with not implementing "using" blocks. I've added some to your inner code below. The blocks for the connection and select command are more wishful thinking on my part. I hope you're doing the same with the data adapter.

using (var connection = new SqlCeConnection(connectionString))
{
    connection.Open();
    var nodeItem = rssDoc.SelectNodes("/edno23/posts/post");

    foreach (XmlNode xn in nodeItem)
    {
        using (
            var selectCommand =
                new SqlCeCommand(
                    "Select * FROM posts Where hash=@endhash",
                    connection))
        {
            var msgText = xn["message"].InnerText;
            var c = xn["user_from"].InnerText;
            var avatar = xn["user_from_avatar"].InnerText;
            var endhash = GetMd5Sum(msgText);
            selectCommand.Parameters.Add("@endhash", endhash);
            selectCommand.CommandText =
                "Select * FROM posts Where hash=@endhash";
            using (var reader = selectCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    var msgs = reader["hash"].ToString();

                    if (msgs == endhash && msgs != null)
                    {
                        continue;
                    }

                    const string COMMAND_TEXT =
                        "INSERT INTO posts([user],msg,avatar,[date],hash) VALUES(@username,@messige,@userpic,@thedate,@hash)";
                    using (
                        var insertCommand =
                            new SqlCeCommand(
                                COMMAND_TEXT, connection))
                    {
                        insertCommand.Parameters.Add("@username", c);
                        insertCommand.Parameters.Add(
                            "@messige", msgText);
                        insertCommand.Parameters.Add(
                            "@userpic", avatar);
                        insertCommand.Parameters.Add("@thedate", dt);
                        insertCommand.Parameters.Add(
                            "@hash", endhash);
                        insertCommand.ExecuteNonQuery();
                            // executes query
                    }
                    adapter.Update(data); // saves teh changes
                }

                reader.Close();
            }
        }
    }

    connection.Close();
}

Of course with the additional nesting, parts should be broken out as separate methods.