Display SQL results in DataGrid

[Steven Smith]

Hi guys

What I'm trying to do today is display the results from
an SQL query on one table in a Datagrid I'm using the
following code to query the table.

\\\
Try

If txtCustomersIDOrders.Text <> "" Then
DataSetCustomersOrders.Clear()

OleDbDataAdapterCustomersOrders.SelectCommand.CommandText
= "SELECT * FROM Orders WHERE " & "[Customer ID] = '" &
txtCustomersIDOrders.Text & "' "

OleDbDataAdapterCustomersOrders.Fill
(DataSetCustomersOrders)

Else
txtCustomersIDOrders.Text = "Enter users ID here"
End If

Catch oledbExceptionparameter As
System.Data.OleDb.OleDbException
Catch invalidoperationexceptionparameter As
InvalidOperationException

End Try
///

The datagrid is bound to the 'DataSetCustomersOrders'
DataSet and a TextBox is being used to feed the WHERE
clause in the SQL statement I also know for sure that
records meeting the criteria exist but no results are
being displayed in the grid when I hit my command button
to trigger the event.

I know I could do this easily using a DataView / Filter
but I would really like to get it working through SQL for
my own benefit

Thanks in advance

Regards Steve

 

[Cor]

Hi Steven,

I mis the connection in your text.
If you already have done a query in some statements before, it maybe will
work

But because the way you do that catch of the exceptions I think it does
nothing.

Doing in that way the catch is absolute something you never should do.

In that way you program run but does nothing or maybe even dangerous
operations.

After every catch has to be an action, the minimum is
messagebox.show(exception.tostring)

I dont do the filling of a dataset it the way you do, I do it mostly like
this
\\\
Dim Conn As New OleDbConnection(connectionstring)
Try
Dim cmd As New OleDbCommand(sqlStr, Conn)
Dim ds As New DataSet
Dim da As New OleDbDataAdapter(cmd)
da.Fill(ds, "table")
etc
//

But you can try the way you did, I did not see it complete in the
documentation, but you can try by adding what I wrote between the rows,
because when the dataadaper command knows the connection, it is not
impossible that it works. (If a dataconnection is not open the datadapter
does it himself)

If you do it that way, tell me if it did work will you?

Cor

OleDbDataAdapterCustomersOrders.SelectCommand.CommandText
= "SELECT * FROM Orders WHERE " & "[Customer ID] = '" &
txtCustomersIDOrders.Text & "' "

OleDbDataAdapterCustomersOrders.SelectCommand.Connection =
(Your OleDbConnection)

 

[Cor]

Hi Steven,

It would be a hell of a job to find the name of that already opened
connection.

But as far as I know you can use more connections in one program.

You need a connection string.
You find it in that initialize part, but you can also look here

http://www.connectionstrings.com/

And then that code in the end yes and all comments I placed between.

I should then not know why it would not work

But if not message, but your problem is simple for me, maybe I made a typeo
somewhere.

Cor

The connection is opened here and is used for other
INSERT, UPDATE & DELETE commands elsewhere in the
application so it must remain live

\\\
Public Sub New()
MyBase.New()

'This call is required by the Windows Form
Designer.
InitializeComponent()

'Add any initialization after the
InitializeComponent() call
OleDbConnection1.Open()
End Sub
///



I added messages to the catch statements and this one is
always thrown:

Because there is no connection yes.

\\\
Catch oledbExceptionparameter As
System.Data.OleDb.OleDbException
MessageBox.Show
("System.Data.OleDb.OleDbException")
///

But my grid still does not show any results


To use your method I should replace:

\\\
OleDbDataAdapterCustomersOrders.Fill
(DataSetCustomersOrders)
///

with

\\\

dim connectionstring as string = "the founded connectionstring"
dim sqlStr = "that created sql string of you"

Dim Conn As New OleDbConnection(connectionstring)
Try
Dim cmd As New OleDbCommand(sqlStr, Conn)
Dim ds As New DataSet
Dim da As New OleDbDataAdapter(cmd)
da.Fill(ds, "table")

here the right datasetname and table name (the table name is not really
necassery)

///

Cor