Run Time Error 80040e37

Hi! I am fairly new to VB coding and am trying to create an ODBC connection and am getting a run time error (80040e37). Does anyone have any remedies? Below is the coding that I have... The purpose of it is to auto-populate the Advertiser field based on the contract id that is entered to avoid misspellings and furthers errors that could inhibit later analysis.


Dim db As adodb.Connection
Dim rs As adodb.Recordset
Dim sql As String
Dim Contractid As Long
Set db = New adodb.Connection
db.ConnectionString = "Driver={SYBASE ASE ODBC Driver};" & _
"Srvr=ADS_2;" & _
"Uid=orangepunter;" & _
"Pwd=adri6;" & _
"Database=ams;"
db.Open
Contractid = txtContractID.Text
sql = "SELECT dbo_tblAdvertisers.Advertiser"
sql = sql & " FROM dbo_tblContract INNER JOIN dbo_tblAdvertisers ON dbo_tblContract.AdvertiserID = dbo_tblAdvertisers.AdvertiserID"
sql = sql & " where contractnumber = " & Contractid

Set rs = New adodb.Recordset
rs.CursorLocation = adUseClient
rs.Open sql, db, adOpenForwardOnly, adLockReadOnly
txtAdvertiser.Text = rs.Fields("Advertiser")

thanks for your help!

What is the complete error text? In IE make sure "show friendly http errors" is not set. What line causes your error? Are you sure your connection string is right? Is the ODBC driver installed on the web server? (I have no clue with Sybase ODBC).

Hi Doug! Thanks for taking a minute with this one. The error message is below:

Run-time error '-2147217865(80040e37)' [Merant][ODBC Sybase driver][sql server]dbo_tblcontract not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

This program is a stand alone VB product, not web based. The rs.open line is where I am getting the error. The connection is correct in terms of spelling and works through access.

Let me know what you think! Thanks again

NM....I got it. I just rewrote the SQL (excluding INNER JOIN) and fixed the variable Contract ID and it is working now. Not sure why it wasn't working before . Later

I also have thris problem please solve it for me:

error:
Runtime error '2147217865 (80040e37)' :

"The microsoft Jet database engine cannot find the input table or query 'Products'. Make Sure it exists and that its name is spelled correctly"


part of my Coding:

Private Sub Form_Load()
'open database and table
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set rs5 = New ADODB.Recordset
Set rs6 = New ADODB.Recordset
Set stors = New ADODB.Recordset


cs = "Provider=Microsoft.Jet.OLEDB.4.0;"
cs = cs & "Data Source=Projectdatabase.mdb;"

conn.Open cs
rs.Open "Products", cs, adOpenDynamic, adLockOptimistic, adCmdTable (debug area!

rs.MoveFirst
Combo1 = rs.Fields("pcode")
Combo2 = rs.Fields("product_category")
Text1.Text = rs.Fields("number")
Text2.Text = rs.Fields("typecode")
Text3.Text = rs.Fields("description")
Text4.Text = rs.Fields("cost")

progrid.Refresh
Set progrid.DataSource = rs

rs.MoveFirst
Do While Not rs.EOF
Combo1.AddItem rs.Fields("pcode")
rs.MoveNext
Loop


rs.MoveFirst
Do While Not rs.EOF
Combo2.AddItem rs6.Fields("product_category")
rs6.MoveNext
Loop


When I debug, it went here:

rs.Open "Products", cs, adOpenDynamic, adLockOptimistic, adCmdTable

Doesn't this error tell you what is wrong?

You could try putting [ ] around the table name in case Products is a reserved word.

ok I got it, but there is still another error!

error:

Runtime error '2147217865 (80040e37)' :
Syntax error(missing operator) in query expression 'product.pcode=stock category.pcodewhere product_category like '&%".

and the code is:

Private Sub Command11_Click()

Set rs2 = New ADODB.Recordset
Dim vsql2 As String
vsql2 = "select product.pcode,number,typecode,description,cost,pro duct_category from product ,stock_category "
vsql2 = vsql2 & " where product.pcode = stock_category.pcode"
vsql2 = vsql2 & "where product_category like '" & Trim(Text5.Text) & "%' "

rs2.Open vsql2, conn, adOpenDynamic, adLockOptimistic, adCmdText (debug area)
Set progrid.DataSource = rs2
progrid.Refresh

progrid.Refresh
rs2.MoveFirst

End Sub

I'm doing a searching using joints because I want to retrieve data from two different table!

ok this is the last one can you help me

Run Time Error 80040e37

Syntax error (missing operator) in query expression 'typecode like '306%' and description = Trim (text5.text)'

Codes

Private Sub Combo2_Click()

Set rs3 = New ADODB.Recordset
Dim vsql2 As String
vsql2 = "select typecode,description from [product] "
vsql2 = vsql2 & " where typecode like '" & Trim(Combo2.Text) & "%' "
vsql2 = vsql2 & " and description = Trim(text5.text) & "

rs3.Open vsql2, conn, adOpenDynamic, adLockOptimistic, adCmdText

rs3.MoveFirst

End Sub