| Author |
Topic |
|
davidshq
Posting Yak Master
119 Posts |
 Posted - 2006-05-20 : 13:24:03
|
| I have created a page that adds an entry to the database. I used a SqlDataSource in combination with a Wizard to create the form. I used a stored procedure to do the actual insert. When I click finish I get the message:Invalid object name 'State'.Any ideas? Here is my stored procedure:ALTER PROCEDURE dbo.AddItem ( @varState int, @varCounty int, @varCategory int, @varTitle varchar(1000), @varDescription text, @varOfferer varchar(500) )AS SET @varState=1 SET @varCounty=1 SET @varCategory=1 SET @varTitle='Hello' SET @varDescription='Hello' SET @varOfferer='Hello' /* SET NOCOUNT ON */ insert into Items (State, County, Category, Title, Description, Offerer) VALUES (@varState, @varCounty, @varCategory, @varTitle, @varDescription, @varOfferer) update State set Posts = Posts+1 where ID=@varState update County set Posts = Posts+1 where ID=@varCounty update Category set Posts=Posts+1 where ID=@varCategory RETURN I added the sets to try and eliminate variables related to the variables I was passing.David.- http://www.gamesecretary.com/- http://www.thehungersite.com/- http://www.grid.org/ |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-20 : 13:31:53
|
| "Invalid object name 'State'."I guess you don't have a table called [State] for this command:update State set Posts = Posts+1 where ID=@varState(or the user doesn't have permission to access it, or [State] is owned by a user OTHER than the current user or [dbo])Or just possibly its the Column in this statement:insert into Items (State, County, Category, Title, Description, Offerer)Kristen |
 |
|
|
davidshq
Posting Yak Master
119 Posts |
|
|
andreliem
Starting Member
8 Posts |
Posted - 2006-05-29 : 14:49:03
|
| What solution worked for you? I'm having a similar problem where all of a sudden, any "new" stored procedures I create will not allow "INSERT" commands. It complains with that same message:"Invalid object name 'test'All the other commands like SELECT, UPDATE, DELETE work fine so it's something quirky going on. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-30 : 04:22:49
|
| if it's same case, check if [test] is thereif yes, then verify if you have insert permission on [test], if you say you create, the owner is your account not dbo which explains the restriction, but I do know that the error should be an error on permission not existence of the tablebut hope that helps--------------------keeping it simple... |
|
|
|
andreliem
Starting Member
8 Posts |
Posted - 2006-05-30 : 12:14:48
|
| Thanks for the reply...Actually, as another member pointed out in this thread (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66833), it was because I was not using [ ] brackets around my fields. Although it worked before last week, so I'm confused as to what changed... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-30 : 21:58:16
|
from what i read, you were using double quotes around the field namesfieldnames are written as is or if they're keywords/reserved words, in between [ ]so it's syntax error, you can always check by parsing the query first  --------------------keeping it simple... |
|
|
|
andreliem
Starting Member
8 Posts |
Posted - 2006-05-31 : 11:54:29
|
| Yeah it did end up being a syntax error but the odd part was that the syntax checker didn't pick anything up, the double quotes do work with a manual Query, and I had existing stored procedures that worked with double quotes :). On the other hand, I'm not sure why I used them as I never did before... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-02 : 06:58:27
|
| If its working JUST from Query Analyser might it be the Tools : Options : [Connection Properties] : "Set quoted_identifier" setting that allowed it to specifically work in QA?Of have I misremembered and "quoted_identifier" is for something completely different??Kristen |
|
|
|
|
Invalid Object Name.