Insert command not working on connection between C# and SQL Server

Question

I have this table in SQL Server:

CREATE TABLE Ciudadano (  
ci int not null,  
Nombre varchar(50),  
Apellido varchar(50),  
Correo varchar(50),  
Usuario varchar(50), 
 Contrasena varchar(50),  
Localidad_cod int,  
Primary key (ci)  
)

I'm trying to add data to the table Ciudadano from Visual Studio c#, using a stored procedure called sp_ConsultarCuenta:

CREATE PROCEDURE sp_ConsultarCuenta
   @ci int, @nom varchar(50), @ape varchar(50), @user varchar(50), 
   @pass varchar(50), @correo varchar(50), @loc varchar(50)
AS
   IF NOT EXISTS(SELECT ci,Usuario FROM Ciudadano 
                 WHERE ci = @ci OR Usuario = @user)
      DECLARE @var varchar(50)
      SET @var = (SELECT cod From Localidad Where Nombre = @loc)

      INSERT INTO Ciudadano (ci, Nombre, Apellido, Correo, Usuario, Contrasena, Localidad_cod)
      VALUES (@ci, @nom, @ape, @user, @pass, @correo, @var)

And on my c# code I have this:

public void AgregarCiudadano()
{
   string con = @"Data Source=(local);Initial Catalog=SistemaDeQuejas;Trusted_Connection=True";
   SqlConnection conexion = new SqlConnection(con);

   conexion.Open();
   SqlDataAdapter Comando = new SqlDataAdapter();

   Comando.InsertCommand = conexion.CreateCommand();
   Comando.InsertCommand.CommandText = "sp_ConsultarCuenta";
   Comando.InsertCommand.CommandType = CommandType.StoredProcedure;
   Comando.InsertCommand.Connection = conexion;

   Comando.InsertCommand.Parameters.Add("@ci", SqlDbType.Int).Value = System.Convert.ToInt32(ci);
   Comando.InsertCommand.Parameters.Add("@nom", SqlDbType.VarChar, 50).Value = Nombre;
   Comando.InsertCommand.Parameters.Add("@ape", SqlDbType.VarChar, 50).Value = Apellido;
   Comando.InsertCommand.Parameters.Add("@user", SqlDbType.VarChar, 50).Value = Usuario;
   Comando.InsertCommand.Parameters.Add("@pass", SqlDbType.VarChar, 50).Value = Contrasena;
   Comando.InsertCommand.Parameters.Add("@correo", SqlDbType.VarChar, 50).Value = Correo;
   Comando.InsertCommand.Parameters.Add("@loc", SqlDbType.VarChar, 50).Value = Localidad;

   conexion.Close();  
}

What I'm doing is a Web form, and that method is called by a button on the form. But when I run it and press the button it doesn't throw any error but the data I'm trying to insert into the database table is not inserting at all. Is there something wrong on my code?

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Jul 1, 2013 at 4:34

MSI · Accepted Answer · 2013-06-30 23:45:46Z

2

cYou can do this to run your insert command in a simple way

using(var connection = new SqlConnection(connectionString))
{
   connection.Open();

   using (var command = new SqlCommand("sp_ConsultarCuenta", connection))
   {
        command.Parameters.AddWithValue("@Nombre", params.Nombre);

        command.CommandType = CommandType.StoredProcedure;

        command.ExecuteNonQuery();
   }

   connection.Close();
 }

edited Jun 30, 2013 at 23:45

answered Jun 30, 2013 at 23:38

MSI

1,1348 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

MSI Over a year ago

This is a bit crude. I would probably create a generic method to handle my command and then just pass the procedure name along with a list of param values.

JJDA Over a year ago

I just ran your code and it throws the following error: Error converting data type nvarchar to int. On the command.ExecuteNonQuery(); line

MSI Over a year ago

Use correct data type in your param. I presume your Nombre field is a number and you are trying to supply nvarchar in it. Lemme know whether or not that's the case.

MSI Over a year ago

How did it go? Don't forget to let us know :).

JJDA Over a year ago

Hi, sorry I didn't answer earlier, I was busy with other stuff. My Nombre field is actually a varchar field that means "Name", but my number field in this case is "ci". How should I put them?

|

Collectives™ on Stack Overflow

Insert command not working on connection between C# and SQL Server

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related