Private Sub Button2_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
sql = "insert into savegoods_production(save_pro_id,pro_id,goods_name,goods_unit,goods_amount,goods_price,emp_name,emp_tell,date_save) values "
Dim i As Integer = Me.ListView1.Items.Count - 1
For j As Integer = 0 To i
Dim po_id As String = Me.ListView1.Items(j).SubItems(1).Text
Dim goods_name As String = Me.ListView1.Items(j).SubItems(2).Text
Dim goods_unit As String = Me.ListView1.Items(j).SubItems(3).Text
Dim goods_amount As Double = Me.ListView1.Items(j).SubItems(4).Text
Dim goods_price As Double = Me.ListView1.Items(j).SubItems(5).Text
Dim em_name As String = Me.em_name.Text
Dim em_tell As String = Me.em_tell.Text
Dim save_date As Date = Me.lbl_date_now.Text
If j = i Then
sql &= String.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')", Me.lbl_save_id.Text, po_id, goods_name, goods_unit, goods_amount, goods_price, em_name, em_tell, save_date)
Else
sql &= String.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}') ,", Me.lbl_save_id.Text, po_id, goods_name, goods_unit, goods_amount, goods_price, em_name, em_tell, save_date)
End If
Next
cmd = New SqlClient.SqlCommand(sql, cn)
If cmd.ExecuteNonQuery = 0 Then
MsgBox("ไม่สามารถบันทึกได้")
Else
MsgBox("บันทึกสำเร็จ")
Dim p As Integer = Me.ListView1.Items.Count - 1
For j As Integer = 0 To p
Dim goods_name As String = Me.ListView1.Items(j).SubItems(2).Text
Dim goods_unit As String = Me.ListView1.Items(j).SubItems(3).Text
Dim goods_amount As Double = Me.ListView1.Items(j).SubItems(4).Text
Dim goods_price As Double = Me.ListView1.Items(j).SubItems(5).Text
'If j = p Then
Dim sql2 As String = "select goods_name from stock_goods where goods_name ='" & goods_name & "'"
Dim cmd As New SqlCommand(sql2, cn)
Dim dr As SqlDataReader
dr = cmd.ExecuteReader
If dr.Read >= 0 Then
sql = "insert into stock_goods(goods_name,goods_unit,goods_amount,goods_price) values "
If j = p Then
sql &= String.Format("('{0}','{1}','{2}','{3}')", goods_name, goods_unit, goods_amount, goods_price)
Else
sql &= String.Format("('{0}','{1}','{2}','{3}') ,", goods_name, goods_unit, goods_amount, goods_price)
End If
Else
sql = "update stock_goods set goods_amount = goods_amount+ " & goods_amount & " where goods_name='" & goods_name & "'"
End If
'End If
Next
cmd = New SqlClient.SqlCommand(sql, cn)
If cmd.ExecuteNonQuery = 0 Then
MsgBox("ไม่สามารถบันทึกได้")
Else
MsgBox("บันทึกสำเร็จ")
End If
End If
End Sub
ในส่วนที่มีปัญหาคือส่วนของ
โคดที่มีปัญหาในการเพิ่มและอัพเดท
Dim p As Integer = Me.ListView1.Items.Count - 1
For j As Integer = 0 To p
Dim goods_name As String = Me.ListView1.Items(j).SubItems(2).Text
Dim goods_unit As String = Me.ListView1.Items(j).SubItems(3).Text
Dim goods_amount As Double = Me.ListView1.Items(j).SubItems(4).Text
Dim goods_price As Double = Me.ListView1.Items(j).SubItems(5).Text
'If j = p Then
Dim sql2 As String = "select goods_name from stock_goods where goods_name ='" & goods_name & "'"
Dim cmd As New SqlCommand(sql2, cn)
Dim dr As SqlDataReader
dr = cmd.ExecuteReader
If dr.Read >= 0 Then
sql = "insert into stock_goods(goods_name,goods_unit,goods_amount,goods_price) values "
If j = p Then
sql &= String.Format("('{0}','{1}','{2}','{3}')", goods_name, goods_unit, goods_amount, goods_price)
Else
sql &= String.Format("('{0}','{1}','{2}','{3}') ,", goods_name, goods_unit, goods_amount, goods_price)
End If
Else
sql = "update stock_goods set goods_amount = goods_amount+ " & goods_amount & " where goods_name='" & goods_name & "'"
End If
'End If
Next
cmd = New SqlClient.SqlCommand(sql, cn)
If cmd.ExecuteNonQuery = 0 Then
MsgBox("ไม่สามารถบันทึกได้")
Else
MsgBox("บันทึกสำเร็จ")
End If
Tag : .NET, Ms SQL Server 2008, Web (ASP.NET), VB.NET
Sub Update_Data()
Dim sql2 As String = "select goods_name from stock_goods where goods_name ='" & textbox.text & "'"
if Sql2 = 0 then
เพิ่มเข้าไปใหม่
else
บวกจำนวนเจ้าไป
end if
End Sub
If j = p Then
sql &= String.Format("('{0}','{1}','{2}','{3}')", goods_name, goods_unit, goods_amount, goods_price)
Else
sql &= String.Format("('{0}','{1}','{2}','{3}') ,", goods_name, goods_unit, goods_amount, goods_price)
End If
debug โค้ดได้ไหมครับ
แล้วตรวจสอบค่าตัวแปร sql หลังจากที่ต่อสตริงเสร็จแล้วได้ค่าอะไร
ได้มาแล้วไปรันใน sql management studio
รับรองเจอคำตอบครับ