EDN Admin
Well-known member
Apologies for the length of the code in this post - im knew to VB and am looking for some advise. Basically im wondering whether its a good idea to have this amount of code for one button. Would this be considered too much code to have? Or is it not that
much at all?
Can anyone give me some tips on how to clean up the code, perhaps some recommendations on how to have it working optimally. Maybe even some links for areas in particular that I should be more aware of. What functionality have I not employed here that I should
have?
Thanks for reading. Sorry again for the length of the code.
<pre lang="x-vbnet Option Strict On
Imports System.Data.SqlClient
Public Class Expenses
Private MotorItems As New List(Of String)
Private TravelItems As New List(Of String)
Private Promo As New List(Of String)
Private MARKETING_EXPS As New List(Of String)
Private Misc_Expenses As New List(Of String)
Private TRAINING As New List(Of String)
Dim currency As DataGridViewComboBoxColumn
Dim rcptNum As DataGridViewTextBoxColumn
Dim price As DataGridViewTextBoxColumn
Dim markCode As DataGridViewComboBoxColumn
Dim accID As DataGridViewComboBoxColumn
Dim cbxCat As ComboBox
Dim lineComment As DataGridViewTextBoxColumn
Dim lineAccounts As DataGridViewTextBoxColumn
Dim rowID As DataGridViewTextBoxColumn
Private Sub Expenses_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim uid As String = Form1.Label4.Text
TODO: This line of code loads data into the Exp_testDataSet12.tblYear table. You can move, or remove it, as needed.
Me.TblYearTableAdapter.Fill(Me.Exp_testDataSet12.tblYear)
ComboBox2.SelectedIndex = -1
Me.TblMonthTableAdapter.Fill(Me.Exp_testDataSet11.tblMonth)
ComboBox1.SelectedIndex = -1
Dim con As New SqlClient.SqlConnection, cmd As New SqlClient.SqlCommand, adp As New SqlClient.SqlDataAdapter
Me.TblCompanyTypeTableAdapter1.Fill(Me.Exp_testDataSet10.tblCompanyType)
Dim dateNow As New Date
dateNow = Date.Now
txtEmployee.Text = Form1.Label2.Text
txtDate.Text = Microsoft.VisualBasic.Left(Form1.Label3.Text, 10)
RemoveHandler DataGridView1.EditingControlShowing, New DataGridViewEditingControlShowingEventHandler(AddressOf DataGridView1_EditingControlShowing)
AddHandler DataGridView1.EditingControlShowing, New DataGridViewEditingControlShowingEventHandler(AddressOf DataGridView1_EditingControlShowing) Add this event handler to initialize comboboxes control
Me.PaymentTypeTableAdapter.Fill(Me.Exp_testDataSet5.PaymentType) loads payment type fields
initialisefields() loads dgv fields
teamCompName() loads team
cbxTeam.SelectedIndex = -1
markCodes() loads marketing codes
DataGridView1.AutoGenerateColumns = False
With MotorItems
.Add("Fuel") : .Add("Parking") : .Add("Cleaning") : .Add("Motor Misc")
.Add("Mileage") : .Add("Tolls") : .Add("Car Hire") : .Add("Tyres")
.Add("Repairs") : .Add("OIL") : .Add("CLEANING PRODUCTS") : .Add("")
End With
With TravelItems
.Add("Accomodation") : .Add("Lunch Allowance") : .Add("Meals")
.Add("Taxis") : .Add("Flights") : .Add("Travel Misc") : .Add("TRAIN FARE")
.Add("FLIGHT NAME CHANGE") : .Add("FLIGHT CANCELLATION")
.Add("")
End With
With Promo
.Add("Meals Supplier") : .Add("Meals Sales") : .Add("Confect")
.Add("Promo Misc") : .Add("")
End With
With MARKETING_EXPS
.Add("PROMO MATERIALS") : .Add("IMAGES") : .Add("PROMO ITEMS")
.Add("Marketing Subscription") : .Add("WEB COSTS")
.Add("CONFERENCE COSTS") : .Add("")
End With
With Misc_Expenses
.Add("POSTAGE") : .Add("SUNDRY COSTS") : .Add("COMPUTER MISC")
.Add("Team Meeting") : .Add("Mobile Exp") : .Add("Broadband")
.Add("Stationary") : .Add("Subscription") : .Add("ACCRUALS")
.Add("STAMP DUTY FEE") : .Add("EDUCATIONAL BOOKS")
.Add("")
End With
With TRAINING
.Add("TRAINING COSTS") : .Add("")
End With
Dim col As DataGridViewComboBoxColumn
col = CType(DataGridView1.Columns("Column1"), DataGridViewComboBoxColumn)
col.Items.Add("Motor")
col.Items.Add("Travel")
col.Items.Add("Promo")
col.Items.Add("Marketing Exps")
col.Items.Add("Misc Expenses")
col.Items.Add("TRAINING")
col.Items.Add("")
col = CType(DataGridView1.Columns("Column2"), DataGridViewComboBoxColumn)
col.Items.AddRange(MotorItems.ToArray)
col.Items.AddRange(TravelItems.ToArray)
col.Items.AddRange(TRAINING.ToArray)
col.Items.AddRange(Promo.ToArray)
col.Items.AddRange(MARKETING_EXPS.ToArray)
col.Items.AddRange(Misc_Expenses.ToArray)
col.Items.Add("N/A")
col.Items.Add("")
DataGridView1.EditMode = DataGridViewEditMode.EditOnEnter
populateGV()
End Sub
Private Sub DataGridView1_DataError(ByVal sender As Object, ByVal e As DataGridViewDataErrorEventArgs) Handles DataGridView1.DataError
MessageBox.Show("Error happened " & e.Context.ToString())
If (e.Context = DataGridViewDataErrorContexts.Commit) Then
MessageBox.Show("Commit error")
End If
If (e.Context = DataGridViewDataErrorContexts.CurrentCellChange) Then
MessageBox.Show("Cell change")
End If
If (e.Context = DataGridViewDataErrorContexts.Parsing) Then
MessageBox.Show("parsing error")
End If
If (e.Context = DataGridViewDataErrorContexts.LeaveControl) Then
MessageBox.Show("leave control error")
End If
If (TypeOf (e.Exception) Is ConstraintException) Then
Dim view As DataGridView = CType(sender, DataGridView)
view.Rows(e.RowIndex).ErrorText = "an error"
view.Rows(e.RowIndex).Cells(e.ColumnIndex).ErrorText = "an error"
e.ThrowException = False
End If
End Sub
Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
If DataGridView1.Columns(DataGridView1.CurrentCell.ColumnIndex).Name = "Column2" Then
Dim editcntrl As DataGridViewComboBoxEditingControl = CType(e.Control, DataGridViewComboBoxEditingControl)
If DataGridView1.CurrentRow.Cells("Column1").Value Is Nothing Then
editcntrl.Items.Clear()
editcntrl.Items.Add("N/A")
Exit Sub
End If
Dim TestCase As String = CType(DataGridView1.CurrentRow.Cells("Column1").Value, String).ToUpper
Select Case TestCase
Case "MOTOR"
RemoveItems(editcntrl, TravelItems)
RemoveItems(editcntrl, Promo)
RemoveItems(editcntrl, MARKETING_EXPS)
RemoveItems(editcntrl, Misc_Expenses)
RemoveItems(editcntrl, Training)
editcntrl.Items.Remove("N/A")
Case "TRAVEL"
RemoveItems(editcntrl, MotorItems)
RemoveItems(editcntrl, Promo)
RemoveItems(editcntrl, MARKETING_EXPS)
RemoveItems(editcntrl, Misc_Expenses)
RemoveItems(editcntrl, Training)
editcntrl.Items.Remove("N/A")
Case "PROMO"
RemoveItems(editcntrl, TravelItems)
RemoveItems(editcntrl, MotorItems)
RemoveItems(editcntrl, Misc_Expenses)
RemoveItems(editcntrl, MARKETING_EXPS)
RemoveItems(editcntrl, Training)
editcntrl.Items.Remove("N/A")
Case "MARKETING EXPS"
RemoveItems(editcntrl, TravelItems)
RemoveItems(editcntrl, MotorItems)
RemoveItems(editcntrl, Misc_Expenses)
RemoveItems(editcntrl, Promo)
RemoveItems(editcntrl, Training)
editcntrl.Items.Remove("N/A")
Case "MISC EXPENSES"
RemoveItems(editcntrl, TravelItems)
RemoveItems(editcntrl, MotorItems)
RemoveItems(editcntrl, MARKETING_EXPS)
RemoveItems(editcntrl, Promo)
RemoveItems(editcntrl, Training)
editcntrl.Items.Remove("N/A")
Case "Training"
RemoveItems(editcntrl, TravelItems)
RemoveItems(editcntrl, MotorItems)
RemoveItems(editcntrl, MARKETING_EXPS)
RemoveItems(editcntrl, Promo)
RemoveItems(editcntrl, Misc_Expenses)
editcntrl.Items.Remove("N/A")
End Select
End If
End Sub
Private Sub initialisefields()
rcptNum = New DataGridViewTextBoxColumn()
rcptNum.Name = "Rcpt Number"
Me.DataGridView1.Columns.Add(rcptNum)
price = New DataGridViewTextBoxColumn()
price.Name = "Cost"
Me.DataGridView1.Columns.Add(price)
currency = New DataGridViewComboBoxColumn()
currency.Name = "Currency"
currency.Width = CInt("65")
Me.DataGridView1.Columns.Add(currency)
markCode = New DataGridViewComboBoxColumn()
markCode.Name = "Marketing Code"
markCode.Width = CInt("160")
Me.DataGridView1.Columns.Add(markCode)
lineComment = New DataGridViewTextBoxColumn()
lineComment.Name = "Comment"
Me.DataGridView1.Columns.Add(lineComment)
lineAccounts = New DataGridViewTextBoxColumn()
lineAccounts.Name = "Comment - Accounts Only"
Me.DataGridView1.Columns.Add(lineAccounts)
rowID = New DataGridViewTextBoxColumn()
rowID.Name = "Row ID"
Me.DataGridView1.Columns.Add(rowID)
End Sub
Public Sub teamCompName()
find the team/area
Dim con As New SqlClient.SqlConnection
con.ConnectionString = ""
Dim var As String = Form1.ComboBox1.SelectedValue.ToString()
Dim name() As String = var.Split(CChar(","))
Dim firstName As String = ""
Dim secondName As String = ""
firstName = name(1)
secondName = name(0)
Dim myCommand As New SqlCommand, myAdapter As New SqlDataAdapter, para As New SqlParameter
para.ParameterName = "@first"
para.Value = Trim(firstName)
para.ParameterName = "@second"
para.Value = Trim(secondName)
myCommand.Parameters.Add("@first", SqlDbType.NVarChar, 40).Value = Trim(firstName)
myCommand.Parameters.Add("@second", SqlDbType.NVarChar, 40).Value = Trim(secondName)
Dim sql As String = "select Area from vw_team where FirstName = @first and Surname = @second"
myCommand.Connection = con
myCommand.CommandText = sql
myAdapter.SelectCommand = myCommand
myCommand.Connection.Open()
Dim var2 As String = CStr(myCommand.ExecuteScalar)
myCommand.Connection.Close()
txtTeam.Text = var2
find the currency
Dim con2 As New SqlConnection
con2.ConnectionString = ""
Dim myCommand2 As New SqlCommand
Dim myAdapter2 As New SqlDataAdapter
Dim sql2 As String = "select curr from Currency"
Dim dt2 As New DataTable
myCommand2.Connection = con2
myCommand2.CommandText = sql2
myAdapter2.SelectCommand = myCommand2
myCommand2.Connection.Open()
myAdapter2.Fill(dt2)
myCommand2.Connection.Close()
dt2.Rows.Add("")
currency.DataSource = dt2
currency.DisplayMember = "curr"
currency.ValueMember = "curr"
End Sub
Private Sub markCodes()
Dim dt As New DataTable
Dim con As New SqlConnection, adp As New SqlDataAdapter, cmd As New SqlCommand
con.ConnectionString = ""
Dim sql As String = "Select Code as Expr1 from tblMarketingCodes"
cmd.Connection = con
cmd.CommandText = sql
adp.SelectCommand = cmd
cmd.Connection.Open()
adp.Fill(dt)
cmd.Connection.Close()
dt.Rows.Add("")
markCode.DataSource = dt
markCode.DisplayMember = "Expr1"
markCode.ValueMember = "Expr1"
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim val As String = ""
Dim dateNow As New Date
Dim myMonth As New Integer
myMonth = Month(dateNow)
Dim MyDate As Date
Dim MyYear As Integer
MyDate = Date.Now Assign a date
MyYear = Year(MyDate) MyYear contains 1969
If myMonth = 1 Then
val = "January"
ElseIf myMonth = 2 Then
val = "February"
ElseIf myMonth = 3 Then
val = "March"
ElseIf myMonth = 4 Then
val = "April"
ElseIf myMonth = 5 Then
val = "May"
ElseIf myMonth = 6 Then
val = "June"
ElseIf myMonth = 7 Then
val = "July"
ElseIf myMonth = 8 Then
val = "August"
ElseIf myMonth = 9 Then
val = "September"
ElseIf myMonth = 10 Then
val = "October"
ElseIf myMonth = 11 Then
val = "November"
ElseIf myMonth = 12 Then
val = "December"
End If
Dim val2 As String = val & " " & MyYear
lblMax.Text = ""
If CStr(cbxTeam.SelectedValue) = "" Then
MsgBox("Please Select Team")
Else
find the max rowID
Dim con1 As New SqlConnection, cmd1 As New SqlCommand, adp1 As New SqlDataAdapter
con1.ConnectionString = ""
Dim sql1 As String = "Select MAX(rowID) from vw_tblP_Usage_Details"
cmd1.Connection = con1
cmd1.CommandText = sql1
cmd1.Connection.Open()
Dim maxID As String = CStr(cmd1.ExecuteScalar)
cmd1.Connection.Close()
MsgBox(maxID)
MsgBox("lblMax.Text - " & lblMax.Text)
create a dataset and add table to it
Dim dataset As New DataSet
dataset.Tables.Add("Main")
add columns
Dim col1 As New DataColumn
for each column in the datagridview add a new column to your table
Dim dgvCol1 As New DataGridViewColumn
For Each dgvCol1 In DataGridView1.Columns
col1 = New DataColumn(dgvCol1.Name)
dataset.Tables("Main").Columns.Add(col1)
Next
Dim row1 As DataRow
Dim colcount1 As Integer = DataGridView1.Columns.Count - 1
For i As Integer = 0 To DataGridView1.Rows.Count - 1
For g As Integer = 0 To DataGridView1.Rows.Count - 1
row = ds.Tables("Main").Rows.Add
For Each column As DataGridViewColumn In DataGridView1.Columns
row.Item(column.Index) = DataGridView1.Rows.Item(g).Cells(column.Index).Value
Next
Next
Try
If lblMax.Text = "" Then
insert details into tblP_Usage
Dim reviewed = "no"
Dim archiveNoShow = "0"
Dim con As New SqlConnection, cmd As New SqlCommand, adp As New SqlDataAdapter, para As New SqlParameter
con.ConnectionString = ""
Dim sql As String = "INSERT INTO tblP_Usage (EmpID, Company, C_Team, username, Comment) Values (@empid, @company, @cteam, @username, @lineComment)"
cmd.Parameters.Add("@empid", SqlDbType.Int).Value = Form1.Label4.Text.ToString
cmd.Parameters.Add("@company", SqlDbType.NVarChar, 50).Value = cbxTeam.SelectedValue
cmd.Parameters.Add("@cteam", SqlDbType.NVarChar, 50).Value = Form1.Label5.Text.ToString
cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value = Form1.Label6.Text.ToString
cmd.Parameters.Add("@lineComment", SqlDbType.NText, 40).Value = lineComment.Selected
cmd.Connection = con
cmd.CommandText = sql
adp.InsertCommand = cmd
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
MsgBox("Details inserted into tblP_Usage")
End If
Catch ex As Exception
MsgBox(ex.Message())
End Try
find the max PUID
Dim cmd2 As New SqlCommand, adp2 As New SqlDataAdapter, con2 As New SqlClient.SqlConnection
con2.ConnectionString = ""
Dim sql2 As String = "Select MAX(PUID) from tblP_Usage"
cmd2.Connection = con2
cmd2.CommandText = sql2
adp2.SelectCommand = cmd2
cmd2.Connection.Open()
Dim PUID As String = CStr(cmd2.ExecuteScalar())
cmd2.Connection.Close()
lblMax.Text = PUID
Insert into tblP_Usage_Details table using PUID as indicator
Dim dt As New DataTable()
dt = ds.Tables("Main")
For m As Integer = 0 To dt.Rows.Count - 1
If dt.Rows(m)("Row ID") Is DBNull.Value Then
If dt.Rows(m)("payment") Is DBNull.Value Or dt.Rows(m)("column1") Is DBNull.Value Or dt.Rows(m)("column2") Is DBNull.Value Or dt.Rows(m)("Cost") Is DBNull.Value Or dt.Rows(m)("Currency") Is DBNull.Value Or dt.Rows(m)("Marketing Code") Is DBNull.Value Or dt.Rows(m)("Comment") Is DBNull.Value Then
Else
Dim cmd3 As New SqlCommand, adp3 As New SqlDataAdapter, con3 As New SqlConnection
con3.ConnectionString = ""
Dim sql3 As String = "INSERT INTO tblP_Usage_Details (PUID, CatA, CatB, CatC, Cost, Currency, MarketingCode, Comment) VALUES (" & lblMax.Text & ", " & CStr(dt.Rows(m)("payment")) & ", " & CStr(dt.Rows(m)("column1")) & ", " & CStr(dt.Rows(m)("column2")) & ", " & CStr(dt.Rows(m)("Cost")) & "," & CStr(dt.Rows(m)("Currency")) & ", " & CStr(dt.Rows(m)("Marketing Code")) & ", " & CStr(dt.Rows(m)("Comment")) & ")"
cmd3.Connection = con3
cmd3.CommandText = sql3
cmd3.Connection.Open()
adp3.InsertCommand = cmd3
cmd3.ExecuteNonQuery()
cmd3.Connection.Close()
End If
End If
populateGV()
Next
MsgBox("Updated@)
Else
MsgBox("Must Be Numeric")
End If
End If
Else
End If
Next
End If
End Sub
Private Sub RemoveItems(ByRef comboboxeditcntrl As DataGridViewComboBoxEditingControl, ByRef removelist As List(Of String))
For Each item As String In removelist
comboboxeditcntrl.Items.Remove(item)
Next
End Sub[/code]
<br/>
<br/>
View the full article
much at all?
Can anyone give me some tips on how to clean up the code, perhaps some recommendations on how to have it working optimally. Maybe even some links for areas in particular that I should be more aware of. What functionality have I not employed here that I should
have?
Thanks for reading. Sorry again for the length of the code.
<pre lang="x-vbnet Option Strict On
Imports System.Data.SqlClient
Public Class Expenses
Private MotorItems As New List(Of String)
Private TravelItems As New List(Of String)
Private Promo As New List(Of String)
Private MARKETING_EXPS As New List(Of String)
Private Misc_Expenses As New List(Of String)
Private TRAINING As New List(Of String)
Dim currency As DataGridViewComboBoxColumn
Dim rcptNum As DataGridViewTextBoxColumn
Dim price As DataGridViewTextBoxColumn
Dim markCode As DataGridViewComboBoxColumn
Dim accID As DataGridViewComboBoxColumn
Dim cbxCat As ComboBox
Dim lineComment As DataGridViewTextBoxColumn
Dim lineAccounts As DataGridViewTextBoxColumn
Dim rowID As DataGridViewTextBoxColumn
Private Sub Expenses_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim uid As String = Form1.Label4.Text
TODO: This line of code loads data into the Exp_testDataSet12.tblYear table. You can move, or remove it, as needed.
Me.TblYearTableAdapter.Fill(Me.Exp_testDataSet12.tblYear)
ComboBox2.SelectedIndex = -1
Me.TblMonthTableAdapter.Fill(Me.Exp_testDataSet11.tblMonth)
ComboBox1.SelectedIndex = -1
Dim con As New SqlClient.SqlConnection, cmd As New SqlClient.SqlCommand, adp As New SqlClient.SqlDataAdapter
Me.TblCompanyTypeTableAdapter1.Fill(Me.Exp_testDataSet10.tblCompanyType)
Dim dateNow As New Date
dateNow = Date.Now
txtEmployee.Text = Form1.Label2.Text
txtDate.Text = Microsoft.VisualBasic.Left(Form1.Label3.Text, 10)
RemoveHandler DataGridView1.EditingControlShowing, New DataGridViewEditingControlShowingEventHandler(AddressOf DataGridView1_EditingControlShowing)
AddHandler DataGridView1.EditingControlShowing, New DataGridViewEditingControlShowingEventHandler(AddressOf DataGridView1_EditingControlShowing) Add this event handler to initialize comboboxes control
Me.PaymentTypeTableAdapter.Fill(Me.Exp_testDataSet5.PaymentType) loads payment type fields
initialisefields() loads dgv fields
teamCompName() loads team
cbxTeam.SelectedIndex = -1
markCodes() loads marketing codes
DataGridView1.AutoGenerateColumns = False
With MotorItems
.Add("Fuel") : .Add("Parking") : .Add("Cleaning") : .Add("Motor Misc")
.Add("Mileage") : .Add("Tolls") : .Add("Car Hire") : .Add("Tyres")
.Add("Repairs") : .Add("OIL") : .Add("CLEANING PRODUCTS") : .Add("")
End With
With TravelItems
.Add("Accomodation") : .Add("Lunch Allowance") : .Add("Meals")
.Add("Taxis") : .Add("Flights") : .Add("Travel Misc") : .Add("TRAIN FARE")
.Add("FLIGHT NAME CHANGE") : .Add("FLIGHT CANCELLATION")
.Add("")
End With
With Promo
.Add("Meals Supplier") : .Add("Meals Sales") : .Add("Confect")
.Add("Promo Misc") : .Add("")
End With
With MARKETING_EXPS
.Add("PROMO MATERIALS") : .Add("IMAGES") : .Add("PROMO ITEMS")
.Add("Marketing Subscription") : .Add("WEB COSTS")
.Add("CONFERENCE COSTS") : .Add("")
End With
With Misc_Expenses
.Add("POSTAGE") : .Add("SUNDRY COSTS") : .Add("COMPUTER MISC")
.Add("Team Meeting") : .Add("Mobile Exp") : .Add("Broadband")
.Add("Stationary") : .Add("Subscription") : .Add("ACCRUALS")
.Add("STAMP DUTY FEE") : .Add("EDUCATIONAL BOOKS")
.Add("")
End With
With TRAINING
.Add("TRAINING COSTS") : .Add("")
End With
Dim col As DataGridViewComboBoxColumn
col = CType(DataGridView1.Columns("Column1"), DataGridViewComboBoxColumn)
col.Items.Add("Motor")
col.Items.Add("Travel")
col.Items.Add("Promo")
col.Items.Add("Marketing Exps")
col.Items.Add("Misc Expenses")
col.Items.Add("TRAINING")
col.Items.Add("")
col = CType(DataGridView1.Columns("Column2"), DataGridViewComboBoxColumn)
col.Items.AddRange(MotorItems.ToArray)
col.Items.AddRange(TravelItems.ToArray)
col.Items.AddRange(TRAINING.ToArray)
col.Items.AddRange(Promo.ToArray)
col.Items.AddRange(MARKETING_EXPS.ToArray)
col.Items.AddRange(Misc_Expenses.ToArray)
col.Items.Add("N/A")
col.Items.Add("")
DataGridView1.EditMode = DataGridViewEditMode.EditOnEnter
populateGV()
End Sub
Private Sub DataGridView1_DataError(ByVal sender As Object, ByVal e As DataGridViewDataErrorEventArgs) Handles DataGridView1.DataError
MessageBox.Show("Error happened " & e.Context.ToString())
If (e.Context = DataGridViewDataErrorContexts.Commit) Then
MessageBox.Show("Commit error")
End If
If (e.Context = DataGridViewDataErrorContexts.CurrentCellChange) Then
MessageBox.Show("Cell change")
End If
If (e.Context = DataGridViewDataErrorContexts.Parsing) Then
MessageBox.Show("parsing error")
End If
If (e.Context = DataGridViewDataErrorContexts.LeaveControl) Then
MessageBox.Show("leave control error")
End If
If (TypeOf (e.Exception) Is ConstraintException) Then
Dim view As DataGridView = CType(sender, DataGridView)
view.Rows(e.RowIndex).ErrorText = "an error"
view.Rows(e.RowIndex).Cells(e.ColumnIndex).ErrorText = "an error"
e.ThrowException = False
End If
End Sub
Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
If DataGridView1.Columns(DataGridView1.CurrentCell.ColumnIndex).Name = "Column2" Then
Dim editcntrl As DataGridViewComboBoxEditingControl = CType(e.Control, DataGridViewComboBoxEditingControl)
If DataGridView1.CurrentRow.Cells("Column1").Value Is Nothing Then
editcntrl.Items.Clear()
editcntrl.Items.Add("N/A")
Exit Sub
End If
Dim TestCase As String = CType(DataGridView1.CurrentRow.Cells("Column1").Value, String).ToUpper
Select Case TestCase
Case "MOTOR"
RemoveItems(editcntrl, TravelItems)
RemoveItems(editcntrl, Promo)
RemoveItems(editcntrl, MARKETING_EXPS)
RemoveItems(editcntrl, Misc_Expenses)
RemoveItems(editcntrl, Training)
editcntrl.Items.Remove("N/A")
Case "TRAVEL"
RemoveItems(editcntrl, MotorItems)
RemoveItems(editcntrl, Promo)
RemoveItems(editcntrl, MARKETING_EXPS)
RemoveItems(editcntrl, Misc_Expenses)
RemoveItems(editcntrl, Training)
editcntrl.Items.Remove("N/A")
Case "PROMO"
RemoveItems(editcntrl, TravelItems)
RemoveItems(editcntrl, MotorItems)
RemoveItems(editcntrl, Misc_Expenses)
RemoveItems(editcntrl, MARKETING_EXPS)
RemoveItems(editcntrl, Training)
editcntrl.Items.Remove("N/A")
Case "MARKETING EXPS"
RemoveItems(editcntrl, TravelItems)
RemoveItems(editcntrl, MotorItems)
RemoveItems(editcntrl, Misc_Expenses)
RemoveItems(editcntrl, Promo)
RemoveItems(editcntrl, Training)
editcntrl.Items.Remove("N/A")
Case "MISC EXPENSES"
RemoveItems(editcntrl, TravelItems)
RemoveItems(editcntrl, MotorItems)
RemoveItems(editcntrl, MARKETING_EXPS)
RemoveItems(editcntrl, Promo)
RemoveItems(editcntrl, Training)
editcntrl.Items.Remove("N/A")
Case "Training"
RemoveItems(editcntrl, TravelItems)
RemoveItems(editcntrl, MotorItems)
RemoveItems(editcntrl, MARKETING_EXPS)
RemoveItems(editcntrl, Promo)
RemoveItems(editcntrl, Misc_Expenses)
editcntrl.Items.Remove("N/A")
End Select
End If
End Sub
Private Sub initialisefields()
rcptNum = New DataGridViewTextBoxColumn()
rcptNum.Name = "Rcpt Number"
Me.DataGridView1.Columns.Add(rcptNum)
price = New DataGridViewTextBoxColumn()
price.Name = "Cost"
Me.DataGridView1.Columns.Add(price)
currency = New DataGridViewComboBoxColumn()
currency.Name = "Currency"
currency.Width = CInt("65")
Me.DataGridView1.Columns.Add(currency)
markCode = New DataGridViewComboBoxColumn()
markCode.Name = "Marketing Code"
markCode.Width = CInt("160")
Me.DataGridView1.Columns.Add(markCode)
lineComment = New DataGridViewTextBoxColumn()
lineComment.Name = "Comment"
Me.DataGridView1.Columns.Add(lineComment)
lineAccounts = New DataGridViewTextBoxColumn()
lineAccounts.Name = "Comment - Accounts Only"
Me.DataGridView1.Columns.Add(lineAccounts)
rowID = New DataGridViewTextBoxColumn()
rowID.Name = "Row ID"
Me.DataGridView1.Columns.Add(rowID)
End Sub
Public Sub teamCompName()
find the team/area
Dim con As New SqlClient.SqlConnection
con.ConnectionString = ""
Dim var As String = Form1.ComboBox1.SelectedValue.ToString()
Dim name() As String = var.Split(CChar(","))
Dim firstName As String = ""
Dim secondName As String = ""
firstName = name(1)
secondName = name(0)
Dim myCommand As New SqlCommand, myAdapter As New SqlDataAdapter, para As New SqlParameter
para.ParameterName = "@first"
para.Value = Trim(firstName)
para.ParameterName = "@second"
para.Value = Trim(secondName)
myCommand.Parameters.Add("@first", SqlDbType.NVarChar, 40).Value = Trim(firstName)
myCommand.Parameters.Add("@second", SqlDbType.NVarChar, 40).Value = Trim(secondName)
Dim sql As String = "select Area from vw_team where FirstName = @first and Surname = @second"
myCommand.Connection = con
myCommand.CommandText = sql
myAdapter.SelectCommand = myCommand
myCommand.Connection.Open()
Dim var2 As String = CStr(myCommand.ExecuteScalar)
myCommand.Connection.Close()
txtTeam.Text = var2
find the currency
Dim con2 As New SqlConnection
con2.ConnectionString = ""
Dim myCommand2 As New SqlCommand
Dim myAdapter2 As New SqlDataAdapter
Dim sql2 As String = "select curr from Currency"
Dim dt2 As New DataTable
myCommand2.Connection = con2
myCommand2.CommandText = sql2
myAdapter2.SelectCommand = myCommand2
myCommand2.Connection.Open()
myAdapter2.Fill(dt2)
myCommand2.Connection.Close()
dt2.Rows.Add("")
currency.DataSource = dt2
currency.DisplayMember = "curr"
currency.ValueMember = "curr"
End Sub
Private Sub markCodes()
Dim dt As New DataTable
Dim con As New SqlConnection, adp As New SqlDataAdapter, cmd As New SqlCommand
con.ConnectionString = ""
Dim sql As String = "Select Code as Expr1 from tblMarketingCodes"
cmd.Connection = con
cmd.CommandText = sql
adp.SelectCommand = cmd
cmd.Connection.Open()
adp.Fill(dt)
cmd.Connection.Close()
dt.Rows.Add("")
markCode.DataSource = dt
markCode.DisplayMember = "Expr1"
markCode.ValueMember = "Expr1"
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim val As String = ""
Dim dateNow As New Date
Dim myMonth As New Integer
myMonth = Month(dateNow)
Dim MyDate As Date
Dim MyYear As Integer
MyDate = Date.Now Assign a date
MyYear = Year(MyDate) MyYear contains 1969
If myMonth = 1 Then
val = "January"
ElseIf myMonth = 2 Then
val = "February"
ElseIf myMonth = 3 Then
val = "March"
ElseIf myMonth = 4 Then
val = "April"
ElseIf myMonth = 5 Then
val = "May"
ElseIf myMonth = 6 Then
val = "June"
ElseIf myMonth = 7 Then
val = "July"
ElseIf myMonth = 8 Then
val = "August"
ElseIf myMonth = 9 Then
val = "September"
ElseIf myMonth = 10 Then
val = "October"
ElseIf myMonth = 11 Then
val = "November"
ElseIf myMonth = 12 Then
val = "December"
End If
Dim val2 As String = val & " " & MyYear
lblMax.Text = ""
If CStr(cbxTeam.SelectedValue) = "" Then
MsgBox("Please Select Team")
Else
find the max rowID
Dim con1 As New SqlConnection, cmd1 As New SqlCommand, adp1 As New SqlDataAdapter
con1.ConnectionString = ""
Dim sql1 As String = "Select MAX(rowID) from vw_tblP_Usage_Details"
cmd1.Connection = con1
cmd1.CommandText = sql1
cmd1.Connection.Open()
Dim maxID As String = CStr(cmd1.ExecuteScalar)
cmd1.Connection.Close()
MsgBox(maxID)
MsgBox("lblMax.Text - " & lblMax.Text)
create a dataset and add table to it
Dim dataset As New DataSet
dataset.Tables.Add("Main")
add columns
Dim col1 As New DataColumn
for each column in the datagridview add a new column to your table
Dim dgvCol1 As New DataGridViewColumn
For Each dgvCol1 In DataGridView1.Columns
col1 = New DataColumn(dgvCol1.Name)
dataset.Tables("Main").Columns.Add(col1)
Next
Dim row1 As DataRow
Dim colcount1 As Integer = DataGridView1.Columns.Count - 1
For i As Integer = 0 To DataGridView1.Rows.Count - 1
For g As Integer = 0 To DataGridView1.Rows.Count - 1
row = ds.Tables("Main").Rows.Add
For Each column As DataGridViewColumn In DataGridView1.Columns
row.Item(column.Index) = DataGridView1.Rows.Item(g).Cells(column.Index).Value
Next
Next
Try
If lblMax.Text = "" Then
insert details into tblP_Usage
Dim reviewed = "no"
Dim archiveNoShow = "0"
Dim con As New SqlConnection, cmd As New SqlCommand, adp As New SqlDataAdapter, para As New SqlParameter
con.ConnectionString = ""
Dim sql As String = "INSERT INTO tblP_Usage (EmpID, Company, C_Team, username, Comment) Values (@empid, @company, @cteam, @username, @lineComment)"
cmd.Parameters.Add("@empid", SqlDbType.Int).Value = Form1.Label4.Text.ToString
cmd.Parameters.Add("@company", SqlDbType.NVarChar, 50).Value = cbxTeam.SelectedValue
cmd.Parameters.Add("@cteam", SqlDbType.NVarChar, 50).Value = Form1.Label5.Text.ToString
cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value = Form1.Label6.Text.ToString
cmd.Parameters.Add("@lineComment", SqlDbType.NText, 40).Value = lineComment.Selected
cmd.Connection = con
cmd.CommandText = sql
adp.InsertCommand = cmd
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
MsgBox("Details inserted into tblP_Usage")
End If
Catch ex As Exception
MsgBox(ex.Message())
End Try
find the max PUID
Dim cmd2 As New SqlCommand, adp2 As New SqlDataAdapter, con2 As New SqlClient.SqlConnection
con2.ConnectionString = ""
Dim sql2 As String = "Select MAX(PUID) from tblP_Usage"
cmd2.Connection = con2
cmd2.CommandText = sql2
adp2.SelectCommand = cmd2
cmd2.Connection.Open()
Dim PUID As String = CStr(cmd2.ExecuteScalar())
cmd2.Connection.Close()
lblMax.Text = PUID
Insert into tblP_Usage_Details table using PUID as indicator
Dim dt As New DataTable()
dt = ds.Tables("Main")
For m As Integer = 0 To dt.Rows.Count - 1
If dt.Rows(m)("Row ID") Is DBNull.Value Then
If dt.Rows(m)("payment") Is DBNull.Value Or dt.Rows(m)("column1") Is DBNull.Value Or dt.Rows(m)("column2") Is DBNull.Value Or dt.Rows(m)("Cost") Is DBNull.Value Or dt.Rows(m)("Currency") Is DBNull.Value Or dt.Rows(m)("Marketing Code") Is DBNull.Value Or dt.Rows(m)("Comment") Is DBNull.Value Then
Else
Dim cmd3 As New SqlCommand, adp3 As New SqlDataAdapter, con3 As New SqlConnection
con3.ConnectionString = ""
Dim sql3 As String = "INSERT INTO tblP_Usage_Details (PUID, CatA, CatB, CatC, Cost, Currency, MarketingCode, Comment) VALUES (" & lblMax.Text & ", " & CStr(dt.Rows(m)("payment")) & ", " & CStr(dt.Rows(m)("column1")) & ", " & CStr(dt.Rows(m)("column2")) & ", " & CStr(dt.Rows(m)("Cost")) & "," & CStr(dt.Rows(m)("Currency")) & ", " & CStr(dt.Rows(m)("Marketing Code")) & ", " & CStr(dt.Rows(m)("Comment")) & ")"
cmd3.Connection = con3
cmd3.CommandText = sql3
cmd3.Connection.Open()
adp3.InsertCommand = cmd3
cmd3.ExecuteNonQuery()
cmd3.Connection.Close()
End If
End If
populateGV()
Next
MsgBox("Updated@)
Else
MsgBox("Must Be Numeric")
End If
End If
Else
End If
Next
End If
End Sub
Private Sub RemoveItems(ByRef comboboxeditcntrl As DataGridViewComboBoxEditingControl, ByRef removelist As List(Of String))
For Each item As String In removelist
comboboxeditcntrl.Items.Remove(item)
Next
End Sub[/code]
<br/>
<br/>
View the full article