Procedure or function 'InsertaUsuario' expects parameter '@name', which was not supplied

Question

Procedure or function 'InsertaUsuario' expects parameter '@name', which was not supplied

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have an error when calling my procedures on vs, I am trying to insert users. My procedures are the following: Here I have the name, type of users and ID

ALTER PROCEDURE [dbo].[InsertaUsuario](
@id int = 0,
@nombre nvarchar(50),
@memo nvarchar (50)
)
AS
BEGIN
Set NOCOUNT ON

MERGE [Vendedores] as TARGET
USING (SELECT @id, @nombre, @memo) AS SOURCE(SlpCode, SlpName, Memo)
ON TARGET.SlpCode = SOURCE.SlpCode
WHEN MATCHED THEN
    UPDATE SET @nombre = SOURCE.SlpName,
               @memo = SOURCE.Memo
WHEN NOT MATCHED THEN
    INSERT (SlpCode, SlpName, Memo) values (@id, @nombre, @memo);
END

And for Passwords I use the following:

ALTER PROCEDURE [dbo].[Usuario](
@id2 int = 0,
@pass nvarchar (50)
)
AS
BEGIN
Set NOCOUNT ON

MERGE [Usuarios] as TARGET
USING (SELECT @id2, @pass) AS SOURCE(SlpCode, passw)
ON (TARGET.SlpCode = SOURCE.SlpCode)
WHEN MATCHED THEN
    UPDATE SET  @pass = SOURCE.passw
WHEN NOT MATCHED THEN
    INSERT (SlpCode, passw) values (@id2, @pass);

END

And in VS I have it like this:

 protected void Button3_Click(object sender, EventArgs e)
{
    int Slpcode = 0;
    if (!string.IsNullOrEmpty(TxtID.Text))
    {
        if (!int.TryParse(TxtID.Text, out Slpcode))
        {
            lblMensaje.Text = ("El ID debe ser un valor numérico");
            return;
        }
            cn.Open();
            using (OdbcCommand cmd = new OdbcCommand("InsertaUsuario", cn))
            {
            cmd.CommandType = CommandType.StoredProcedure;
            OdbcParameter pam = new OdbcParameter("id", OdbcType.Int);
            pam.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(pam);
            cmd.Parameters.AddWithValue("@id", Convert.ToInt32(TxtID.Text));
            cmd.Parameters.AddWithValue("@nombre", TxtNombre.Text);
            cmd.Parameters.AddWithValue("@memo", List.SelectedItem.ToString());
            int rows = cmd.ExecuteNonQuery();
            using (OdbcCommand cnn = new OdbcCommand("Usuario", cn))
                {
                    cnn.CommandType = CommandType.StoredProcedure;
                    OdbcParameter pa = new OdbcParameter("id2", OdbcType.Int);
                    pam.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(pa);
                    cnn.Parameters.AddWithValue("@id2", Convert.ToInt32(TxtID.Text));
                    cnn.Parameters.AddWithValue("@pass", TxtContra.Text);
                    int dv = cnn.ExecuteNonQuery();
                if (rows == 1)
                {
                    if (dv == 1)
                    {
                        lblMensaje.Text = "Datos añadidos correctamente";
                        Grilla.DataBind();
                        Limpiar();
                        return;
                    }
                }
                else
                {
                    lblMensaje.Text = "Error al ingresar datos";
                    Limpiar();
                    return;
                }
                }
            }


    }

}

c# sql-server asp.net

asked by Elizabeth 08.06.2018 в 16:17

source

2 answers

0

Simply modify the instruction lines to validate the parameters.

 cn.Open();
            using (OdbcCommand cmd = new OdbcCommand("{call InsertaUsuario(?)}", cn))
            {
            cmd.CommandType = CommandType.StoredProcedure;
            OdbcParameter pam = new OdbcParameter("id", OdbcType.Int);
            pam.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(pam);
            cmd.Parameters.Add("1", OdbcType.Int).Value = Convert.ToInt32(TxtID.Text);
            cmd.Parameters.Add("2", OdbcType.NVarChar,50).Value = TxtNombre;
            cmd.Parameters.Add("3", OdbcType.NVarChar,50).Value= List.SelectedItem.ToString();
            int rows = cmd.ExecuteNonQuery();
            using (OdbcCommand cnn = new OdbcCommand("{call Usuario(?)}", cn))
                {
                    cnn.CommandType = CommandType.StoredProcedure;
                    OdbcParameter pa = new OdbcParameter("id2", OdbcType.Int);
                    pam.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(pa);
                    cnn.Parameters.Add("1", OdbcType.Int).Value =  Convert.ToInt32(TxtID.Text);
                    cnn.Parameters.Add("2", OdbcType.NVarChar,50).Value= TxtContra.Text;
                    int dv = cnn.ExecuteNonQuery();
                if (rows == 1)
                {
                    if (dv == 1)
                    {
                        lblMensaje.Text = "Datos añadidos correctamente";
                        Grilla.DataBind();
                        Limpiar();
                        return;
                    }
                }
                else
                {
                    lblMensaje.Text = "Error al ingresar datos";
                    Limpiar();
                    return;
                }
                }
            }

answered by 08.06.2018 в 17:53

Obtain data from a regular expression exception http.client in Python

score 1 · Accepted Answer

Hi, I think your problem is the way you declare the parameters

if you use @parametro visual studio interprets it as if it were a parameter using System.Data.SqlClient however in your code you see that you use ODBC

The correct way to declare the paramatros should be:

Ex:

Select columna FROM tabla WHERE parametro = ?

The sign? would represent your parameter and in the example that I gave corresponds to the parameter with ID 1

So, when you declare it in your code:

OdbcCommand.Parameters.Add("1", data);

If there are more parameters are defined according to the order you have in your sql query, correlatively (1, 2, 3, etc.)

for more information you can see this link: assign parameters to queries

Greetings.

For a stored procedure:

cmd = new OdbcCommand("{call MiProcedimiento(?)}", OdbcConnection);
cmd.parameters.add("@parameter", OdbcType.Char, 5);
cmd.Value = "TuValor";

Edit:

According to your code, the "Insert User ()" procedure receives three parameters and not one, so you should modify {call InsertarUsuario(?)} by

{call InsertarUsuario(?, ?, ?)}

and pass the three parameters with the names you defined in your procedure, the same for the procedure User who receives two parameters for what should be:

{call Usuario(?, ?)}

for the parameters you could leave it like this (User)

cmd.Parameters.Add(new OdbcParameter("@id2", "tu dato"));
cmd.Parameters.Add(new OdbcParameter("@pass", "tu dato"));

InsertUser

 cmd.Parameters.Add(new OdbcParameter("@id", "tu dato"));
 cmd.Parameters.Add(new OdbcParameter("@nombre", "tu dato"));
 cmd.Parameters.Add(new OdbcParameter("@memo", "tu dato"));

Verify that the data types match the data types you define in your stored procedure.

Finally I think that these lines are ill defined or simply should not go:

OdbcParameter pam = new OdbcParameter("id", OdbcType.Int);
pam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(pam);

the same with this:

OdbcParameter pa = new OdbcParameter("id2", OdbcType.Int);
pam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(pa);

try removing that line and only leave the parameters that correspond to the parameters you defined in your procedures.

The error System.InvalidCastException may be because they try to convert different types of data, so check that the data you are sending in your code is correct and that they are not void.