Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

ADO Error Messages when using sqloledb (SQL Server 7.0)

Why can't I access the SQL error messages in the connection error object.  It always returns "Errors Occured" and the Native Error = 0.  I can't imagine that this is supposed to work this way being that all the products are Microsoft products.

I am using ADO2.1, visual basic 6.0 sp3, and SQL Server 7.0 sp1.

The code for a test table is
CREATE TABLE Test(
      Test_Identity       int IDENTITY(1,1) NOT NULL,
      Test_Value      tinyint      NOT NULL

      CONSTRAINT Test_PKC PRIMARY KEY CLUSTERED
            (Test_Value),

      CONSTRAINT Test_U1 UNIQUE NONCLUSTERED
            (Test_Identity)
)
GO      

This is the code in vb.
Private Sub Command1_Click()


    Dim cn              As ADODB.Connection
    Dim rs              As ADODB.Recordset
    Dim sSQLCommand     As String
    Dim sConnectionString   As String
    Dim I               As Integer
   
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    On Error GoTo ErrorHandler
   
    sConnectionString = "Provider=sqloledb;" _
                        & "Server=jadekra_office;" _
                        & "UID=IntelliLog_VB;" _
                        & "PWD=vb$app;" _
                        & "Database=IntelliLog;" _
                        & "App=Test;"
   
    cn.ConnectionString = sConnectionString
    cn.Open
   
    sSQLCommand = ""
    sSQLCommand = sSQLCommand & "SELECT * "
    sSQLCommand = sSQLCommand & " FROM Test"
   
    rs.Open sSQLCommand, cn, adOpenKeyset, adLockOptimistic, adCmdText

    ' Add a record
    rs.AddNew
    rs.Fields("Test_Value").Value = 1
    rs.Update
   
    ' Try to Add a duplicate record to generate the error
    rs.AddNew
    rs.Fields("Test_Value").Value = 1
    rs.Update
   
   
    If rs.State = adStateOpen Then
        rs.Close
    End If

CleanExit:

    Set rs = Nothing
    Set cn = Nothing


Exit Sub
   
ErrorHandler:
        MsgBox Err.Description
        For I = 0 To cn.Errors.Count - 1
            MsgBox cn.Errors(I).Description
            MsgBox cn.Errors(I).NativeError
        Next
        cn.Errors.Clear
        Resume CleanExit

End Sub

I expect to see the error Violation of Primary key constraint.  
0
jadekraf
Asked:
jadekraf
1 Solution
 
MarineCommented:
Your connection string doesn't look right to me.
sConnectionString = "Provider=sqloledb;" _                  & "Server=jadekra_office;" _                       & "UID=IntelliLog_VB;" _              & "PWD=vb$app;" _
& "Database=IntelliLog;" _
& "App=Test;"
     
SEe where you have the semilcolmnut? Try placing your samicolumn outsite the quotes. I know this may not be related to your primary constrait vaiolation problem but htats how i do my my connection strings.    
0
 
p_biggelaarCommented:
Just to make sure: you tested your code and everything works fine indeed up to the moment where you want to add the new key field, containing the duplicate value?
0
 
p_biggelaarCommented:
Oh and I agree with Marine that there might be something wrong with your connection string. Try to get something like this (By the way: I don't know why you specify the application?):

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=IntelliLog_VB;Password=vb$app;Initial Catalog=IntelliLog;Data Source=jadekra_office
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jadekrafAuthor Commented:
There is nothing wrong with the connection string.  I tested the procedure before I posted it to make sure it would work.  If you bothered to test it first instead of guessing at the problem you would see that it works.
0
 
Éric MoreauSenior .Net ConsultantCommented:
Set your CursorLocation to the Client instead of Server (default value). You can set it this way:

rs.CursorLocation = adUseClient
rs.Open sSQLCommand, cn, adOpenKeyset, adLockOptimistic, adCmdText

Using this, you will obtain this error: -2147217900:Violation of PRIMARY KEY constraint 'Test_PKC'. Cannot insert duplicate key in object 'Test'.

Instead of the infamous:
-2147217887:Errors occurred
0
 
jadekrafAuthor Commented:
You da man.  I've searched everywhere but here for the answer to that one.  If it was in help I couldn't find it.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now