can't insert a record into table

Question

Hi,

I wrote a program which includes writing and reading from database. When I run the app and try to perform writing I call the following method:

public static void AddMessage(string callID, string content)
    {
        string select =
            "INSERT INTO Sporocilo (oznaka_klica, smer, vsebina, prebrano, cas_zapisa) VALUES (@callId, 0, @content, 0, @insertTime)";
        SqlCommand cmd = new SqlCommand(select, conn);
        cmd.Parameters.AddWithValue("callId", callID.ToString());
        cmd.Parameters.AddWithValue("content", content);
        cmd.Parameters.AddWithValue("insertTime", "10.10.2008");
        try
        {
            conn.Open();
            cmd.ExecuteScalar();
        }
        catch(Exception ex)
        {
            string sDummy = ex.ToString();
        }
        finally
        {
            conn.Close();
        }
    }

After the method call I read all the records from the table and display them in the form. The record inserted before refresh could be seen but then when I exit the app and look at the table I don't see the record.

Does anyone know what could cause such behavior?

Answer 1

It seems there is an exception and you catch it. Try to debug and check ex.Message

Answer 2

There is no exception when I run the debug.

Also when I try to execute a generated sql statement in sql pane the record is stored.

Answer 3

Are you performing a commit after this? It might be running your statement but then not committing the changes and doing an implicit rollback.

I think the exception handling looks dodgy. There is no point catching something unless you can actually handle it in some way. The top level of your framework is the place for catching and reporting unexpected exceptions.

Answer 4

Have you tried setting the return value of the ExecuteScalar method to an int and then checking value against the table?

ExecuteScalar -

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored

public static int AddMessage(string callID, string content)
    {
        Int32 newProdID = 0
        string select =
            "INSERT INTO Sporocilo (oznaka_klica, smer, vsebina, prebrano, cas_zapisa) VALUES (@callId, 0, @content, 0, @insertTime); SELECT CAST(scope_identity() AS int);";
        SqlCommand cmd = new SqlCommand(select, conn);
        cmd.Parameters.AddWithValue("callId", callID.ToString());
        cmd.Parameters.AddWithValue("content", content);
        cmd.Parameters.AddWithValue("insertTime", "10.10.2008");
        try
        {
            conn.Open();
            newProdID = (Int32)cmd.ExecuteScalar();
        }
        catch(Exception ex)
        {
            string sDummy = ex.ToString();
        }
        finally
        {
            conn.Close();
        }
        return (int)newProdID
    }

Answer 5

I found the problem. I modified the automatically generated connection string

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\URSZRDB.mdf;Integrated Security=True;User Instance=True"

with

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Niko\Documents\Visual Studio 2008\Projects\URSZRWAPChat\URSZRWAPChat\URSZRDB.mdf;Integrated Security=True;User Instance=True"

and now it works.

It's not the first time I write this kind of program and so far everything has gone well this way...

Answer 6

Thank you guys you helped me a lot.

Answer 7

Thanks for the help on this, was driving me mad, Nikos answer is correct. The autogenerated connection string doesnt work correctly. By replacing the location of the database with the exact location rather than |DataDirectory| it solves the problem. It would make sense to do this especially once the program is released, but for some reason it doesnt work. Also, why would this not throw an exception? If it did, we could probably have figured it out quicker. Anyways, thanks Niko.