Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    66

    Unhappy Unanswered: How to Check recordset return?

    how to check if a sql statement has recordset return.
    thanks.
    Public Function m_execsql()
    Dim connStr As String
    connStr = "User ID=sa;Password=sa;Initial Catalog=Northwind;Provider=""SQLOLEDB.1"";Data Source=MINGLEI"
    conn = New System.Data.OleDb.OleDbConnection(connStr)
    conn.Open()
    Dim sql(3) As String
    sql(0) = "select * from product;"
    sql(1) = "select * from orders;"
    sql(2) = "SELECT * FROM Customers;"
    sql(3) = "SELECT * FROM Customers;"
    Dim i As Integer
    For i = 0 To sql.GetUpperBound(0) - 1
    Try
    da = New OleDb.OleDbDataAdapter(sql(i), conn)
    Catch e As Exception
    MessageBox.Show(e.Message)
    End Try
    Try
    dt = New DataTable
    ds = New DataSet("n" & i.ToString)
    MessageBox.Show("n" & i.ToString)
    da.Fill(ds, "n" & i.ToString)
    grd.SetDataBinding(ds, "n" & i.ToString)
    'grd.DataSource = ds
    Catch eds As Exception
    MessageBox.Show(eds.Message)
    End Try
    Next
    conn.Close()
    conn = Nothing
    End Function

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The global variable @@Rowcount always indicates the number of records affected by the last query. If @@Rowcount > 0, your statement returned a recordset.

    blindman

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    look for something like this:

    if rs.BOF and rs.EOF then
    'recordset is empty
    else
    'recordset has at least one row returned
    end if

  4. #4
    Join Date
    Sep 2003
    Posts
    66
    see this code:
    conn.Open()
    Dim sql(3) As String
    sql(0) = "select * from products;"
    sql(1) = "select * from orders;"
    sql(2) = "create table t1;"
    sql(3) = "seletc * from t1;"
    Dim i As Integer
    For i = 0 To sql.GetUpperBound(0) - 1
    Try
    da = New OleDb.OleDbDataAdapter(sql(i), conn)
    Catch e As Exception
    MessageBox.Show(e.Message)
    End Try
    'Try
    ds = New DataSet("dsTable")
    dt = New DataTable
    da.Fill(ds)
    'grd.SetDataBinding(ds, i.ToString)
    grd.DataSource = dt
    'Catch eds As Exception
    ' MessageBox.Show(eds.Message)
    'End T
    Next
    conn.Close()
    if the table is empty, as t1 in sql(2), after exec sql(3), select * from t1;
    will meet a system.data.oledb.oledbexecption at line da.fill(ds),
    I do not have chance to check ds.tables(0).row.cout, what should i do.
    thanks.

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    stick with ADO. it's clearer and cleaner

  6. #6
    Join Date
    Sep 2003
    Posts
    66
    ? i cannt understand.
    can show me a sample code?
    thanks.
    Originally posted by ms_sql_dba
    stick with ADO. it's clearer and cleaner

  7. #7
    Join Date
    Sep 2003
    Posts
    60
    iqueen, you may be in the wrong forum (ADO.NET is probably in the .Net section) but this may help...It shows how to handle errors during the fill operation.


    AddHandler myDA.FillError, New FillErrorEventHandler(AddressOf FillError)

    Dim myDS As DataSet = New DataSet
    myDA.Fill(myDS, "MyTable")

    Private Shared Sub FillError(sender As Object, args As illErrorEventArgs)
    If args.Errors.GetType() Is Type.GetType "System.OverflowException") Then
    ' Code to handle precision loss.
    ' Add a row to table using the values from the first two columns.
    DataRow myRow = args.DataTable.Rows.Add(New Object() {args.Values(0), args.Values(1), DBNull.Value})
    ' Set the RowError containing the value for the third column.
    args.RowError = "OverflowException Encountered. Value from data source: " & args.Values(2)

    args.Continue = True
    End If
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •