Vb.net form to access mysql query help

Question

0.00/5 (No votes)

See more:

VB

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

Posted 19-Jan-12 4:01am

Member 8580885

Updated 1-Mar-18 1:58am

Chris Maunder772.4K

v2

Add a Solution

Comments

fjdiewornncalwe 19-Jan-12 12:56pm

Could you tell us what is going wrong? We're not psychic.

Member 8580885 19-Jan-12 13:33pm

Im not sure how to explain it any better, if the table column headers were Name, Email, FormId there wouldnt be an issue, but the table structure is not like that

under the column header FieldName is the Name and Email fields, and Under the FieldValue column header is the values for Name and Email.

The query string is what i need fixed. this isnt right but may give you an idea:

SELECT FormId, FieldName, FieldValue FROM jos_rsform_submission_values WHERE FieldName='Name' AND FieldName='Email'

And gets the FieldValues from those Table Rows

A_Griffin 28-Feb-18 12:42pm

Really I think you should re-think your database design. It's not very good as is, and you will have difficulty making sensible queries on it.

Maciej Los 1-Mar-18 5:10am

Are you sure that FormId is the same for Jack Doll and Ron Young?
Are there any other fields in your table?

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2018-03-01T01:58:00

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 of FieldName 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, because FormId is the same for both names and email addresses.

To workaround it, use below trick:

SQL

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;

VB.NET

'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()

Now finaldt 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 form to access mysql query help

1 solution

Solution 1

Add your solution here

Preview 0

Vb.net form to access mysql query help

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us