 |
|
VB.Net Save ข้อมูลจาก DataTable ลง Sql Server แนะนำด้วยครับ |
|
 |
|
|
 |
 |
|
จะ Add ค่าลง DataTable ก่อน เมื่อ Add ค่าตามที่ต้องการแล้วจะ Save ลง DB

Code (VB.NET)
Imports System.Data.SqlClient
Public Class Manage_Package
Inherits System.Web.UI.Page
Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim strConnString As String
Dim dt As DataTable
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If IsPostBack Then
LoadSizeData()
End If
End Sub
Public Sub ConnectDB()
strConnString = ConfigurationManager.ConnectionStrings("con").ConnectionString
objConn = New SqlConnection(strConnString)
End Sub
Private Sub LoadSizeData()
If IsNothing(Session("SizeTable")) Then
dt = New DataTable()
dt.Columns.Add(New DataColumn("TopWidth", GetType(String)))
dt.Columns.Add(New DataColumn("MidWidth", GetType(String)))
dt.Columns.Add(New DataColumn("BottomWidth", GetType(String)))
dt.Columns.Add(New DataColumn("WidthAVG", GetType(String)))
dt.Columns.Add(New DataColumn("LeftLength", GetType(String)))
dt.Columns.Add(New DataColumn("RigthLength", GetType(String)))
dt.Columns.Add(New DataColumn("LengthAVG", GetType(String)))
Session("SizeTable") = dt
End If
dt = DirectCast(Session("SizeTable"), DataTable)
gvST_LoadSizeTest.DataSource = dt
gvST_LoadSizeTest.DataBind()
End Sub
Private Sub AddToTable()
Dim dr As DataRow = dt.NewRow
dr("TopWidth") = txtPK_WidthTop.Text.Trim
dr("MidWidth") = txtPK_WidthMid.Text.Trim
dr("BottomWidth") = txtPK_WidthBottom.Text.Trim
dr("WidthAVG") = ((CType(txtPK_WidthTop.Text.Trim, Decimal) + CType(txtPK_WidthMid.Text.Trim, Decimal) + CType(txtPK_WidthBottom.Text.Trim, Decimal)) / 3).ToString("n2")
dr("LeftLength") = txtPK_LengthLeft.Text.Trim
dr("RigthLength") = txtPK_LengthRigth.Text.Trim
dr("LengthAVG") = ((CType(txtPK_LengthLeft.Text.Trim, Decimal) + CType(txtPK_LengthRigth.Text.Trim, Decimal)) / 2).ToString("n2")
dt.Rows.Add(dr)
Session("SizeTable") = dt
LoadSizeData()
Reset()
End Sub
Private Sub Reset()
txtPK_WidthTop.Text = String.Empty
txtPK_WidthMid.Text = String.Empty
txtPK_WidthBottom.Text = String.Empty
txtPK_LengthLeft.Text = String.Empty
txtPK_LengthRigth.Text = String.Empty
End Sub
Private Sub ResetAll()
Reset()
Response.Redirect("~/Manage_Package.aspx")
End Sub
Protected Sub btnPK_AddTo_Click(sender As Object, e As EventArgs) Handles btnPK_AddTo.Click
AddToTable()
End Sub
Protected Sub btnPK_Cancel_Click(sender As Object, e As EventArgs) Handles btnPK_Cancel.Click
ResetAll()
End Sub
End Class
Tag : .NET, Ms SQL Server 2008, Web (ASP.NET), VB.NET, VS 2012 (.NET 4.x)
|
ประวัติการแก้ไข 2017-02-07 16:25:49
|
 |
 |
 |
 |
Date :
2017-02-07 16:25:14 |
By :
NUTKA |
View :
4192 |
Reply :
9 |
|
 |
 |
 |
 |
|
|
|
 |
 |
|
 |
 |
 |
|
|
 |
 |
|
ติดตรงไหนเหรอครับ
ถ้าเป็นตอนบันทึกก็วนลูปใน datatable แล้วบันทึกได้เลยครับ
for each dr as datarow in dt.row
....
next
|
 |
 |
 |
 |
Date :
2017-02-08 07:54:52 |
By :
fonfire |
|
 |
 |
 |
 |
|
|
 |
 |
|
 |
 |
 |
|
|
 |
 |
|

ต้องแก้อะไรครับ
|
 |
 |
 |
 |
Date :
2017-02-08 10:09:12 |
By :
NUTKA |
|
 |
 |
 |
 |
|
|
 |
 |
|
 |
 |
 |
|
|
 |
 |
|
ผมก๊อปโค้ดมาจากด้านบนน่ะครับ
ไม่ได้เทสน่ะครับ พิมพ์ใหม่เลย
เอาไปเติม ๆ เอาน่ะครับ
Code (VB.NET)
dt = DirectCast(Session("SizeTable"), DataTable)
strSql="Insert into TableName(F1,F2,F3,F4) Values(@f1,@f2,@f3,@f4) "
objConn.Open
objCmd= New Sqlcommand(strSql,objConn)
For Each dr As Datarow in dt.Rows
with objCmd
คำสั่งเพิ่ม parameter T_T
End with
objCmd.ExecuteNonQuery
objCmd.Parameters.Clear
Next
|
 |
 |
 |
 |
Date :
2017-02-08 10:18:11 |
By :
fonfire |
|
 |
 |
 |
 |
|
|
 |
 |
|
 |
 |
 |
|
|
 |
 |
|
ตัวอย่างการ Loop เพือ่ Insert ครับ
Code (VB.NET)
objConn.ConnectionString = strConnString
objConn.Open()
'*** Insert to orders ***'
strSQL = "INSERT INTO orders (OrderDate,Name,Address,Tel,Email) " & _
" VALUES " & _
" (@sOrderDate,@sName,@sAddress,@sTel,@sEmail)"
Dim objCmd As New SqlCommand(strSQL, objConn)
objCmd.Parameters.Add("@sOrderDate", SqlType.Date).Value = Now()
objCmd.Parameters.Add("@sName", SqlType.VarChar).Value = Me.txtName.Text
objCmd.Parameters.Add("@sAddress", SqlType.VarChar).Value = Me.txtAddress.Text
objCmd.Parameters.Add("@sTel", SqlType.VarChar).Value = Me.txtTel.Text
objCmd.Parameters.Add("@sEmail", SqlType.VarChar).Value = Me.txtEmail.Text
objCmd.ExecuteNonQuery()
'*** Insert to orders_detail ***'
dt2 = DirectCast(Session("myCart"), DataTable)
For i = 0 To dt2.Rows.Count - 1
strSQL = "INSERT INTO orders_detail (OrderID,ProductID,Qty) " & _
" VALUES " & _
" ('" & strOrderID & "','" & dt2.Rows(i)("ProductID") & "','" & dt2.Rows(i)("Qty") & "')"
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With
Next
objConn.Close()
objConn = Nothing
|
 |
 |
 |
 |
Date :
2017-02-08 10:24:04 |
By :
mr.win |
|
 |
 |
 |
 |
|
|
 |
 |
|
 |
 |
 |
|
|
 |
 |
|
Code นี้ Error : {"Column 'S_WIDTHTOP' does not belong to table ."} แบบนี้ครับ
Package table

Size table

Code (VB.NET)
Imports System.Data.SqlClient
Imports System.Globalization
Public Class Manage_Package
Inherits System.Web.UI.Page
Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim strConnString, strSQL As String
Dim dtAdapter As SqlDataAdapter
Dim ds As DataSet
Dim dt As DataTable
Dim result As Integer
Dim TopWidth As Decimal
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If IsPostBack Then
LoadSizeData()
End If
lblPK_AddReceiveDate.Text = Format(Now.Date.ToString("dd/MM/yyyy", New CultureInfo("en-US")))
End Sub
Public Sub ConnectDB()
strConnString = ConfigurationManager.ConnectionStrings("con").ConnectionString
objConn = New SqlConnection(strConnString)
End Sub
Private Sub LoadSizeData()
If IsNothing(Session("SizeTable")) Then
dt = New DataTable()
dt.Columns.Add(New DataColumn("TopWidth", GetType(String)))
dt.Columns.Add(New DataColumn("MidWidth", GetType(String)))
dt.Columns.Add(New DataColumn("BottomWidth", GetType(String)))
dt.Columns.Add(New DataColumn("WidthAVG", GetType(String)))
dt.Columns.Add(New DataColumn("LeftLength", GetType(String)))
dt.Columns.Add(New DataColumn("RigthLength", GetType(String)))
dt.Columns.Add(New DataColumn("LengthAVG", GetType(String)))
Session("SizeTable") = dt
End If
dt = DirectCast(Session("SizeTable"), DataTable)
gvS_LoadSize.DataSource = dt
gvS_LoadSize.DataBind()
End Sub
Private Sub AddToTable()
Dim dr As DataRow = dt.NewRow
dr("TopWidth") = txtPK_WidthTop.Text.Trim
dr("MidWidth") = txtPK_WidthMid.Text.Trim
dr("BottomWidth") = txtPK_WidthBottom.Text.Trim
dr("WidthAVG") = ((CType(txtPK_WidthTop.Text.Trim, Decimal) + CType(txtPK_WidthMid.Text.Trim, Decimal) + CType(txtPK_WidthBottom.Text.Trim, Decimal)) / 3).ToString("n2")
dr("LeftLength") = txtPK_LengthLeft.Text.Trim
dr("RigthLength") = txtPK_LengthRigth.Text.Trim
dr("LengthAVG") = ((CType(txtPK_LengthLeft.Text.Trim, Decimal) + CType(txtPK_LengthRigth.Text.Trim, Decimal)) / 2).ToString("n2")
dt.Rows.Add(dr)
Session("SizeTable") = dt
LoadSizeData()
Reset()
End Sub
Private Sub AddToDB()
ConnectDB()
objConn.Open()
strSQL = "INSERT INTO Package (PK_RECEIVEDATE) " &
" VALUES " &
" (@PK_RECEIVEDATE)"
Dim objCmd As New SqlCommand(strSQL, objConn)
objCmd.Parameters.Add("@PK_RECEIVEDATE", SqlDbType.Date).Value = Format(Now.Date.ToString("dd-MM-yyyy", New CultureInfo("en-US")))
objCmd.ExecuteNonQuery()
dt = DirectCast(Session("SizeTable"), DataTable)
For i = 0 To dt.Rows.Count - 1
strSQL = "INSERT INTO Size (S_WIDTHTOP,S_WIDTHMID,S_WIDTHBOTTOM,S_LENGTHLEFT,S_LENGTHRIGTH) " &
" VALUES " &
" ('" & dt.Rows(i)("S_WIDTHTOP") & "','" & dt.Rows(i)("S_WIDTHMID") & "','" & dt.Rows(i)("S_WIDTHBOTTOM") & "','" & dt.Rows(i)("S_LENGTHLEFT") & "','" & dt.Rows(i)("S_LENGTHRIGTH") & "')"
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With
Next
objConn.Close()
objConn = Nothing
End Sub
Private Sub Reset()
txtPK_WidthTop.Text = String.Empty
txtPK_WidthMid.Text = String.Empty
txtPK_WidthBottom.Text = String.Empty
txtPK_LengthLeft.Text = String.Empty
txtPK_LengthRigth.Text = String.Empty
End Sub
Private Sub ResetAll()
Reset()
Response.Redirect("~/Manage_Package.aspx")
End Sub
Protected Sub btnPK_AddTo_Click(sender As Object, e As EventArgs) Handles btnPK_AddTo.Click
AddToTable()
End Sub
Protected Sub btnPK_Cancel_Click(sender As Object, e As EventArgs) Handles btnPK_Cancel.Click
ResetAll()
End Sub
Protected Sub btnPK_Save_Click(sender As Object, e As EventArgs) Handles btnPK_Save.Click
AddToDB()
End Sub
End Class
|
ประวัติการแก้ไข 2017-02-08 11:29:47
 |
 |
 |
 |
Date :
2017-02-08 11:26:15 |
By :
NUTKA |
|
 |
 |
 |
 |
|
|
 |
 |
|
 |
 |
 |
|
|
 |
 |
|
ชื่อฟิลด์ตรงนี้
Code (VB.NET)
dt.Rows(i)("S_WIDTHTOP")
ต้องตรงกับที่เราตั้งไว้ครับ
If IsNothing(Session("SizeTable")) Then
dt = New DataTable()
dt.Columns.Add(New DataColumn("TopWidth", GetType(String)))
dt.Columns.Add(New DataColumn("MidWidth", GetType(String)))
dt.Columns.Add(New DataColumn("BottomWidth", GetType(String)))
dt.Columns.Add(New DataColumn("WidthAVG", GetType(String)))
dt.Columns.Add(New DataColumn("LeftLength", GetType(String)))
dt.Columns.Add(New DataColumn("RigthLength", GetType(String)))
dt.Columns.Add(New DataColumn("LengthAVG", GetType(String)))
Session("SizeTable") = dt
End If
มันน่าจะเป็น dt.Rows(i)("TopWidth") หรือปล่าวครับ
|
 |
 |
 |
 |
Date :
2017-02-08 11:56:34 |
By :
fonfire |
|
 |
 |
 |
 |
|
|
 |
 |
|
 |
 |
 |
|
|
 |
 |
|
Package เป็นตารางหลังโดยมี Size เป็นส่วนประกอบ
คือ ผมต้องการนำ ID ของ Package ที่เป็น PK ในตารางตัวมันเอง
แต่เป็น FK ในตาราง Size
แล้วผมจะบันทึก ID ของ Package ลงตาราง Size ยังไงครับ
Package table

Size table

Code (VB.NET)
Imports System.Data.SqlClient
Imports System.Globalization
Public Class Manage_Package
Inherits System.Web.UI.Page
Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim strConnString, strSQL As String
Dim dtAdapter As SqlDataAdapter
Dim ds As DataSet
Dim dt As DataTable
Dim result As Integer
Dim TopWidth As Decimal
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If IsPostBack Then
LoadSizeData()
End If
lblPK_AddReceiveDate.Text = Format(Now.Date.ToString("dd/MM/yyyy", New CultureInfo("en-US")))
End Sub
Public Sub ConnectDB()
strConnString = ConfigurationManager.ConnectionStrings("con").ConnectionString
objConn = New SqlConnection(strConnString)
End Sub
Private Sub LoadSizeData()
If IsNothing(Session("SizeTable")) Then
dt = New DataTable()
dt.Columns.Add(New DataColumn("TopWidth", GetType(String)))
dt.Columns.Add(New DataColumn("MidWidth", GetType(String)))
dt.Columns.Add(New DataColumn("BottomWidth", GetType(String)))
dt.Columns.Add(New DataColumn("WidthAVG", GetType(String)))
dt.Columns.Add(New DataColumn("LeftLength", GetType(String)))
dt.Columns.Add(New DataColumn("RigthLength", GetType(String)))
dt.Columns.Add(New DataColumn("LengthAVG", GetType(String)))
Session("SizeTable") = dt
End If
dt = DirectCast(Session("SizeTable"), DataTable)
gvS_LoadSize.DataSource = dt
gvS_LoadSize.DataBind()
End Sub
Private Sub AddToTable()
Dim dr As DataRow = dt.NewRow
dr("TopWidth") = txtPK_WidthTop.Text.Trim
dr("MidWidth") = txtPK_WidthMid.Text.Trim
dr("BottomWidth") = txtPK_WidthBottom.Text.Trim
dr("WidthAVG") = ((CType(txtPK_WidthTop.Text.Trim, Decimal) + CType(txtPK_WidthMid.Text.Trim, Decimal) + CType(txtPK_WidthBottom.Text.Trim, Decimal)) / 3).ToString("n2")
dr("LeftLength") = txtPK_LengthLeft.Text.Trim
dr("RigthLength") = txtPK_LengthRigth.Text.Trim
dr("LengthAVG") = ((CType(txtPK_LengthLeft.Text.Trim, Decimal) + CType(txtPK_LengthRigth.Text.Trim, Decimal)) / 2).ToString("n2")
dt.Rows.Add(dr)
Session("SizeTable") = dt
LoadSizeData()
Reset()
End Sub
Private Sub AddToDB()
ConnectDB()
objConn.Open()
strSQL = "INSERT INTO Package (PK_RECEIVEDATE) " &
" VALUES " &
" (@PK_RECEIVEDATE)"
Dim objCmd As New SqlCommand(strSQL, objConn)
objCmd.Parameters.Add("@PK_RECEIVEDATE", SqlDbType.Date).Value = Format(Now.Date.ToString("dd-MM-yyyy", New CultureInfo("en-US")))
objCmd.ExecuteNonQuery()
dt = DirectCast(Session("SizeTable"), DataTable)
For i = 0 To dt.Rows.Count - 1
strSQL = "INSERT INTO Size (PK_ID,S_WIDTHTOP,S_WIDTHMID,S_WIDTHBOTTOM,S_LENGTHLEFT,S_LENGTHRIGTH) " &
" VALUES " &
" ('" & dt.Rows(i)("TopWidth") & "','" & dt.Rows(i)("MidWidth") & "','" & dt.Rows(i)("BottomWidth") & "','" & dt.Rows(i)("LeftLength") & "','" & dt.Rows(i)("RigthLength") & "')"
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With
Next
objConn.Close()
objConn = Nothing
End Sub
Private Sub Reset()
txtPK_WidthTop.Text = String.Empty
txtPK_WidthMid.Text = String.Empty
txtPK_WidthBottom.Text = String.Empty
txtPK_LengthLeft.Text = String.Empty
txtPK_LengthRigth.Text = String.Empty
End Sub
Private Sub ResetAll()
Reset()
Response.Redirect("~/Manage_Package.aspx")
End Sub
Protected Sub btnPK_AddTo_Click(sender As Object, e As EventArgs) Handles btnPK_AddTo.Click
AddToTable()
End Sub
Protected Sub btnPK_Cancel_Click(sender As Object, e As EventArgs) Handles btnPK_Cancel.Click
ResetAll()
End Sub
Protected Sub btnPK_Save_Click(sender As Object, e As EventArgs) Handles btnPK_Save.Click
AddToDB()
End Sub
End Class
|
ประวัติการแก้ไข 2017-02-08 13:22:55
 |
 |
 |
 |
Date :
2017-02-08 13:22:19 |
By :
NUTKA |
|
 |
 |
 |
 |
|
|
 |
 |
|
 |
 |
|
|