How do I complete this?

[Jeff]

I'm still new to vb.net and even newer to using stored procedures in mysql.
The called procedure will return a single row from the DB.
I'm not sure how to get that value into a DataTable. I think I need to use
some type of adapter. Could someone help me complete this?

Thanks in advance
Jeff

Dim connection2 As New OdbcConnection(MyConnString)
Dim command2 As New OdbcCommand("call SelectFromParticipants(?)",
connection2)
Dim param As New OdbcParameter("User", OdbcType.VarChar)
param.Value = Session("User")
command2.Parameters.Add(param)
connection2.Open()
command2.ExecuteNonQuery()
DataTable = <- not sure how to complete this.
connection2.Close()

 

[rowe_newsgroups]

Dim connection2 As New OdbcConnection(MyConnString)

Dim command2 As New OdbcCommand("call SelectFromParticipants(?)",
connection2)
Dim param As New OdbcParameter("User", OdbcType.VarChar)
param.Value = Session("User")
command2.Parameters.Add(param)
connection2.Open()
command2.ExecuteNonQuery()
DataTable = <- not sure how to complete this.
connection2.Close()

Change this to something like this:

Dim connection2 As New OdbcConnection(MyConnString)
Dim command2 As New OdbcCommand("call
SelectFromParticipants(?)", connection2)
command2.Parameters.Add("User", OdbcType.VarChar).Value =
Session("User")
Dim da As New OdbcDataAdapter(command2)
Dim dt As New DataTable("My Table")
da.Fill(dt)

Please note I left out the disposing statements for simplicity's sake
(though you should dispose of these objects to ensure fast cleanup) I
usually wrap the usage of each object in using statements.

Also, is there a reason you want to dump a single row into a
datatable? If you just want to retrieve that value you could also use
a DataReader instead of the DataAdapter/DataTable pair.

Thanks,

Seth Rowe

 

[Jeff]

...I left out the disposing statements for simplicity's sake
(though you should dispose of these objects to ensure fast cleanup) I
usually wrap the usage of each object in using statements.

Also, is there a reason you want to dump a single row into a
datatable? If you just want to retrieve that value you could also use
a DataReader instead of the DataAdapter/DataTable pair.

Seth Rowe

It worked. Thanks.
By "using statements," I assume that you mean something like below?
This works, but I am not yet certain that I understand the cleanup issue and
when I do and don't need to consider cleanup.
I am using dataAdapter/DataTable for a single row only because the first
code that I learned required an entire table and I don't yet have an example
for a single row (i.e., the WWWTable below contains multiple rows).
Everytime I write something like this, Dim sss As New DataRow , I get an
error about sss being "protected" and can't go further. Would you be so kind
as to provide an example or give me a hint about what might be wrong?

Jeff

WWWTable = New DataTable
UUUTable = New DataTable
Using conn As New OdbcConnection(MyConnString)
Dim adapter As OdbcDataAdapter = New OdbcDataAdapter("call
XXX", conn)
adapter.Fill(WWWTable)

Dim command As New OdbcCommand("call YYY(?)", conn)
command.Parameters.Add("User", OdbcType.VarChar).Value =
Session("User")
Dim da As New OdbcDataAdapter(command)
da.Fill(UUUTable)
End Using

 

[rowe_newsgroups]

By "using statements," I assume that you mean something like below?

Yes, only I would also use them on the command and adapter objects
(IMO you should use them on any object the implements the IDisposable
interface (has a dispose method)).

This works, but I am not yet certain that I understand the cleanup issue and
when I do and don't need to consider cleanup.

**Note - I'm not trying to start another Dispose/Don't Dispose war
here**

Consider the following:

Public Sub Foo()
Dim connection as new OleDbConnection(connString)
Dim com as OleDbCommand = conn.CreateCommand()
com.commandtext = "Select * From SomeWhere"
com.ExecuteNonQuery()
End Sub

As is, that procedure will create two objects(a connection and command
object) and use them. When the procedure exits, both of these objects
will go out of scope and be marked for collection. Eventually, the
garbage collector (the GC) will execute it's collect method and "spot"
these two objects and will call their Finalize methods. The Finalize
methods will call the objects Dispose method which will do the actual
clean up of resources it uses. After having the Finalize method called
by the GC, the object will be collected during the next garbage
collection cycle. During the time between the objects going out of
scope and being collected by the GC, the objects are needlessly using
resources.

So we can change the code to this and use Using statements to manually
dispose of the objects:

Public Sub Foo()
Dim conn as new OleDbConnection(connString)
Using (conn)
Dim com as OleDbCommand = conn.CreateCommand()
Using (com)
com.commandtext = "Select * From SomeWhere"
com.ExecuteNonQuery()
End Using
End Using
End Sub

This way, as soon as the using statements finish the objects will
dispose of themselves and clean up any resources they uses, as well as
suppressing their finalize methods. The first benefit of this is that
the object is no longer needlessly holding on to other resources while
it waits to go through the garbage collection process. The second
benefit is that when the GC executes, it will see that the objects
don't need to have there Finalize methods called (because of the
suppressfinialize call in the dispose method), and will immediately
collect the object, instead of having to wait until the next
collection cycle to do so.

I hope I made sense there, I was typing awfully fast. If you have any
more questions please ask and I'll try to answer them!

Thanks,

Seth Rowe

 

[Jeff]

I hope I made sense there, I was typing awfully fast. If you have any
more questions please ask and I'll try to answer them!

Seth Rowe

....made sense. I'll try the code. ...but could you give me a brief example
of using the datarow rather than the data table for the second part of my
code below where UUUTable is really a single row. When I try, I get close,
but something is still missing.

Thanks

Jeff

 

[rowe_newsgroups]

...but could you give me a brief example

of using the datarow rather than the data table for the second part of my
code below where UUUTable is really a single row. When I try, I get close,
but something is still missing.

Oops, I meant to do that in the last post, but I went to lunch in the
middle of my reply and forgot about it when I got back

Here's two methods for retrieving the single row, the first uses a
DataRow, while the second uses a DataReader instead. Both will show a
messagebox for each value in the datarow/datareader. Also, note I used
the System.Data.SqlClient class instead of the System.Data.Obdc class,
so you'll need to change that.

' Using a DataRow
Private Sub Foo()
Dim conn As New SqlConnection(connString)
Using (conn)
conn.Open()
Dim com As SqlCommand = conn.CreateCommand()
Using (com)
com.CommandType = CommandType.Text
com.CommandText = "Select Top 1 * From MyTable"
Dim da As New SqlDataAdapter(com)
Using (da)
Dim dt As New DataTable("My Table")
Using (dt)
da.Fill(dt)
Dim dr As DataRow = dt.Rows(0)
For i As Int32 = 0 To dr.ItemArray.Length - 1
MessageBox.Show(dr(i).ToString())
Next
End Using
End Using
End Using
End Using
End Sub

' Using the DataReader
Private Sub Foo2()
Dim conn As New SqlConnection(connString)
Using (conn)
conn.Open()
Dim com As SqlCommand = conn.CreateCommand()
Using (com)
com.CommandType = CommandType.Text
com.CommandText = "Select Top 1 * From MyTable"
Dim dr As SqlDataReader =
com.ExecuteReader(CommandBehavior.SingleRow)
Using (dr)
If (dr.Read()) Then
For i As Int32 = 0 To dr.FieldCount - 1
MessageBox.Show(dr.GetValue(i).ToString())
Next
End If
End Using
End Using
End Using
End Sub


I hope that helps!

Thanks,

Seth Rowe

 

[Jeff]

I hope that helps!

Thanks,

Seth Rowe

Yes. Thanks. ...but now that I see how this is accomplished, I am wondering
why exactly it is better to use a DataRow instead of a DataTable when
returning a single row. ...i.e. you actually return the entire table first
and then subsequently strip off all but the first row, so you have an extra
computational step. If you just left the query result in the form of a
table, you would save that step. ...but perhaps this is offset by how vb
manages memory? Does it matter?

Jeff

 

[rowe_newsgroups]

Yes. Thanks. ...but now that I see how this is accomplished, I am wondering
why exactly it is better to use a DataRow instead of a DataTable when
returning a single row. ...i.e. you actually return the entire table first
and then subsequently strip off all but the first row, so you have an extra
computational step. If you just left the query result in the form of a
table, you would save that step. ...but perhaps this is offset by how vb
manages memory? Does it matter?

Jeff

What do you plan on doing with the single row returned from the
database? If you just want to retrieve the value(s) then using a
datareader is going to be your best bet. DataRows and DataTables are
too much overhead to just get some values from a database.

Thanks,

Seth Rowe

 

[Jeff]

What do you plan on doing with the single row returned from the
database? If you just want to retrieve the value(s) then using a
datareader is going to be your best bet. DataRows and DataTables are
too much overhead to just get some values from a database.
Seth Rowe

The application requires me to use a number of values from the DB, but only
one row at a time. I'm still new to this and haven't yet learned to use the
datareader. ...don't suppose that you would want to provide an example
where a few columns are placed into variables?

Jeff

 

[Jeff]

....don't suppose that you would want to provide an example

where a few columns are placed into variables?

Oops, I skimmed your earlier post and just looked at it more carefully and
realized that you already did this.

Thanks.

....but it would seems that the use of the datarow might be better in the
case where the values are a mixture of integer, character, etc.?

Jeff

 

[rowe_newsgroups]

...don't suppose that you would want to provide an example


Oops, I skimmed your earlier post and just looked at it more carefully and
realized that you already did this.

Thanks.

...but it would seems that the use of the datarow might be better in the
case where the values are a mixture of integer, character, etc.?

Jeff

--


...but it would seems that the use of the datarow might be better in the
case where the values are a mixture of integer, character, etc.?

In my opinion the datareader is better for getting db values into
variables. I really only use DataTables when I display data to the
user and have him/her change things, and then I update the database
from that. For simple data retrieval I always rely on a datareader to
do the job.

The datareader is forward reading only, moving forward one "row"
whenever you call it's .Read() method. You pull the information out of
the reader by it's column index in it's GetType() method.

So you could do something like this:

' This is all typed in the message so it may have some errors

Dim dr as DataReader = command.ExecuteReader()
while (dr.Read())
Dim int as Int32 = dr.GetInt32(0)
Dim str as String = dr.GetString(1)
Dim dbl as Double = dr.GetDouble(2)
' Then do something with the variables
end while

Thanks,

Seth Rowe