Insert into the database "MDF" does not work

Question

Honestly I've tried everything and in many ways, but I can not give a simple INSERT in the database. Please could someone tell me what is wrong with this code?

using (SqlConnection con = new SqlConnection(AcessoBD.ConnectionString))
{
    string queryUsuario = @"INSERT INTO si_usuario (senha, login) VALUES ('aaa', 'aaa');";

    using (SqlCommand cmd = new SqlCommand(queryUsuario, con))
    {
       con.Open();
       cmd.BeginExecuteNonQuery();

    }
}

Making it clear that I've tried with: cmd.ExecuteNonQuery(); But it did not work!

The funny thing is that I can make the bench query above using the same structure, only even changing the query. Is it a permission problem? But no error occurs!

public class AcessoBD
    {
        static public String ConnectionString
        {
            get
            {    
                return ConfigurationManager.ConnectionStrings["Conexao"].ConnectionString;
            }
        }
    }

Table Structure

CREATE TABLE [dbo].[si_usuario] (
    [id]    INT           IDENTITY (1, 1) NOT NULL,
    [senha] VARCHAR (100) NOT NULL,
    [login] VARCHAR (100) NOT NULL,
    CONSTRAINT [PK_si_usuario] PRIMARY KEY CLUSTERED ([id] ASC)
);

App.config

<add name="Conexao" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Base\Database.mdf;Integrated Security=True" providerName="System.Data.SqlClient" />

What do you mean by _it did not work_? Any exception or error message? — Soner Gönül, Jan 05 '14 at 19:52
I'm sorry if there are any errors, but I'm using the google translator. I put the connection string. See that! — Ronald Araújo, Jan 05 '14 at 19:54
No error message occurs! But when I look in the database the insert does not work! — Ronald Araújo, Jan 05 '14 at 19:56
You're still **NOT SHOWING** the **actual connection string** that's stored inside your config file! — marc_s, Jan 05 '14 at 20:37

score 0 · Answer 1 · answered Jan 05 '14 at 20:00

0

Try changing to this:

using (SqlConnection con = new SqlConnection(AcessoBD.ConnectionString))
{
    con.Open();
    using (SqlCommand cmd = con.CreateCommand())
    {
        cmd.CommandText = @"INSERT INTO si_usuario (senha, login) VALUES ('aaa', 'aaa');";
        cmd.ExecuteNonQuery();

    }
}

answered Jan 05 '14 at 20:00

mopicus

159
4

Have you tried with select or update? Do you have halt on exceptions enabled on Visual Studio? Have you tried running the statement using same credentials, server ... in query analyzer? – mopicus Jan 05 '14 at 20:12
To select works, but to delete, update and insert not working! – Ronald Araújo Jan 05 '14 at 20:13
There is no exception. I have already put the same query and INSERT is done correctly. – Ronald Araújo Jan 05 '14 at 20:18
If select works but no modify operations it must be a permission problem for sure. Please check grants for the user and the given table. – mopicus Jan 05 '14 at 20:19

CRAFTY DBA · Accepted Answer · 2014-01-05T22:37:01.143

It must be a permission issue. Your code works fine in the SQL Server 2012 developer version. See quick test below.

I can only assume you are using SQL Server express that comes with Visual Studio and did not give the trusted user INSERT, UPDATE, DELETE permissions to the table.

Download the SQL Server Management Studio Express and start debugging your issue!

http://msdn.microsoft.com/en-us/evalcenter/hh230763

Take a look at the windows application log or SQL Server Error log to see if you are ignoring errors in your C# program. Trace the connection to the database using the profiler to make sure you are passing the INSERT.

Take a look at the security under logins / user mapping. Does the user map to the database with default schema dbo? What permission did you give them.

If they only have public, add db_datareader and db_datawriter so the can read/write to all tables.

Otherwise, GRANT them rights at the table level. But you will to do this for each table, more maintenance.

Take a look at my blog article on using SSMS to create logins/users.

http://craftydba.com/?p=4369

Good luck

-- Code works fine!
use tempdb
go

CREATE TABLE [dbo].[si_usuario] (
    [id]    INT           IDENTITY (1, 1) NOT NULL,
    [senha] VARCHAR (100) NOT NULL,
    [login] VARCHAR (100) NOT NULL,
    CONSTRAINT [PK_si_usuario] PRIMARY KEY CLUSTERED ([id] ASC)
);
go

INSERT INTO si_usuario (senha, login) VALUES ('aaa', 'aaa');
go

select * from si_usuario
go

enter image description here

Thanks for the tip. I'll try here. :) – Ronald Araújo Jan 05 '14 at 22:52 — Ronald Araújo, Jan 05 '14 at 22:52

Insert into the database "MDF" does not work

2 Answers2