NEED HELP WITH A QUERY 1

[bryant89]

I want to write an sql query that will essentially create a record in a table that has an id set as an autonumber. Then after the record is created (but within the same procedure) retrieve that autonumber. So like an insert and select all in one procedure. I tried to something like this but I cannot get it to work.

any help would be appreciated

 

[sndkick]

Right. part_instance_id is the ID/PrimKey field of the database. The other two (part_id, nsn_instance_id) are the other 2 fields in my table. Have you set up a recordset to catch the ID? What is the error you get on your web-page? Scott

 

[bryant89]

no I never bothered creating a recordset on my asp page yet because if it doesnt select by executing. I doubt it will work if a recordset is used. Or would it? I dont know.

I tried to do this for my query and it still will not work
Alter Procedure stpCreateReturn


@copName varchar(70),
@copCity varchar(40),
@copProv char(2),
@copCountry varchar(30),
@copAddress varchar(70),
@copStatus varchar(70),
@copEstCost money


AS


INSERT INTO tblContProj (copName, copCity, copProv, copCountry, copAddress, copStatus, copEstCost)
VALUES (@copName, @copCity, @copProv, @copCountry, @copAddress, @copStatus, @copEstCost)


SELECT @@IDENTITY


go

return

 

[sndkick]

I guess I'm stumped. Everything looks good to me. You might want to try pulling it from the ASP page just because IIS will give you a pretty good explanation on any errors that may happen. But if it doesn't give you an error, AND it doesn't return anything, then I'm not sure what else to do. Scott

 

[sndkick]

You could, i guess, pass all of the same values into another procedure that will return the id where all of the fields are equal to those you're passing in. That would get you what you need. It's just a little messier than the other way. Scott

 

[bryant89]

When you do your test in SEM where is the confirmation that the select got returned to you. All I got was that the query was successful. But it doesnt tell me the value of the select statement

 

[bryant89]

well I will try retrieving from an asp page with a recordset.
and see what happens

How do I pass from one procedure to another?
Then there is the possibility of 2 people entering the same record at the same time.

 

[bryant89]

when I set up a recordset dtc in my asp page it doesnt show a parameter for the copID. Or how do I retrieve the id from the recordset?

 

[sndkick]

When I run a query in the SEM it displays the id field and the ID. Something is wrong somewhere, it won't come up in the ASP page either. You could have the stored procedure insert the values, then do a select for the id,

That might work. You should be able to do it all in one procedure.

CREATE PROCEDURE stpInsertGet

@copName varchar,
...

AS

INSERT INTO tblContProj (...)
VALUES (...)

SELECT copID
FROM tblContProj
WHERE copName = @copName
AND copCity = @copCity
AND copState = @copState
AND copProv = @copProv
AND ...

This would pretty much prevent the same field from being added twice. Scott

 

[bryant89]

nope that will not work either.

I guess I cant do an insert and then a select in the same procedure

 

[sndkick]

Sounds like something is configured funny in your SQL Server. Which version are you running? Scott

 

[bryant89]

SQL Server 2000 Standard Edition

 

[sndkick]

Try posting another Message and see if anyone knows why you cant insert and select within the same procedure. It works on everything I have run. Or poke around and see of there's some setting that is preventing the SP from being able to do this. Other than that, I'm fresh out of ideas. What you have should work. In SEM right click on the table, go to properties, then once it's open, check the permissions. Maybe you don't have select permissions on that table. That's the only thing I can think of, but even then it would give you some sort of error message. Try posting another query. Sounds like the @@IDENTITY would work for you if you can get it to let you insert and select w/in the same SP. I have not run into that problem before. If you figure it out, please let me know, i'm curious. Scott

 

[sndkick]

Also, try checking the permissions on the SP, you do this the same way as for the table. Scott

 

[bryant89]

K I will check that out. I bet that is what it is you know.

I will check and let you know. I will start a new thread.

Thanks for all of your help Scott. You are a very good guy to help me out all day like this. As you can probably tell I am new to asp and interdev and sql.

Thank you

 

[sndkick]

No prob. I'm still in the learning stages myself. I've learned a lot from people in these forums, so I try my hardest to help anyone else out.

Scott

 

[bryant89]

No I have select rights for the table and for the SP you can only select exec rights