Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 INSERT problems - basic!
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 07/26/2006 :  16:32:27  Show Profile  Visit dcleslie's Homepage  Reply with Quote
I created an application using Access and converted it to a MS SQL database - it worked well in access but I'm now having troubles with it in MS SQL - even just doing a simple insert.

To locate the problem, I created a simple table "Member" with only one field "FirstName" - a page with only one form field "FirstName" which goes to regchk.asp which has this code:
strconn = "Provider=SQLOLEDB; "
strconn = strconn & "Data Source=PHSSQLWEBRPD2; "
strconn = strconn & "Initial Catalog=OPRS; "
strconn = strconn & "User Id=MYUSERIDHERE; "
strconn = strconn & "Password=MYPASSWORDHERE"

Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = strconn

strSQL1 = "SELECT * " & _
"FROM Member"

Set rs = conn.Execute(strSQL1)
If rs.BOF AND rs.EOF Then

strSQL = "INSERT INTO Member(FirstName)" & _
"Values('" & request("FirstName") & "')"


Response.Redirect "success.asp"

response.redirect("register.asp?signuperror=" & " UserName"& " " & "<b>" & username & "</b>" & " already exists, Please try another username" )

End If

Set rs=Nothing
Set rst=Nothing
Set conn=Nothing

The page generates this error:
Invalid object name 'Member'.

/members/regchk.asp, line 16

which makes me think that it doesn't even recognize the table as existing?? Any ideas what I'm doing wrong? It's driving me crazy..

Please help! and thank you in advance!

Flowing Fount of Yak Knowledge

3575 Posts

Posted - 07/26/2006 :  21:02:34  Show Profile  Reply with Quote
check the ownership of the member table using enterprise manager. maybe when you created the table you created it as a user other than dbo.

It is a best practice to create all tables in SQL Server (at least sql 2000 and older) owned by the special dbo user. If you need to change the ownership of your table, you can use the sp_changeobjectowner stored procedure.

here are the docs for that stored procedure:

Go to Top of Page

Norsk Yak Master

3271 Posts

Posted - 07/27/2006 :  02:44:45  Show Profile  Reply with Quote
Are you sure that you are in the right database? Does the Member-table reside here -> "Initial Catalog=OPRS;" If it does eyecharts suggestion may be the way to go...

"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

Starting Member

2 Posts

Posted - 07/27/2006 :  09:11:46  Show Profile  Visit dcleslie's Homepage  Reply with Quote
Thank you guys, but unfortunately I did create it as a dbo and the table does exist there :( I've been on the phone and email with the help desk at my work about this and they are saying it must be my code, but I just don't see how!

Go to Top of Page

Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 07/27/2006 :  09:21:16  Show Profile  Reply with Quote
Suggestions :

Just recheck the Datasource, ODBC, DatabaseName, & Table Name
if u r using ODBC -- > check the Database there as well.

Go to Top of Page

Yak Posting Veteran

71 Posts

Posted - 07/27/2006 :  14:24:18  Show Profile  Visit samuelclay's Homepage  Reply with Quote
If you output your sql string, and paste that in QA does the code run?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000