Vb.net表单访问mysql查询帮助 [英] Vb.net form to access mysql query help
问题描述
我有一个包含以下属性的表:
列是:FormId | FieldName | FieldValue |
行数:
6 | epFname |杰克娃娃
6 | epEmail | jdoll@mail.com
6 | epFname | Ron Young
6 | epEmail | ryoung@mail.com
我试图创建一个列表视图,将数据检索到ListView中预设的列中,但我似乎无法获得正确的查询数据,因此它在列表视图中正确列出如下:
ListView列:FormId |全名|电子邮件地址
ListView数据:6 |杰克娃娃| jdoll@mail.com
6 |罗恩杨| ryoung@mail.com
这是我使用MySQL.Data连接器的当前vb.net代码
======= ================================================== ========================
私有 Sub Button1_Click( ByVal sender As System。 Object , ByVal e As System.EventArgs)句柄 Button1.Click
Dim sqlQuery 作为 字符串 = SELECT(FormId),(FieldName),(FieldValue)FROM jos_rsform_submission_values WHERE FormId ='6'
< span class =code-keyword> Dim sqlAdapter 作为 新 MySqlDataAdapter
Dim sqlCommand < span class =code-keyword>作为 新 MySqlCommand
Dim 表作为 新 DataTable
Dim i 作为 整数
lvList.Items.Clear()
使用(sqlCommand)
.CommandText = sqlQuery
.Connection = sConnection
End 使用
使用 sqlAdapter
.SelectCommand = sqlCommand
.Fill(表)
结束 使用
对于 i = 0 To Table.Rows.Count - 1
使用 lvList
.Items.Add(Table.Rows(i)( FormId))
使用 .Items(.Items.Count - 1 )。SubItems
.Add (Table.Rows(i)( FieldName))
.Add(表。行(i)( FieldValue))
结束 使用
结束 使用
下一步
结束 Sub
假设您想在以下表格之间转换数据:
原始数据 预期输出 FormId | FieldName | FieldValue
6 | epFname |杰克娃娃
6 | epEmail | jdoll@mail.com
6 | epFname | Ron Young
6 | epEmail | ryoung@mail.com
FormId | FullName | EmailAddress
6 |杰克娃娃| jdoll@mail.com
6 |罗恩杨| ryoung@mail.com
您需要透视数据。这意味着什么?您必须将FieldName
volumn的唯一数据转换为列。在伪代码中:
[FullName] =如果[FieldName] =epFname返回[FieldValue] Else Nothig
[EmailAddres] =如果[FieldName] =epEmail返回[FieldValue] Else Nothig
在T-SQL中,您可以使用结束时声明 [ ^ ] + GROUP BY [ ^ ]。
MySQL不知道 PIVOT和UNPIVOT [ ^ ]运算符在MS SQL中是众所周知的。在MySQL中,您必须创建视图。请参阅:
sql - MySQL - Rows to Columns - Stack Overflow [ ^ ]
在MySQL中透视表 - 教程 - Stratos博客 [ ^ ]
但是 你将无法实现这一目标,因为FormId
对于名称和电子邮件地址都是相同的。
要解决它,请使用以下技巧:
SELECT t1.FormId,t1。 [索引],t1.FullName,t2.EmailAddress
FR OM (
SELECT FormId,ROW_NUMBER() OVER w AS ' 索引',FieldValue AS ' FullName'
FROM YourTable
WHERE FieldName = ' epFname' AND FormId = 6
WINDOW w AS ( ORDER BY FormId)
) AS t1 INNER JOIN (
SELECT FormId,ROW_NUMBER() OVER w AS ' 索引',FieldValue AS ' EmailAddress'
FROM YourTable
WHERE FieldName = ' epEmail' AND FormId = 6
WINDOW w AS ( ORDER BY FormId)) AS t2 ON t1.FormId = t2.FormId AND t1。索引 = t2。索引 ;
' 原始数据变为表格数据库
' 我需要创建样本数据,所以...
Dim dt As DataTable = 新 DataTable()
dt.Columns.Add(新 DataColumn( FormId,Type。 GetType ( System.Int32)))
dt.Columns.Add( New DataColumn(< span class =code-string> FieldName,Type。 GetType (< span class =code-string> System.String)))
dt.Columns.Add(新 DataColumn( FieldValue,输入。 GetType ( System.String )))
dt.Rows.Add( New Object (){ 6 , epFname, Jack Doll})
dt.Rows.Add(新 对象(){ 6 , epEmail, jdoll @ mail.com})
dt.Rows.Add(新 对象 (){ 6 , epFname , Ron Young })
dt.Rows.Add( New Object (){ 6 , epEmail, ryoung@mail.com})
' 创建另一个数据表来存储数据透视数据
Dim finalDt < span class =code-keyword> As DataTable = New DataTable()
finaldt.Columns.Add(新 DataColumn( FormId,输入。 GetType ( System.Int32)))
finaldt.Columns.Add( New DataColumn( I ndex,类型。 GetType ( System.Int32)))
finaldt.Columns.Add( New DataColumn( FullName,类型。 GetType ( System.String)))
finaldt.Columns.Add( New DataColumn ( EmailAddress,类型。 GetType ( System.String)))
' 从原始数据表中获取数据
Dim data = (来自 In dt.AsEnumerable()_
.Where( Function (x)x.Field( Of String )( FieldName)= epFname)_
。选择(功能(x,i)新 使用 _
{_
.FormId = x.Field( Of 整数)( FormId),_
.Index = i,_
.FullName = x.Field( of 字符串)( FieldValue)_
}。ToList()Join b In dt.AsEnumerable()_
.Where( Function (x)x.Field( Of String )( FieldName)= epEmail)_
。选择(功能(x,i)新 < span class =code-keyword>使用 _
{_
.FormId = x.Field( of 整数)( FormId),_
.Index = i,_
.EmailAddress = x.Field( Of String ) ( FieldValue)_
})。ToList() On a.FormId Equals b.FormId a.Index Equals b.Index)_
。选择(功能(已加入)finaldt.LoadDataRow(新 对象()_
{_
joined.a.FormId,_
joined.a.Index,_
joined.a.FullName,_
joined.b.EmailAddress _
}, False ))。ToList()
finaldt = data.CopyToDataTable( )
现在finaldt
存储透视数据:
FormId索引FullName EmailAddress
6 0 Jack Doll jdoll@mail.com
6 1 Ron Young ryoung@mail.com
I have a Table with the following properties:
Columns are: FormId | FieldName | FieldValue |
Rows are:
6 | epFname | Jack Doll
6 | epEmail | jdoll@mail.com
6 | epFname | Ron Young
6 | epEmail | ryoung@mail.com
Im trying to create a list view that retrieves the data into columns preset in the ListView, but i cannot seem to get the correct query to pull the data so it lists correctly in the listview as follows:
ListView Columns: FormId | Full Name | Email Address
ListView Data: 6 | Jack Doll | jdoll@mail.com
6 | Ron Young | ryoung@mail.com
this is my current vb.net code using the MySQL.Data connector
=================================================================================
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlQuery As String = "SELECT (FormId), (FieldName), (FieldValue) FROM jos_rsform_submission_values WHERE FormId='6'"
Dim sqlAdapter As New MySqlDataAdapter
Dim sqlCommand As New MySqlCommand
Dim Table As New DataTable
Dim i As Integer
lvList.Items.Clear()
With (sqlCommand)
.CommandText = sqlQuery
.Connection = sConnection
End With
With sqlAdapter
.SelectCommand = sqlCommand
.Fill(Table)
End With
For i = 0 To Table.Rows.Count - 1
With lvList
.Items.Add(Table.Rows(i)("FormId"))
With .Items(.Items.Count - 1).SubItems
.Add(Table.Rows(i)("FieldName"))
.Add(Table.Rows(i)("FieldValue"))
End With
End With
Next
End Sub
Assuming that you would like to transform data between below forms:
Original data Expected output FormId | FieldName | FieldValue 6 | epFname | Jack Doll 6 | epEmail | jdoll@mail.com 6 | epFname | Ron Young 6 | epEmail | ryoung@mail.com FormId | FullName | EmailAddress 6 | Jack Doll | jdoll@mail.com 6 | Ron Young | ryoung@mail.com
You need to pivot data. What this means? You have to "convert" unique data ofFieldName
volumn into columns. In pseudo code:
[FullName] = If [FieldName]="epFname" Return [FieldValue] Else Nothig [EmailAddres] = If [FieldName]="epEmail" Return [FieldValue] Else Nothig
In T-SQL you can achieve that by using CASE WHEN statement[^] + GROUP BY[^].
MySQL doesn't know PIVOT and UNPIVOT[^] operators as is well known in MS SQL. In MySQL you have to create view. See:
sql - MySQL - Rows to Columns - Stack Overflow[^]
Pivot a table in MySQL – Tutorial - Stratos Blog[^]
BUT! You won't be able to achieve that, becauseFormId
is the same for both names and email addresses.
To workaround it, use below trick:
SELECT t1.FormId, t1.[Index], t1.FullName, t2.EmailAddress FROM ( SELECT FormId, ROW_NUMBER() OVER w AS 'Index', FieldValue AS 'FullName' FROM YourTable WHERE FieldName ='epFname' AND FormId = 6 WINDOW w AS (ORDER BY FormId) ) AS t1 INNER JOIN ( SELECT FormId, ROW_NUMBER() OVER w AS 'Index', FieldValue AS 'EmailAddress' FROM YourTable WHERE FieldName ='epEmail' AND FormId = 6 WINDOW w AS (ORDER BY FormId)) AS t2 ON t1.FormId = t2.FormId AND t1.Index = t2.Index;
'original data becomes form database 'i need to create sample data, so... Dim dt As DataTable = New DataTable() dt.Columns.Add(New DataColumn("FormId", Type.GetType("System.Int32"))) dt.Columns.Add(New DataColumn("FieldName", Type.GetType("System.String"))) dt.Columns.Add(New DataColumn("FieldValue", Type.GetType("System.String"))) dt.Rows.Add(New Object(){6, "epFname", "Jack Doll"}) dt.Rows.Add(New Object(){6, "epEmail", "jdoll@mail.com"}) dt.Rows.Add(New Object(){6, "epFname", "Ron Young"}) dt.Rows.Add(New Object(){6, "epEmail", "ryoung@mail.com"}) 'create another datatable to store pivoted data Dim finalDt As DataTable = New DataTable() finaldt.Columns.Add(New DataColumn("FormId", Type.GetType("System.Int32"))) finaldt.Columns.Add(New DataColumn("Index", Type.GetType("System.Int32"))) finaldt.Columns.Add(New DataColumn("FullName", Type.GetType("System.String"))) finaldt.Columns.Add(New DataColumn("EmailAddress", Type.GetType("System.String"))) 'get data from original datatable Dim data = (From a In dt.AsEnumerable() _ .Where(Function(x) x.Field(Of String)("FieldName")="epFname") _ .Select(Function(x, i) New With _ { _ .FormId = x.Field(Of Integer)("FormId"), _ .Index = i, _ .FullName = x.Field(Of String)("FieldValue") _ }).ToList() Join b In dt.AsEnumerable() _ .Where(Function(x) x.Field(Of String)("FieldName")="epEmail") _ .Select(Function(x, i) New With _ { _ .FormId = x.Field(Of Integer)("FormId"), _ .Index = i, _ .EmailAddress = x.Field(Of String)("FieldValue") _ }).ToList() On a.FormId Equals b.FormId And a.Index Equals b.Index) _ .Select(Function(joined) finaldt.LoadDataRow(New Object() _ { _ joined.a.FormId, _ joined.a.Index, _ joined.a.FullName, _ joined.b.EmailAddress _ }, False)).ToList() finaldt = data.CopyToDataTable()
Nowfinaldt
is storing pivoted data:
FormId Index FullName EmailAddress 6 0 Jack Doll jdoll@mail.com 6 1 Ron Young ryoung@mail.com
More:
MySQL :: MySQL 8.0 Reference Manual :: 12.19.1 Window Function Descriptions[^]
这篇关于Vb.net表单访问mysql查询帮助的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!