We help IT Professionals succeed at work.

[ADODC]: Unknown error [ADO]

rjcpjc
rjcpjc asked
on
I have a data grid control bound to an ADO data control on a VB form.  When the form loads, the ADO data control's data source is refreshed.  

The user will enter text in a text box and then click the search button.  That button takes the text in the box and then uses it to build new SQL statement as the recordsource for the ADO data control.  Then the data control is refreshed.

When I do that I get the error above.  [ADODC]: Unknown error [ADO].

I did look on the KB and found two options.  1.  Install latest VB Service Pack.  Did that already.  The other option was to make sure that the command type was set to adCmdText or adCmdUnknown.  Did that too.  

This started this afternoon.  I know I was fiddling with the code at that point, but as far as I know, all I changed to make this happen was try to unbind the data grid from the ADO data control and try to bind it at run time.  That didn't work so I rebound it at design time.  Then I got the error.  I repaired the database (Microsoft Access 2000) and also created a new grid control and a new ADO data control and redid the code.  No luck.  

Any ideas?  Thanks in advance!!!
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Just a thought - double check that all the tables you are using have a primary key - ADO likes to have a primary key in each table. Not a very helpfull error message is it!
CERTIFIED EXPERT

Commented:
It may be an idea to post your code so we can see it.
CERTIFIED EXPERT

Commented:
I should look a bit like this:

assumimng that CN is an open connection

sql="Select * from yourtable;"
Set rs = New ADODB.Recordset
rs.Open sql, CN, adOpenStatic, adLockReadOnly

set YourADOControl.Recordset=RS
Set YourGridControl.DataSource=YourADOControl

Author

Commented:
They all have a primary key.  One KB article said make sure you don't have any default values in the table.  I changed one field so that it didn't have a default value. Still no help.

Here's the code (bound to a command button's click event):

Dim tmpSearch As String
Dim tmpSearchType As String

tmpSearch = frmEditDatabase.txtSearchString
If frmEditDatabase.optCompany = True Then
    tmpSearchType = "Company Name"
Else
    If frmEditDatabase.optEmail = True Then
        tmpSearchType = "Email"
    Else
        If frmEditDatabase.optLast = True Then
            tmpSearchType = "Last Name"
        End If
    End If
End If


frmEditDatabase.Adodc1.RecordSource = "SELECT tbl_Contacts.Email, tbl_Contacts.[Last Name], tbl_Contacts.[First Name], tbl_Contacts.Position, tbl_Contacts.[Position Type], tbl_Contacts.[Company Name] FROM tbl_Contacts WHERE tbl_Contacts.[" & tmpSearchType & "] = " & "'" & tmpSearch & "'" & ";"
frmEditDatabase.Adodc1.Refresh

-----
Disclaimer...I'm just starting with VB. If you see problems with my code, my feelings won't be hurt by constructive criticism (-:

Thanks for the help.
Try This


Dim adoConn as ADODB.Connection
      Dim intFldCnt As Integer
    Dim intTotalBugs As Integer
    Dim strDeveloperName As String
     
      Set adoConn = new ADODB.Connection
      adoConn.CursorLocation = adUseClient
    Set adoRecsetBugCnt = New ADODB.Recordset
    Set adoRecsetUser = New ADODB.Recordset
    adoRecsetUser.Open "select ProblemNo, ModuleName, FormName, Status, ProblemDescription, " _
    & " TesterName ,DeveloperName ,ExpectedOutput ,ExpectedInput ," _
      & " ReportingDate ,TestCase ,CodeAreaFixed ,SolvedOn ," _
    & " SolutionDescription ,RetestDate ,Comments ,Priority , DataBaseUsed ," _
    & " TestResult ,RetesterName ,RetestResult from netCRM2Bugs where " _
    & " DeveloperName = '" & glbStrUserName & "' order by " & glbStrGrdColCaption, _
    adoConn, adOpenStatic, adLockOptimistic, adCmdText
    Set DBGRDBugs.DataSource = adoRecsetUser
   
     
TimCotteeHead of Software Services
CERTIFIED EXPERT

Commented:
rjcpjc: Lose the & ";" at the end of the Recordsource assignment. Whilst this is part of the standard Access syntax (which I guess you are using as your backend) it is not necessary for ADO statements whether or not they are referencing access tables. This of itself may be part or all of the problem. Try it without first and let me know.

Author

Commented:
Thanks everyone.  Actually, the problem ended up being that Position is a keyword.  So when I changed it to [Position] it worked fine.

Now...what should we do about points...Anyone have any thoughts?  
CERTIFIED EXPERT
Author of the Year 2009

Commented:
Hi rjcpjc,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Refund points and save as a 0-pt PAQ.

rjcpjc, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit.  Explain why.
==========
DanRollins -- EE database cleanup volunteer
Per recommendation, points refunded and question closed by
Netminder
Community Support Moderator