ต้องการดึงข้อมูลจาก sql มาแสดงใน datagridview แค่บางคอลัมภ์ ค่ะ ส่วนคอลัมภ์ที่เหลือ จะให้ user ทำการกรอกเอง ตอนนี้สร้าง คอลัมภ์มาแล้วค่ะ แต่ติดเรื่องการดึงข้อมูลจาก sql มาแสดงใน คอลัมภ์ค่ะ รบกวนช่วยสอน หน่อยค่ะ
ต้องการดึงข้อมูลจาก sql มาแสดงใน datagridview แค่บางคอลัมภ์ ค่ะ ส่วนคอลัมภ์ที่เหลือ จะให้ user ทำการกรอกเอง ตอนนี้สร้าง คอลัมภ์มาแล้วค่ะ แต่ติดเรื่องการดึงข้อมูลจาก sql มาแสดงใน คอลัมภ์ค่ะ รบกวนช่วยสอน หน่อยค่ะ
Code
Sub setHeaderGridview()
'DataGridView1.Columns(0).HeaderText = "วันที่"
'DataGridView1.Columns(1).HeaderText = "เป้าหมาย"
'DataGridView1.Columns(2).HeaderText = "ยอดโอน"
'DataGridView1.Columns(3).HeaderText = "ยอดโอน(PCS)"
'DataGridView1.Columns(4).HeaderText = "Diff"
Me.DataGridView1.Columns.Add("เป้าหมายจริง", "เป้าหมายจริง") '5 '0
Me.DataGridView1.Columns.Add("กะ1", "กะ1") '6 '1
Me.DataGridView1.Columns.Add("กะ2", "กะ2") '7 '2
Me.DataGridView1.Columns.Add("รวม", "รวม") '8 '3
Me.DataGridView1.Columns.Add("กะ1", "กะ1") '9 '4
Me.DataGridView1.Columns.Add("กะ2", "กะ2") '10 '5
Me.DataGridView1.Columns.Add("รวม", "รวม") '11 '6
Tag : .NET, Ms SQL Server 2008, VB.NET, Windows
Date :
2019-05-15 10:14:51
By :
nunan13
View :
2328
Reply :
12
ลองทำแบบโค๊ดที่แปะ ได้ผลลัพธ์ตามต้องการอยู่
แต่มันมาติดปัญหา ตอนนับคอลัมภ์ มันแยกกัน เวลาที่นำค่าในแต่ละคอลัมมาคำนวณ เลยติดปัญหา อย่างเช่น จะเอา ยอดคอลัมภ์ที่ 2 คือยอดโอน มาคำนวณ แต่ในโปรแกรมมอง ช่อง กะ 2 เป็น คอลัมภ์ที่ 2 อย่างนี้จะต้องแก้ยังไงจะต้องทำยังไงคะ
Code
Sub Viewdatagridview()
' Try
'*** DataTable ***'
Dim dtAdapter As SqlDataAdapter
Dim monthName As String
Dim dt As New DataTable
' monthName = Me.DataGridView1.Item(0, Me.DataGridView1.CurrentRow.Index).Value.ToString()
monthName = cboMonth02.Text
Dim monthIndex As Integer = getMonthNumber(MonthName)
'strSQL = "SELECT m.re_pro , m.re_namepro,m.re_qtye,re_qty,m.re_unit,m.re_supid,p.pro_stock FROM MTrepair m left join MTcodeproducts p on m.re_pro=p.pro_code left join MTst s on m.re_status=s.st_sts where re_runauto= '" & ttxtref1 & "' order by cast( m.re_ref as int)"
' strSQL = " select pt_date ,pt_target, select sum(pk_qty) as pk_qty from PTtranpack , pt_produc, pt_produc-pt_target from PTsum where month(pt_date) = '" & monthIndex & "' and year(pt_date) ='" & cboYear02.Text & "' and pt_dept ='" & cmbdept.Text.Trim & "'"
strSQL = " select s.pt_date ,s.pt_target, s.pt_produc, (select sum(t.pk_qty) as pk_qty from PTtranpack t left join PTpack p on t.p_ref = p.p_ref where t.pk_date = s.pt_date and t.p_ref = p.p_ref and p.p_line = '" & cmbdept.Text.Trim & "' ) , s.pt_produc - s.pt_target from PTsum s where month(s.pt_date) = '" & monthIndex & "' and year(s.pt_date) ='" & cboYear02.Text & "' and s.pt_dept = '" & cmbdept.Text.Trim & "'"
connection()
dtAdapter = New SqlDataAdapter(strSQL, conn)
dtAdapter.Fill(dt)
If dt.Rows.Count > 0 Then
'DataGridView2.DataSource = Nothing
'DataGridView2.Refresh()
DataGridView1.DataSource = dt
setHeaderGridview()
End If
conn.Close()
' Catch ex As Exception
'MsgBox("ตรวจสอบการดึงข้อมูลมาแสดงใน Datagridview [frmucEdit:Viewdatagridview()]")
' End Try
End Sub
Sub setHeaderGridview()
DataGridView1.Columns(0).HeaderText = "วันที่"
DataGridView1.Columns(1).HeaderText = "เป้าหมาย"
DataGridView1.Columns(2).HeaderText = "ยอดโอน"
DataGridView1.Columns(3).HeaderText = "ยอดโอน(PCS)"
DataGridView1.Columns(4).HeaderText = "Diff"
Me.DataGridView1.Columns.Add("เป้าหมายจริง", "เป้าหมายจริง") '5 '0
Me.DataGridView1.Columns.Add("กะ1", "กะ1") '6 '1
Me.DataGridView1.Columns.Add("กะ2", "กะ2") '7 '2
Me.DataGridView1.Columns.Add("รวม", "รวม") '8 '3
Me.DataGridView1.Columns.Add("กะ1", "กะ1") '9 '4
Me.DataGridView1.Columns.Add("กะ2", "กะ2") '10 '5
Me.DataGridView1.Columns.Add("รวม", "รวม") '11 '6
Me.DataGridView1.Columns.Add("หยุดงาน", "หยุดงาน") '12 '7
Me.DataGridView1.Columns.Add("ปกติ 8 ชม", "ปกติ 8 ชม") '13 '8
Me.DataGridView1.Columns.Add("ชม. OT", "ชม. OT") '14 '9 **************************************
Me.DataGridView1.Columns.Add("OT Manual", "OT Manual") '14 '9
Me.DataGridView1.Columns.Add("ค่าแรงปกติ", "ค่าแรงปกติ") '15 '10
Me.DataGridView1.Columns.Add("OT/ชม", "OT/ชม") '16 '11
Me.DataGridView1.Columns.Add("ค่าแรงรวม", "ค่าแรงรวม") '17 '12
Me.DataGridView1.Columns.Add("% OT", "% OT") '18 '13
Me.DataGridView1.Columns.Add("% ยอดแพ็ค", "% ยอดแพ็ค") '19 '14
Me.DataGridView1.Columns.Add("ปกติ", "ปกติ") '20 '15
Me.DataGridView1.Columns.Add("OT", "OT") '21 '16
Me.DataGridView1.Columns.Add("รวม", "รวม") '22 '17
Me.DataGridView1.Columns.Add("% OT", "% OT") '23
Me.DataGridView1.Columns.Add("% ค่าแรง/PACK", "% ค่าแรง/PACK") '24 '18
Me.DataGridView1.Columns.Add("ปกติ", "ปกติ") '25 '19
Me.DataGridView1.Columns.Add("OT", "OT") '26 '20
Me.DataGridView1.Columns.Add("ปกติ", "ปกติ") '27 '21
Me.DataGridView1.Columns.Add("OT", "OT") '28 '22
Me.DataGridView1.Columns.Add("รวมค่าแรงทั้งหมด", "รวมค่าแรงทั้งหมด") '29 '23
Me.DataGridView1.Columns.Add("% OT", "% OT") '30 '24
Me.DataGridView1.Columns.Add("% ต่อยอด PACK [Target 12%]", "% ต่อยอด PACK [Target 12%]") '31 '25
' Me.DataGridView1.Rows.Add("1", "2", "3", "2", "2", "2", "4", "2")
' DataGridView1.Columns(12).HeaderCell.Style.BackColor = Color.LightGreen
' DataGridView1.Columns(0).HeaderCell.Style.BackColor = Color.LightGreen
DataGridView1.Columns(0).Width = 110
DataGridView1.Columns(1).Width = 100
DataGridView1.Columns(2).Width = 100
DataGridView1.Columns(3).Width = 100
DataGridView1.Columns(4).Width = 100
DataGridView1.Columns(5).Width = 100
DataGridView1.Columns(6).Width = 50
DataGridView1.Columns(7).Width = 50
DataGridView1.Columns(8).Width = 60
DataGridView1.Columns(9).Width = 50
DataGridView1.Columns(10).Width = 50
DataGridView1.Columns(11).Width = 60
DataGridView1.Columns(12).Width = 100
DataGridView1.Columns(13).Width = 100
DataGridView1.Columns(14).Width = 50
DataGridView1.Columns(15).Width = 100
DataGridView1.Columns(16).Width = 100
DataGridView1.Columns(17).Width = 100
DataGridView1.Columns(18).Width = 120
DataGridView1.Columns(19).Width = 100
DataGridView1.Columns(20).Width = 80
DataGridView1.Columns(21).Width = 100
DataGridView1.Columns(22).Width = 100
DataGridView1.Columns(23).Width = 120
DataGridView1.Columns(24).Width = 80
DataGridView1.Columns(25).Width = 80
DataGridView1.Columns(26).Width = 120
DataGridView1.Columns(27).Width = 120
DataGridView1.Columns(28).Width = 100
DataGridView1.Columns(29).Width = 100
DataGridView1.Columns(30).Width = 120
DataGridView1.Columns(31).Width = 80
DataGridView1.Columns(32).Width = 100
DataGridView1.Columns(1).DefaultCellStyle.Format = "#,###.00" 'ถ้าจะจัดการเรื่อง format ต้องทำตรงนี้ครับ
DataGridView1.Columns(2).DefaultCellStyle.Format = "#,###.00"
DataGridView1.Columns(3).DefaultCellStyle.Format = "#,###.00"
DataGridView1.Columns(4).DefaultCellStyle.Format = "#,###.00"
DataGridView1.Columns(5).DefaultCellStyle.Format = "#,###.00"
DataGridView1.Columns(0).ReadOnly = True
DataGridView1.Columns(1).ReadOnly = True
DataGridView1.Columns(2).ReadOnly = True
DataGridView1.Columns(3).ReadOnly = True
DataGridView1.Columns(4).ReadOnly = True
DataGridView1.Columns(8).ReadOnly = True
DataGridView1.Columns(11).ReadOnly = True
DataGridView1.Columns(12).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
DataGridView1.Columns(25).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
DataGridView1.Columns(32).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
' DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
' DataGridView3.Columns(7).Width = 100
DataGridView1.Font = New Font("Microsoft Sans Serif", 11, FontStyle.Regular)
DataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
DataGridView1.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
DataGridView1.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(9).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(10).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(11).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(12).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(13).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(14).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(15).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(16).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(17).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(18).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(19).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(20).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(21).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(22).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(23).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(24).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(25).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(26).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(27).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(28).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(29).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(30).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(31).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(32).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
DataGridView1.Columns(0).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(1).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(2).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(3).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(4).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(5).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(6).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(7).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(8).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(9).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(10).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(11).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(12).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(13).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(14).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(14).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(14).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(15).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(16).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(17).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(18).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(19).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(20).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(21).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(22).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(23).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(24).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(25).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(26).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(27).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(28).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(29).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(30).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(31).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(32).SortMode = DataGridViewColumnSortMode.NotSortable
DataGridView1.Columns(0).HeaderCell.Style.BackColor = Color.LightGreen
' DataGridView1.DefaultCellStyle.SelectionBackColor = Color.White
' DataGridView1.DefaultCellStyle.SelectionForeColor = Color.Black
'For i As Integer = 0 To Me.DataGridView1.ColumnCount - 1
' ' Me.DataGridView1.Columns(12).Width = 70 'กว้างซัก 70
' Me.DataGridView1.Columns(12).DefaultCellStyle.SelectionBackColor = Color.Red
'Next
' Me.DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing 'ยืดได้หดได้
Me.DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing 'ยืดได้หดได้
Me.DataGridView1.ColumnHeadersHeight = Me.DataGridView1.ColumnHeadersHeight * 3.3 'ขยายความยาวเพื่อเตรียมสำหรับการ Merge
Me.DataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter 'ให้ตัวหนังสืออยู่ ล่าง,กลาง
' Me.DataGridView1.ColumnHeadersDefaultCellStyle.BackColor = Color.Aqua
End Sub
Date :
2019-05-15 11:03:57
By :
nunan13
งง code มาก
1.ต้องการดึงข้อมูลจาก sql มาแสดงใน datagridview แค่บางคอลัมภ์
ตอบ ก็ใช้คำสั่ง SQL ดึงข้อมูลมาตาม column ที่ ต้องการ ครับ หรือ ถ้าดึงมาแบบ *(Star)
ก็มาลบใน DATATABLE เอา ครับ table.column("TEST").removeAT("TEST")
ประวัติการแก้ไข 2019-05-15 14:10:27
Date :
2019-05-15 14:09:08
By :
2127832830566218
จะดึงข้อมูลบางส่วนมาจาก DB ลงใน DataGridView แล้วให้ user กรอกข้อมูลเพิ่มบางส่วน ใน DataGridView นั้น ใช่ไหมครับ
Date :
2019-05-15 15:08:33
By :
CPU4Core
ตอบความคิดเห็นที่ : 3 เขียนโดย : CPU4Core เมื่อวันที่ 2019-05-15 15:08:33
รายละเอียดของการตอบ ::
ใช่ค่ะ พอดีทำตารางแบบมีการ mage แล้วไม่ค่อยคุ้นเท่าไหร่ ตอนที่ทำตารางสร้างคอลัมภ์ สร้างแบบ นี้ค่ะ
Me.DataGridView1.Columns.Add("เป้าหมายจริง", "เป้าหมายจริง") ******** คอลัมภ์นี้ต้องการดึงข้อมูลจาก sql มาแสดงค่ะ
Me.DataGridView1.Columns.Add("กะ1", "กะ1") '6 '1 ส่วน คอลัภม์นี้ ต้องการให้ user กรอกเอง
ตอนที่ทำการ select ข้อมูล ทำแบบนี้ค่ะ
Code (VB.NET)
Dim dtAdapter As SqlDataAdapter
Dim monthName As String
Dim dt As New DataTable
' monthName = Me.DataGridView1.Item(0, Me.DataGridView1.CurrentRow.Index).Value.ToString()
monthName = cboMonth02.Text
Dim monthIndex As Integer = getMonthNumber(MonthName)
'strSQL = "SELECT m.re_pro , m.re_namepro,m.re_qtye,re_qty,m.re_unit,m.re_supid,p.pro_stock FROM MTrepair m left join MTcodeproducts p on m.re_pro=p.pro_code left join MTst s on m.re_status=s.st_sts where re_runauto= '" & ttxtref1 & "' order by cast( m.re_ref as int)"
' strSQL = " select pt_date ,pt_target, select sum(pk_qty) as pk_qty from PTtranpack , pt_produc, pt_produc-pt_target from PTsum where month(pt_date) = '" & monthIndex & "' and year(pt_date) ='" & cboYear02.Text & "' and pt_dept ='" & cmbdept.Text.Trim & "'"
strSQL = " select s.pt_date ,s.pt_target, s.pt_produc, (select sum(t.pk_qty) as pk_qty from PTtranpack t left join PTpack p on t.p_ref = p.p_ref where t.pk_date = s.pt_date and t.p_ref = p.p_ref and p.p_line = '" & cmbdept.Text.Trim & "' ) , s.pt_produc - s.pt_target from PTsum s where month(s.pt_date) = '" & monthIndex & "' and year(s.pt_date) ='" & cboYear02.Text & "' and s.pt_dept = '" & cmbdept.Text.Trim & "'"
connection()
dtAdapter = New SqlDataAdapter(strSQL, conn)
dtAdapter.Fill(dt)
If dt.Rows.Count > 0 Then
'DataGridView2.DataSource = Nothing
'DataGridView2.Refresh()
DataGridView1.DataSource = dt
setHeaderGridview()
End If
conn.Close()
********************* แต่ไม่รู้จะนำที่ select มา ยัดใส่ใน datagrid ที่ชื่อ Me.DataGridView1.Columns.Add("เป้าหมายจริง", "เป้าหมายจริง") ยังไงค่ะ
Date :
2019-05-15 15:24:11
By :
nunan13
ลองดูนะครับ ไม่แน่ใจว่าผมเข้าใจเจ้าของกระทู้หรือป่าว จากนั้นก็วนลูป Save เมื่อ User ทำการ Input data...
Code (SQL)
USE [THAI_CREATE]
GO
/****** Object: Table [dbo].[Cars] Script Date: 15/05/2019 17:12:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cars](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Trademark] [nvarchar](50) NULL,
[Model] [nvarchar](50) NULL,
[HP] [smallint] NULL,
[Liter] [float] NULL,
[Cyl] [smallint] NULL,
[TransmissSpeedCount] [smallint] NULL,
[TransmissAutomatic] [nvarchar](3) NULL,
[MPG_City] [smallint] NULL,
[MPG_Highway] [smallint] NULL,
[Category] [nvarchar](7) NULL,
[Description] [ntext] NULL,
[Hyperlink] [nvarchar](50) NULL,
[Picture] [image] NULL,
[Price] [money] NULL,
[RtfContent] [ntext] NULL,
CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Cars] ON
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (1, N'Mercedes-Benz', N'SL500 Roadster', 302, 4.966, 8, 5, N'Yes', 16, 23, N'SPORTS', NULL, N'http://www.mercedes.com', NULL, 83800.0000, NULL)
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (2, N'Mercedes-Benz', N'CLK55 AMG Cabriolet', 342, 5.439, 8, 5, N'Yes', 17, 24, N'SPORTS', NULL, N'http://www.mercedes.com', NULL, 79645.0000, NULL)
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (3, N'Mercedes-Benz', N'C230 Kompressor Sport Coupe', 189, 1.796, 4, 5, N'Yes', 21, 28, N'SPORTS', NULL, N'http://www.mercedes.com', NULL, 25600.0000, NULL)
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (4, N'BMW', N'530i', 225, 3, 6, 5, N'No', 21, 30, N'SALOON', NULL, N'http://www.bmw.com', NULL, 39450.0000, NULL)
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (5, N'Rolls-Royce', N'Corniche', 325, 6.75, 8, 4, N'Yes', 11, 16, N'SALOON', NULL, N'http://www.rollsroyce.com', NULL, 370485.0000, NULL)
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (6, N'Jaguar', N'S-Type 3.0', 235, 3, 6, 5, N'No', 18, 25, N'SALOON', NULL, N'http://www.jaguar.com', NULL, 44320.0000, NULL)
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (7, N'Cadillac', N'Seville', 275, 4.6, 8, 4, N'Yes', 18, 27, N'SALOON', NULL, N'http://www.cadillac.com', NULL, 49600.0000, NULL)
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (8, N'Cadillac', N'DeVille', 275, 4.6, 8, 4, N'Yes', 18, 27, N'SALOON', NULL, N'http://www.cadillac.com', NULL, 47780.0000, NULL)
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (9, N'Lexus', N'LS430', 290, 4.3, 8, 5, N'Yes', 18, 25, N'SALOON', NULL, N'http://www.lexus.com', NULL, 54900.0000, NULL)
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (10, N'Lexus', N'GS 430', 300, 4.3, 8, 5, N'Yes', 18, 23, N'SALOON', NULL, N'http://www.lexus.com', NULL, 41242.0000, NULL)
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (11, N'Ford', N'Ranger FX-4', 135, 2.3, 4, 5, N'Yes', 21, 25, N'TRUCK', NULL, N'http://www.ford.com', NULL, 12565.0000, NULL)
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (12, N'Dodge', N'Ram 1500', 215, 3.7, 6, 4, N'Yes', 15, 19, N'TRUCK', NULL, N'http://www.dodge.com', NULL, 17315.0000, NULL)
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (13, N'GMC', N'Siera Quadrasteer', 200, 4.3, 6, 4, N'Yes', 15, 20, N'TRUCK', NULL, N'http://www.gmc.com', NULL, 17748.0000, NULL)
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (14, N'Nissan', N'Crew Cab SE', 143, 2.4, 4, 4, N'Yes', 20, 23, N'TRUCK', NULL, N'http://www.NissanDriven.com', NULL, 12800.0000, NULL)
INSERT [dbo].[Cars] ([ID], [Trademark], [Model], [HP], [Liter], [Cyl], [TransmissSpeedCount], [TransmissAutomatic], [MPG_City], [MPG_Highway], [Category], [Description], [Hyperlink], [Picture], [Price], [RtfContent]) VALUES (15, N'Toyota', N'Tacoma S-Runner', 190, 3.4, 6, 5, N'No', 18, 22, N'TRUCK', NULL, N'http://www.toyota.com', NULL, 20000.0000, NULL)
SET IDENTITY_INSERT [dbo].[Cars] OFF
ALTER TABLE [dbo].[Cars] ADD DEFAULT ((0)) FOR [HP]
GO
ALTER TABLE [dbo].[Cars] ADD DEFAULT ((0)) FOR [Liter]
GO
ALTER TABLE [dbo].[Cars] ADD DEFAULT ((0)) FOR [Cyl]
GO
ALTER TABLE [dbo].[Cars] ADD DEFAULT ((0)) FOR [TransmissSpeedCount]
GO
ALTER TABLE [dbo].[Cars] ADD DEFAULT ((0)) FOR [MPG_City]
GO
ALTER TABLE [dbo].[Cars] ADD DEFAULT ((0)) FOR [MPG_Highway]
GO
ALTER TABLE [dbo].[Cars] ADD DEFAULT ((0)) FOR [Price]
GO
Code (VB.NET)
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Dim strCon As String = "Server=127.0.0.1;Database=THAI_CREATE;User ID=sa;Password=P@ssw0rd69;"
Dim DbConn As New SqlConnection(strCon)
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim dtClm As New DataTable()
With dtClm
.Columns.Add("Brand", GetType(String))
.Columns.Add("Model", GetType(String))
.Columns.Add("Price", GetType(String))
'Field ที่คุณต้องการ
.Columns.Add("เป้าหมายจริง", GetType(String))
.Columns.Add("กะ1", GetType(String))
.Columns.Add("กะ2", GetType(String))
End With
DbConn.Open()
Dim row As DataRow
Dim sql As String = <sql>select * from Cars</sql>
Dim da As New SqlDataAdapter(sql, DbConn)
Dim dt As New DataTable("TB")
da.Fill(dt)
For i = 0 To dt.Rows.Count - 1
Dim Brand As String = dt.Rows(i).Item("Trademark")
Dim Model As String = dt.Rows(i).Item("Model")
Dim Price As String = FormatNumber(dt.Rows(i).Item("Price"), 2)
row = dtClm.Rows.Add(Brand, Model, Price)
Next
DataGridView1.DataSource = dtClm
DbConn.Close()
End Sub
End Class
Output--->ก็จะประมาณนี้ครับ
ประวัติการแก้ไข 2019-05-15 17:17:25
Date :
2019-05-15 17:14:09
By :
beerkingdom
ลอง
select field1, field2, field3, '' as input1, '' as input2, '' as input3
from tablename
สีแดงเป็น field ว่างเตรียมใส่ข้อมูล
Date :
2019-05-15 18:23:59
By :
Chaidhanan
Code (SQL)
case when (e.e_dinm is null) or (e.e_dinm<=0) then 0 else (xxxx / e.e_dinm * yyyyy) end
ตัวอย่าง algorithm ไม่ใช่โค๊ดสำเร็จ ประยุกต์เอาเองตามข้อมูลจริงนะครับ
ประวัติการแก้ไข 2019-05-18 07:08:22 2019-05-18 07:08:56
Date :
2019-05-18 07:07:32
By :
Chaidhanan
For i = 0 To DataGridView1.Rows.Count - 1
แก้เป็น For i = 0 To DataGridView1.Rows.Count - 2
เพราะ Rows.count -1 คือบันทัด กรอบแดงหรือเปล่า (เดาเอาจากรูปภาพ)
Date :
2019-05-20 20:03:48
By :
Chaidhanan
Load balance : Server 05