ตอนกดปุ่มบันทึกมันขึ้นไดอะล็อกบ๊อกซ์นี้
SqlException was unhandled
implict conversion from data type nvarchar to varbinary is not allowed. use the conver function to run this query.
Private Function IsExist(ByVal room As System.String, ByVal month As System.String, ByVal year As System.String) As Boolean
Dim connection ....
Dim command ....
connection.Open()
Boolean result = If(((command.ExecuteScalar() As Integer) > 0), True, False)
connection.Close()
Return result
End Function
Private Sub ButtonSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSave.Click
If Not IsExist(ComboRoom.SelectedItem, ComboMonth.SelectedItem, ComboYear.SelectedItem) Then
Insert()
Else
Update()
End If
End Sub
Dim aDateVariable As String = "1/2/2012"
Dim commandObject As New SqlCommand()
commandObject.Connection = conn
commandObject.CommandType = CommandType.StoredProcedure
commandObject.Parameters.AddWithValue("@aDateParameter","1/2/2012" )
Imports System.Data.SqlClient
Imports System.Text
Public Class Form1
Private connectionString As String
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.connectionString = "ใส่ connection string เอง"
End Sub
Private Sub ButtonSave_Click(sender As Object, e As EventArgs) Handles ButtonSave.Click
If Me.IsExist() Then
Me.UpdateItem()
Else
Me.InsertItem()
End If
End Sub
Private Function IsExist() As Boolean
Dim result As Boolean
Using connection As New SqlConnection(connectionString)
Using command = connection.CreateCommand()
Dim commandString As New StringBuilder
commandString.AppendLine("SELECT COUNT(*)")
commandString.AppendLine("FROM Paymentt")
commandString.AppendLine("WHERE (RoomNo = @Room) AND (Month = @Month) AND (Year = @Year);")
command.CommandTimeout = 240
command.CommandType = CommandType.Text
command.CommandText = commandString.ToString()
command.Parameters.AddWithValue("@Room", ComboRoom.SelectedItem)
command.Parameters.AddWithValue("@Month", ComboMonth.SelectedItem)
command.Parameters.AddWithValue("@Year", ComboYear.SelectedItem)
Try
connection.Open()
result = If((DirectCast(command.ExecuteScalar(), Integer) > 0), True, False)
connection.Close()
Catch ex As Exception
Me.HandlerException(ex)
result = False
End Try
End Using
End Using
Return result
End Function
Private Sub InsertItem()
Dim rowAffect As Integer : rowAffect = 0
Using connection As New SqlConnection(connectionString)
Using command = connection.CreateCommand()
Dim commandString As New StringBuilder
commandString.AppendLine("INSERT INTO Paymentt")
commandString.AppendLine("(")
commandString.AppendLine("RoomNo,")
commandString.AppendLine("Month,")
commandString.AppendLine("Year,")
commandString.AppendLine("BeforWaterSupply,")
commandString.AppendLine("AfterWaterSupply,")
commandString.AppendLine("TotalUnitWater,")
commandString.AppendLine("WaterSupply,")
commandString.AppendLine("BeforElectricity,")
commandString.AppendLine("AfterElectricity,")
commandString.AppendLine("TotalUnitElectricity,")
commandString.AppendLine("Electricity,")
commandString.AppendLine("Rental,")
commandString.AppendLine("Clean,")
commandString.AppendLine("ToTal,")
commandString.AppendLine("FireUint,")
commandString.AppendLine("WaterUnit,")
commandString.AppendLine("BeginFire,")
commandString.AppendLine("BeginWater")
commandString.AppendLine(")")
commandString.AppendLine("VALUES")
commandString.AppendLine("(")
commandString.AppendLine("@RoomNo,")
commandString.AppendLine("@Month,")
commandString.AppendLine("@Year,")
commandString.AppendLine("@BeforWaterSupply,")
commandString.AppendLine("@AfterWaterSupply,")
commandString.AppendLine("@TotalUnitWater,")
commandString.AppendLine("@WaterSupply,")
commandString.AppendLine("@BeforElectricity,")
commandString.AppendLine("@AfterElectricity,")
commandString.AppendLine("@TotalUnitElectricity,")
commandString.AppendLine("@Electricity,")
commandString.AppendLine("@Rental,")
commandString.AppendLine("@Clean,")
commandString.AppendLine("@ToTal,")
commandString.AppendLine("@FireUint,")
commandString.AppendLine("@WaterUnit,")
commandString.AppendLine("@BeginFire,")
commandString.AppendLine("@BeginWater")
commandString.AppendLine(")")
command.CommandTimeout = 30
command.CommandType = CommandType.Text
command.CommandText = commandString.ToString()
command.Parameters.AddWithValue("@Room", ComboRoom.SelectedItem)
command.Parameters.AddWithValue("@Month", ComboMonth.SelectedItem)
command.Parameters.AddWithValue("@Year", ComboYear.SelectedItem)
command.Parameters.AddWithValue("@BeforW", Textbeforf.Text)
command.Parameters.AddWithValue("@AfterWater", Textafterf.Text)
command.Parameters.AddWithValue("@UnitWater", Textfunit.Text)
command.Parameters.AddWithValue("@Water", TextWater.Text)
command.Parameters.AddWithValue("@BeforElectric", TextOthers.Text)
command.Parameters.AddWithValue("@AfterElectric", Textbeforw.Text)
command.Parameters.AddWithValue("@UnitElectric", Textwunit.Text)
command.Parameters.AddWithValue("@Electric", TextElectric.Text)
command.Parameters.AddWithValue("@rent", TextRental.Text)
command.Parameters.AddWithValue("@cleans", TextOthers.Text)
command.Parameters.AddWithValue("@totals", TextTotal.Text)
command.Parameters.AddWithValue("@UFire", Textunitwater.Text)
command.Parameters.AddWithValue("@UWater", Textunitfire.Text)
command.Parameters.AddWithValue("@BeginF", TextbeginFireUint.Text)
command.Parameters.AddWithValue("@BeginW", TextbeginWaterUnit.Text)
Try
connection.Open()
rowAffect = command.ExecuteNonQuery()
connection.Close()
Me.ExecuteSuccess("Insert complete", rowAffect)
Catch ex As Exception
Me.HandlerException(ex)
End Try
End Using
End Using
End Sub
Private Sub UpdateItem()
Dim rowAffect As Integer : rowAffect = 0
Using connection As New SqlConnection(connectionString)
Using command = connection.CreateCommand()
Dim commandString As New StringBuilder
commandString.AppendLine("UPDATE Paymentt")
commandString.AppendLine("SET")
commandString.AppendLine("BeforWaterSupply = @BeforW,")
commandString.AppendLine("AfterWaterSupply = @AfterWater,")
commandString.AppendLine("TotalUnitWater = @UnitWater,")
commandString.AppendLine("FireUint= @UFire,")
commandString.AppendLine("WaterUnit = @UWater,")
commandString.AppendLine("BeginFire = @BeginF,")
commandString.AppendLine("BeginWater = @BeginW,")
commandString.AppendLine("WaterSupply = @Water,")
commandString.AppendLine("BeforElectricity = @BeforElectric,")
commandString.AppendLine("AfterElectricity = @AfterElectric,")
commandString.AppendLine("TotalUnitElectricity = @UnitElectric,")
commandString.AppendLine("Electricity = @Electric,")
commandString.AppendLine("Rental = @rent,")
commandString.AppendLine("Clean = @cleans,")
commandString.AppendLine("ToTal = @totals")
commandString.AppendLine("WHERE")
commandString.AppendLine("(RoomNo = @Room) AND (Month = @Month) AND (Year = @Year)")
command.CommandTimeout = 30
command.CommandType = CommandType.Text
command.CommandText = commandString.ToString()
command.Parameters.AddWithValue("@BeforW", Textbeforf.Text)
command.Parameters.AddWithValue("@AfterWater", Textafterf.Text)
command.Parameters.AddWithValue("@UnitWater", Textfunit.Text)
command.Parameters.AddWithValue("@Water", TextWater.Text)
command.Parameters.AddWithValue("@BeforElectric", TextOthers.Text)
command.Parameters.AddWithValue("@AfterElectric", Textbeforw.Text)
command.Parameters.AddWithValue("@UnitElectric", Textwunit.Text)
command.Parameters.AddWithValue("@Electric", TextElectric.Text)
command.Parameters.AddWithValue("@rent", TextRental.Text)
command.Parameters.AddWithValue("@cleans", TextOthers.Text)
command.Parameters.AddWithValue("@totals", TextTotal.Text)
command.Parameters.AddWithValue("@UFire", Textunitwater.Text)
command.Parameters.AddWithValue("@UWater", Textunitfire.Text)
command.Parameters.AddWithValue("@BeginF", TextbeginFireUint.Text)
command.Parameters.AddWithValue("@BeginW", TextbeginWaterUnit.Text)
command.Parameters.AddWithValue("@Room", ComboRoom.SelectedItem)
command.Parameters.AddWithValue("@Month", ComboMonth.SelectedItem)
command.Parameters.AddWithValue("@Year", ComboYear.SelectedItem)
Try
connection.Open()
rowAffect = command.ExecuteNonQuery()
connection.Close()
Me.ExecuteSuccess("Update complete", rowAffect)
Catch ex As Exception
Me.HandlerException(ex)
End Try
End Using
End Using
End Sub
Private Sub ExecuteSuccess(ByVal Message As String, ByVal RowAffect As Integer)
MessageBox.Show(String.Format("{0}: {1} records.", Message, RowAffect.ToString("#,##0")), "บันทึกข้อมูลแล้ว")
End Sub
Private Sub HandlerException(ByVal ex As Exception)
MessageBox.Show(ex.Message, "เกิดข้อผิดพลาดในการบันทึกข้อมูล")
End Sub
End Class
Load balance : Server 00
