Stored Procedure - Getting PK from Inserted Record - HOW??

I have an ASP.NET page where administrators can enter questions/answers into a SQL Server database.  Every question has 4 answers and 1 category assigned to each question.  Thus, there is a 1 to MANY relationship between tblQuestions and tblAnswers.  I am having trouble in the SQL logic for the following stored procedure on INSERTING a question and its 4 answers.  My logic jus seems very inefficient.  I am also having trouble on figuring out the PK for the Question that was just submitted into tblQuestions so I can use it as a FK for tblAnswers for each Answer submitted.

CREATE PROCEDURE spInsertQuestionAnswers

@Question varchar(500),
@EnteredBy varchar(50),
@Category int,
@Answer1 varchar(1000),
@Answer2 varchar(1000),
@Answer3 varchar(1000),
@Answer4 varchar(1000)
INSERT INTO tblQuestions
(Question, CategoryID, EnteredBy)
VALUES(@Question, @CategoryID, @EnteredBy)
@(SELECT MAX(QuestionID)
FROM tblQuestions) ' don't know how to do this?!?

INSERT INTO tblAnswers
(Answer, QuestionID, EnteredBy)
VALUES(@Answer1, @QuestionID, @EnteredBy)
INSERT INTO tblAnswers
(Answer, QuestionID, EnteredBy)
VALUES(@Answer2, @QuestionID, @EnteredBy)
INSERT INTO tblAnswers
(Answer, QuestionID, EnteredBy)
VALUES(@Answer3, @QuestionID, @EnteredBy)
INSERT INTO tblAnswers
(Answer, QuestionID, EnteredBy)
VALUES(@Answer4, @QuestionID, @EnteredBy)

Any assistance will be greatly appreciated!
10/18/2004 5:05:30 PM 16182 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 41
Get it on Google Play
Get it on Apple App Store


This post contains all you need.
Anthony Mason MCSD
10/18/2004 6:19:48 PM

Similar Artilces:

View Inserted Record Using Object Datasource, Details View and Stored Procedure
I am trying to display the record just inserted into a database using an object datasource, a stored procedure and a details view Below is the stored procedureALTER PROCEDURE [dbo].[Employees_Insert]( @LastName varchar(50), @FirstName varchar(30) = null, @JobTitle varchar(50))AS SET NOCOUNT OFF;INSERT INTO [dbo].[Employees] ([LastName], [FirstName], [JobTitle])VALUES (@LastName, @FirstName, @JobTitle);  SELECT  LastName, FirstName, JobTitleFROM Employees WHERE (EmployeeID = SCOPE_IDENTITY()) ________________________________________________________________...

Use Stored Procedure or insert template to insert a new record?
Hi there, I am using stored procedure for all my database website before I use .net 2. When I come to .net 2, I found almost all the videoes are using SP for select functions, and using insert template to add new ones. What's the differenence and which way is better? Thanks!  >which way is better?Whenever you can, use stored procedures - preferably your own, not some of the autogenerated horrors! If you want some help in generating them - see my post in plus the helper bits in

how to excute stored procedure that insert record and return last inserted value
Dear all, I am using C# , and sql server 2005. Let me explain the situation. I have written procedure to insert data into the table and return last inserted value by @@identity variable. Now my question is how do I execute this process so that I can Get last inserted variable values       Please help  thanks    I'm not sure I'm following completely.  If you are inserting data into SQL via this stored procedure, I'm assuming the data to be inserted is passed to the SP as SQL parameters.  Are you saying you want...

Stored Procedure to Insert Record
I have an SP to add a record to the database but i get the error shown below. Any help appreciated. Stored Procedure: CREATE PROCEDURE addUser @username char(15), @password char(12) AS INSERT INTO users(username, password) VALUES (@username, @password) GO Code calling SP: Dim myConnection As New System.Data.SqlClient.SqlConnection(ConnectionString) Dim cmd As New SqlCommand("addUser", myConnection) MyDataAdapter = New SqlDataAdapter() 'MyDataAdapter = New SqlDataAdapter("addUser", myConnection) ...

Inserting Records via Stored Procedure
I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails... here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"   === Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim conn As SqlConnection = Nothing Dim trans As SqlTransaction = Nothing Dim cmd As SqlCommand conn = New SqlConnection(ConfigurationManage...

Duplicate Record Inserted With Stored Procedure
I'm calling the stored procedure below to insert a record but every record is inserted into my table twice. I can't figure out why. I'm using Sql Server 2000.  Thanks.CREATE PROCEDURE sp_AddUserLog(@Username varchar(100),@IP varchar(50))AS SET NOCOUNT ONINSERT INTO TUserLogs (Username, IP) VALUES (@Username, @IP)GO  Sub AddUserLog(ByVal Username As String) Dim SqlText As String Dim cmd As SqlCommand Dim strIPAddress As String 'Get the users IP address strIPAddress = Request.UserHostAddress Dim con As New...

Using stored procedures to insert recorders
Hi I am not familiar with stored procedures, but I tried to use it with this code and it worked fine   ALTER PROCEDURE dbo.p_insert_shortleave      @Leave_Date datetime,      @Start_time datetime,      @End_time datetime,      @Reason varchar(150),      @Submission_date datetime,      @L_D_id_Priority varchar(20)AS      INSERT INTO HR_Leave_request (Leave_Date, Start_time, End_time, Reason)   ...

i am trying to create a stored procedure to insert record...
but when i try and save it says "Invalid Object Name 'dbo.AddList'" does anyone know why? Where exactly (or what steps do you take to do this) are you doing, to create the sProc?David WierMCP/ - where to look first!Please Vote for ASPNet101 - 'Best Community Resource'!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on the web!(Please 'Mark as Answer' when it applies) I am working in Visual Studio 2005 and am just right clicking on stored Procedu...

Using a stored Procedure to insert a new record
ok I have a stored procedure...... I pass in the variables that are requried....What is the best way to add a record using my stored procedure in code in a button click event...... How might i do this with a data reader,,data adapter.....OR What.......................Do I need to declare all my varaibles I am adding to this new record in the line after POSCODE or can do this without a parameter statemetn CREATE procedure dbo.Appt_AddAppt ( @ClinicID int, @AccountNum nvarchar(10), @DOS nvarchar(12), @POSCODE nvarchar(5) ) as Insert ...

Stored procedure inserting duplicate records randomly
I have a web app that calculates tax filing status and then stores data about the person. Facts The insert is done through a stored procedure. All the sites that this program is being used are connecting through a VPN so this is not an external site. The duplicate records are coming from multiple sites (I am capturing there IP address). I am getting a duplicate about 3 or 4 times a day out of maybe 300 record inserts. Any help would be greatly appreciated. There are many sqlcmdInsert.Parameters("@item").Value = cnTaxInTake.Open() sqlcmdInsert...

Using ADO.NET Execute Second Stored Procedure Depending of Output of First Stored Procedure
When a user logs onto the app, I capture their UserId, today's date and thier WINNT using ADO.NET calling a stored procedue-no problem. Now, once the above mention fields have been populated, when the same user logs on again and this time click their daily attendance time, I need to execute and update stored procedure-no problem. Each time the user logs onto the attendance page, they need to be able to view the previuos time stamps, on page load.- no problem The problem is that I cannot get all three to work in concert, although they will work independently, somewhat. Sotred proc...

Error when attempting to insert record using stored procedure
I am trying to save user data to a sql table but keep getting the following error; Unable to cast object of type 'System.Boolean' to type 'System.Data.SqlClient.SqlParameter'. I am using VWD Express Edition with .NET 2.0 and a SQL 2000 database. The code that generates the error is as follows; Public Function chkUser(ByVal strUser As String) As Stringdim MyConnection As SqlConnectionDim MyCommand As SqlCommandDim ReturnString As StringDim params As SqlParameterDim SelectCmd As String = "wm_CheckUser"MyConnection = New SqlConnection(myConnectionString)MyCommand = New SqlCommand(SelectCmd, ...

Insert multiple objects using one stored procedure
This is my scenerio:I have a webform. The webform is used to enter company information. The company information includes Name, Description, Address, Phone and Email.The trick is that I use gridviews(and some custom programming) to allow the user to enter multiple Addresses, Phones and Emails.When the user submits the form, I will have a Company object, and 3 generic lists of type Address, Phone, and Email. Currently, in each Class: Company, Address, Phone, and Email, I have a function that inserts one row. In my form code behind, I loop through my list using that function to insert everythin...

Problem with inserting a Record using textbox and stored procedure
Below is the code for a form with several textboxes that I would like to use the values from to insert a record using a stored procedure. I am not having any sucess. After entering all the values and clicking the submit button no record gets entered. Any help would be greatly appreciated. Thanks in advance Gary <%@ Page Language="VB" Debug="true" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <script runat="server"> Sub Register_Click(ByVal sender A...

Inserting a new record from DetailsView using a stored procedure
I am using an <asp:DetailsView ...> on my <asp:Content ...> page.I have the AutoGenerateInsertButton="True".The DataSourceID for the DetailsView is a SQLDataSource.My Insert command uses a stored procedure that takes the fields a input parameters:   InsertCommand="AddMovie" InsertCommandType="StoredProcedure"I am using a stored procedure to do add a record to my table.My question is: After I click on the "New" button and add data to the DetailsView, how do the values entered get to my stored procedure? What am I missing here? ChuckChuck Ops, my stored procedure ...

Web resources about - Stored Procedure - Getting PK from Inserted Record - HOW?? -

Parliamentary procedure - Wikipedia, the free encyclopedia
... of the House of Commons of the Parliament of the United Kingdom , from which it derives its name. In the United States, parliamentary procedure ...

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

We need to reform the culture of law enforcement, not just the procedures
We need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

Rescued tiger cub "Himmel" dies during medical procedure - CBS News 8 - San Diego, CA News Station ...
Alpine animal rescue sanctuary Lions, Tigers & Bears is sadly reporting Himmel, the tiger cub found roaming the streets of Hemet and rescued ...

Catholic hospital allowed to deny sterilization procedure, judge says
Hospital in California argued that the procedure known as ‚Äčtubal ligation would violate its religious freedom

TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners
... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...

Resources last updated: 2/3/2016 10:58:20 PM