 |
| |
รบกวนช่วยดูคำสั่ง command.ExecuteNonQuery() มันเอ่อเร่อ |
| |
 |
|
|
 |
 |
|
Code
Private Sub ButtonSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSave.Click
sql = "SELECT COUNT(*) FROM Paymentt WHERE " & _
"(RoomNo = @r) AND (Month = @m) AND (Year = @y)"
AddParameters(sql)
Dim count As Integer = command.ExecuteScalar()
If count = 0 Then
sql = "INSERT INTO Paymentt(" & _
"RoomNo, Month, Year, BeforWaterSupply, AfterWaterSupply, TotalUnitWater, WaterSupply,BeforElectricity, AfterElectricity,TotalUnitElectricity,Electricity,Rental,Clean,ToTal,FireUint,WaterUnit,BeginFire,BeginWater)" & _
"VALUES(@room, @month, @year, @BeforW, @AfterWater, @UnitWater, @Water, @BeforElectric, @AfterElectric, @UnitElectric, @Electric, @rent, @cleans, @totals,@UFire,@UWater,@BeginF,@BeginW)"
Else
sql = "UPDATE Paymentt SET " & _
"RoomNo = @room, Month = @month, Year = @year, BeforWaterSupply = @BeforW,AfterWaterSupply = @AfterWater,TotalUnitWater = @UnitWater, FireUin t= @UFire, WaterUnit = @UWater, BeginFire = @BeginF, BeginWater = @BeginW" & _
"WaterSupply = @Water, BeforElectricity = @BeforElectric, AfterElectricity = @AfterElectric, TotalUnitElectricity = @UnitElectric, Electricity = @Electric, Rental = @rent, Clean = @cleans, ToTal = @totals " & _
"WHERE (RoomNo = @r) AND (Month = @m) AND (Year = @y)"
End If
command.Parameters.Clear()
command.CommandText = sql
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)
If count > 0 Then
command.Parameters.AddWithValue("r", ComboRoom.SelectedItem)
command.Parameters.AddWithValue("m", ComboMonth.SelectedItem)
command.Parameters.AddWithValue("y", ComboYear.SelectedItem)
End If
Dim r As Integer = command.ExecuteNonQuery() << รองรันดูแล้วมันขึ้นเอ่อเร่อตรงนี้ ต้องแก้ยังไงเหรอค่ะรบกวนช่วยบอกที -/\- ขอบคุณค่ะ
If r > 0 Then
MessageBox.Show("บันทึกข้อมูลแล้ว")
Else
MessageBox.Show("เกิดข้อผิดพลาดในการบันทึกข้อมูล")
End If
End Sub
Tag : .NET, Win (Windows App), VB.NET
|
|
|
 |
 |
 |
 |
| Date :
2014-10-26 20:31:49 |
By :
kkan |
View :
3357 |
Reply :
12 |
|
 |
 |
 |
 |
|
|
|
|
 |
 |
|
 |
 |
| |
|
|
|
| |
|
Error อะไรก็ไม่บอก
|
| |
|
|
| |
| Date :
2014-10-27 07:20:57 |
By :
zarooman |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
มันควรประกาศตัวแปร sql ก่อนไหมครับ
dim sql as String;
Dim count As Integer = command.ExecuteScalar() บันทัดนี้
ควรมีคำสั่งอะไรมาทำก่อน หรือเปล่าครับ
|
| |
|
|
| |
| Date :
2014-10-27 08:19:09 |
By :
Chaidhanan |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ตอนกดปุ่มบันทึกมันขึ้นไดอะล็อกบ๊อกซ์นี้
SqlException was unhandled
implict conversion from data type nvarchar to varbinary is not allowed. use the conver function to run this query.
คือต้องการให้ตรวจสอบว่าเคยได้บันทึกข้อมูลในเดือนที่เลือกหรือยัง ถ้ายังไม่มีข้อมูลให้บันทึกข้อมูลในตาราง แต่ถ้ามีข้อมูลอยู่แล้วให้อ่านค่าแล้วอัพเดทข้อมูลเดิม ประมาณนี้นะค่ะ
Add Parameter ถ้าใส่ @ มันขึ้นSyntax error ค่ะ
(เพิ่งเริ่มศึกษาแล้วทำตามหนังสือดู แต่ทำแล้วมันขึ้นเอ่อเร่อ)
|
ประวัติการแก้ไข
2014-10-28 14:21:05
2014-10-28 14:25:03
| |
|
|
| |
| Date :
2014-10-28 14:18:50 |
By :
kkan |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
.php 
|
| |
|
|
| |
| Date :
2014-10-28 14:28:44 |
By :
ห้ามตอบเกินวันละ 2 กระทู้ |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ตั้งแต่บรรทัด
command.Parameters.AddWithValue("room", ComboRoom.SelectedItem)
ไล่มาเลยพวก AddWithValue ให้ใส่ @ ด้วยทุกบรรทัดเลย
command.Parameters.AddWithValue("@room", ComboRoom.SelectedItem)
|
| |
|
|
| |
| Date :
2014-10-28 14:49:58 |
By :
gunnermontana |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
Code (VB.NET)
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
แต่จริงๆ มันแหม่งๆ ตรง AddParameters(sql) นะ
ทำไมโผล่มาอีกทีเป็น ExecuteScalar เฉยเลย
|
| |
|
|
| |
| Date :
2014-10-28 15:53:28 |
By :
ห้ามตอบเกินวันละ 2 กระทู้ |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
http://jwcooney.com/2012/09/10/asp-net-choosing-parameters-add-or-parameters-addwithvalue/
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" )
มันต้องเซ็ต CommandType ใหม่ป่ะครับ(อันนี้ไม่รู้) ไม้งั้นก็น่าจะเป็นที่ SQL
ไม่ลองทำแยกดูระหว่าง INSERT กะ UPDATE จะได้ตัดปัญหาเรื่อง SQL ได้
|
| |
|
|
| |
| Date :
2014-10-28 16:06:32 |
By :
lamaka.tor |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
นายทำแบบนี้แล้วกัน
1. ตรวจสอบสอบข้อมูลว่ามีอยู่แล้วหรือไม่ (is exist)
2. ถ้ามี -> update
3. ถ้าไม่มี -> insert
ส่วนการติดต่อ database หลักๆ เลยก็คือ
1. สร้าง connection
2. สร้าง command
3. execute command
Code (VB.NET)
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
|
| |
|
|
| |
| Date :
2014-10-29 08:48:20 |
By :
ห้ามตอบเกินวันละ 2 กระทู้ |
|
|
| |
|
|
|
|
|
| |
|
|
|
|
|
Load balance : Server 04
