Member Avatar for shankarmca 
Sub modify_inventory(ByVal frm As frminventory)
        Dim writesqltime As String
        Dim writesqldate As String
        Dim msg As String
        writesqldate = Format(CDate(sqldate), "MM/dd/yyyy")
       
        Dim cmd = New SqlClient.SqlCommand

        If active_frm.Name <> "frminventory" Then
            Exit Sub
        Else
            
            writesqldate = Format(CDate(sqldate), "MM/dd/yyyy")
            Dim j As Integer
           
            con = connect()
            con.Open()
            For j = 0 To 5
                For i = 0 To frm.dgvitem(j).RowCount - 1
                    cmd = New SqlClient.SqlCommand("INSERT INTO minventory(groupcode,itemcode,itemqty,entrydate,locationcode,addt) VALUES('" & frm.dgvitem(j).Item(0, i).Value & "','" & frm.dgvitem(j).Item(1, i).Value & "'," & frm.dgvitem(j).Item(4, i).Value & ",'" & writesqldate & "'," & location_code & ",'" & writesqltime & "'  )", con)
                    

                    cmd.ExecuteNonQuery()
                Next i
            Next j        

           con.Close()
         
        End If
    End Sub

"array loop problem"
'my problem is when i am inserting data then 1st row is inserted correctly but the 2nd row is not taking the quantity(frm.dgvitem(j).Item(4, i).Value ) field right for inserting data i am using data grid

Edited by kvprajapati because: Added [code] tags. Do wrap your programming code blocks within [code] ... [/code] tags
  • 2 Contributors
  • 2 Replies
  • 105 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by shankarmca

Recommended Answers

All 2 Replies

Member Avatar for lolafuertes

Did you refresh the data gird source after inserting into the DB?
I can't see it in this sub.

Hope this helps

Member Avatar for shankarmca

thanks "lolafuertes"
the code is running fine in farminventory page but i want to run this code in module the basic problem is that in module while running this code i am inserting data only the addquantity column is not working from second row it shows qddquantity value to( o ) . farminventory page load code is like below-:


Private Sub frminventory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con.Close()

active_frm.MdiParent = MDIpos


MDIpos.Panel1.Visible = False
MDIpos.mnumaster.Enabled = True
MDIpos.MenuItem1.Enabled = False
MDIpos.mnureport.Enabled = True
MDIpos.mnuexit.Enabled = True

MDIpos.mnumodify.Visible = True
MDIpos.mnumodify.Enabled = True

MDIpos.mnuadd.Visible = True
MDIpos.mnuadd.Enabled = True

MDIpos.Mnuclear.Visible = True
MDIpos.Mnuclear.Enabled = True

MDIpos.Mnudelete.Visible = True
MDIpos.Mnudelete.Enabled = True


MDIpos.mnuexport.Enabled = True
MDIpos.mnuimport.Enabled = True
MDIpos.mnuuser.Enabled = True
MDIpos.mnupos.Enabled = True
tab_page(0) = Me.TabPage1
tab_page(1) = Me.TabPage2
tab_page(2) = Me.TabPage3
tab_page(3) = Me.TabPage4
tab_page(4) = Me.TabPage5
tab_page(5) = Me.TabPage6
dgvitem(0) = Me.DataGridView1
dgvitem(1) = Me.DataGridView2
dgvitem(2) = Me.DataGridView3
dgvitem(3) = Me.DataGridView4
dgvitem(4) = Me.DataGridView5
dgvitem(5) = Me.DataGridView6
taballgroup.DrawMode = TabDrawMode.OwnerDrawFixed
taballgroup.SizeMode = TabSizeMode.Fixed
Dim tab_size As Size = taballgroup.ItemSize
tab_size.Width = 35
tab_size.Height = 80
taballgroup.ItemSize = tab_size
Dim writesqldate As String
Dim writesqldate1 As String
writesqldate = Format(CDate(sqldate), "MM/dd/yyyy")
'Me.lbldate.Text = writesqldate

con = connectlogin()
con.Open()
cmd = New SqlClient.SqlCommand("Select GetDate()", con)
dtServerDateTime1 = cmd.ExecuteScalar
writesqltime = Format(dtServerDateTime1, " hh:mm:ss tt")
writesqldate1 = Format(dtServerDateTime1, "dd/MM/yyyy ")
Me.lbldate.Text = writesqldate1
Me.Label1.Text = writesqltime
con.Close()
clk = writesqltime

con = connect()
con.Open()
i = 0
cmd = New SqlClient.SqlCommand("Select groupname,groupcode from mgroup where locationcode=" & location_code & " order by groupcode", con)
dr1 = cmd.ExecuteReader
While dr1.Read
Me.taballgroup.Controls(i).Text = Trim(dr1("groupname"))
a(i) = dr1("groupcode")
b(i) = Trim(dr1("groupname"))
If i < 5 Then
i = i + 1
Else
Exit While
End If
End While
dr1.Close()
con.Close()


con = connect()
con.Open()
cmd = New SqlClient.SqlCommand("DELETE FROM minventory WHERE itemqty=0 and entrydate<>'" & writesqldate & "'", con)
'cmd = New SqlClient.SqlCommand("DELETE FROM minventory WHERE NULLIF(RTRIM([itemqty]), '') IS NULL and entrydate<>'" & writesqldate & "'", con)
cmd.ExecuteNonQuery()
con.Close()

con = connect()
con.Open()
SQLda.SelectCommand = New SqlClient.SqlCommand("SELECT count(itemqty) FROM minventory where entrydate='" & writesqldate & "'", con)
qty = SQLda.SelectCommand.ExecuteScalar
con.Close()
If qty = 0 Then
Me.Btnadd.Enabled = True
Me.btnmodify.Enabled = False
For i = 0 To 5
populate_datagrid("select groupcode,itemcode,itemname ,0 as Quantity from mitem where groupcode='" & a(i) & "'order by itemname", Me.dgvitem(i))
Me.dgvitem(i).Columns(0).Width = 10
Me.dgvitem(i).Columns(0).Visible = False
Me.dgvitem(i).Columns(1).Width = 50
Me.dgvitem(i).Columns(1).Visible = False
Me.dgvitem(i).Columns(2).Width = 290
Me.dgvitem(i).Columns(3).Width = 200
Me.dgvitem(i).Columns(3).ReadOnly = False
Me.dgvitem(i).Columns(0).ReadOnly = True
Me.dgvitem(i).Columns(1).ReadOnly = True
Next
ElseIf qty > 0 Then

MDIpos.mnuadd.Enabled = False
MDIpos.mnumodify.Enabled = True
Me.Btnadd.Enabled = False
Me.btnmodify.Enabled = True

For i = 0 To 5
'populate_datagrid("select mitem.groupcode,mitem.itemcode,mitem.itemname ,totalqty as TotalQuantity,0 as Quantity from mitem,minventory where mitem.groupcode='" & a(i) & "' and mitem.itemcode=minventory.itemcode and entrydate='" & writesqldate & "' and minventory.id='" & c & "' order by itemname", Me.dgvitem(i))
populate_datagrid(" select c.GROUPCODE,c.ITEMCODE ,ITEMNAME ,TOTALQTY = t.totalqty,0 as ADDQUANTITY from MITEM c inner join (select groupcode,itemcode, totalqty= SUM(itemqty)from MINVENTORY where entrydate='" & writesqldate & "' and groupcode='" & a(i) & "' group by itemcode,groupcode) t on t.itemcode = c.itemcode and t.groupcode = c.GROUPCODE ", Me.dgvitem(i))
Me.dgvitem(i).Columns(0).Width = 110
Me.dgvitem(i).Columns(0).Visible = False
Me.dgvitem(i).Columns(1).Width = 150
Me.dgvitem(i).Columns(1).Visible = False
Me.dgvitem(i).Columns(2).Width = 267
Me.dgvitem(i).Columns(3).Width = 110
Me.dgvitem(i).Columns(3).ReadOnly = True
Me.dgvitem(i).Columns(4).Width = 110
Me.dgvitem(i).Columns(2).ReadOnly = True
Me.dgvitem(i).Columns(0).ReadOnly = True
Me.dgvitem(i).Columns(1).ReadOnly = True

Next


End If

'Dim cnt7 As Integer
'con = connect()
'con.Open()
'Dim dr7 As SqlClient.SqlDataReader
'Dim cmd7 As New SqlClient.SqlCommand
'cmd7 = New SqlClient.SqlCommand("select count(entrydate) from minventory2 WHERE entrydate='" & writesqldate & "'", con)
'dr7 = cmd7.ExecuteReader()
'While dr7.Read
' cnt7 = dr7(0)
'End While
'dr7.Close()
'con.Close()
'If cnt7 = 0 Then
' con = connect()
' con.Open()
' cmd = New SqlClient.SqlCommand("DELETE FROM minventory ", con)
' cmd.ExecuteNonQuery()
' con.Close()
'End If


'''con = connect()
'''con.Open()
'''cmd = New SqlClient.SqlCommand("UPDATE minventory2 SET totalqty = itemqty + itemqty2 + itemqty3 + itemqty4 + itemqty5 ", con)
'''cmd.ExecuteNonQuery()
'''con.Close()

'''con = connect()
'''con.Open()
'''cmd = New SqlClient.SqlCommand("UPDATE minventory2 SET balqty = totalqty - todaysell + todaysellref ", con)
'''cmd.ExecuteNonQuery()
'''con.Close()


End Sub


my datagrid is in farminventory page,my insert button is in mdimenu page,and iam inserting from module. while running the farm inventory page is open i insert data to addquantity column in farm inventory then click mdimenu insert button all data is inserted only addquantity column data from 2nd row onwards is wrong but the first row is right

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.