none
VB.NET Insert rows from DataGridView To DB "Could not find Stored Procedure Insert into IV00101" RRS feed

  • Question

  • Hi There,

    I am trying to insert rows in the DB and cannot, please let me know it is throwing an exception "Could not find Stored Procedure Insert into IV00101"

     Private Sub BtnSendToGP_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSendToGP.Click
            Dim VarItemNumber As String
            Dim VarItemDescription As String
            Dim VarUnitOfMeasure As String
            Dim VarItemClass As String
            Dim VarCurrentCost As String
            Dim Conn As SqlConnection
            Dim rowsAffected As Integer


            Conn = New SqlConnection("Server = gpdb02; Initial Catalog=PRD; User=xx; Password=xxx1" & "Data Source=DynamicsGP;Integrated Security=SSPI;")
            'Dim myCommand As SqlCommand = New SqlCommand("dbo.taUpdateCreateItemRcd", Conn)
            'If Conn.State = ConnectionState.Open Then Conn.Close()
            'Conn.Open()

            Try
                For cn As Integer = 0 To DtGridView.RowCount - 1
                    VarItemNumber = DtGridView(0, cn).Value.ToString()
                    VarItemDescription = DtGridView(3, cn).Value.ToString()
                    VarUnitOfMeasure = DtGridView(4, cn).Value.ToString()
                    VarItemClass = DtGridView(5, cn).Value.ToString()
                    VarCurrentCost = DtGridView(6, cn).Value.ToString()

                    Dim myCommand As SqlCommand = New SqlCommand("dbo.taUpdateCreateItemRcd", Conn)
                    If Conn.State = ConnectionState.Open Then Conn.Close()
                    Conn.Open()
                    myCommand.CommandType = Data.CommandType.StoredProcedure
                    myCommand.CommandText = "Insert into IV00101 Values(@I_vITEMNMBR, @I_vITMSHNAM, @I_vITMGEDSC, @I_vUOMSCHDL, @I_vITMCLSCD, @I_vCURRCOST)"
                    myCommand.Parameters.AddWithValue("@I_vITEMNMBR", VarItemNumber)
                    myCommand.Parameters.AddWithValue("@I_vITMSHNAM", VarItemDescription)
                    myCommand.Parameters.AddWithValue("@I_vITMGEDSC", "")
                    myCommand.Parameters.AddWithValue("@I_vITMTSHID", "")
                    myCommand.Parameters.AddWithValue("@I_vITEMSHWT", 0)
                    myCommand.Parameters.AddWithValue("@I_vTCC", "")
                    myCommand.Parameters.AddWithValue("@I_vCNTRYORGN", "")
                    myCommand.Parameters.AddWithValue("@I_vPurchase_Item_Tax_Schedu", "")
                    myCommand.Parameters.AddWithValue("@I_vSTNDCOST", 0)
                    myCommand.Parameters.AddWithValue("@I_vLISTPRCE", 0)
                    myCommand.Parameters.AddWithValue("@I_vNOTETEXT", "")
                    myCommand.Parameters.AddWithValue("@I_vALTITEM1", "")
                    myCommand.Parameters.AddWithValue("@I_vALTITEM2", "")
                    myCommand.Parameters.AddWithValue("@I_vITMTRKOP", 1)
                    myCommand.Parameters.AddWithValue("@I_vLOTTYPE", "")
                    myCommand.Parameters.AddWithValue("I_vLOTEXPWARN", 0)
                    myCommand.Parameters.AddWithValue("@I_vLOTEXPWARNDAYS", 0)
                    myCommand.Parameters.AddWithValue("@I_vINCLUDEINDP", 0)
                    myCommand.Parameters.AddWithValue("@I_vMINSHELF1", 0)
                    myCommand.Parameters.AddWithValue("@I_vMINSHELF2", 0)
                    myCommand.Parameters.AddWithValue("@I_vALWBKORD", 0)
                    myCommand.Parameters.AddWithValue("@I_vWRNTYDYS", 0)
                    myCommand.Parameters.AddWithValue("@I_vABCCODE", 1)
                    myCommand.Parameters.AddWithValue("@I_vUSCATVLS_1", "")
                    myCommand.Parameters.AddWithValue("@I_vUSCATVLS_2", "")
                    myCommand.Parameters.AddWithValue("@I_vUSCATVLS_3", "")
                    myCommand.Parameters.AddWithValue("@I_vUSCATVLS_4", "")
                    myCommand.Parameters.AddWithValue("@I_vUSCATVLS_5", "")
                    myCommand.Parameters.AddWithValue("@I_vUSCATVLS_6", "")
                    myCommand.Parameters.AddWithValue("@I_vIVIVACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vIVIVOFACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vIVCOGSACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vIVSLSACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vIVSLDSACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vIVSLRNACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vIVINUSACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vIVINSVACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vIVDMGACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vIVVARACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vDPSHPACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vPURPVACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vUPPVACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vIVRETACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vASMVRACTNUMST", "")
                    myCommand.Parameters.AddWithValue("@I_vKTACCTSR", 0)
                    myCommand.Parameters.AddWithValue("@I_vPRCHSUOM", CboUOM.Text)
                    myCommand.Parameters.AddWithValue("@I_vRevalue_Inventory", 1)
                    myCommand.Parameters.AddWithValue("@I_vTolerance_Percentage", 0.0)
                    myCommand.Parameters.AddWithValue("@I_vLOCNCODE", "STC01HOLD")
                    myCommand.Parameters.AddWithValue("@I_vPriceGroup", "DEFAULT")
                    myCommand.Parameters.AddWithValue("@I_vUSRDEFND1", "")
                    myCommand.Parameters.AddWithValue("@I_vUSRDEFND2", "")
                    myCommand.Parameters.AddWithValue("@I_vUSRDEFND3", "")
                    myCommand.Parameters.AddWithValue("@I_vUSRDEFND4", "")
                    myCommand.Parameters.AddWithValue("@I_vUSRDEFND5", "")
                    myCommand.Parameters.AddWithValue("@I_vITEMTYPE", 1)
                    myCommand.Parameters.AddWithValue("@I_vVCTNMTHD", 1)
                    myCommand.Parameters.AddWithValue("@I_vTAXOPTNS", 2)
                    myCommand.Parameters.AddWithValue("@I_vDECPLQTY", 1)
                    myCommand.Parameters.AddWithValue("@I_vDECPLCUR", 3)
                    myCommand.Parameters.AddWithValue("@I_vPurchase_Tax_Options", 2)
                    myCommand.Parameters.AddWithValue("@I_vKPCALHST", 1)
                    myCommand.Parameters.AddWithValue("@I_vKPERHIST", 1)
                    myCommand.Parameters.AddWithValue("@I_vKPTRXHST", 1)
                    myCommand.Parameters.AddWithValue("@I_vKPDSTHST", 1)
                    myCommand.Parameters.AddWithValue("@I_vPRICMTHD", 1)
                    myCommand.Parameters.AddWithValue("@I_vUseItemClass", 1)
                    myCommand.Parameters.AddWithValue("@I_vUpdateIfExists", 0)
                    myCommand.Parameters.AddWithValue("@I_vRequesterTrx", 0)
                    myCommand.Parameters.AddWithValue("@I_vITEMDESC", VarItemDescription)
                    myCommand.Parameters.AddWithValue("@I_vUOMSCHDL", VarUnitOfMeasure)
                    myCommand.Parameters.AddWithValue("@I_vITMCLSCD", VarItemClass)
                    myCommand.Parameters.AddWithValue("@I_vCURRCOST", VarCurrentCost)
                    myCommand.Parameters.AddWithValue("@O_iErrorState", 0)
                    myCommand.Parameters.AddWithValue("@oErrString", "")
                    myCommand.ExecuteNonQuery()
                    rowsAffected = myCommand.ExecuteNonQuery()
                Next
            Catch ex As Exception
                MsgBox(ex.ToString)
            Finally
                If Conn.State = ConnectionState.Open Then
                    Conn.Close()
                End If
            End Try
            MsgBox("All The Records Added To GP Successfully...", MsgBoxStyle.Information)
        End Sub


    Farquest

    • Moved by Ego Jiang Monday, May 6, 2013 9:40 AM
    Friday, May 3, 2013 9:25 PM

All replies

  • Hi,

    Based on this case related to Data, I am moving this case to Data forum for further support.

    Best regards,


    Ego [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, May 6, 2013 9:37 AM
  • You need to provide the name of your stored procedure in the CommandText, instead of a SQL statement:

    myCommand.CommandText = "storedprocedurename"

    or change the CommandType to Text:

    myCommand.CommandType = Data.CommandType.Text


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, May 6, 2013 3:37 PM
  • Hi Paul,

    I do not get any error now except that it displays the message box that Record added to the Database but still cannot see that record in the database.

    Appreciate it.

    Also This is the Stored Procedure

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
     ALTER procedure [dbo].[taUpdateCreateItemRcd]  @I_vITEMNMBR char(30),    @I_vITEMDESC char(100),    @I_vITMSHNAM char(15),          @I_vITMGEDSC char(10),    @I_vITMCLSCD char(10),    @I_vITEMTYPE smallint,     @I_vVCTNMTHD smallint,    @I_vTAXOPTNS smallint,    @I_vITMTSHID char(15),    @I_vUOMSCHDL char(10),    @I_vITEMSHWT numeric(8,2),   @I_vTCC char(30),     @I_vCNTRYORGN char(6),    @I_vDECPLQTY smallint,    @I_vDECPLCUR smallint,    @I_vPurchase_Tax_Options smallint, @I_vPurchase_Item_Tax_Schedu char(15), @I_vSTNDCOST numeric(19,5),   @I_vCURRCOST numeric(19,5),   @I_vLISTPRCE numeric(19,5),   @I_vNOTETEXT varchar(8000),   @I_vALTITEM1 char(30),    @I_vALTITEM2 char(30),    @I_vITMTRKOP smallint,    @I_vLOTTYPE char(10),    @I_vLOTEXPWARN tinyint,    @I_vLOTEXPWARNDAYS smallint,  @I_vINCLUDEINDP tinyint,   @I_vMINSHELF1 smallint,    @I_vMINSHELF2 smallint,    @I_vALWBKORD tinyint,    @I_vWRNTYDYS smallint,    @I_vABCCODE smallint,    @I_vUSCATVLS_1 char(10),   @I_vUSCATVLS_2 char(10),   @I_vUSCATVLS_3 char(10),   @I_vUSCATVLS_4 char(10),   @I_vUSCATVLS_5 char(10),   @I_vUSCATVLS_6 char(10),   @I_vKPCALHST tinyint,    @I_vKPERHIST tinyint,    @I_vKPTRXHST tinyint,    @I_vKPDSTHST tinyint,    @I_vIVIVACTNUMST varchar(75),  @I_vIVIVOFACTNUMST varchar(75),  @I_vIVCOGSACTNUMST varchar(75),  @I_vIVSLSACTNUMST varchar(75),  @I_vIVSLDSACTNUMST varchar(75),  @I_vIVSLRNACTNUMST varchar(75),  @I_vIVINUSACTNUMST varchar(75),  @I_vIVINSVACTNUMST varchar(75),  @I_vIVDMGACTNUMST varchar(75),  @I_vIVVARACTNUMST varchar(75),  @I_vDPSHPACTNUMST varchar(75),  @I_vPURPVACTNUMST varchar(75),  @I_vUPPVACTNUMST varchar(75),  @I_vIVRETACTNUMST varchar(75),  @I_vASMVRACTNUMST varchar(75),  @I_vKTACCTSR smallint,    @I_vPRCHSUOM char(8),    @I_vRevalue_Inventory tinyint,  @I_vTolerance_Percentage numeric(19,2),  @I_vLOCNCODE char(10),    @I_vPRICMTHD smallint,    @I_vPriceGroup char(10),   @I_vUseItemClass tinyint,   @I_vUpdateIfExists tinyint,   @I_vRequesterTrx smallint,   @I_vUSRDEFND1 char(50),       @I_vUSRDEFND2 char(50),       @I_vUSRDEFND3 char(50),       @I_vUSRDEFND4 varchar(8000),  @I_vUSRDEFND5 varchar(8000),  @O_iErrorState int output, @oErrString varchar(255) output  as  set nocount on  select @O_iErrorState = 0  return (@O_iErrorState)  

    Thanks,


    Farquest


    • Edited by Farquest Monday, May 6, 2013 10:04 PM
    Monday, May 6, 2013 9:55 PM
  • Hello

    The reason of not showing any error and no record insertion/updating is,

    Your Stored Procedure does not have any DB instruction i.e. insert/update statement.

    Below few links will help you to create a complete stored procedure and calling it from ado.net

    http://www.c-sharpcorner.com/UploadFile/718fc8/saving-record-using-stored-procedure-in-ado-net/

    http://www.aspnettutorials.com/tutorials/database/storedprocedure-vb.aspx

    http://www.aspsnippets.com/Articles/Calling-Insert-SQL-Server-Stored-Procedures-using-ADO.Net.aspx

    http://www.dreamincode.net/forums/topic/300487-creating-sql-server-stored-procedures-and-using-them-in-vbnet/


    Lingaraj Mishra

    Tuesday, May 7, 2013 6:14 AM
  • What happened to the INSERT statement? Your stored procedure looks like Transact SQL DML/DDL.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, May 7, 2013 7:20 PM
  • Will I be able to create the Stored Proc like this and then Insert or it has to be just plain insert, please let me know, all the knowledge appreciated. Thanks,

    CREATE PROCEDURE [dbo].[SD_InsertInventoryItem]
     -- Add the parameters for the stored procedure here
     (
     @ITEMNMBR char(30),   
     @ITEMDESC char(100),   
     @ITMSHNAM char(15),         
     @ITMGEDSC char(10),   
     @ITMCLSCD char(10),   
     @ITEMTYPE smallint,    
     @VCTNMTHD smallint,   
     @TAXOPTNS smallint,   
     @ITMTSHID char(15),   
     @UOMSCHDL char(10),   
     @ITEMSHWT numeric(8,2),  
     @TCC char(30)
     @iErrorState int output,
     @ErrString varchar(255) output    

     )

    AS
    BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;

        -- Insert the Transfer Line Item
        EXECUTE dbo.taUpdateCreateItemRcd
     @I_vITEMNMBR = @ITEMNMBR,   
     @I_vITEMDESC = @ITEMDESC,   
     @I_vITMSHNAM = @ITMSHNAM,         
     @I_vITMGEDSC = @ITMGEDSC,   
     @I_vITMCLSCD = @ITMCLSCD,   
     @I_vITEMTYPE = @ITEMTYPE,    
     @I_vVCTNMTHD = @VCTNMTHD,   
     @I_vTAXOPTNS = @TAXOPTNS,   
     @I_vITMTSHID = @ITMTSHID,   
     @I_vUOMSCHDL = @UOMSCHDL,   
     @I_vITEMSHWT = @ITEMSHWT,  
     @I_vTCC = @TCC,    
     @O_iErrorState = @iErrorState,
     @oErrString = @ErrString
        )


    Farquest

    Tuesday, May 7, 2013 10:45 PM
  • I changed it, please see if this will help

    CREATE PROCEDURE [dbo].[SD_InsertInventoryItem]
    (
    @I_vITEMNMBR char(30),   
     @I_vITEMDESC char(100),   
     @I_vITMSHNAM char(15),         
     @I_vITMGEDSC char(10),   
     @I_vITMCLSCD char(10),   
     @I_vITEMTYPE smallint,    
     @I_vVCTNMTHD smallint,   
     @I_vTAXOPTNS smallint,   
     @I_vITMTSHID char(15),   
     @I_vUOMSCHDL char(10),   
     @I_vITEMSHWT numeric(8,2),  
     @I_vTCC char(30),   
     @O_iErrorState int output, /* Return value: 0 = No Errors, Any Errors > 0 */  
     @oErrString varchar(255) output /* Return Error Code List */  
    )
    AS     
    declare         
    @O_oErrorState int,    
    @iError int,      
    @iAddCodeErrState int

    /*********************** Initialize locals *****************************************************/
    select   
    @O_iErrorState = 0,    
    @oErrString = '',        
    @iAddCodeErrState = 0

    INSERT INTO [IV00101]           
    (
     [ITEMNMBR],   
     [ITEMDESC],   
     [ITMSHNAM],         
     [ITMGEDSC],   
     [ITMCLSCD],   
     [ITEMTYPE],    
     [VCTNMTHD],   
     [TAXOPTNS],   
     [ITMTSHID],   
     [UOMSCHDL],   
     [ITEMSHWT],  
     [TCC]         
     )        
     VALUES
     (
     @I_vITEMNMBR,   
     @I_vITEMDESC,   
     @I_vITMSHNAM,         
     @I_vITMGEDSC,   
     @I_vITMCLSCD,   
     @I_vITEMTYPE,    
     @I_vVCTNMTHD,   
     @I_vTAXOPTNS,   
     @I_vITMTSHID,   
     @I_vUOMSCHDL,   
     @I_vITEMSHWT,  
     @I_vTCC                                           
     )   return (@O_iErrorState)


    Farquest

    Tuesday, May 7, 2013 11:40 PM