Click here to Skip to main content
15,868,019 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi There,

The Code below works good and stepped into the code also and seems to be working ok and once everything is done it does not want to add the records to the DB, can someone let me know if there is any problem here, below is the code for reference.

VB
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

        Conn = New SqlConnection("Server = db02; Initial Catalog=PRD; User=xx; Password=xxxxx" & "Data Source=Dynamics;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.Parameters.AddWithValue("@I_vITEMNMBR", VarItemNumber)
                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("@VarVendor", VarVendor)
                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", 0)
                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


Below is the Stored Procedure that I am using to insert the records in the db, let me know if any ideas there as to why it is not inserting records into the db. Answer highly appreciated.

SQL
GO
/****** Object:  StoredProcedure [dbo].[taUpdateCreateItemRcd]    Script Date: 05/02/2013 15:44:03 ******/
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)
Posted
Updated 2-May-13 12:47pm
v2
Comments
DinoRondelly 1-May-13 18:36pm    
Where is the stored procedure?
FARMAN786 2-May-13 18:49pm    
I have updated the stored procedure for you to take a look, please check and let me know, highly appreciate it.
FARMAN786 1-May-13 18:39pm    
The Stored Procedure is in the DB, it adds one time though but after that it does not add
[no name] 1-May-13 19:15pm    
Then you need to debug the stored procedure.
FARMAN786 2-May-13 18:49pm    
I have updated the stored procedure for you to take a look, please check and let me know, highly appreciate it.

1 solution

Your stored procedure takes in a bunch of parameters, but doesn't actually do anything with them... you need an insert or update statement in there!!
 
Share this answer
 
Comments
FARMAN786 3-May-13 14:23pm    
So the Insert statement would go after my ExecuteNonQuery Statement then
myCommand.ExecuteNonQuery()
insert into IV00101 Values(@I_vITEMNMBR, @I_vITMSHNAM, @I_vITMGEDSC)

All the help highly appreciated.

Thanks a lot
_Damian S_ 5-May-13 19:59pm    
No, the insert statement goes inside your Stored Procedure. That's why you are passing all the parameters in...
FARMAN786 6-May-13 14:27pm    
but the Stored Proc that I am using is locked, cannot modify it, is there any other way that I could do it then, appreciate all the help

Thanks,
_Damian S_ 6-May-13 20:07pm    
No. Your stored procedure is wrong. Your only other option would be to get a new stored procedure created (preferable), or create a string representation of your SQL and execute that (bad idea due to leaving you open to a SQL injection attack).
FARMAN786 6-May-13 20:25pm    
I mean I cannot change the stored proc but if I have to use the Insert statement how and where would be the good idea to do this.

Thanks a lot

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900