SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

dcleslie
Starting Member

USA
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
conn.Open


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

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

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

conn.execute(strSQL)

Response.Redirect "success.asp"

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

End If

rs.Close
Set rs=Nothing
rst.Close
Set rst=Nothing
conn.Close
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!

eyechart
Flowing Fount of Yak Knowledge

USA
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: http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_ca-cz_1lpu.asp



-ec
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
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...

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

dcleslie
Starting Member

USA
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!
sigh..

Go to Top of Page

Srinika
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.

Srinika
Go to Top of Page

samuelclay
Yak Posting Veteran

USA
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  
 New 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.09 seconds. Powered By: Snitz Forums 2000