Imports System.Data.SqlClient
Imports System.Globalization
Public Class Test_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 dt, dtddl As DataTable
Dim iMAX, Count As Integer
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
lblPK_AddReceiveDate.Text = Format(Now.Date.ToString("dd/MM/yyyy", New CultureInfo("en-US")))
If Not IsPostBack Then
DropdownListRawMaterialType()
LoadSizeData()
End If
End Sub
Public Sub ConnectDB()
strConnString = ConfigurationManager.ConnectionStrings("con").ConnectionString
objConn = New SqlConnection(strConnString)
End Sub
Function DropdownListRawMaterialType() As DataTable
ConnectDB()
strSQL = "Select RMT_RKEY,RMT_NAME From RawMaterialType WHERE RMT_NAME <> 'แป้ง' AND RMT_NAME <> 'สารเคมี' ORDER BY RMT_NAME ASC"
dtAdapter = New SqlDataAdapter(strSQL, objConn)
dt = New DataTable
objConn.Open()
dtAdapter.Fill(dt)
objConn.Close()
ddlPK_LoadRMT.DataSource = dt
ddlPK_LoadRMT.DataTextField = "RMT_NAME"
ddlPK_LoadRMT.DataValueField = "RMT_RKEY"
ddlPK_LoadRMT.DataBind()
ddlPK_LoadRMT.Items.Insert(0, New ListItem("กรุณาเลือกประเภทวัตถุดิบ", "0"))
ddlPK_LoadRM.Items.Insert(0, New ListItem("กรุณาเลือกวัตถุดิบ", "0"))
ddlPK_LoadRMT.SelectedIndex = ddlPK_LoadRMT.Items.IndexOf(ddlPK_LoadRMT.Items.FindByValue("0"))
ddlPK_LoadRM.SelectedIndex = ddlPK_LoadRM.Items.IndexOf(ddlPK_LoadRM.Items.FindByValue("0"))
ddlPK_LoadRM.Enabled = False
Return dt
End Function
Private Sub ddlPK_LoadRMT_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddlPK_LoadRMT.SelectedIndexChanged
Try
ConnectDB()
'ddlPK_LoadRMT.AppendDataBoundItems = True
strSQL = "SELECT RM_ID,RM_NAME FROM RawMaterial WHERE RMT_RKEY=@RMT_RKEY ORDER BY RM_NAME ASC"
objCmd = New SqlCommand(strSQL, objConn)
objCmd.Parameters.AddWithValue("@RMT_RKEY", ddlPK_LoadRMT.SelectedItem.Value)
objConn.Open()
ddlPK_LoadRM.DataSource = objCmd.ExecuteReader()
ddlPK_LoadRM.DataTextField = "RM_NAME"
ddlPK_LoadRM.DataValueField = "RM_ID"
ddlPK_LoadRM.DataBind()
ddlPK_LoadRM.Items.Insert(0, New ListItem("กรุณาเลือกวัตถุดิบ", "0"))
ddlPK_LoadRM.SelectedIndex = ddlPK_LoadRM.Items.IndexOf(ddlPK_LoadRM.Items.FindByValue("0"))
If ddlPK_LoadRM.Items.Count > 1 Then
ddlPK_LoadRM.Enabled = True
Else
ddlPK_LoadRM.Enabled = False
End If
Catch ex As Exception
Throw ex
Finally
objConn.Close()
End Try
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)))
dt.Columns.Add(New DataColumn("SizeNote", GetType(String)))
Session("SizeTable") = dt
End If
dt = DirectCast(Session("SizeTable"), DataTable)
gvS_LoadSize.DataSource = dt
gvS_LoadSize.DataBind()
End Sub
Private Sub AddToTable()
If txtPK_WidthTop.Text.Trim <> String.Empty And txtPK_WidthMid.Text.Trim <> String.Empty And txtPK_WidthBottom.Text.Trim <> String.Empty And txtPK_LengthLeft.Text.Trim <> String.Empty And txtPK_LengthRigth.Text.Trim <> String.Empty Then
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")
dr("SizeNote") = txtPK_SizeNote.Text.Trim
dt.Rows.Add(dr)
Session("SizeTable") = dt
LoadSizeData()
Reset()
txtPK_WidthTop.Focus()
ElseIf txtPK_WidthTop.Text.Trim = String.Empty And txtPK_WidthMid.Text.Trim <> String.Empty And txtPK_WidthBottom.Text.Trim <> String.Empty And txtPK_LengthLeft.Text.Trim <> String.Empty And txtPK_LengthRigth.Text.Trim <> String.Empty Then
MsgBox("กรุณากรอกความกว้างส่วนบน")
txtPK_WidthTop.Focus()
ElseIf txtPK_WidthTop.Text.Trim <> String.Empty And txtPK_WidthMid.Text.Trim = String.Empty And txtPK_WidthBottom.Text.Trim <> String.Empty And txtPK_LengthLeft.Text.Trim <> String.Empty And txtPK_LengthRigth.Text.Trim <> String.Empty Then
MsgBox("กรุณากรอกความกว้างส่วนกลาง")
txtPK_WidthMid.Focus()
ElseIf txtPK_WidthTop.Text.Trim <> String.Empty And txtPK_WidthMid.Text.Trim <> String.Empty And txtPK_WidthBottom.Text.Trim = String.Empty And txtPK_LengthLeft.Text.Trim <> String.Empty And txtPK_LengthRigth.Text.Trim <> String.Empty Then
MsgBox("กรุณากรอกความกว้างส่วนล่าง")
txtPK_WidthBottom.Focus()
ElseIf txtPK_WidthTop.Text.Trim <> String.Empty And txtPK_WidthMid.Text.Trim <> String.Empty And txtPK_WidthBottom.Text.Trim <> String.Empty And txtPK_LengthLeft.Text.Trim = String.Empty And txtPK_LengthRigth.Text.Trim <> String.Empty Then
MsgBox("กรุณากรอกความยาวด้านซ้าย")
txtPK_LengthLeft.Focus()
ElseIf txtPK_WidthTop.Text.Trim <> String.Empty And txtPK_WidthMid.Text.Trim <> String.Empty And txtPK_WidthBottom.Text.Trim <> String.Empty And txtPK_LengthLeft.Text.Trim <> String.Empty And txtPK_LengthRigth.Text.Trim = String.Empty Then
MsgBox("กรุณากรอกความยาวด้านขวา")
txtPK_LengthRigth.Focus()
Else
MsgBox("กรุณากรอกข้อมูลให้ครบถ้วน")
txtPK_WidthTop.Focus()
End If
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
txtPK_SizeNote.Text = String.Empty
End Sub
Protected Sub btnPK_Add_Click(sender As Object, e As EventArgs) Handles btnPK_Add.Click
AddToTable()
End Sub
Private Sub AddTODB()
LoadSizeData()
If dt.Rows.Count > 0 Then
Try
ConnectDB()
objConn.Open()
strSQL = "SELECT ISNULL(MAX(RIGHT(PK_ID,2)),0) As PK_ID FROM Package"
objCmd = New SqlCommand(strSQL, objConn)
Count = objCmd.ExecuteScalar()
iMAX = IIf(Count <= 0, 1, Count + 1)
If iMAX < 100 Then
strSQL = "INSERT INTO Package (PK_ID, PK_RECEIVEDATE) VALUES ('" & "PK" & CStr(Format(Now.Date.ToString("yyyyMMdd", New CultureInfo("en-US")))) & iMAX.ToString("00") & "',@PK_RECEIVEDATE)"
objCmd = 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()
iMAX = IIf(Count <= 0, 1, Count + 1)
If iMAX < 100 Then
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,S_NOTE) VALUES ('" & "PK" & CStr(Format(Now.Date.ToString("yyyyMMdd", New CultureInfo("en-US")))) & iMAX.ToString("00") & "','" & dt.Rows(i)("TopWidth") & "','" & dt.Rows(i)("MidWidth") & "','" & dt.Rows(i)("BottomWidth") & "','" & dt.Rows(i)("LeftLength") & "','" & dt.Rows(i)("RigthLength") & "','" & dt.Rows(i)("SizeNote") & "')"
objCmd = New SqlCommand(strSQL, objConn)
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With
Next
objConn.Close()
objConn = Nothing
MsgBox("บันทึกข้อมูลสำเร็จ")
dt.Clear()
Response.Redirect("~/Test/Test_Manage_Package.aspx")
End If
Else
MsgBox("ข้อมูลเต็ม")
End If
Catch ex As Exception
Response.Write("Error : " + ex.Message)
End Try
Else
MsgBox("กรุณาเพิ่มข้อมูลขนาดก่อนบันทึก")
txtPK_WidthTop.Focus()
End If
End Sub
Private Sub ResetAll()
dt.Clear()
Response.Redirect("~/Test/Test_Manage_Package.aspx")
End Sub
Protected Sub btnPK_Save_Click(sender As Object, e As EventArgs) Handles btnPK_Save.Click
AddTODB()
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)