Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Smile Unanswered: visual basic6 sql2005

    Hello all
    I work with VisioalStudio6 and sql2005,
    i do connection with the sql , with ADO

    I am excute ,a transactions that contain severl store-pocedures,from vb6 aplication.
    and i want to catch the error if done in transaction. if one procedure is succeeded and the other is fail i did not get response aboute the failer one.
    or if i excute sp from my vb6 application,if the sp
    contain 2 commands “delete” and “insert”.
    the “delete ” success and the “insert ” fail ,
    the fail MSG did not over to the vb6 .
    how can i catch the Messages

    Thank you in advance
    yacov

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Please show us the code where you execute the stored procedure...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jan 2009
    Posts
    5
    Hi
    i send some code now
    1 in the sql i have table its name temp with 2 colomns
    id (samllint) and VALUE_NAME(varchar(5)).
    --
    i have procedure with the name spInsTemp
    --
    create proc spInsTemp

    as
    delete from temp where id=1
    insert into temp(id,value_name) values (1,'dfdfdfdfgdfgdfgdfgdfgdfgdfg')

    -

    when i execute the procedure - exec spInsTemp
    i get the mesege
    (1 row(s) affected)
    Msg 8152, Level 16, State 14, Procedure spInsTemp, Line 4
    String or binary data would be truncated.
    The statement has been terminated.

    --
    and its ok

    when i execute the procedure from vb6 with ADO
    i cant get the error msg . iget only the first msg.
    in this case i dont have controle on the procedure i dont know if the procedure succeed or not

    please advice ,how can i get evrey msg from the sql
    in transactions or Storeprocedures

    Thank you verey much inadvance
    jaco8594
    Last edited by jaco8594; 02-01-09 at 08:14.

  4. #4
    Join Date
    Jan 2009
    Posts
    5
    i have procedure with the name spInsTemp
    --
    create proc spInsTemp

    as
    delete from temp where id=1
    insert into temp(id,value_name) values (1,'dfdfdfdfgdfgdfgdfgdfgdfgdfg')

    -

    when i execute the procedure - exec spInsTemp
    i get the mesege
    (1 row(s) affected)
    Msg 8152, Level 16, State 14, Procedure spInsTemp, Line 4
    String or binary data would be truncated.
    The statement has been terminated.

    --
    and its ok

    when i execute the procedure from vb6 with ADO
    i cant get the error msg . iget only the first msg.
    in this case i dont have controle on the procedure i dont know if the procedure succeed or not

    please advice ,how can i get evrey msg from the sql
    in transactions or Storeprocedures

    Thank you verey much inadvance
    jaco8594

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    As you have discovered, the VB error object only displays the most recent error from the database, and underlying warnings or informational data probably won't be returned to the user at all.

    WHat you need to do is to interrogate the connection.Errors collection after the command has been executed.

    First, look at the Connection.Errors Count property, to see if any errors or warnings resulted. If the count is greater than 0, there is information to be had.

    Code:
    If cn.Errors.Count > 0 then
      For N = 0 to cn.Errors.Count-1
        debug.Print N, cn.Errors(N).Number, cn.Errors(0).NativeError, cn.Errors(0).Description, cn.Errors(0).Source, cn.Errors(0).SQLState
      Next N
    End If
    
    cn.Errors.Clear
    Let us know if this helps.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Jan 2009
    Posts
    5

    Visual Basic6 sql2005

    Hi Lou
    I have tried your code ,i get same problem
    look at thise simple cod that i send to the sql
    '--
    Dim rs As Recordset

    mcon.Open SQL_SERVER_CONN
    Set rs = mcon.Execute("exec spInsTemp")
    MsgBox mcon.Errors.Count
    '----
    i get "0" from the msg

    when i do it "exec spInsTemp" on the SQL mnage studio
    i get
    --
    (0 row(s) affected)
    Msg 8152, Level 16, State 14, Procedure spInsTemp, Line 4
    String or binary data would be truncated.
    The statement has been terminated.
    --
    The err msg did not over to the vb(ado)
    you can try it

    Thank you in advance
    Jaco8594

  7. #7
    Join Date
    Jan 2009
    Posts
    5
    Hi Lou
    I have tried your code ,i get same problem
    look at thise simple cod that i send to the sql
    '--
    Dim rs As Recordset

    mcon.Open SQL_SERVER_CONN
    Set rs = mcon.Execute("exec spInsTemp")
    MsgBox mcon.Errors.Count
    '----
    i get "0" from the msg

    when i do it "exec spInsTemp" on the SQL mnage studio
    i get
    --
    (0 row(s) affected)
    Msg 8152, Level 16, State 14, Procedure spInsTemp, Line 4
    String or binary data would be truncated.
    The statement has been terminated.
    --
    The err msg did not over to the vb(ado)
    you can try it

    Thank you in advance
    Jaco8594

Posting Permissions

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