Query Problem - Errors every step

[dzr]

I tried to get be independant but, please oh please will someone help me out! I just can't seem to get a grip on the way to approach this and there is way too much chaotic info on the web...

I need:

1. entry form submit -- 2 fields from form inserted into table 1
2. remaining form fields and primary key of above insert ->inserted into table 2 & 3

I have been able to insert the record but to do it "correctly" I am thinking that I need to use stored procedures and such as opposed to manually running query after query...


Please can someone look at the code below
(and this if you want to see my working insert ->

http://www.tek-tips.com/viewthread.cfm?qid=1207921&page=1)

and help me...

==> fix the problem with the below call to stored procedure

==> let me know what i do next in the DB (?add another stored procedure? add more into the initial?)

==> let me know what i do next in the aspx.cs page (?add another query/call to stored procedure before the "conn.Close();"? add that after the close() and re-do all code to open the connection??)




//****C# code *******//

string ltlUserId;
SqlConnection conn = new SqlConnection("server=xxx;database=xxx;uid=xxx;pwd=xxx
SqlCommand cmd = new SqlCommand("AddProgramAdmin", conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param1 = new SqlParameter("@username", SqlDbType.VarChar);
param1.Value = (Request.Form["cpm_email"]);
SqlParameter param2 = new SqlParameter("@admin_level", SqlDbType.VarChar);
param2.Value = "ARC Program Admin";
SqlParameter param3 = new SqlParameter("RETURN_VALUE", SqlDbType.Int);
param3.Direction = ParameterDirection.ReturnValue;

cmd.Parameters.Add(param1);
cmd.Parameters.Add(param2);
cmd.Parameters.Add(param3);

conn.Open();
cmd.ExecuteNonQuery();
//Console.writeLine ( cmd.Parameters["RETURN_VALUE"].Value.ToString());
conn.Close();



//****Stored Procedure *******//

CREATE Procedure dbo.AddProgramAdmin
(
@username varchar(100),
@admin_level varchar(100)
)
AS
declare @user_id int
insert into acr_admin (username,admin_level) values(@username,@admin_level)
select @user_id=@@IDENTITY from acr_admin
return @user_id
GO


//****Error *******//

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'acr_admin'.

--yes there is a table acr_admin
--I have also tried above with scope_identity...

 

[jbenson001]

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'acr_admin'.

This is telling you the table does not exist, however you say it does. Make sure you are trying to create the procedure on the right database.

Jim

 

[ca8msm]

Also, it could be that the user doesn't have access to the table (hence it not being a valid object).


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[dzr]

i knew the DB was right after several successful selects...

good tip ca8 - thanks for the tip. the "CREATE Procedure dbo.AddProgramAdmin" was wrongs since the user isn't DBO - I changed that and it worked!

Any input on my next 2 questions...

==> let me know what i do next in the DB (?add another stored procedure? add more into the initial?)

==> let me know what i do next in the aspx.cs page (?add another query/call to stored procedure before the "conn.Close();"? add that after the close() and re-do all code to open the connection??)

 

[jbenson001]

After you get the identity just entered.. you want to insert a row into table2 and table3?

 

[dzr]

yeah - i even got that working up to table 2 insert! but now i need the identity of the insert to table 2 to put in table 3 ... i have it set up w/ 1 stored procedure and then 1 insert...

i know i should use a stored procedure for the second insert but the code seems so much longer ! because you have to get the form.request() value for the field, then do the param setups like:SqlParameter param2 = new SqlParameter("@admin_level", SqlDbType.VarChar); and all the setting up the field types in the StoredProcedure and then the insert...

when i can just do the form.request(), insert code alone...

and do i need to put it all in 1 stored procedure or 2 .. or 3 really?

 

[jbenson001]

Use one procedure:

Pass the parameters to the SP
Do the insert to the first table
Grab that identity just enterd(Use scope_identity)
Use that identity and insert into table 2
.... repeat for table 3


Wrap everyting up in a TRANSACTION in the SP.

Jim

 

[dzr]

ok, will do that...

Here's a database set up question

i have 5 tables ...

admin/user (table with login info)

program_main
program_topics

program_main_temp
program_topics_temp

if a program is added or edited - a user gets added into the admin table and the program data goes into the temp tables until approved by the Site admin - when approved -the info goes into the live tables and can be viewed on the site.

i did have the primary key of the program_main table running the whole show but would it make more sense to have the user's primary key in the Admin table holding all the data together?

currently say joe@sld.com id=10 gets his program data entered into the temp table:
program_main_temp.add_user=10
say the primary key of that row is 7...

we then put the program topics into the topic_temp table:
program_topics_temp.program_temp_id=7

admin Sally comes, approves the add -- all the info gets put into the program_main, program_topics table - we now have a program_id...

<<<that's all my current set up... i am leaning towards running it all off the admin id--what do you think..

 

[jbenson001]

I think you would need a compsite key of AdminID and UserID.