how to use the combo box value as a table name in access database query statement in vb.net

Question

 Dim maxaccess As Date = Date.Parse((maxaccess))
        Dim RecordCount As Long = 0
        Using cn As New OleDbConnection With _
        { _
            .ConnectionString = _
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TSOSM\" + combobox1.SelectedItem.ToString() + "\" + combobox1.SelectedItem.ToString() + ".accdb" _
        }
            cn.Open()
            Using RecordCountcon As New OleDbCommand With _
                { _
                    .Connection = cn, _
                    .CommandText = _
                    <SQL>
                     SELECT count(*) 
                     FROM " + combobox1.SelectedItem.ToString() + "
                 </SQL>.Value _
                }
                'cn.Open()
                Using maxaccesscon As New OleDbCommand With _
                    { _
                        .Connection = cn, _
                        .CommandText = _
                        <SQL>
                 SELECT max(date_en) 
                 FROM " + combobox1.SelectedItem.ToString() + "
             </SQL>.Value _
                    }
                    RecordCount = CLng(RecordCountcon.ExecuteScalar())
                    If RecordCount <> 0 Then
                        maxaccess = (maxaccesscon.ExecuteScalar())
                        Databaselatestdate.Text = maxaccess
                    Else
                        MessageBox.Show("Data base empty")
                    End If
                End Using
            End Using
        End Using

the name of the file , the name of the file that contain the database , the name of database and the name of the table is

= to the combobox1 value

as Source=C:\TSOSM\" + combobox1.SelectedItem.ToString() + "\" + combobox1.SelectedItem.ToString() + ".accdb

how can get the name of the table that = to the name of the combobox1 when i select it.

<SQL>
                 SELECT max(date_en)
                 FROM " + combobox1.SelectedItem.ToString() + "
             </SQL>.Value _

please help and

thank you

Answer 1

It should be pretty much what you have... that looks like it should be about right (though you should use & instead of + for strings).  You could also just use string.format:

Dim queryString As String = String.Format("SELECT max(date_en) FROM {0}", ComboBox1.SelectedItem)

---

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

Answer 2

thank you for your reply

this very short code

Dim queryString As String = String.Format("SELECT max(date_en) FROM {0}", ComboBox1.SelectedItem)

1-can i replace my code by using it.

this the code

Dim maxaccess As Date = Date.Parse((maxaccess))
        Dim RecordCount As Long = 0
        Using cn As New OleDbConnection With _
        { _
            .ConnectionString = _
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TSOSM\" & combobox1.SelectedItem.ToString() & "\" & combobox1.SelectedItem.ToString() & ".accdb" _
        }
            cn.Open()
            Using RecordCountcon As New OleDbCommand With _
                { _
                    .Connection = cn, _
                    .CommandText = _
                    <SQL>
                     SELECT count(*) 
                     FROM " & combobox1.SelectedItem.ToString() & "
                 </SQL>.Value _
                }
                'cn.Open()
                Using maxaccesscon As New OleDbCommand With _
                    { _
                        .Connection = cn, _
                        .CommandText = _
                        <SQL>
                 SELECT max(date_en) 
                 FROM " & combobox1.SelectedItem.ToString() & "
             </SQL>.Value _
                    }
                    RecordCount = CLng(RecordCountcon.ExecuteScalar())
                    If RecordCount <> 0 Then
                        maxaccess = (maxaccesscon.ExecuteScalar())
                        Databaselatestdate.Text = maxaccess
                    Else
                        MessageBox.Show("Data base empty")
                    End If
                End Using
            End Using
        End Using

2- i got Error Syntax error.  when i used &  as:

n.Open()
            Using RecordCountcon As New OleDbCommand With _
                { _
                    .Connection = cn, _
                    .CommandText = _
                    <SQL>
                     SELECT count(*) 
                     FROM " & combobox1.SelectedItem.ToString() & "
                 </SQL>.Value _
                }
                'cn.Open()
                Using maxaccesscon As New OleDbCommand With _
                    { _
                        .Connection = cn, _
                        .CommandText = _
                        <SQL>
                 SELECT max(date_en) 
                 FROM " & combobox1.SelectedItem.ToString() & "
             </SQL>.Value _
                    }
                    RecordCount = CLng(RecordCountcon.ExecuteScalar())
                    If RecordCount <> 0 Then
                        maxaccess = (maxaccesscon.ExecuteScalar())
                        Databaselatestdate.Text = maxaccess

thank you

Answer 3

Don't use those <SQL> tags...

Using RecordCountcon As New OleDbCommand With _
   { _
       .Connection = cn, _
       .CommandText = "SELECT count(*) FROM(" & combobox1.SelectedItem.ToString() & ")
   }

---

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

Answer 4

thank you for your reply

this very short code

Dim queryString As String = String.Format("SELECT max(date_en) FROM {0}", ComboBox1.SelectedItem)

1-can i replace my code by using it.

this the code

Dim maxaccess As Date = Date.Parse((maxaccess))
        Dim RecordCount As Long = 0
        Using cn As New OleDbConnection With _
        { _
            .ConnectionString = _
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TSOSM\" & combobox1.SelectedItem.ToString() & "\" & combobox1.SelectedItem.ToString() & ".accdb" _
        }
            cn.Open()
            Using RecordCountcon As New OleDbCommand With _
                { _
                    .Connection = cn, _
                    .CommandText = _
                    <SQL>
                     SELECT count(*) 
                     FROM " & combobox1.SelectedItem.ToString() & "
                 </SQL>.Value _
                }
                'cn.Open()
                Using maxaccesscon As New OleDbCommand With _
                    { _
                        .Connection = cn, _
                        .CommandText = _
                        <SQL>
                 SELECT max(date_en) 
                 FROM " & combobox1.SelectedItem.ToString() & "
             </SQL>.Value _
                    }
                    RecordCount = CLng(RecordCountcon.ExecuteScalar())
                    If RecordCount <> 0 Then
                        maxaccess = (maxaccesscon.ExecuteScalar())
                        Databaselatestdate.Text = maxaccess
                    Else
                        MessageBox.Show("Data base empty")
                    End If
                End Using
            End Using
        End Using

2- i got Error Syntax error.  when i used &  as:

n.Open()
            Using RecordCountcon As New OleDbCommand With _
                { _
                    .Connection = cn, _
                    .CommandText = _
                    <SQL>
                     SELECT count(*) 
                     FROM " & combobox1.SelectedItem.ToString() & "
                 </SQL>.Value _
                }
                'cn.Open()
                Using maxaccesscon As New OleDbCommand With _
                    { _
                        .Connection = cn, _
                        .CommandText = _
                        <SQL>
                 SELECT max(date_en) 
                 FROM " & combobox1.SelectedItem.ToString() & "
             </SQL>.Value _
                    }
                    RecordCount = CLng(RecordCountcon.ExecuteScalar())
                    If RecordCount <> 0 Then
                        maxaccess = (maxaccesscon.ExecuteScalar())
                        Databaselatestdate.Text = maxaccess

thank you

I see no reason not to use Reed's method but if you want to go the direction you are then

Using maxaccesscon As New OleDb.OleDbCommand With _
    { _
        .Connection = cn, _
        .CommandText = _
            <SQL>
                SELECT max(date_en) 
                FROM <%= ComboBox1.SelectedItem.ToString() %>
            </SQL>.Value _
    }
End Using

---

KSG

Answer 5

thank you it is working

 Dim maxaccess As Date = Date.Parse((maxaccess))
        Dim RecordCount As Long = 0
        Using cn As New OleDbConnection With _
        { _
            .ConnectionString = _
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TSOSM\" & combobox1.SelectedItem.ToString() & "\" & combobox1.SelectedItem.ToString() & ".accdb" _
        }
            cn.Open()
            Using RecordCountcon As New OleDbCommand With _
     { _
         .Connection = cn, _
         .CommandText = "SELECT count(*) FROM(" & combobox1.SelectedItem.ToString() & ")"
     }
                Using maxaccesscon As New OleDb.OleDbCommand With _
    { _
        .Connection = cn, _
        .CommandText = "SELECT max(date_en)  FROM(" & combobox1.SelectedItem.ToString() & ")"
    }
                    RecordCount = CLng(RecordCountcon.ExecuteScalar())
                    If RecordCount <> 0 Then
                        maxaccess = (maxaccesscon.ExecuteScalar())
                        Databaselatestdate.Text = maxaccess
                    Else
                        MessageBox.Show("Data base empty")
                    End If
                End Using
            End Using
        End Using

Thanks a lot

Answer 6

thank you

Kevininstructor

 Dim maxaccess As Date = Date.Parse((maxaccess))
        Dim RecordCount As Long = 0
        Using cn As New OleDbConnection With _
        { _
            .ConnectionString = _
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TSOSM\" & combobox1.SelectedItem.ToString() & "\" & combobox1.SelectedItem.ToString() & ".accdb" _
        }
            cn.Open()
            Using RecordCountcon As New OleDbCommand With _
                { _
                    .Connection = cn, _
                    .CommandText = _
                    <SQL>
                        SELECT count(*) 
                        FROM <%= combobox1.SelectedItem.ToString() %>
                    </SQL>.Value _
                }
                'cn.Open()
                Using maxaccesscon As New OleDb.OleDbCommand With _
     { _
         .Connection = cn, _
         .CommandText = _
             <SQL>
                   SELECT max(date_en) 
                   FROM <%= combobox1.SelectedItem.ToString() %>
             </SQL>.Value _
     }
                    RecordCount = CLng(RecordCountcon.ExecuteScalar())
                    If RecordCount <> 0 Then
                        maxaccess = (maxaccesscon.ExecuteScalar())
                        Databaselatestdate.Text = maxaccess
                    Else
                        MessageBox.Show("Data base empty")
                    End If
                End Using
            End Using
        End Using

it is working