0

I tried "SELECT" MYSQL syntax below using MySQL Query Browser, work normally. When I used this syntax into dtTable.Select(), error is "Syntax error on expression". Please help me, Thanks.

Dim dtTable As DataTable = MyDataset.Tables("machine")
Dim sql As String = String.Format("SELECT product_name, operator_name, totalizer_name 
                                   FROM product, operator, totalizer_type, machine 
                                   WHERE product.product_id = machine.product_id AND 
                                         operator.operator_id = machine.operator_id AND 
                                         totalizer_type.totalizer_id = machine.totalizer_id 
                                   ORDER BY machine.machine_id ASC;")
Dim rowSearching() As DataRow
rowSearching = dtTable.Select(sql)  ' <--- (error in here - "Syntax error on expression")

If rowSearching.Length > 0 Then
   For Each dr As DataRow In rowSearching
      MessageBox.Show(CStr(dr.Item(0)) & " " & CStr(dr.Item(1)) & " " & CStr(dr.Item(2)))
   Next
End If
Mat
  • 202,337
  • 40
  • 393
  • 406
dewi putri
  • 1
  • @Mike Miller, thanks. I tried to eliminate the "String.Format", still appears the error: "Syntax error: Missing operand after 'product_name' operator". :( – dewi putri Jun 22 '11 at 18:54

2 Answers2

2

DataTable.Select does not work like that. It just accepts a filter on it's columns with a syntax like the where clause syntax in sql.

Example:

dtTable.Select('operator_id = 1')

will return all rows in dtTable having operator_id = 1

For more informations:

Filter syntax

DataTable.Select Method

Community
  • 1
  • 1
manji
  • 47,442
  • 5
  • 96
  • 103
  • thanks. I want to retrieve data from multiple DataTable with Master Detail related in the dataset. How do it? – dewi putri Jun 22 '11 at 19:35
0

You are mistaken in how Select works on the Datatable object, http://msdn.microsoft.com/en-us/library/det4aw50.aspx

You need to be using something along these lines

Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As New DataTable
Dim SQL As String

conn.ConnectionString = myConnString
conn.Open()

myCommand.Connection = conn
myCommand.CommandText = SQL

myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData)

See http://www.vbmysql.com/articles/vbnet-mysql-tutorials/the-vbnet-mysql-tutorial-part-4 for a decent tutorial.

Mike Miller
  • 16,195
  • 1
  • 20
  • 27
  • I've created a database in mysql and then I put in the Dataset with "Fill" method from "MySQLDataAdapter". My difficulty is when I want to retrieve data from FOUR DataTable which Master Detail related in the Dataset. Relation between DataTable I have created. But when I try to do the command "rowSearching = dtTable.Select(sql)" to display some data from DataTable, I have not succeeded. Thanks – dewi putri Jun 22 '11 at 19:15