Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Sep 2003
    Posts
    66

    Question Unanswered: howto receive "SQL State"

    I am a rookie using vb.net ado.net connect sql server. How to get sql state after each sql execute.
    tanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SQL state?

    I'm guessing what you mean here, but lookup @@ERROR in Books Online.

    blindman

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    If your statement fails, you can retrieve ADO.NET's error collection. If your statements succeeds, one of your EXECUTE parameters contains the number of rows affected in case of an DML statement, otherwise you use the RecordCount property of your result recordset.

    Was this the answer you are looking for?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Sep 2003
    Posts
    66
    I think yes. But how to retrieve ADO.Net 's Error Collection? Many thanks.
    Originally posted by DoktorBlue
    If your statement fails, you can retrieve ADO.NET's error collection. If your statements succeeds, one of your EXECUTE parameters contains the number of rows affected in case of an DML statement, otherwise you use the RecordCount property of your result recordset.

    Was this the answer you are looking for?

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    It's a property of your connection object.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Sep 2003
    Posts
    66
    can show me a code sample?thanks.
    (C# or VB.net)

  7. #7
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Look for this example in C#.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  8. #8
    Join Date
    Sep 2003
    Posts
    66
    I have read it before, but I still do not know how ...?
    In the sample code I did not see any ADO.error. ... nor EffectedRow ...
    Last edited by iqueen; 09-11-03 at 06:45.

  9. #9
    Join Date
    Sep 2003
    Posts
    66
    there have more than one sql 'stmt to be 'exec in one connection.
    How to retrieve error like "myOLEDBException.Errors(i).Message" this.

  10. #10
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    You see the line

    catch(OleDbException myOLEDBException)

    ? .NET banned the term error, and replaced it with exception, but it's the same. You don't see a recordaffected, because no DML operation was performed.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  11. #11
    Join Date
    Sep 2003
    Posts
    66
    see this code:
    Sub AddRecord()
    Dim sConnectionString As String _
    = "User ID=sa;Initial Catalog=pubs;Data Source=(local)"
    Dim objConn As New SqlConnection(sConnectionString)
    objConn.Open()
    Dim sSQL As String = "INSERT INTO Employee " & _
    "(emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)" & _
    "VALUES ('MSD12923F', 'Duncan', 'W', 'Mackenzie', " & _
    "10, 82,'0877','2001-01-01')"
    Dim objCmd As New SqlCommand(sSQL, objConn)
    Try
    objCmd.ExecuteNonQuery()
    Catch e As Exception
    Console.WriteLine(e.Message)
    End Try
    Console.WriteLine("Record Added")
    End Sub
    why try catch after a insert sql stmt , then how to catch the error of the insert sql stmt.

  12. #12
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by iqueen
    see this code:
    Catch e As Exception
    Console.WriteLine(e.Message)
    Your problem is, that you are catching the (client) application error / exception, but you should catch the DB error / exception as in C#

    catch(OleDbException myOLEDBException)

    I guess in VB.NET it will look like:

    catch e as OleDbException

    Herewith, you have access to the error /exception collection.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  13. #13
    Join Date
    Sep 2003
    Posts
    66
    yes,
    Try
    cn = New System.Data.OleDb.OleDbConnection(m_loginform.logi n_str)
    cn.Open()
    da = New OleDb.OleDbDataAdapter(in_stmt(i).value, cn)
    Catch e As System.Data.OleDb.OleDbException
    m_status.addmsg("connect : " & e.Message, "error")
    End Try
    if I send a wrong sql code "select * from product"(the correct is "products"),
    if only return "object name 'product' invalidation '
    i did not see any sql state .
    how to get sql state, such as "error code = 2029" ...

  14. #14
    Join Date
    Sep 2003
    Posts
    66
    btw, in_stmt(i).value is a system.string(string type) , it is a string of sql code.

  15. #15
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    It's because you are just printing the message.

    First of all: e is not just a string, but it references to a whole collection of Exception objects. An exception object has beside it's message (which is probably the default property) also the SQL error code as a property.

    Use your object browser to look up the object properties of OleDbException, and adapt some example code to iterate through your exception collection.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

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