Command object Problem (ExecuteScalar)

[Rick]

Hello all -

the following code runs once fine but during the second
pass i get a system error and it stops. the purpose of
the code is to take the rows of the dataset table which
contains sql statements for each table in the oracle and
sql server databases that i need to query to find out if
there are any records in each table given a searchvalue
from the user. The general error is of no user so i would
really appreaciate any help in this problem. TIA

Rick

************************

Private Sub Button2_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button2.Click
'this sub uses a dataset to populate a DataTable
and send each row
'of the DataTable to the sub FindRecords to
'get the record count of each sql statement sent
to the sub FindRecords

Dim strConn As String
Dim strSQL As String

strConn = Me.SqlConnection1.ConnectionString
strSQL = "SELECT
TBL_MASTER_DATASOURCES.DataSourcesConnection, " & _
"TBL_MASTER_DATASOURCES_SQL.SearchSQL,
" & _
"TBL_MASTER_DATASOURCES.DataSourcesDesc
ription, " & _
"TBL_MASTER_DATASOURCES.DataSources, "
& _
"TBL_MASTER_DATASOURCES_SQL.SearchbyID,
" & _
"TBL_MASTER_DATASOURCES.[DATABASE] " &
_
"FROM " & _
"TBL_MASTER_DATASOURCES " & _
"INNER JOIN " & _
"TBL_MASTER_DATASOURCES_SQL " & _
"ON " & _
"TBL_MASTER_DATASOURCES.SearchID =
TBL_MASTER_DATASOURCES_SQL.SearchID " & _
"WHERE " & _
"(TBL_MASTER_DATASOURCES_SQL.SearchbyID
= 1)"

Dim da As New SqlClient.SqlDataAdapter(strSQL,
strConn)
Dim ds As New DataSet
da.Fill(ds, "test")
Dim tbl As DataTable = ds.Tables(0)
Dim row As DataRow = tbl.Rows(0)
Dim i As Integer
For i = 0 To tbl.Rows.Count - 1
FindRecords(tbl.Rows(i), Me.TextBox1.Text)
Next i


End Sub
****************************************
Private Sub FindRecords(ByVal row As DataRow, ByVal
strSearchValue As String)
Dim strSql As String
'construct the sql from the datarow
strSql = row(1) & "'" & strSearchValue & "'"
'determine which database to query from the row
Dim strDB As String = Trim(row(5)).ToString


Select Case strDB
Case Is = "SQL"
'create and open new connection using the
connect string from the existing connection object
Dim cn As New SqlClient.SqlConnection
(Me.SqlConnection1.ConnectionString.ToString)
cn.Open()
'create a command object
Dim cmd As SqlClient.SqlCommand =
cn.CreateCommand
cmd.CommandText = strSql
'create variable to hold the answer
Dim intAnswer As Integer = CInt
(cmd.ExecuteScalar())
'close the connection
cn.Close()
'display answer
MsgBox(intAnswer)

Case Is = "Oracle"
Dim cn As New OracleClient.OracleConnection
(Me.OracleConnection1.ConnectionString.ToString)
cn.Open()
Dim cmd As OracleClient.OracleCommand =
cn.CreateCommand
cmd.CommandText = strSql
Dim intAnswer As Integer = CInt
(cmd.ExecuteScalar())
cn.Close()
MsgBox(intAnswer)
End Select

End Sub

 

[Guest]

well i figured it out... thought i would share... i was
sending bad sql statements to the command object and it
thru the error. Other wise the code works fine. The
error message is certainly criptic at best... ie
system.error. hope this helps someone else..

rick

-----Original Message-----
Hello all -

the following code runs once fine but during the second
pass i get a system error and it stops. the purpose of
the code is to take the rows of the dataset table which
contains sql statements for each table in the oracle and
sql server databases that i need to query to find out if
there are any records in each table given a searchvalue
from the user. The general error is of no user so i would
really appreaciate any help in this problem. TIA

Rick

************************

Private Sub Button2_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button2.Click
'this sub uses a dataset to populate a DataTable
and send each row
'of the DataTable to the sub FindRecords to
'get the record count of each sql statement sent
to the sub FindRecords

Dim strConn As String
Dim strSQL As String

strConn = Me.SqlConnection1.ConnectionString
strSQL = "SELECT
TBL_MASTER_DATASOURCES.DataSourcesConnection, " & _
"TBL_MASTER_DATASOURCES_SQL.SearchSQL,

" & _
"TBL_MASTER_DATASOURCES.DataSourcesDes c
ription, " & _
"TBL_MASTER_DATASOURCES.DataSources, "

& _
"TBL_MASTER_DATASOURCES_SQL.SearchbyID ,
" & _
"TBL_MASTER_DATASOURCES.[DATABASE] " &
_
"FROM " & _
"TBL_MASTER_DATASOURCES " & _
"INNER JOIN " & _
"TBL_MASTER_DATASOURCES_SQL " & _
"ON " & _
"TBL_MASTER_DATASOURCES.SearchID =
TBL_MASTER_DATASOURCES_SQL.SearchID " & _
"WHERE " & _
"(TBL_MASTER_DATASOURCES_SQL.SearchbyI D
= 1)"

Dim da As New SqlClient.SqlDataAdapter(strSQL,
strConn)
Dim ds As New DataSet
da.Fill(ds, "test")
Dim tbl As DataTable = ds.Tables(0)
Dim row As DataRow = tbl.Rows(0)
Dim i As Integer
For i = 0 To tbl.Rows.Count - 1
FindRecords(tbl.Rows(i), Me.TextBox1.Text)
Next i


End Sub
****************************************
Private Sub FindRecords(ByVal row As DataRow, ByVal
strSearchValue As String)
Dim strSql As String
'construct the sql from the datarow
strSql = row(1) & "'" & strSearchValue & "'"
'determine which database to query from the row
Dim strDB As String = Trim(row(5)).ToString


Select Case strDB
Case Is = "SQL"
'create and open new connection using the
connect string from the existing connection object
Dim cn As New SqlClient.SqlConnection
(Me.SqlConnection1.ConnectionString.ToString)
cn.Open()
'create a command object
Dim cmd As SqlClient.SqlCommand =
cn.CreateCommand
cmd.CommandText = strSql
'create variable to hold the answer
Dim intAnswer As Integer = CInt
(cmd.ExecuteScalar())
'close the connection
cn.Close()
'display answer
MsgBox(intAnswer)

Case Is = "Oracle"
Dim cn As New OracleClient.OracleConnection
(Me.OracleConnection1.ConnectionString.ToString)
cn.Open()
Dim cmd As OracleClient.OracleCommand =
cn.CreateCommand
cmd.CommandText = strSql
Dim intAnswer As Integer = CInt
(cmd.ExecuteScalar())
cn.Close()
MsgBox(intAnswer)
End Select

End Sub
.