datarowview

[Guest]

I have a listbox that pulls data from an access database through the OLEDBDataAdapter. In the same project I have OLEDBDataAdapter Command using SQL that's referencing the selected item in the listbox. I get an error in "Fill" command. The reason being I think is that I'm trying to convert a datarowview to a string in my SQL. Here's the SQL.

"select * from '" & ListBox1.SelectedItem.ToString & "' where Entity = '" & ComboBox1.Text.Trim & "'", MyConnection)

The oddity of it all is that the "ComboBox1.Text.Trim" has a similar relationship where it's being filled through an OLEDBDataAdapter. The SQL substitutes combobox1.text into query.

Any help appreciated. I've been stuck on this line for two weeks.

thanks

 

[John Saunders]

I have a listbox that pulls data from an access database through the

OLEDBDataAdapter. In the same project I have OLEDBDataAdapter Command using
SQL that's referencing the selected item in the listbox. I get an error in
"Fill" command. The reason being I think is that I'm trying to convert a
datarowview to a string in my SQL. Here's the SQL.

"select * from '" & ListBox1.SelectedItem.ToString & "' where Entity =

'" & ComboBox1.Text.Trim & "'", MyConnection)

The oddity of it all is that the "ComboBox1.Text.Trim" has a similar

relationship where it's being filled through an OLEDBDataAdapter. The SQL
substitutes combobox1.text into query.

ListBox1.SelectedItem will be a DataRowView when you have bound the listbox
to a data source. You don't want SelectedItem at all. You want
ListBox1.SelectedValue instead.

 

[Guest]

Thanks John. I had tried selectedvalue before as well. I even have a textbox that displays the selected item from the listbox and tried substituting it in the query. I get an error on the Mycommand.Fill(DS1) in the following code. The error is:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

The code is:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim DS1 As System.Data.DataSet
Dim DT As System.Data.DataTable
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection

Dim Cat As String
Cat = ListBox1.SelectedValue
MsgBox(Cat)


MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Inetpub\wwwroot\intelis.MDB")

MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from '" & ListBox1.SelectedValue.ToString & "' where Entity = '" & ComboBox1.Text.Trim & "'", MyConnection)

DS1 = New System.Data.DataSet
DS1.Clear()

MyCommand.Fill(DS1)

DT = DS1.Tables(0)
' DataGrid1.DataMember =
DataGrid1.DataSource = DT


' Dim form2 As New Form2
'form2.Show()

' MyConnection.Close()
End Sub

 

[John Saunders]

I got it to work. Thanks John. The SelectedValue was the answer. I had

single quotes surrounding the reference to the listbox. Here's the correct
SQL for future reference.

"select * from " & ListBox1.SelectedValue.ToString & " where Entity = '"

& ComboBox1.Text.Trim & "'", MyConnection)

I'm glad you got it to work. However, I can't let you go without the
requisite lecture about dynamic SQL. You should use parameterized queries
instead. See "Protecting Against SQL Injection"
(http://www.winnetmag.com/Article/ArticleID/42216/42216.html)
--
John Saunders
johnwsaundersiii at hotmail

textbox that displays the selected item from the listbox and tried
substituting it in the query. I get an error on the Mycommand.Fill(DS1) in
the following code. The error is: