Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Visual-Studio VB.NET
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.
 
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.
 
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 1-May-13 13:32pm
Edited 2-May-13 13:47pm
v2
Comments
DinoRondelly at 1-May-13 18:36pm
   
Where is the stored procedure?
FARMAN786 at 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 at 1-May-13 18:39pm
   
The Stored Procedure is in the DB, it adds one time though but after that it does not add
ThePhantomUpvoter at 1-May-13 19:15pm
   
Then you need to debug the stored procedure.
FARMAN786 at 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.
Bernhard Hiller at 2-May-13 3:23am
   
ExecuteNonQuery returns the number of rows affected. Check that return value.
FARMAN786 at 2-May-13 13:55pm
   
hi there sorry, new to this but what way would i find out what is the result that is returned by mycommand.executenonquery
FARMAN786 at 2-May-13 14:06pm
   
HI There,
 
Sorry I was able to get the value for the myCommand.ExecuteNonQuery and it is returning -1, appreciate all your help, how to take care of this
_Damian S_ at 6-May-13 20:06pm
   
You have the following in your SP:
 
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)
 
Just before the last part where it says select @O_iErrorState = 0 return is where you need to put an insert statement like this:
 
insert into TABLENAME(FIELD1, FIELD2, ... , FIELDLAST) values (@PARAM1, @PARAM2, ... , @PARAMLAST)

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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!!
  Permalink  
Comments
FARMAN786 at 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_ at 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 at 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_ at 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 at 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
_Damian S_ at 6-May-13 20:27pm
   
As I said above, either in a new SP, or create the SQL in a variable and execute that rather than a SP.
FARMAN786 at 7-May-13 18:44pm
   
Will I be able to do this in the Stored Proc and Insert, Sorry to paste it here
 
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
)
_Damian S_ at 7-May-13 19:53pm
   
No. You cannot use the stored procedure taUpdateCreateItemRcd to do an insert, as there is no insert code in it.
 
You could, however, complete your new SP SD_InsertInventoryItem by including an insert statement where you have execute dbo.taUpdateCreateItemRcd.
FARMAN786 at 8-May-13 18:18pm
   
Hi There I changed my Stored Procedure and I think it inserted a couple of records into the db and then again it stopped, it gives me a message that it did but no record, I am pasting my Stored Proc here sorry
 
USE [SDPRD]
GO
/****** Object: StoredProcedure [dbo].[taUpdateCreateItemRcdPost] Script Date: 05/08/2013 10:58:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[taUpdateCreateItemRcdPost]

@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_vALTITEM1 char(30),
@I_vALTITEM2 char(30),
@I_vITMTRKOP 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_vPURPVACTNUMST varchar(75),
@I_vIVRETACTNUMST 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,
@O_iErrorState int output,
@oErrString varchar(255) output
as set nocount on select @O_iErrorState = 0 return (@O_iErrorState)

 
/*********************** Initialize locals *****************************************************/
select
@O_iErrorState = 0,
@oErrString = ''
 
INSERT INTO [IV00101]
(
[ITEMNMBR],
[ITEMDESC],
[ITMSHNAM],
[ITMGEDSC],
[ITMCLSCD],
[ITEMTYPE],
[VCTNMTHD],
[TAXOPTNS],
[ITMTSHID],
[UOMSCHDL],
[ITEMSHWT],
[TCC],
[CNTRYORGN],
[DECPLQTY],
[DECPLCUR],
[Purchase_Tax_Options],
[Purchase_Item_Tax_Schedu],
[STNDCOST],
[CURRCOST],
[ALTITEM1],
[ALTITEM2],
[ITMTRKOP],
[INCLUDEINDP],
[MINSHELF1],
[MINSHELF2],
[ALWBKORD],
[WRNTYDYS],
[ABCCODE],
[USCATVLS_1],
[USCATVLS_2],
[USCATVLS_3],
[USCATVLS_4],
[USCATVLS_5],
[USCATVLS_6],
[KPCALHST],
[KPDSTHST],
[KPERHIST],
[KPTRXHST],
[IVIVINDX],
[IVIVOFIX],
[IVCOGSIX],
[IVSLSIDX],
[IVSLDSIX],
[IVSLRNIX],
[IVINUSIX],
[IVINSVIX],
[IVDMGIDX],
[IVVARIDX],
[PURPVIDX],
[IVRETIDX],
[KTACCTSR],
[PRCHSUOM],
[Revalue_Inventory],
[Tolerance_Percentage],
[LOCNCODE],
[PRICMTHD],
[PriceGroup]
)
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,
@I_vCNTRYORGN,
@I_vDECPLQTY,
@I_vDECPLCUR,
@I_vPurchase_Tax_Options,
@I_vPurchase_Item_Tax_Schedu,
@I_vSTNDCOST,
@I_vCURRCOST,
@I_vALTITEM1,
@I_vALTITEM2,
@I_vITMTRKOP,
@I_vINCLUDEINDP,
@I_vMINSHELF1,
@I_vMINSHELF2,
@I_vALWBKORD,
@I_vWRNTYDYS,
@I_vABCCODE,
@I_vUSCATVLS_1,
@I_vUSCATVLS_2,
@I_vUSCATVLS_3,
@I_vUSCATVLS_4,
_Damian S_ at 8-May-13 19:50pm
   
It looks like some of your SP is missing, but that's alright, if you say it added a couple of records then stopped, it's obviously working. For the ones where it didn't work, was there something different? A NULL where a value is expected? A foreign key that isn't right? Data integrity faults? Trying to insert a duplicate into a primary key field? These are common things that will cause your SP to fail sometimes...
FARMAN786 at 9-May-13 14:25pm
   
Actually I tried one example with the existing record in the db and tried to re insert it but gave me the message which is right and after that I completely enter a new sku ans tried to insert it, again it gives me the message Duplicate key and then it inserts into a table. which is weird.

All help appreciated

Thanks a lot
_Damian S_ at 9-May-13 19:45pm
   
Well of course you can't insert the same record twice - and the error message gives you the answer... Is one of the fields you are trying to insert the Primary Key? (Or several fields together forming a composite primary key.) If so, you will need to use different test data each time.

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

  Print Answers RSS
0 OriginalGriff 325
1 Sergey Alexandrovich Kryukov 289
2 CPallini 275
3 DamithSL 260
4 Maciej Los 215
0 OriginalGriff 5,455
1 DamithSL 4,422
2 Maciej Los 3,860
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,010


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 3 May 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100