 |
| |
เช็ค Datagrid View กับ Database ไม่ให้ซ้ำกับข้อมูลที่มีใน Database ตอนนี้ Add เข้าเเล้ว เเต่จะเช็คยังไงไม่ให้ซ้ำ |
| |
 |
|
|
 |
 |
|
Code (VB.NET)
Private Sub Button3_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportdata.Click
Using con As New SqlConnection("data source=tnhkwg-wf;initial catalog=MedicalExpense;uid=sa;pwd=password")
con.Open()
Dim tr As SqlTransaction = con.BeginTransaction
Dim cm As New SqlCommand("insert into MT_Employee(EMP_CODE,EMP_NAME,EMP_SECTION,EMP_WORK,EMP_TYPE,ST_FATHER,ST_MOTHER) values (@a,@b,@c,@d,@e,@f,@g)", con, tr)
With cm
.Parameters.Add("@a", SqlDbType.Int)
.Parameters.Add("@b", SqlDbType.NVarChar)
.Parameters.Add("@c", SqlDbType.NVarChar, 50)
.Parameters.Add("@d", SqlDbType.NVarChar, 50)
.Parameters.Add("@e", SqlDbType.NVarChar, 50)
.Parameters.Add("@f", SqlDbType.NVarChar, 50)
.Parameters.Add("@g", SqlDbType.NVarChar, 50)
'.Parameters.Add("@h", SqlDbType.NVarChar, 50)
''.Parameters.Add("@i", SqlDbType.NVarChar, 50)
'.Parameters.Add("@i", SqlDbType.NVarChar, 50)
End With
For i As Integer = 0 To DataGridView1.RowCount - 1
If Not DataGridView1.Rows(i).IsNewRow Then
cm.Parameters("@a").Value = DataGridView1.Rows(i).Cells(0).Value
cm.Parameters("@b").Value = DataGridView1.Rows(i).Cells(1).Value
cm.Parameters("@c").Value = DataGridView1.Rows(i).Cells(2).Value
cm.Parameters("@d").Value = DataGridView1.Rows(i).Cells(3).Value
cm.Parameters("@e").Value = DataGridView1.Rows(i).Cells(4).Value
cm.Parameters("@f").Value = DataGridView1.Rows(i).Cells(5).Value
cm.Parameters("@g").Value = DataGridView1.Rows(i).Cells(6).Value
'cm.Parameters("@h").Value = DataGridView2.Rows(i).Cells(7).Value
'cm.Parameters("@i").Value = DataGridView2.Rows(i).Cells(8).Value
cm.ExecuteNonQuery()
'Label1.Text = ""
End If
Next
tr.Commit()
Windows.Forms.MessageBox.Show("บันทึกการแก้ไขข้อมูลเรียบร้อยแล้ว", "Database", Windows.Forms.MessageBoxButtons.OK)
DataGridView1.DataSource = dt
End Using
End Sub
Tag : .NET, Ms SQL Server 2005, Windows
|
|
|
 |
 |
 |
 |
| Date :
2015-12-02 09:36:38 |
By :
kooha |
View :
1214 |
Reply :
22 |
|
 |
 |
 |
 |
|
|
|
|
 |
 |
|
 |
 |
| |
|
|
|
| |
|
ลองเพิ่มการเชคลงไปครับ
Code (VB.NET)
Function CheckEMP(emp As String) As Boolean
Dim result As Boolean
Dim con As New SqlConnection("data source=tnhkwg-wf;initial catalog=MedicalExpense;uid=sa;pwd=password")
con.Open()
Dim cmd As New SqlCommand("SELECT EMP_CODE FROM MT_Employee WHERE EMP_CODE like '%" & emp & "%'", con)
Dim count As Integer = cmd.ExecuteNonQuery
result = IIf(count <= 0, False, True)
con.Close()
Return result
End Function
แล้วเปลี่ยน แบบนี้
Code (VB.NET)
Private Sub Button3_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportdata.Click
Using con As New SqlConnection("data source=tnhkwg-wf;initial catalog=MedicalExpense;uid=sa;pwd=password")
con.Open()
Dim tr As SqlTransaction = con.BeginTransaction
Dim cm As New SqlCommand("insert into MT_Employee(EMP_CODE,EMP_NAME,EMP_SECTION,EMP_WORK,EMP_TYPE,ST_FATHER,ST_MOTHER) values (@a,@b,@c,@d,@e,@f,@g)", con, tr)
With cm
.Parameters.Add("@a", SqlDbType.Int)
.Parameters.Add("@b", SqlDbType.NVarChar)
.Parameters.Add("@c", SqlDbType.NVarChar, 50)
.Parameters.Add("@d", SqlDbType.NVarChar, 50)
.Parameters.Add("@e", SqlDbType.NVarChar, 50)
.Parameters.Add("@f", SqlDbType.NVarChar, 50)
.Parameters.Add("@g", SqlDbType.NVarChar, 50)
'.Parameters.Add("@h", SqlDbType.NVarChar, 50)
''.Parameters.Add("@i", SqlDbType.NVarChar, 50)
'.Parameters.Add("@i", SqlDbType.NVarChar, 50)
End With
For i As Integer = 0 To DataGridView1.RowCount - 1
If CheckEMP(DataGridView1(0,i).Value.ToString()) = True Then
cm.Parameters("@a").Value = DataGridView1(0,i).Value
cm.Parameters("@b").Value = DataGridView1(1,i).Value
cm.Parameters("@c").Value = DataGridView1(2,i).Value
cm.Parameters("@d").Value = DataGridView1(3,i).Value
cm.Parameters("@e").Value = DataGridView1(4,i).Value
cm.Parameters("@f").Value = DataGridView1(5,i).Value
cm.Parameters("@g").Value = DataGridView1(6,i).Value
'cm.Parameters("@h").Value = DataGridView2(7,i).Value
'cm.Parameters("@i").Value = DataGridView2(8,i).Value
cm.ExecuteNonQuery()
'Label1.Text = ""
End If
Next
tr.Commit()
Windows.Forms.MessageBox.Show("บันทึกการแก้ไขข้อมูลเรียบร้อยแล้ว", "Database", Windows.Forms.MessageBoxButtons.OK)
DataGridView1.DataSource = dt
End Using
End Sub
ถ้าแค่ Insert ก็ประมาณนี้ครับ ยังเหลือ Update อีกใช่ไม๊ 555
|
| |
|
|
| |
| Date :
2015-12-02 10:14:02 |
By :
lamaka.tor |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
check null value ด้วยครับ
|
| |
|
|
| |
| Date :
2015-12-02 10:57:53 |
By :
lamaka.tor |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
Code (VB.NET)
If Not String.IsNullOrEmpty(DataGridView1(0,i).Value.ToString()) And CheckEMP(DataGridView1(0,i).Value.ToString()) = False Then
บ้านๆ ก็น่าจะประมาณนี้ครับ
|
| |
|
|
| |
| Date :
2015-12-02 11:01:03 |
By :
lamaka.tor |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
Code (VB.NET)
Private Sub Button3_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportdata.Click
Using con As New SqlConnection("data source=tnhkwg-wf;initial catalog=MedicalExpense;uid=sa;pwd=password")
con.Open()
Dim tr As SqlTransaction = con.BeginTransaction
Dim cm As New SqlCommand("insert into MT_Employee(EMP_CODE,EMP_NAME,EMP_SECTION,EMP_WORK,EMP_TYPE,ST_FATHER,ST_MOTHER) values (@a,@b,@c,@d,@e,@f,@g)", con, tr)
With cm
.Parameters.Add("@a", SqlDbType.Int)
.Parameters.Add("@b", SqlDbType.NVarChar)
.Parameters.Add("@c", SqlDbType.NVarChar, 50)
.Parameters.Add("@d", SqlDbType.NVarChar, 50)
.Parameters.Add("@e", SqlDbType.NVarChar, 50)
.Parameters.Add("@f", SqlDbType.NVarChar, 50)
.Parameters.Add("@g", SqlDbType.NVarChar, 50)
'.Parameters.Add("@h", SqlDbType.NVarChar, 50)
''.Parameters.Add("@i", SqlDbType.NVarChar, 50)
'.Parameters.Add("@i", SqlDbType.NVarChar, 50)
End With
For i As Integer = 0 To DataGridView1.RowCount - 1
If Not String.IsNullOrEmpty(DataGridView1(0, i).Value.ToString()) And CheckEMP(DataGridView1(0, i).Value.ToString()) = False Then
cm.Parameters("@a").Value = DataGridView1(0, i).Value
cm.Parameters("@b").Value = DataGridView1(1, i).Value
cm.Parameters("@c").Value = DataGridView1(2, i).Value
cm.Parameters("@d").Value = DataGridView1(3, i).Value
cm.Parameters("@e").Value = DataGridView1(4, i).Value
cm.Parameters("@f").Value = DataGridView1(5, i).Value
cm.Parameters("@g").Value = DataGridView1(6, i).Value
'cm.Parameters("@h").Value = DataGridView2(7,i).Value
'cm.Parameters("@i").Value = DataGridView2(8,i).Value
cm.ExecuteNonQuery()
'Label1.Text = ""
End If
Next
tr.Commit()
Windows.Forms.MessageBox.Show("บันทึกการแก้ไขข้อมูลเรียบร้อยแล้ว", "Database", Windows.Forms.MessageBoxButtons.OK)
DataGridView1.DataSource = dt
End Using
End Sub
|
| |
|
|
| |
| Date :
2015-12-02 11:19:19 |
By :
kooha |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
แปลกดีนะครับ
ไม่น่าจะมี dup ได้ เพราะมัน return มาจาก CheckEMP(DataGridView1(0, i).Value.ToString())
ลองตรวจตามนี้ครับ
Dim count As Integer = cmd.ExecuteNonQuery
result = IIf(count <= 0, False, True)
1. ตรวจว่า count มีเท่าไหร่
2. result ที่ออกมาถูกต้องหรือป่าว คือถ้ามี EMP_CODE ที่เราใส่เข้าไปใน MT_Employee มันควรจะเป็น true ถ้่าไม่มีควรจะเป็น False
|
| |
|
|
| |
| Date :
2015-12-02 13:13:42 |
By :
lamaka.tor |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
กลับมาตอบพร้อมกับขออภัยด้วยครับ
รบกวนช่วยแก้เป็นแบบนี้ด้วยครับ
Code (VB.NET)
Function CheckEMP(emp As String) As Boolean
Dim result As Boolean
Dim con As New SqlConnection("data source=tnhkwg-wf;initial catalog=MedicalExpense;uid=sa;pwd=password")
con.Open()
Dim cmd As New SqlCommand("SELECT EMP_CODE FROM MT_Employee WHERE EMP_CODE like '%" & emp & "%'", con)
result = IIf(cmd.ExecuteReader().HasRows(), True, False)
con.Close()
Return result
End Function
คราวนี้รับรองได้ครับ
|
| |
|
|
| |
| Date :
2015-12-02 13:36:25 |
By :
lamaka.tor |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ลองเปลี่ยนจาก Not String.IsNullOrEmpty(DataGridView1(0, i).Value.ToString())
เป็น
DataGridView1(0, i).Value= Nothing
ดูครับ
|
| |
|
|
| |
| Date :
2015-12-02 14:30:23 |
By :
lamaka.tor |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
Convert.ToString(DataGridView1(0, i).Value) = String.Empty
ครับได้ชัว ถ้าไม่มั่วนิ่ม 5555
|
| |
|
|
| |
| Date :
2015-12-02 15:19:53 |
By :
lamaka.tor |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
Convert.ToString(DataGridView1(0, i).Value) = String.Empty
ผมลองแล้วได้นะครับ
Error ไรครับ
|
| |
|
|
| |
| Date :
2015-12-02 18:46:49 |
By :
lamaka.tor |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
Dim tr As SqlTransaction = con.BeginTransaction
ในเมื่อคุณใช้ BeginTransaction มันจะซ้ำได้ไงครับ
นอกซะจาก Database คุณ ไม่ได้กำหนด PK ครับ
----- ลองใช้ Try ,Catch อาจช่วยได้ครับ --------------
Try
BeginTrans()
''' --------- คำสั่ง update insert delete ------------------
Trans.Commit()
Catch ex As Exception
Trans.Rollback()
Finally
conDB.Close()
End Try
-------- ผิดพลาดขออภัยครับ (มือใหม่ครับ) ---------
|
ประวัติการแก้ไข
2015-12-02 19:29:01
| |
|
|
| |
| Date :
2015-12-02 19:26:13 |
By :
weerachai.va |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
If Convert.ToString(DataGridView1(0, i).Value) = String.Empty Then
Windows.Forms.MessageBox.Show("String.Empty ")
End If
If CheckEMP(DataGridView1(0, i).Value.ToString()) = False Then
Windows.Forms.MessageBox.Show("False")
End If
นี่คือการเช็คแบบบ้านๆครับ
|
| |
|
|
| |
| Date :
2015-12-03 08:26:22 |
By :
lamaka.tor |
|
|
| |
|
|
|
|
|
| |
|
|
|
|
|
