Click here to Skip to main content
13,589,266 members

Comments by FARMAN786 (Top 21 by date)

FARMAN786 9-May-13 14:25pm View
   
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
FARMAN786 8-May-13 18:18pm View
   
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,
FARMAN786 7-May-13 18:44pm View
   
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
)
FARMAN786 6-May-13 20:25pm View
   
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
FARMAN786 6-May-13 14:27pm View
   
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,
FARMAN786 3-May-13 14:23pm View
   
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
FARMAN786 2-May-13 18:49pm View
   
I have updated the stored procedure for you to take a look, please check and let me know, highly appreciate it.
FARMAN786 2-May-13 18:49pm View
   
I have updated the stored procedure for you to take a look, please check and let me know, highly appreciate it.
FARMAN786 2-May-13 14:06pm View
   
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
FARMAN786 2-May-13 13:55pm View
   
hi there sorry, new to this but what way would i find out what is the result that is returned by mycommand.executenonquery
FARMAN786 1-May-13 18:39pm View
   
The Stored Procedure is in the DB, it adds one time though but after that it does not add
FARMAN786 30-Apr-13 19:49pm View
   
Sorry Did not read your comment, next time will put the code in Improve question
FARMAN786 30-Apr-13 19:14pm View
   
Deleted
Hi Sergey,

I think I found the problem, looks like when I am inserting the data from my text box to the grid it is creating a blank row at the end and that is causing this error to happen, I am pasting the code below can you let me know as to why it is creating that extra row.

Appreciate all the help,

Private Sub BtnAddRows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAddRows.Click
DtGridView.ColumnCount = 7
DtGridView.ColumnHeadersVisible = True

' Set the column header style.
Dim columnHeaderStyle As New DataGridViewCellStyle()

columnHeaderStyle.BackColor = Color.Beige
columnHeaderStyle.Font = New Font("Verdana", 7, FontStyle.Bold)
columnHeaderStyle.WrapMode = DataGridViewTriState.True
DtGridView.ColumnHeadersDefaultCellStyle = columnHeaderStyle


' Set the column header names.
DtGridView.Columns(0).Name = "Item Number"
DtGridView.Columns(1).Name = "Vendor"
DtGridView.Columns(2).Name = "Size"
DtGridView.Columns(3).Name = "Item Description"
DtGridView.Columns(4).Name = "Unit Of Measure"
DtGridView.Columns(5).Name = "Item Class"
DtGridView.Columns(6).Name = "Current Cost"

If CboSize.Text = "Shoes" Then
DtGridView.ClearSelection()
Dim rowNum As Integer = DtGridView.Rows.Add()
Dim Str As String
Dim VendID As String
Str = cboVendor.Text
VendID = Str.Substring(0, 5)


DtGridView.Rows.Item(rowNum).Cells(0).Value = "213-000009-0201-0551"
DtGridView.Rows.Item(rowNum).Cells(1).Value = "ACC21"
DtGridView.Rows.Item(rowNum).Cells(2).Value = CboSize.Text
DtGridView.Rows.Item(rowNum).Cells(3).Value = TxtItmDesc.Text
DtGridView.Rows.Item(rowNum).Cells(4).Value = CboUOM.Text
DtGridView.Rows.Item(rowNum).Cells(5).Value = CboItmClass.Text
DtGridView.Rows.Item(rowNum).Cells(6).Value = TxtCurrCost.Text

Dim rownum1 As Integer = DtGridView.Rows.Add()
DtGridView.Rows.Item(rownum1).Cells(0).Value = "213-000009-0201-0601"
DtGridView.Rows.Item(rownum1).Cells(1).Value = "ACC21"
DtGridView.Rows.Item(rownum1).Cells(2).Value = CboSize.Text
DtGridView.Rows.Item(rownum1).Cells(3).Value = TxtItmDesc.Text
DtGridView.Rows.Item(rownum1).Cells(4).Value = CboUOM.Text
DtGridView.Rows.Item(rownum1).Cells(5).Value = CboItmClass.Text
DtGridView.Rows.Item(rownum1).Cells(6).Value = TxtCurrCost.Text

Dim rownum2 As Integer = DtGridView.Rows.Add()
DtGridView.Rows.Item(rownum2).Cells(0).Value = "213-000009-0201-0651"
DtGridView.Rows.Item(rownum2).Cells(1).Value = "ACC21"
DtGridView.Rows.Item(rownum2).Cells(2).Value = CboSize.Text
DtGridView.Rows.Item(rownum2).Cells(3).Value = TxtItmDesc.Text
DtGridView.Rows.Item(rownum2).Cells(4).Value = CboUOM.Text
DtGridView.Rows.Item(rownum2).Cells(5).Value = CboItmClass.Text
DtGridView.Rows.Item(rownum2).Cells(6).Value = TxtCurrCost.Text

Dim rownum3 As Integer = DtGridView.Rows.Add()
DtGridView.Rows.Item(rownum3).Cells(0).Value = "213-000009-0201-0701"
DtGridView.Rows.Item(rownum3).Cells(1).Value = "ACC21"
DtGridView.Rows.Item(rownum3).Cells(2).Value = CboSize.Text
DtGridView.Rows.Item(rownum3).Cells(3).Value = TxtItmDesc.Text
DtGridView.Rows.Item(rownum3).Cells(4).Value = CboUOM.Text
DtGridView.Rows.Item(rownum3).Cells(5).Value = CboItmClass.Text
DtGridView.Rows.Item(rownum3).Cells(6).Value = TxtCurrCost.Text


Else
DtGridView.Refresh()
MsgBox("yet To Code")
End If
FARMAN786 30-Apr-13 18:11pm View
   
Thanks Sergey, I am new to this but in my code how and where do I check for null and throw an error, appreciate it.

Thanks,
FARMAN786 30-Apr-13 17:19pm View
   
Thanks, I will check that but is there any other mthod to code the above line in order to grab the data from the grid and feed it to the stored proc
FARMAN786 30-Apr-13 15:06pm View
   
how do i debug a .dll, any ideas.

Thanks,
FARMAN786 29-Apr-13 15:03pm View
   
Hi There,

I went further ahead and was able to get the results but not fully. I am unable to insert all the rows from the Grid to the database, except one and also it shows me an error "The Connection was not closed. The Connection's current state is open", please see the code below and let me know how it can be corrected, all the help appreciated.

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

VarItemNumber = Nothing
VarVendor = Nothing
VarItemDescription = Nothing
VarUnitOfMeasure = Nothing
VarItemClass = Nothing


Conn = New SqlConnection("Server = gpdb02; Initial Catalog=XXX; User=XX; Password=XXXXXXX" & "Data Source=Dynamics;Integrated Security=SSPI;")

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

Dim myCommand As SqlCommand = New SqlCommand("dbo.taUpdateCreateItemRcd", Conn)
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_vPriceGroup", "DEFAULT".ToString)
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("@O_iErrorState", 0)
myCommand.Parameters.AddWithValue("@oErrString", 0)
myCommand.ExecuteNonQuery().ToString()
Next
Conn.Close()
MsgBox("All The Records Added To GP Successfully", MsgBoxStyle.Information)

End Sub
FARMAN786 26-Apr-13 14:20pm View
   
Any Example code will be highly appreciated.
FARMAN786 26-Apr-13 13:19pm View
   
Currently this is what I am doing in order to store the text box values to Datagrid. Do i need to define a datatable and then re do this or what.Please let me know.

<pre lang="vb">Private Sub BtnAddRows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAddRows.Click
DtGridView.ColumnCount = 7
DtGridView.ColumnHeadersVisible = True

' Set the column header style.
Dim columnHeaderStyle As New DataGridViewCellStyle()

columnHeaderStyle.BackColor = Color.Beige
columnHeaderStyle.Font = New Font("Verdana", 7, FontStyle.Bold)
columnHeaderStyle.WrapMode = DataGridViewTriState.True
DtGridView.ColumnHeadersDefaultCellStyle = columnHeaderStyle

' Set the column header names.
DtGridView.Columns(0).Name = "Item Number"
DtGridView.Columns(1).Name = "Vendor"
DtGridView.Columns(2).Name = "Size"
DtGridView.Columns(3).Name = "Item Description"
DtGridView.Columns(4).Name = "Unit Of Measure"
DtGridView.Columns(5).Name = "Item Class"
DtGridView.Columns(6).Name = "Current Cost"

If CboSize.Text = "Shoes" Then
DtGridView.ClearSelection()
Dim rowNum As Integer = DtGridView.Rows.Add()
Dim Str As String
Dim VendID As String
Str = cboVendor.Text
VendID = Str.Substring(0, 5)
DtGridView.Rows.Item(rowNum).Cells(0).Value = String.Join("-", New String() {CType(cboVendor.SelectedValue, DataRowView)("VENDORSKUCODE").ToString, Me.txtStyle.Text, CType(cboPrimaryColor.SelectedValue, DataRowView)("ColorNumber").ToString & CType(cboSecondaryColor.SelectedValue, DataRowView)("ColorNumber").ToString}) + "-" + "0551"
DtGridView.Rows.Item(rowNum).Cells(1).Value = VendID
DtGridView.Rows.Item(rowNum).Cells(2).Value = CboSize.Text
DtGridView.Rows.Item(rowNum).Cells(3).Value = TxtItmDesc.Text
DtGridView.Rows.Item(rowNum).Cells(4).Value = CboUOM.Text
DtGridView.Rows.Item(rowNum).Cells(5).Value = CboItmClass.Text
DtGridView.Rows.Item(rowNum).Cells(6).Value = TxtCurrCost.Text

Dim rownum1 As Integer = DtGridView.Rows.Add()
DtGridView.Rows.Item(rownum1).Cells(0).Value = String.Join("-", New String() {CType(cboVendor.SelectedValue, DataRowView)("VENDORSKUCODE").ToString, Me.txtStyle.Text, CType(cboPrimaryColor.SelectedValue, DataRowView)("ColorNumber").ToString & CType(cboSecondaryColor.SelectedValue, DataRowView)("ColorNumber").ToString}) + "-" + "0601"
DtGridView.Rows.Item(rownum1).Cells(1).Value = VendID
DtGridView.Rows.Item(rownum1).Cells(2).Value = CboSize.Text
DtGridView.Rows.Item(rownum1).Cells(3).Value = TxtItmDesc.Text
DtGridView.Rows.Item(rownum1).Cells(4).Value = CboUOM.Text
DtGridView.Rows.Item(rownum1).Cells(5).Value = CboItmClass.Text
DtGridView.Rows.Item(rownum1).Cells(6).Value = TxtCurrCost.Text

Dim rownum2 As Integer = DtGridView.Rows.Add()
DtGridView.Rows.Item(rownum2).Cells(0).Value = String.Join("-", New String() {CType(cboVendor.SelectedValue, DataRowView)("VENDORSKUCODE").ToString, Me.txtStyle.Text, CType(cboPrimaryColor.SelectedValue, DataRowView)("ColorNumber").ToString & CType(cboSecondaryColor.SelectedValue, DataRowView)("ColorNumber").ToString}) + "-" + "0651"
DtGridView.Rows.Item(rownum2).Cells(1).Value = VendID
DtGridView.Rows.Item(rownum2).Cells(2).Value = CboSize.Text
DtGridView.Rows.Item(rownum2).Cells(3).Value = TxtItmDesc.Text
DtGridView.Rows.Item(rownum2).Cells(4).Value = CboU
FARMAN786 25-Apr-13 17:46pm View
   
Private Sub BtnAddRows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAddRows.Click
DtGridView.ColumnCount = 7
DtGridView.ColumnHeadersVisible = True

' Set the column header style.
Dim columnHeaderStyle As New DataGridViewCellStyle()

columnHeaderStyle.BackColor = Color.Beige
columnHeaderStyle.Font = New Font("Verdana", 7, FontStyle.Bold)
columnHeaderStyle.WrapMode = DataGridViewTriState.True
DtGridView.ColumnHeadersDefaultCellStyle = columnHeaderStyle

' Set the column header names.
DtGridView.Columns(0).Name = "Item Number"
DtGridView.Columns(1).Name = "Vendor"
DtGridView.Columns(2).Name = "Size"
DtGridView.Columns(3).Name = "Item Description"
DtGridView.Columns(4).Name = "Unit Of Measure"
DtGridView.Columns(5).Name = "Item Class"
DtGridView.Columns(6).Name = "Current Cost"

' Populate the rows.
DtGridView.Rows(0).Cells(0).Value = String.Join("-", New String() {CType(cboVendor.SelectedValue, DataRowView)("VENDORSKUCODE").ToString, Me.txtStyle.Text, CType(cboPrimaryColor.SelectedValue, DataRowView)("ColorNumber").ToString & CType(cboSecondaryColor.SelectedValue, DataRowView)("ColorNumber").ToString}) + "-" + "0551"
DtGridView.Rows(0).Cells(1).Value = cboVendor.DisplayMember = "DisplayName"
DtGridView.Rows(0).Cells(2).Value = CboSize.Text
DtGridView.Rows(0).Cells(3).Value = TxtItmDesc.Text
DtGridView.Rows(0).Cells(4).Value = CboUOM.Text
DtGridView.Rows(0).Cells(5).Value = CboItmClass.Text
DtGridView.Rows(0).Cells(6).Value = TxtCurrCost.Text
FARMAN786 25-Apr-13 17:45pm View
   
Ryan,

I have submitted the code. let me know what you think is the best.

Thanks a lot

Advertise | Privacy | Cookies | Terms of Service
Web02-2016 | 2.8.180615.1 | Last Updated 1 Jan 1900
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid