Problem with INSERT INTO

Question

I’m trying to make a INSERT in the MS SQL, when running the code, there is no error, however INSERT in sql, running a script manually in sql saves it without any problem.

I have tried both ways, the one that is also commented.

        private void InsertToDatabase(string Name, string Document, string Board, int Block, int apartment, string AuthorizedFrom, string DataEntry, string Comments)
    {
        try
        {
            SqlConnection connect = new SqlConnection(DBOConnect.connectionString);
            SqlCommand cmd = new SqlCommand("INSERT INTO visitors (name, document, board, block, apartment, authorized_from, date_entry, username, comments) VALUES ('" + Name + "', '" + Document + "', '" + Board + "', '" + Block + "', '" + apartment + "', '" + AuthorizedFrom + "', '" + DataEntry + "', '" + Profile.LoginInformation[0].Username + "', '" + Comments + "')", connect);
            connect.Open();
            cmd.ExecuteNonQuery();
            connect.Close();

            //using (SqlConnection connect = new SqlConnection(DBOConnect.connectionString))
            //using (SqlCommand cmd = connect.CreateCommand())
            //{
            //    cmd.CommandText = @"INSERT INTO visitors (name, document, board, block, apartment, authorized_from, date_entry, username, comments) VALUES (@Name, @Document, @Board, @Block, @apartment, @AuthorizedFrom, @DataEntry, @Username, @Comments)";
            //    cmd.Parameters.AddWithValue("@Name", Name);
            //    cmd.Parameters.AddWithValue("@Document", Document);
            //    cmd.Parameters.AddWithValue("@Board", Board);
            //    cmd.Parameters.AddWithValue("@Block", Block);
            //    cmd.Parameters.AddWithValue("@apartment", apartment);
            //    cmd.Parameters.AddWithValue("@AuthorizedFrom", AuthorizedFrom);
            //    cmd.Parameters.AddWithValue("@DataEntry", DataEntry);
            //    cmd.Parameters.AddWithValue("@Username", Profile.LoginInformation[0].Username);
            //    cmd.Parameters.AddWithValue("@Comments", Comments);
            //    connect.Open();
            //    cmd.ExecuteNonQuery();
            //    connect.Close();
            //}
        }
        catch (SqlException ex)
        {
            System.Media.SystemSounds.Exclamation.Play();
            var frmMain = System.Windows.Application.Current.Windows.OfType<MainWindow>().FirstOrDefault();
            frmMain.ShowMessageAsync("Erro", ex.Message);

            baseGrid.Children.Remove(psbState);
            btnSave.IsEnabled = true;
        }

        System.Media.SystemSounds.Asterisk.Play();

        baseGrid.Children.Remove(psbState);
        baseGrid.Children.Add(lbState);

        ClearContent();
    }

Table structure:

    CREATE TABLE [dbo].[visitors] (
    [Id]              INT           IDENTITY (1, 1) NOT NULL,
    [name]            VARCHAR (MAX) NULL,
    [document]        VARCHAR (50)  NULL,
    [board]           VARCHAR (50)  NULL,
    [block]           INT           NULL,
    [apartment]       INT           NULL,
    [authorized_from] VARCHAR (MAX) NULL,
    [date_entry]      DATETIME      NULL,
    [username]        VARCHAR (50)  NULL,
    [comments]        VARCHAR (MAX) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

I found where the bug is, but I don’t know how to fix it, error:

Msg 208, Level 16, State 1, Line 4 Invalid Object name 'Visitors'.

String Connection:

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

Answer 1

Variable Apartment and block are integer so do not use the 'in them.

Answer 2

I believe the problem is in your user’s default schema which is not dbo. To check if this is so, just put [dbo]. [Visitors] in INSERT INTO and see the result. If it works as expected, you can leave the user schema as default [dbo]. Follow a reference link https://stackoverflow.com/questions/3282665/possible-to-set-default-schema-from-connection-string. Remember that your user is the Windows account that is running the application because you are using the in the string Connection parameter Integrated Security=True.

Answer 3

an example of persistence using Sqlclient

public class Conexao
    {
        //declarar atributos..
        //protected -> somente pode ser acessado por herança
        protected SqlConnection Con;    //conexão com o banco de dados
        protected SqlCommand Cmd;       //executar comandos SQL
        protected SqlDataReader Dr;     //Ler dados de consultas 
        protected SqlTransaction Tr;    //Transações em banco de dados (commit/rollback)

        //declarar os metodos..
        protected void OpenConnection() //conexão...
        {
            Con = new SqlConnection(ConfigurationManager.ConnectionStrings["aula"].ConnectionString);
            Con.Open(); //conexão aberta!
        }

        protected void CloseConnection() //desconectar...
        {
            Con.Close(); //conexão fechada!
        }
    }



public class ClienteDal : Conexao
 {

    public void Insert(Cliente c)
            {
                try
                {
                    OpenConnection(); //abrir conexão..
                    Cmd = new SqlCommand("insert into Cliente(Nome, Email, Sexo, DataCadastro) values(@v1, @v2, @v3, GetDate())", Con);
                    Cmd.Parameters.AddWithValue("@v1", c.Nome);
                    Cmd.Parameters.AddWithValue("@v2", c.Email);
                    Cmd.Parameters.AddWithValue("@v3", c.Sexo.ToString());
                    Cmd.ExecuteNonQuery(); //executar..
                }
                catch (Exception e)
                {
                    //lançar uma exceção para o projeto principal..
                    throw new Exception("Erro ao inserir Cliente: " + e.Message);
                }
                finally
                {
                    CloseConnection(); //fechar conexão..
                }
            }
}