Insert data into two tables in SQL...

[adaytay]

Hi all,

Brand new to the site and also brand new to ASP.NET development (although have been an Excel / Access VBA developer for the last few years).

Wonder if you can help, here's the situation: I have a web front end which sits on a SQL database (and also has an Access FE for internal staff).

The web front end is used by remote engineers to submit "jobs", and I have basic data entry form successfully hooked up to the database, which is testing fine and allowing me to submit jobs from the web directly to the SQL database.

I've got a bit of a "challenge" now though - each of the "jobs" can have one or more "P Codes" allocated to them. The code could either be P1 - P5, or P10. Or any combination of these.

I have the PCodes listed in a table in the BE, and have successfully created a multiple-select listbox which contains these entries (from the table containing these).

When the submit button is pressed, currently all job information is recorded back to the table tblJobs - this bit works fine. What I would like to do though is determine what the JobID (this is the PK from tblJobs once the information has been stored, and is an Identity column), then store both this, and the value of each of the selected entries in the listbox, into table tbljncJobsPCode (a junction table so that Access can pick the details up correctly).

As I said at the start of this post though, I'm really green when it comes to ASP.net stuff, and can't figure out how I should do this. Ideally though I don't want to de-normalise my database! (although this would be a solution!)

Hope someone can help - thanks in advanc, please shout if you need any further information.

Cheers,

Ad

[Brandoe85]

Create a stored procedure that inserts the "job" info (or modify it if you already have one). Once you run the insert statement you can use @@IDENTITY (http://msdn.microsoft.com/en-us/library/aa933167%28SQL.80%29.aspx) to obtain the last identity value generated (from the insert you just did above). Once you have this you can do an insert into your other table in the stored procedure. So, maybe the sproc looks like this - pseudo

insert into jobs....
insert into new table values (@@IDENTITY)...and so forth

[adaytay]

Create a stored procedure that inserts the "job" info (or modify it if you already have one). Once you run the insert statement you can use @@IDENTITY (http://msdn.microsoft.com/en-us/library/aa933167%28SQL.80%29.aspx) to obtain the last identity value generated (from the insert you just did above). Once you have this you can do an insert into your other table in the stored procedure. So, maybe the sproc looks like this - pseudo

insert into jobs....
insert into new table values (@@IDENTITY)...and so forth


Hi Brandoe85, thanks for looking in!

I thought this would be the way I would need to go, and I've adjusted my SP to handle the getting the @@IDENTITY. However, I'm still unsure as to how I would add the selected items in the list box - how can I step through each of the items in SQL, bearing in mind there may be no items selected, or any number to up 6 selected?

Thanks in advance,

Ad

[Brandoe85]

Are you storing the selected items currently in the job table? When you populate the list box do you have an ID on that table it is reading from? Is tbljncJobsPCode going to have a record for each item? e.g if you select 2 items it will have 2 rows in the tbljncJobsPCode table and so forth?

I'm thinking you could do this - pass an array (sort of) to the sproc and then parse out those values and insert them into your table. So say you have 3 items selected in your application (if they have id's you can use those) so you would pass all the necessary info, and the string would be like "1,4,2" which would be the id's for the values you want to insert into your pCode table. In your sproc you can handle those and insert them with something like:


insert into table select @@IDENTITY, /* whatever value from your table that fills the select box here */ from table where id in(@stringEnteredFromApp)


Logically I think that will work, but if you can't get it I can post up some examples if you let me know the structures.

[adaytay]

Hi Brandoe,

Wow, thanks for that. Just trying to get my head around this - here's the modified SQL string (can post the whole lot if needed!)

SELECT @JobID=@@IDENTITY

INSERT INTO tbljncJobNoPCode SELECT @@IDENTITY, PCodeID FROM tblPCodes WHERE PCodeID IN(@PCodeID)

Here's where I'm firing the information to the sproc - have I got the detail correct?

With e.Command
.Parameters("@TechClerk").Value = CInt(TechClerk.SelectedValue)
.Parameters("@RequestType").Value = CInt(RequestType.SelectedValue)
.Parameters("@JobStatus").Value = CInt(JobStatus.SelectedValue)
.Parameters("@DateStarted").Value = CDate(DateStarted.Text)
.Parameters("@DateIssued").Value = CDate(DateIssued.Text)
.Parameters("@DateRequired").Value = CDate(DateRequired.Text)
.Parameters("@Priority").Value = CInt(Priority.SelectedIndex)
.Parameters("@RequestedBy").Value = CInt(RequestedBy.SelectedValue)
.Parameters("@Comments").Value = Comments.Text
.Parameters("@Resource").Value = CInt(Resource.SelectedValue)
.Parameters("@ApprovalTo").Value = CInt(ApprovalTo.SelectedValue)
.Parameters("@EstimateHours").Value = CInt(EstimateHours.Text)
.Parameters("@ActualHours").Value = CInt(ActualHours.Text)
.Parameters("@PCodeID").Value = CInt(lstPCodes.SelectedValue)
End With

Thanks for your help :)

Ad

[adaytay]

Right time for an update I think. I used the code (after a couple of stupid typos on my part) and it now runs successfully - HOWEVER it will only store the first selected item in the listbox.

How do I iterate through the list box collection and create a record for every item that has been selected?

Cheers,

Ad

[Brandoe85]

Give this a shot - the vb syntax might be a little off but you get the idea:

Dim ids As String = ""
Dim li As ListItem

For Each li in lstPCodes.Items
If li.Selected Then
ids &= li.Value.ToString() & ","
End If
Next

If ids.Length > 0 Then
ids = ids.Substring(0, ids.Length - 1) ' remove trailing comma
End If
You'll want to pass ids to your procedure and don't CInt() it since you'll want it to be a string. I would also recommend you look into the Convert (http://msdn.microsoft.com/en-us/library/system.convert.aspx) class(there are other methods as well) - CInt, CDate...etc are depreciated. Good luck.

[adaytay]

Hi Brandoe,

Hmmm... thanks for that. I've added the code into the Inserting procedure and it's successfully building a string. However, it seems to be failing on conversion - ie my line that says "e.Command..Parameters("@PCodeID").Value = ids" looks like it can't convert it properly - it advises that it is trying to convert from int32 to int16, whatever that means??

I'm really struggling with this, could do this no problem at all in VBA but this is a huge leap forward in coding methodology - I don't think I've found me feet yet (although I'm still reading up and trying a number of different ways to do this!!)

Thanks for the help though, really appreciate it. Any pointers for where I should look next?

[edit]I'm guessing that the problem must lie with part of my SQL statement - so here's that:

SELECT @JobID=@@IDENTITY

INSERT INTO tbljncJobNoPCode SELECT @@IDENTITY, PCodeID FROM tblPCodes WHERE PCodeID IN(@PCodeID)

Is this correct?[/code]

TIA,

Ad

[Brandoe85]

Yep - my mistake, the IN() statement won't work like I said, heh :o. Here's what you can do in your procedure:

DECLARE @JobID INT -- you might be declaring this already it seems like
SELECT @JobID = @@IDENTITY
DECLARE @str VARCHAR(100) -- used variable to avoid confusion
SET @str = @PCodeID

WHILE LEN(@str) > 0
BEGIN
INSERT INTO tbljncJobNoPCode SELECT @JobID, PCodeID FROM tblPCodes WHERE PCodeID = + left(@str, charindex(',', @str) - 1)
SET @str = RIGHT(@str, LEN(@str) - CHARINDEX(',', @str))
END
I noticed you already were setting JobId to the @@IDENTITY so use that in your query because it's value will change when we do an insert. Make sure @PCodeID is declared as varchar. Also, take out the the if statement in the vb code that removes the trailing comma here:

' remove this section, trailing comma is ok for our sql
If ids.Length > 0 Then
ids = ids.Substring(0, ids.Length - 1) ' remove trailing comma
End If
Give that a shot and let me know what you get.

/edit
take out the '+' in the sql statement I put up....I had it as a print statement to test :O

[adaytay]

I am truly at a loss for words... a little bit of jiggery-pokery, and job done - thank you soooo much! :D

I knew there must be a way to do what I needed - just couldn't figure it out lol. Thanks!!

[Brandoe85]

Cool glad you got it working :)