Filling dataset, Incorrect syntac near error

[Merovingian]

I have a listbox and a datagrid.

When the form is loaded the listbox is filled with a list of tables from a selected database (SQL 2008).

sqlstr = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME"

Then I have a SelectedIndexChanged that populates the datagrid with the contents of the selected table.

Dim ConnString As String = mySQLconn

        Dim ds As DataSet
        Dim dv As DataView
        Try
            Dim SQLConn As New SqlConnection(ConnString) 'The SQL Connection

            ds = New DataSet
            dv = New DataView

            Dim ad As New SqlDataAdapter("SELECT * FROM " + lbListAllTables.SelectedItem, SQLConn)
            ad.Fill(ds, "SelectedTable")
            dv.Table = ds.Tables("SelectedTable")

            Me.DataGridView1.DataSource = dv
        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try

This works just fine if the table name is one word (clients) but if it two words (setup client) I get a 'Incorrect syntax near the keyword 'setup'' error.

[TomW]

First of all, you should use parameters instead of concatenating query strings. That in itself might resolve the issue but either way you can add brackets around the [table name] so the database knows.

[GeekByChoiCe]

Dim ad As New SqlDataAdapter("SELECT * FROM '" & lbListAllTables.SelectedItem.ToString & "'", SQLConn)

i have to agree with TomW. it is very unsafe not to use parameter in sql queries.

[Merovingian]

This is my first day working with datagridview etc so I have no idea how you would complete the same thing with parameters.

[TomW]

Here is an example.

Also in you example, a dataview is just making an additional copy of your datasets table. You dont really need it unless you want to filter the results further.

sqlstr = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME"

Dim ConnString As String = mySQLconn

        Dim ds As DataSet
        Dim dv As DataView
        Dim cmd As New DataCommand

        Try
            Dim SQLConn As New SqlConnection(ConnString) 'The SQL Connection

            ds = New DataSet
            'dv = New DataView

            Dim ad As New SqlDataAdapter()
            
            cmd.Connection = SQLConn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT * FROM @TableName"
            cmd.Parameters.AddWithValue("@TableName", "[" & lbListAllTables.SelectedItem & "]")

            ad.SelectCommand = cmd
            ad.Fill(ds, "SelectedTable")

            'dv.Table = ds.Tables("SelectedTable")

            Me.DataGridView1.DataSource = ds.Tables(0)
        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try

[Merovingian]

Thank you all so much for your help. When I try to use you code I get a Type 'Sql.DataCommand' is not defined error.

Do I need to imports something?

[sknake]

DataCommand is defined in Microsoft.ManagementConsole.Internal but you could use an SqlCommand which is defined in System.Data.SqlClient

[TomW]

My fault, Sknake is right - the command object is actually sqlCommand not datacommand. See what I get for coding in this text box with out the autocomplete features... lol

[Merovingian]

Thanks for the help, working good.

Do I have to setup something different to update the database if users make changes in the datagridview ?

[sknake]

Yes. You have Select Queries, Update Queries, Insert Queries, and delete queries. In your example you provided the select query:

cmd.Connection = SQLConn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT * FROM @TableName"
            cmd.Parameters.AddWithValue("@TableName", "[" & lbListAllTables.SelectedItem & "]")

            ad.SelectCommand = cmd

Take a look at ad.UpdateCommand , ad.InsertCommand , and ad.DeleteCommand on your SqlDataAdapter