i want to select product from stock_registor database (access database)
in gride he show product which qty > 0 but when i want select it it through the msg
"
There is no row at position 0. index out of range ,exception was unhandeled
"
What I have tried:
Public Class SALES
Private access As New GSTDBCONTROL
Public Function NOTEMPTY(ByVal TEXT As String) As Boolean
Return Not String.IsNullOrEmpty(TEXT)
End Function
Public Function EMPTY(ByVal TEXT As String) As Boolean
Return String.IsNullOrWhiteSpace(TEXT)
End Function
Private Function noerrors(Optional ByVal report As Boolean = False) As Boolean
If Not String.IsNullOrEmpty(access.Exception) Then
If report = True Then MsgBox(access.Exception)
Return False
Else
Return True
End If
End Function
Dim currentrecord As Integer = 0
Dim qt As String = 0
Dim custcode As String = 0
Dim cust_id As String = 0
Dim x_cond As String = 0
' Public textBoxWithPlaceHolder = New Placeholder.PlaceholderTextBox()
Public SL_TYPE As Object = 0
Public CaSHMEMO_NO As String
Public CREDMEMO_NO As String
Public CHALLAN_NO As String
Public prod_type_sl As Single
Public TX_TYPE1 As Object = "0"
Dim prod_sl_code As Single = 0
Dim dmy_freegoods As String
Dim dmy_qty As String
Dim dmy_rt As String
Private Sub EXITToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EXITToolStripMenuItem.Click
MainMenu.GRPmain.Location = New Point(23, 7)
MainMenu.Show()
Me.Close()
End Sub
Private Sub MENUToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MENUToolStripMenuItem.Click
Me.Hide()
MainMenu.GRPmain.Location = New Point(23, 7)
MainMenu.Show()
End Sub
Private Sub BACKToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BACKToolStripMenuItem.Click
GRPSALES1.Visible = True
GRPdeleMENU.Visible = False
lbl_slstype.Visible = False
lbl_invno.Visible = False
lblcust.Visible = False
txt_cust.Visible = False
CMD_CUSTFIND.Visible = False
Label3.Visible = False
Label4.Visible = False
txt_custgst.Visible = False
txt_custpan.Visible = False
GRPSL1.Visible = False
lbl_stat.Visible = False
End Sub
Private Sub SALESToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SALESToolStripMenuItem.Click
GRPSALES1.Visible = False
GRPdeleMENU.Visible = True
S_tran1()
End Sub
Private Sub SALES_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
GRPSALES1.Visible = True
GRPSALES1.Size = New Size(322, 44)
GRPSALES1.Location = New Point(12, 9)
lblmyysoft.Text = access.mysoft
dgvsals_tran.Size = New Size(432, 187)
dgvsals_tran.Location = New Point(6, 81)
End Sub
Private Sub S_tran1()
access.Execquery("select * from my_code ")
If NOTEMPTY(access.Exception) Then MsgBox(access.Exception) : Exit Sub
If access.dbdt.Rows.Count < 1 OrElse currentrecord > access.dbdt.Rows.Count - 1 Then Exit Sub
Dim r As DataRow = access.dbdt.Rows(currentrecord)
txt_Sl_tranid.Text = r("id").ToString
txt_Sl_trancode.Text = r("sl_trancode").ToString
txt_SLjv_no.Text = r("jv_no").ToString
CaSHMEMO_NO = r("SL_invoice").ToString
CREDMEMO_NO = r("sl_invoice").ToString
CHALLAN_NO = r("SL_CHALLAN").ToString
If Not String.IsNullOrEmpty(txt_Sl_tranid.Text) Then txt_Sl_trancode.Text = Val(txt_Sl_trancode.Text) + 1
If Not String.IsNullOrEmpty(txt_Sl_tranid.Text) Then txt_SLjv_no.Text = Val(txt_SLjv_no.Text) + 1
If Not String.IsNullOrEmpty(txt_Sl_tranid.Text) Then CaSHMEMO_NO = Val(CaSHMEMO_NO) + 1
If Not String.IsNullOrEmpty(txt_Sl_tranid.Text) Then CREDMEMO_NO = Val(CREDMEMO_NO) + 1
If Not String.IsNullOrEmpty(txt_Sl_tranid.Text) Then CHALLAN_NO = Val(CHALLAN_NO) + 1
SLs_TYPE1()
' GETCUSTOMER()
End Sub
Private Sub SLs_TYPE1()
GRPSL_TYPE.Visible = True
GRPSL_TYPE.Size = New Size(97, 48)
GRPSL_TYPE.Location = New Point(33, 8)
End Sub
Private Sub CASHSALESToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CASHSALESToolStripMenuItem1.Click
cashsl()
End Sub
Private Sub cashsl()
GRPSL_TYPE.Visible = False
SL_TYPE = "1"
lbl_slstype.Visible = True
lbl_slstype.Text = "CASH SALES INVOICE "
lbl_invno.Text = Str(CaSHMEMO_NO)
lbl_invdt.Text = Date.Now
CMD_CUSTFIND.Visible = True
txt_cust.Visible = True
lblcust.Visible = True
Me.txt_cust.Select()
ref_cust()
End Sub
Private Sub credsl()
GRPSL_TYPE.Visible = False
SL_TYPE = "2"
lbl_slstype.Visible = True
lbl_slstype.Text = "CREDIT SALES INVOICE "
lbl_invno.Text = Str(CREDMEMO_NO)
lbl_invdt.Text = Date.Now
CMD_CUSTFIND.Visible = True
txt_cust.Visible = True
lblcust.Visible = True
Me.txt_cust.Select()
ref_cust()
End Sub
Private Sub CREDITSALEToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CREDITSALEToolStripMenuItem.Click
credsl()
End Sub
Private Sub chalsl()
GRPSL_TYPE.Visible = False
SL_TYPE = "3"
lbl_slstype.Visible = True
lbl_slstype.Text = "DELIVARY CHALLAN "
lbl_invno.Text = Str(CHALLAN_NO)
lbl_invdt.Text = Date.Now
CMD_CUSTFIND.Visible = True
txt_cust.Visible = True
lblcust.Visible = True
Me.txt_cust.Select()
ref_cust()
End Sub
Private Sub CHALLANToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CHALLANToolStripMenuItem.Click
chalsl()
End Sub
Private Sub GRPSL_TYPE_Paint(ByVal sender As Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles GRPSL_TYPE.Paint
Dim gfx = e.Graphics
Dim pen As New Pen(Brushes.Red, 0.5)
gfx.DrawLine(pen, 0, 5, 0, e.ClipRectangle.Height - 2)
gfx.DrawLine(pen, 0, 5, 10, 5)
gfx.DrawLine(pen, 2, 5, e.ClipRectangle.Width - 2, 5)
gfx.DrawLine(pen, e.ClipRectangle.Width - 2, 5, e.ClipRectangle.Width - 2, e.ClipRectangle.Height - 2)
gfx.DrawLine(pen, e.ClipRectangle.Width - 2, e.ClipRectangle.Height - 2, 0, e.ClipRectangle.Height - 2)
End Sub
Private Sub GETCUSTOMER()
End Sub
Private Sub ref_cust()
dgvcust.Size = New Size(348, 350)
dgvcust.Location = New Point(19, 65)
access.Execquery("select id,cust_name,cust_code,state,cust_stat_code,gst_no,pan_no from CUSTOMER_MASTER ORDER BY cust_name asc")
dgvcust.DataSource = access.dbdt
Me.dgvcust.ColumnHeadersDefaultCellStyle.Font = New Font(Me.dgvcust.ColumnHeadersDefaultCellStyle.Font, FontStyle.Bold)
Me.dgvcust.ColumnHeadersDefaultCellStyle.BackColor = Color.LightPink
Me.dgvcust.DefaultCellStyle.Font = New Font("Arial", 7, FontStyle.Regular)
Me.dgvcust.EnableHeadersVisualStyles = False
dgvcust.Columns(1).HeaderText = "CUSTOMER NAME"
dgvcust.Columns(2).HeaderText = "CUSTOMER CODE"
dgvcust.Columns(3).HeaderText = "STATE"
dgvcust.Columns(4).HeaderText = "GST NO"
dgvcust.Columns(5).HeaderText = "PAN "
dgvcust.Columns(0).HeaderText = "ID"
dgvcust.Refresh()
If access.recordcount > 0 Then access.recordcount = 0
End Sub
Private Sub txt_cust_Validating(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_cust.Validating
If String.IsNullOrWhiteSpace(txt_cust.Text) Then
x_cond = 1
txt_cust.Text = "-by cash "
txt_custgst.Text = "RETAIL CUSTOMER -N.A"
txt_custpan.Text = "RETAIL CUSTOMER -N.A"
TRAN1()
End If
End Sub
Private Sub txt_cust_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_cust.TextChanged
If Not String.IsNullOrEmpty(txt_cust.Text) And x_cond < 1 Then
dgvcust.Visible = True
serch_cust(txt_cust.Text)
End If
End Sub
Private Sub serch_cust(ByVal name As String)
access.addparam("@cust", "%" & name & "%")
access.Execquery("SELECT id,cust_name,cust_code,state,cust_stat_code,gst_no,pan_no " & _
"from CUSTOMER_MASTER " & _
"where cust_name like @cust")
If noerrors(True) = False Then Exit Sub
dgvcust.DataSource = access.dbdt
End Sub
Private Sub dgvcust_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvcust.CellContentClick
If e.RowIndex < 0 Or e.ColumnIndex < 0 Then Exit Sub
select_cust(dgvcust.Item(1, e.RowIndex).Value)
End Sub
Private Sub select_cust(ByVal cust_name As String)
access.addparam("@user", cust_name)
access.Execquery("SELECT id,cust_name,cust_code,state,cust_stat_code,gst_no,pan_no FROM CUSTOMER_MASTER WHERE cust_name=@user ")
'prod_size,prod_pack,
' REPORT & ABORT ON ERRORS OR NO RECORDS FOUND
If noerrors(True) = False OrElse access.recordcount < 1 Then Exit Sub
'GET FIRST ROW FOUND
Dim r As DataRow = access.dbdt.Rows(0)
txt_cust.Text = r("cust_name").ToString
If NOTEMPTY(access.Exception) Then MsgBox(access.Exception)
If txt_cust.Text = "*****" Then
CUSTOMER.Show()
txt_cust.Text = CUSTOMER.txt_custnm.Text
txt_custgst.Text = CUSTOMER.txt_custgst.Text
txt_custpan.Text = CUSTOMER.txt_custpan.Text
lbl_stat.Text = CUSTOMER.txt_stat.Text
lbl_cust_stat.Text = CUSTOMER.m_srno
lbl_stat.BorderStyle = BorderStyle.FixedSingle
custcode = CUSTOMER.txt_custcode.Text
Me.CMD_CUSTFIND.Select()
TRAN1()
Else
txt_cust.Text = r("cust_name").ToString
txt_custgst.Text = r("gst_no").ToString
txt_custpan.Text = r("pan_no").ToString
lbl_stat.Visible = True
lbl_stat.BorderStyle = BorderStyle.FixedSingle
lbl_stat.Text = r("state").ToString
lbl_cust_stat.Text = r("cust_stat_code").ToString
custcode = r("cust_code").ToString
cust_id = r("id").ToString
dgvcust.Visible = False
Me.CMD_CUSTFIND.Select()
TRAN1()
End If
End Sub
Private Sub TRAN1()
'MsgBox("GO FOR BILLING")
txt_srno.Text = Val(1)
mystat()
End Sub
Private Sub mystat()
access.Execquery("select * from myaccount ")
If NOTEMPTY(access.Exception) Then MsgBox(access.Exception) : Exit Sub
If access.dbdt.Rows.Count < 1 OrElse currentrecord > access.dbdt.Rows.Count - 1 Then Exit Sub
Dim r As DataRow = access.dbdt.Rows(currentrecord)
txt_myid.Text = r("id").ToString
txt_mystate.Text = r("state_code").ToString
If txt_mystate.Text = lbl_cust_stat.Text OrElse lbl_cust_stat.Text = txt_mystate.Text Then TX_TYPE1 = "2"
If txt_mystate.Text <> lbl_cust_stat.Text OrElse lbl_cust_stat.Text <> txt_mystate.Text Then TX_TYPE1 = "1"
Return
End Sub
Private Sub CMD_CUSTFIND_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CMD_CUSTFIND.Click
dgvcust.Visible = False
GRPSL1.Visible = True
GRPSL1.Size = New Size(1012, 274)
GRPSL1.Location = New Point(5, 84)
CMD_CUSTFIND.Visible = False
refresh_prod()
End Sub
Public Sub refresh_prod()
Me.txt_prodname.Select()
access.Execquery("select * from prodmast ")
If noerrors(True) = False Then Exit Sub
dgvprod.DataSource = access.dbdt
dgvprod.Size = New Size(432, 189)
dgvprod.Location = New Point(6, 79)
lb_s_amt.Visible = True
' MsgBox("SELECT ***** from list TO for new Product ", MsgBoxStyle.OkOnly)
If access.recordcount > 0 Then access.recordcount = 0
End Sub
Private Sub serch_prod(ByVal name As String)
access.addparam("@code", "%" & name & "%")
access.addparam1("@code1", qt)
access.Execquery("SELECT id,product_name,product_code,prod_type,prod_size,prod_pack,prod_gst_code,prod_gst_type,gst_rate,sales_rate,purches_rate " & _
"from prodmast " & _
"where product_name like @code ")
If noerrors(True) = False Then Exit Sub
dgvprod.DataSource = access.dbdt
End Sub
Private Sub txt_prodname_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_prodname.TextChanged
If Not String.IsNullOrEmpty(txt_prodname.Text) Then
dgvprod.Visible = True
serch_prod(txt_prodname.Text)
End If
End Sub
Private Sub dgvprod_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvprod.CellContentClick
If e.RowIndex < 0 Or e.ColumnIndex < 0 Then Exit Sub
Select_prod(dgvprod.Item(1, e.RowIndex).Value)
dgvprod.Visible = False
End Sub
Private Sub Select_prod(ByVal product_name As String)
'QUERY(USER)
' Dim xigst As String = 1.0
Me.txt_prodname.Select()
access.addparam("@user", product_name)
access.Execquery("SELECT product_name,product_code,prod_type,prod_gst_code,prod_size,prod_pack,prod_gst_type,gst_rate,sales_rate,purches_rate FROM prodmast WHERE product_name=@user ")
' REPORT & ABORT ON ERRORS OR NO RECORDS FOUND
If noerrors(True) = False OrElse access.recordcount < 1 Then Exit Sub
Dim r As DataRow = access.dbdt.Rows(0)
txt_prodname.Text = r("product_name").ToString
prod_type_sl = r("prod_type").ToString
If Not String.IsNullOrEmpty(prod_type_sl) Then
Label7.Text = TX_TYPE1
If prod_type_sl = 1 Then
lb_unit.Enabled = False
lb_pkg.Enabled = False
lb_blank1.Enabled = False
lb_no.Enabled = False
lb_bach.Enabled = False
lb_free.Enabled = False
lb_goods.Enabled = False
txt_unit.Enabled = False
txt_packing.Enabled = False
txt_batch.Enabled = False
txt_free_goods.Enabled = False
If TX_TYPE1 = "1" Then
lb_cgst.Enabled = False
lb_c_taxt.Enabled = False
lb_c_amt.Enabled = False
lb_sgst.Enabled = False
lb_s_amt.Enabled = False
lb_s_tax.Enabled = False
txt_cgst_persent.Enabled = False
txt_cgst_amt.Enabled = False
txt_sgst_persent.Enabled = False
txt_sgst_amt.Enabled = False
Else
If TX_TYPE1 = "2" Then
lb_cgst.Enabled = True
lb_c_taxt.Enabled = True
lb_c_amt.Enabled = True
lb_sgst.Enabled = True
lb_s_amt.Enabled = True
lb_s_tax.Enabled = True
txt_cgst_persent.Enabled = True
txt_cgst_amt.Enabled = True
txt_sgst_persent.Enabled = True
txt_sgst_amt.Enabled = True
lb_i_amt.Enabled = False
lb_i_tax.Enabled = False
lb_igst.Enabled = False
txt_igst_persent.Enabled = False
txt_igst_amt.Enabled = False
End If
End If
txt_prodcode.Text = r("product_code").ToString
Label7.Text = prod_sl_code
dgvsals_tran.Visible = True
prod_tran_mode()
Else
If prod_type_sl = 2 Then
lb_unit.Enabled = True
lb_pkg.Enabled = True
lb_blank1.Enabled = True
lb_no.Enabled = True
lb_bach.Enabled = True
lb_free.Enabled = True
lb_goods.Enabled = True
txt_unit.Enabled = True
txt_packing.Enabled = True
txt_batch.Enabled = True
txt_free_goods.Enabled = True
If TX_TYPE1 = "1" Then
lb_cgst.Enabled = False
lb_c_taxt.Enabled = False
lb_c_amt.Enabled = False
lb_sgst.Enabled = False
lb_s_amt.Enabled = False
lb_s_tax.Enabled = False
txt_cgst_persent.Enabled = False
txt_cgst_amt.Enabled = False
txt_sgst_persent.Enabled = False
txt_sgst_amt.Enabled = False
Else
If TX_TYPE1 = "2" Then
lb_cgst.Enabled = True
lb_c_taxt.Enabled = True
lb_c_amt.Enabled = True
lb_sgst.Enabled = True
lb_s_amt.Enabled = True
lb_s_tax.Enabled = True
txt_cgst_persent.Enabled = True
txt_cgst_amt.Enabled = True
txt_sgst_persent.Enabled = True
txt_sgst_amt.Enabled = True
lb_i_amt.Enabled = False
lb_i_tax.Enabled = False
lb_igst.Enabled = False
txt_igst_persent.Enabled = False
txt_igst_amt.Enabled = False
End If
End If
txt_prodcode.Text = r("product_code").ToString
dgvsals_tran.Visible = True
Label7.Text = prod_sl_code
prod_tran_mode()
End If
End If
End If
End Sub
Private Sub prod_tran_mode()
access.Execquery("select id,prod_name,sale_rate,QTY_purches,prod_type,prod_size,prod_pack,batch_no,free_goods,prod_gst_code,prod_gst_type,GIST_TAX_RATE_perches,CGST_TAX_RT_perches,SGST_TAX_RT_perches,srno_id,prod_code from stock_registor ")
If NOTEMPTY(access.Exception) Then MsgBox(access.Exception)
dgvsals_tran.DataSource = access.dbdt
Me.dgvsals_tran.Select()
serch_prodqty(txt_prodcode.Text)
End Sub
Private Sub serch_prodqty(ByVal NUMBERS As String)
access.addparam1("@code", "%" & NUMBERS & "%")
access.addparam1("@code1", qt)
' prod_type,prod_size,prod_pack,batch_no,free_goods,prod_gst_code,prod_gst_type,GIST_TAX_RATE_perches,CGST_TAX_RT_perches,SGST_TAX_RT_perches
access.Execquery("SELECT id,prod_name,sale_rate,QTY_purches,prod_type,prod_size,prod_pack,batch_no,free_goods,prod_gst_code,prod_gst_type,GIST_TAX_RATE_perches,CGST_TAX_RT_perches,SGST_TAX_RT_perches,srno_id,prod_code " & _
"from stock_registor " & _
"where prod_code like @code and qty_purches>code1 ")
If noerrors(True) = False Then Exit Sub
dgvsals_tran.DataSource = access.dbdt
End Sub
Private Sub dgvsals_tran_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvsals_tran.CellContentClick
If e.RowIndex < 0 Or e.ColumnIndex < 0 Then Exit Sub
sele_prod1(dgvsals_tran.Item(1, e.RowIndex).Value)
End Sub
Private Sub sele_prod1(ByVal prod_code As String)
dgvsals_tran.Visible = False
MsgBox("ok")
access.addparam1("@p1", prod_code)
access.Execquery("SELECT id,prod_name,sale_rate,QTY_purches,prod_type,prod_size,prod_pack,batch_no,free_goods,prod_gst_code,prod_gst_type,GIST_TAX_RATE_perches,CGST_TAX_RT_perches,SGST_TAX_RT_perches,srno_id,prod_code from stock_registor where prod_code=@p1 ")
' If noerrors(True) = False OrElse access.recordcount < 1 Then Exit Sub
Dim r As DataRow = access.dbdt.Rows(currentrecord)
End Sub
End Class