Multiple executescalar()

16 Jul 2006 2:57 PM

Stephen Plotnick

I think I have a speed issue I'd like to resolve.

I have a DataGrid that on every line I need to get four records from a data
base as I scroll thorugh the records.

THanks to Steve I can get the four records with ExeuteScalar but the speed
is very slow now.

In my code I do four conn.open and four conn.close with four different OleDb
Commands and four different SQL statements.

If there a way to setup one and keep changing the SQL statement and than
closing it at the end.

I'm posting one of the four here:

If myDV(rowIndex)("SDSNo").ToString <> "" Then

Dim conn3 As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
source=C:\BMActivityReporting.mdb;Persist Security Info=False")

Dim sSQL3 As String = "select UserName FROM UserTable WHERE UserNo='" &
myDV(rowIndex)("SDSNo").ToString() & "'"

Dim da3 As New System.Data.OleDb.OleDbCommand(sSQL3, conn3)

conn3.Open()

Try

da3.ExecuteScalar()

Me.SDS.Text = da3.ExecuteScalar.ToString()

Catch ex As System.Data.OleDb.OleDbException

Dim errorMessages As String

Dim i As Integer

For i = 0 To ex.Errors.Count - 1

errorMessages += "Index #" & i.ToString() & ControlChars.Cr _

& "Message: " & ex.Errors(i).Message & ControlChars.Cr _

& "NativeError: " & ex.Errors(i).NativeError & ControlChars.Cr _

& "Source: " & ex.Errors(i).Source & ControlChars.Cr _

& "SQLState: " & ex.Errors(i).SQLState & ControlChars.Cr

Next i

MsgBox(errorMessages)

Finally

conn3.Close()

End Try

End If

If myDV(rowIndex)("SDSNo").ToString() = "" Then

Me.SDS.Text = ""

End If

Thanks in advance,

Steve

7 Jul 2006 4:13 PM

Cor Ligthert [MVP]

Stephen,

Using an execute scalar to get a part of data for a datagrid is strange.

It is absolute not bounded than anymore.

Probably would a related datatable do its job better, so reply to your
original problem, or give us the problem again.

Cor

Show quoteHide quote

"Stephen Plotnick" <splotn***@groupcbf.com> schreef in bericht
news:MeednS4OSpFlzifZnZ2dnUVZ_sednZ2d@giganews.com...
>I think I have a speed issue I'd like to resolve.
>
> I have a DataGrid that on every line I need to get four records from a
> data base as I scroll thorugh the records.
>
> THanks to Steve I can get the four records with ExeuteScalar but the speed
> is very slow now.
>
> In my code I do four conn.open and four conn.close with four different
> OleDb Commands and four different SQL statements.
>
> If there a way to setup one and keep changing the SQL statement and than
> closing it at the end.
>
> I'm posting one of the four here:
>
> If myDV(rowIndex)("SDSNo").ToString <> "" Then
>
> Dim conn3 As New
> System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
> source=C:\BMActivityReporting.mdb;Persist Security Info=False")
>
> Dim sSQL3 As String = "select UserName FROM UserTable WHERE UserNo='" &
> myDV(rowIndex)("SDSNo").ToString() & "'"
>
> Dim da3 As New System.Data.OleDb.OleDbCommand(sSQL3, conn3)
>
> conn3.Open()
>
> Try
>
> da3.ExecuteScalar()
>
> Me.SDS.Text = da3.ExecuteScalar.ToString()
>
> Catch ex As System.Data.OleDb.OleDbException
>
> Dim errorMessages As String
>
> Dim i As Integer
>
> For i = 0 To ex.Errors.Count - 1
>
> errorMessages += "Index #" & i.ToString() & ControlChars.Cr _
>
> & "Message: " & ex.Errors(i).Message & ControlChars.Cr _
>
> & "NativeError: " & ex.Errors(i).NativeError & ControlChars.Cr _
>
> & "Source: " & ex.Errors(i).Source & ControlChars.Cr _
>
> & "SQLState: " & ex.Errors(i).SQLState & ControlChars.Cr
>
> Next i
>
> MsgBox(errorMessages)
>
> Finally
>
> conn3.Close()
>
> End Try
>
> End If
>
> If myDV(rowIndex)("SDSNo").ToString() = "" Then
>
> Me.SDS.Text = ""
>
> End If
>
>
>
>
>
> Thanks in advance,
>
> Steve
>
>