Problems connecting to SQL Server database [duplicated]

2

I have the following code:

public static int Guardar(string codigo, string nombre, string apellido)
{
    Clase_Conexion conexion = new Clase_Conexion();

    int retorno = 0;//existe codigo. Producto repetido
    String sql="INSERT into Table (codigo, nombre, apellido) VALUES (@codigo, @nombre, @apellido)";
    //String cstring = ConfigurationManager.ConnectionStrings["conecta"].ConnectionString;
    //using (SqlConnection connection = new SqlConnection(cstring))
    //{
        using (SqlCommand command = new SqlCommand())
        {
            command.Connection = conexion.con;            // <== lacking
            command.CommandType = CommandType.Text;
            command.CommandText = "INSERT into Table (codigo, nombre, apellido) VALUES (@codigo, @nombre, @apellido)";
            command.Parameters.AddWithValue("@codigo", codigo);
            command.Parameters.AddWithValue("@nombre", nombre);
            command.Parameters.AddWithValue("@apellido", apellido);

            try
            {
                conexion.conectar();
                int recordsAffected = command.ExecuteNonQuery();
                retorno = 1;
            }
            catch (SqlException)
            {
                // error here
            }
            finally
            {
                conexion.cerrar();
            }
        }
    //}
    return retorno;
}

And I have the following connection class:

public class Clase_Conexion
{
    public SqlConnection con;
    public void conectar()
    {           
        con = new SqlConnection("data source = localhost; initial catalog = BD_ejemplo"); // LOCALHOST
        con.Open();
    }
    //metodo para cerrar
    public void cerrar()
    {
        con.Close();
    }
}

I have a database called BD_ejemplo.mdf in App_Data

And in the web.config I have the following conectionString of the following form:

 <add name="conecta" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-ejemplo_ajax_webform-20170317135041;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-ejemplo_ajax_webform-20170317135041.mdf" />

Firstly I was using the connection through the web.config, but it did not work out because it said it could not find the database path. Later I decided to do it through a connection class, but I can not either, since I get the following error:

  

Error related to the network or specific to the instance while establishing a connection to the SQL Server. The server was not found or it was not accessible. Verify that the name of the instance is correct and that SQL Server is configured to support remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection with SQL Server)

I would like to know how I make the connection both through the web.config and especially with the connection file (class). I know that something is badly written, or some parameter I need to enter in:

con = new SqlConnection("data source = localhost; initial catalog = BD_ejemplo") //(para el caso de la clase de conexion)

I hope I can find the solution.

Observation: For now I am without a password to the database. Since you add from the same Visual Studio.

I have checked the connection and it is correct. Here I show the following:

The name of the database file is visual studio 2013 \ Projects \ example_ajax_webform \ example_ajax_webform \ App_Data \ BD_example.dmdf

And in Advance, there is a part that says:

Data Source = (LocalDB) \ v11.0; AttachDbFilename="C: \ Users \ X_user \ Documents \ Visual Studio 2013 \ Projects \ WebAxExample \ WebAxExample \ App_Data \ BD_Example.dmdf.mdf"; Integrated Security = True

asked by Danilo 22.03.2017 в 20:01
source

2 answers

0

I recommend you verify the obvious: The first thing I suggest is that you make the connection from the visual studio through the server browser and copy and paste it into the WebConfig, obviously after confirming the connection.

Also Verify that the service is running. Verify that it is not a problem with the firewall (port 1433).

Most of the times I get that error is an error in the connection string in the webconfig.

    
answered by 22.03.2017 в 20:17
0

Look, I recommend this method better, it has bailed me out. Create a method in a class called "Connection" and add this:

public class clsConexioncs
{

    private SqlCommand cmd;
    private SqlConnection Conn;
    public string Conexion;// { get; set; }        

    public void AbrirConexion()
    {
        try
        {
            this.cmd = new SqlCommand();                                                                   
            this.Conn = new SqlConnection();
            this.Conn.ConnectionString = Conexion;
            this.Conn.Open();

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             this.cmd.CommandTimeout = 10000;
            this.cmd.Connection = Conn;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message.ToString().Trim());
            CerrarConexion();
        }
    }
}

Inside the webConfig, add this:

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-Dash_Femsa-20170215043808.mdf;Initial Catalog=aspnet-Dash_Femsa-20170215043808;Integrated Security=True" providerName="System.Data.SqlClient"/>
    <add name="DefaultDW" connectionString="Data Source=10.12.14.6;Initial Catalog=PRM; User ID= CorrelatorUser; Password= CorrelatorUser01" providerName="System.Data.SqlClient"/>
  </connectionStrings>

Only configure it with your variables and you will see that it works for you, it is more secure and concrete.

Mandala to call from your main and you'll see that it works for your entire program.

    
answered by 22.03.2017 в 20:18