Invalid object name

Syed Naveed Khursheed · November 16th, 2004, 10:26 PM

Greetings,

I am trying to connect to SQL database via MSDE using try it out in chapter 12(pg.475). The problem is that I keep getting this message:

Microsoft OLE DB Provider for SQL Server (0x80040E37)
Invalid object name 'Movies'.
/begasp/connect.asp, line 35

I have checked the code and there seems to be no problem. I tried working with Access it is running perfectly. I would really appreciate any help. Thanks in advance, my code is as follows:

Code:

dim adOpenForwardOnly, adLockReadOnly, adCmdTable
adOpenForwardOnly = 0
adLockReadOnly = 1
adCmdTable = 2

dim objConn, objRS
set objConn = server.CreateObject ("ADODB.Connection")
set objRS = server.CreateObject ("ADODB.Recordset")

dim strDatabaseType
'strDatabaseType = "ACCESS"
strDatabaseType = "MSDE"

if strDatabaseType = "ACCESS" then
    objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
        "Data Source=C:\datastores\Movie2000.mdb;" &_
        "Persist Security Info=False"
else
objConn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;" &_
        "User ID=sa;Data Source=NAVEED-HOME;" &_
        "Initial File Name=C:\MSSQL7\Data\MovieMSDE.mdf"
end if

objRS.Open "Movies", objConn, adOpenForwardOnly,adLockReadOnly, adCmdTable

While not objRS.EOF
    Response.Write objRS("Title") & "<br>"
    objRS.MoveNext
wend

objRS.Close
objConn.Close
set objRS = nothing
set objConn = nothing

Syed Naveed Khursheed

om_prakash · November 17th, 2004, 01:16 AM

Does Movie2000.mdb contains movies table?

Om Prakash

Syed Naveed Khursheed · November 17th, 2004, 06:38 AM

Actually I only have MSDE, frankly I do'nt know how can I open the database to check, any suggestions? I have downloaded the database file MovieMSDE.mdf from the wrox website and also the access version of the same program is working fine, I presume that it should have "Movies" table.

Thanks

Syed Naveed Khursheed

om_prakash · November 17th, 2004, 07:08 AM

you need to restore the database and then use it. You have to use SQL server and not Microsoft.Jet.OLEDB.4.0 drivers.

Om Prakash

Syed Naveed Khursheed · November 17th, 2004, 07:35 AM

Thanks for the quick response, actually I am using the SQLOLEDB Provider, if you look at the code more carefully you will see that I am using a string variable to store the database type and based on the value of that string I am deciding which connection string to use. In this case the access database and hence the microsoft jet provider is commented out.

Regards

Syed Naveed Khursheed

om_prakash · November 17th, 2004, 08:19 AM

I think you don't have a valid MSDE installation. You can get the latest version from the following link:

http://www.microsoft.com/downloads/d...DisplayLang=en

Please read the Readme for installing the MSDE part. You have to use some command line stuff to get your installation and Named Instance to be setup correctly.

Om Prakash

Syed Naveed Khursheed · November 17th, 2004, 02:40 PM

I tried re-loading the MSDE but no luck (I tried installing with switches). I am getting desperate now, it has been more than 18 hours and I am stuck.

Thank you very much

Syed Naveed Khursheed

Imar · November 17th, 2004, 05:01 PM

You're using an incorrect (outdated? Obsolete? Illegal?) connection string.

Take a look here: http://www.able-consulting.com/MDAC/...erForSQLServer

You need something like:

oConn.Open "Provider=sqloledb;" & _
           "Data Source=myServerName;" & _
           "Initial Catalog=myDatabaseName;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"

Where myServerName takes the form of ServerName\InstanceName if you have a named instance of the MSDE. EG: DevelopmentMachines\VsDotNet

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Syed Naveed Khursheed · November 20th, 2004, 01:42 AM

Thanks Imar,

I an actually using the same connection string as you recommended, which was
oConn.Open "Provider=sqloledb;" & _
           "Data Source=myServerName;" & _
           "Initial Catalog=myDatabaseName;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"

In my case myServerName is Naveed-Home and User ID is sa, with no password, I tried the same with Initial Catalog switch also. Could you please elaborate where am I wrong?

Thanks in advance

Syed Naveed Khursheed