Imports System.Data.SqlClient
Imports System.Globalization
Imports System.IO
Imports System.Windows.Forms.DataFormats
Imports Microsoft.SqlServer
Public Class FrmDataBackUp
Private table As New DataTable
Private adapter As SqlDataAdapter = New SqlDataAdapter()
Private sqlString As String
Private dtb As New DataTable
Private dts As New DataSet
Private changes As DataSet
Private cmd As New SqlCommand
Private cmdBuilder As SqlCommandBuilder
Private AddFlag As Boolean = False
Private adapterCx As New SqlDataAdapter
Private adapt2 As New SqlDataAdapter
Private row As Integer = 0
Private NoOfRows As Integer = 0
Private GridWidth As Integer = 0
Private Sub FrmDataBackUp_Load(sender As Object, e As EventArgs) Handles MyBase.Load
' Public variables declared elsewhere, values here for test purposes only
ChildID = 17
ParentID = 100
DataID = 133
' Connection is made elsewhere
adapterCx = CreateCustomerAdapter(connection)
adapterCx.Fill(dts)
DgvData.DataSource = dts.Tables(0)
Call ConfigureGrid()
BtnSave.Enabled = True
LblSibling.Visible = Sibling
Call CalcTotals(ChildID)
LastId = GetLastID()
End Sub
Public Function CreateCustomerAdapter(ByVal connection As SqlConnection) As SqlDataAdapter
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
' Create the SelectCommand.
sqlString = "SELECT [DataID], [ChildID], [ParentID]" &
", [Date], [PaidIn], [Breakfast], [1500-1600], [1600-1630], [1630-1700], [1700-1730]" &
", [ExtraMins], [Total], [TimeOut] FROM [Data] WHERE " &
"[ChildID]=@ChildID ORDER BY [Date] DESC"
Dim command As SqlCommand = New SqlCommand(sqlString, connection)
' Add the parameters for the SelectCommand.
Call LoadParams(command, sqlString)
adapter.SelectCommand = command
' Create the InsertCommand.
' Code commented out for brevity
' Create the UpdateCommand.
sqlString = "UPDATE Data SET " &
"PaidIn=@PMPaidIn, Breakfast=@Breakfast, [1500-1600]=@PM1500, [1600-1630]=@PM1600, " &
"[1630-1700]=@PM1630, [1700-1730]=@PM1700, ExtraMins=@Extra, Total=@PMTotal, TimeOut=@Time" &
"WHERE DataID = @DataID"
command = New SqlCommand(sqlString, connection)
adapter.UpdateCommand.Parameters.Add("@DataID", SqlDbType.Int).Value = DataID
adapter.UpdateCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = ParentID
adapter.UpdateCommand.Parameters.Add("@ChildID", SqlDbType.Int).Value = ChildID
adapter.UpdateCommand.Parameters.Add("@Date", SqlDbType.Date).Value = dteDate
adapter.UpdateCommand.Parameters.Add("@Breakfast", SqlDbType.Decimal).Value = Breakfast
adapter.UpdateCommand.Parameters.Add("@PM1500", SqlDbType.Decimal).Value = PM1500
adapter.UpdateCommand.Parameters.Add("@PM1600", SqlDbType.Decimal).Value = PM1600
adapter.UpdateCommand.Parameters.Add("@PM1630", SqlDbType.Decimal).Value = PM1630
adapter.UpdateCommand.Parameters.Add("@PM1700", SqlDbType.Decimal).Value = PM1700
adapter.UpdateCommand.Parameters.Add("@PMTotal", SqlDbType.Decimal).Value = PMTotal
adapter.UpdateCommand.Parameters.Add("@PMPaidIn", SqlDbType.Decimal).Value = PMPaidIn
adapter.UpdateCommand.Parameters.Add("@Extra", SqlDbType.Decimal).Value = Extra
adapter.UpdateCommand.Parameters.Add("@Discount", SqlDbType.Decimal).Value = PDiscount
adapter.UpdateCommand.Parameters.Add("@Time", SqlDbType.DateTime).Value = tmeOut
adapter.UpdateCommand.Parameters.Add("@PTimeOut", SqlDbType.Time).Value = PTimeOut
adapter.UpdateCommand = command
command.ExecuteNonQuery()
' Create the DeleteCommand.
' Code commented out for brevity
Return adapter
End Function
Private Sub DgvData_Click(sender As System.Object, e As System.EventArgs) Handles DgvData.Click
End Sub
Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
Try
adapt2.MissingSchemaAction = MissingSchemaAction.AddWithKey
cmdBuilder = New SqlCommandBuilder(adapt2)
changes = dts.GetChanges()
If changes IsNot Nothing Then
adapt2.Update(changes)
Call ThumbsUp("Data saved")
End If
Catch ex As Exception
Call ThumbsDown(ex.ToString)
End Try
End Sub
Private Sub BtnExit_Click(sender As Object, e As EventArgs) Handles BtnExit.Click
Form1.ToolStripStatusLabel1.Text = ""
Close()
End Sub
Private Sub DgvData_CellValueChanged(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DgvData.CellValueChanged
If e.RowIndex < 0 Then Exit Sub
row = e.RowIndex
With DgvData.Rows(row)
If IsDBNull(.Cells(e.ColumnIndex).Value) And e.ColumnIndex > 4 And e.ColumnIndex < 12 Then _
.Cells(e.ColumnIndex).Value = "0.00"
DataID = .Cells("DataID").Value
ParentID = .Cells("ParentID").Value
ChildID = .Cells("ChildID").Value
dteDate = .Cells("Date").Value
PMPaidIn = .Cells("PaidIn").Value
Breakfast = .Cells("Breakfast").Value
PM1500 = .Cells("1500-1600").Value
PM1600 = .Cells("1600-1630").Value
PM1630 = .Cells("1630-1700").Value
PM1700 = .Cells("1700-1730").Value
Extra = .Cells("ExtraMins").Value
PTimeOut = .Cells("TimeOut").Value
Dim AddedUp As Double = .Cells("Total").Value = .Cells("Breakfast").Value +
.Cells("1500-1600").Value +
.Cells("1600-1630").Value +
.Cells("1630-1700").Value +
.Cells("1700-1730").Value +
.Cells("ExtraMins").Value
PMTotal = AddedUp
DgvData.EndEdit()
DgvData.Columns(0).Visible = False
DgvData.Columns(1).Visible = False
DgvData.Columns(2).Visible = False
End With
Call CalcCols()
End Sub
End Class
Public Sub ConOpen()
conString = "Data Source=192.168.1.64,1433;Initial Catalog=" & Catalog & ";User ID=" & UserId & ";Password=" & Password
Try
connection = New SqlConnection With {.ConnectionString = conString}
connection.Open()
Catch ex As Exception
MessageBox.Show("", ex.Message)
connection.Close()
End
End Try
End Sub
' Create the UpdateCommand.
sqlString = "UPDATE Data SET PaidIn = @PMPaidIn WHERE DataID =@DiD"
command = New SqlCommand(sqlString, connection)
command.Parameters.Add("@DID", SqlDbType.Int).Value = DiD
PMPaidIn = 5.55
command.Parameters.Add("@PMPaidIn", SqlDbType.Decimal).Value = PMPaidIn
adapter.UpdateCommand = command
command.ExecuteNonQuery()
'command.ExecuteReader()
connection.Close()
sqlString = "UPDATE Data SET PaidIn = @PMPaidIn WHERE DataID =@DiD"
command = New SqlCommand(sqlString, connection)
command.Parameters.Add("@DID", SqlDbType.Int).Value = DiD
'PMPaidIn = 5.55
command.Parameters.Add("@PMPaidIn", SqlDbType.Decimal).Value = PMPaidIn
adapter.UpdateCommand = command
command.ExecuteNonQuery()
connection.Close()
adapter.Fill(dts)
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
DGVData.DataSource = dts.Tables(0)
dts.Tables(0).Rows(5).Item("PaidIn") = dts.Tables(0).Rows(5).Item("PaidIn") + 5
cmdBuilder = New SqlCommandBuilder(adapter)
adapter.Update(dts)
-saige-