none
No se ejecuta mi store procedure

    Question

  • Hola

    Estoy diseñando una pagina de asp con visual basic con una rutina en la cual intento ejecutar un store procedure. EL error es que al momento de hacer el llamado al store procedure me indica que los datos se han agregado sin embargo este no se refleja en la base de datos. Ya ejecute el store procedures desde sql server y no tiene problema alguno. Les envio mi codigo.

    My store procedure:

    ALTER PROCEDURE [dbo].[CUSTADD]
    @Customer nvarchar(50)
    AS 
    BEGIN
    INSERT INTO [dbo].[TBL_Customer]
    ([Customer])
    VALUES
    (@Customer)
    SET IDENTITY_INSERT dbo.TBL_Customer ON
    END
    BEGIN
    SELECT * FROM TBL_Customer
    ORDER BY Cust_Id
    END

    My Rutina de ejecicion:

    Protected Sub btnSndCust_Click(sender As Object, e As EventArgs) Handles btnSndCust.Click
            Dim AddSql As String
            Try
                'Check if the new customer values not have empty or have a mismatch value
                If txtNewCust.Text = Nothing Then
                    txtCustRes.Text = "The field CUSTOMER is empty please add a value"
                    Label1.Visible = False
                    txtNewCust.Visible = False
                    btnSndCust.Visible = False
                    Exit Sub
                Else
                    'search if the customer exists
                    AddSql = "SELECT * FROM TBL_Customer WHERE Customer = '" & txtNewCust.Text.ToUpper & "'"
                    sqlcmd = New SqlCommand(AddSql)
                    sqlcmd.Connection = sqlconn
                    sqlcmd.CommandType = CommandType.Text
                    sqladap = New SqlDataAdapter(sqlcmd)
                    sqladap.SelectCommand.CommandTimeout = 300
                    sqlds = New DataSet
                    'fill the dataset
                    sqladap.Fill(sqlds)
                    'close connection
                    sqlconn.Close()
                    'Check if customer exists on the dataset
                    For Each dr As DataRow In sqlds.Tables(0).Rows
                        'if customer is found
                        If dr("Customer").Equals(txtNewCust.Text.ToUpper) Then
                            'confirm the record founded and exit process
                            txtCustRes.Text = "The customer is found, please add a new customer"
                            Label1.Visible = False
                            txtNewCust.Visible = False
                            btnSndCust.Visible = False
                            Exit Sub
                        End If
                    Next
                    'If not exist add the new customer
                    Call NewCust()
                End If
            Catch ex As Exception
                'for errors
                txtCustRes.Visible = True
                txtCustRes.Text = "Error : " & ex.Message & "-" & ex.Source
            Finally
                sqlconn.Close()
            End Try
        End Sub
        Private Sub NewCust()
            Try
                'Call the stored procedure
                sqlcmd = New SqlCommand("CUSTADD")
                sqlcmd.Connection = sqlconn
                sqlcmd.CommandType = CommandType.StoredProcedure
                sqlcmd.Parameters.Add("@Customer", SqlDbType.NVarChar).Value = txtNewCust.Text.ToString
                sqladap = New SqlDataAdapter(sqlcmd)
                sqladap.SelectCommand.CommandTimeout = 300
                sqlconn.Close()
                'confirm the new customer and reload the gridview
                txtCustRes.Visible = True
                txtCustRes.Text = "New Customer data created"
                'Reload gridview
                Call loadCust()
            Catch ex As Exception
                'for errors
                txtCustRes.Visible = True
                txtCustRes.Text = "Error : " & ex.Message & "-" & ex.Source
            Finally
                sqlconn.Close()
            End Try
        End Sub
    Saludos y Gracias
    Tuesday, May 21, 2013 6:02 PM

Answers

  • hola

    pero ese procedure hace un insert, deberias usar

    SqlCommand sqlcmd = New SqlCommand("CUSTADD", sqlconn)
    sqlcmd.CommandType = CommandType.StoredProcedure
    sqlcmd.Parameters.Add("@Customer", SqlDbType.NVarChar).Value = txtNewCust.Text

    sqlcmd.ExecuteNonQuery()

    si la idea es tomar el id generado y defines el campo de tu tabla como identity (autonumerico) se utiliza el SCOPE_IDENTITY

     [ADO.NET] – Parte 6 - Ejemplos simples – Campos identity

    en el articulo explico como podrias recuperar el id generado usando el ExecuteScalar()

    saludos


    Leandro Tuttini

    Blog
    Buenos Aires
    Argentina

    • Marked as answer by FcoyClau Tuesday, May 21, 2013 7:27 PM
    Tuesday, May 21, 2013 6:16 PM

All replies

  • hola

    pero ese procedure hace un insert, deberias usar

    SqlCommand sqlcmd = New SqlCommand("CUSTADD", sqlconn)
    sqlcmd.CommandType = CommandType.StoredProcedure
    sqlcmd.Parameters.Add("@Customer", SqlDbType.NVarChar).Value = txtNewCust.Text

    sqlcmd.ExecuteNonQuery()

    si la idea es tomar el id generado y defines el campo de tu tabla como identity (autonumerico) se utiliza el SCOPE_IDENTITY

     [ADO.NET] – Parte 6 - Ejemplos simples – Campos identity

    en el articulo explico como podrias recuperar el id generado usando el ExecuteScalar()

    saludos


    Leandro Tuttini

    Blog
    Buenos Aires
    Argentina

    • Marked as answer by FcoyClau Tuesday, May 21, 2013 7:27 PM
    Tuesday, May 21, 2013 6:16 PM
  • Listo

    Quedo resuelto muchas gracias

    Tuesday, May 21, 2013 7:27 PM