Insert Query problems. Please help.

[RotorTorque]

Hi all,
I have an insert query that needs to save data into 2 tables (tblCases, tblAttachments). There could be multiple attachments per 1 Case.

Table tblCases Has the following fields:
CaseID
Description

Table tblAttachments has the following fields:
CaseID
AttachmentFilePath


Thanks in advance,
RotorTorque

 

[bborissov]

Could you post some data and what you want?


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[RotorTorque]

bborissov,
Here is some sample data:

Table tblCases (should save 1 record)
CaseID = 1
Description = "Error File"

Table tblAttachments (should save 2 records)
CaseID = 1
AttachmentFilePath = C:\Image1

CaseID = 1
AttachmentFilePath = C:\Image2

I also want the Query to Return The CaseID from table tblCases.

Thanks once again,
RotorTorque

 

[bborissov]

How many could be these attachments?
10, 20, 30?
Could you have Case record in tblCases w/o attachments?
What you want to do if your you insert record in tblCases but inserting records in tblAttachments failed?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLSister]

How will you know there are multiple files to be saved for a particular CaseID? Is CaseID an identity field?

"NOTHING is more important in a database than integrity." ESquared

 

[RotorTorque]

bborisev anhd SQLSister,

There could be an unlimited number of records, but usually no more than 3. I will know how many files there are because I will do an attachment count in my VB.Net 2005 application. CaseID is an Identity field in tblCases, and a foreign key in tblAttachments.

Thank you guys. It's people like you that make this site awesome.

RotorTorque

 

[SQLSister]

So really waht you are looking to get is the identity value, so that you can insert into the second table?

Look at
scope_identity() in Books on line. I thin you will find it helpful. Under no circumstances use @@Identity to get the value as it will not always give the correct values and use of @@Identity can give you data integrity problems if you ever add a trigger to the table which inserts records into another table with an identity field.

"NOTHING is more important in a database than integrity." ESquared

 

[SQLSister]

declare @idfield int
insert table1 (field1, field)
values (10, 'test')

set @idfield = scope_Identity()

insert table2 (field1, field2, field3)
values (@idfield, 'another test', 100)

Note - scope_identity() is only useful if you insert one record at a time.


"NOTHING is more important in a database than integrity." ESquared

 

[RotorTorque]

Thanks SQLSister.

RotorTorque ;-)

 

[bborissov]

RotorTorque,
I would build the whole INSERT string at frontend and then execute it at once, the final result should look like SQLSister's suggestion:
I am not familiar with VB.NET syntax but I'll try to write something:

// VB.NET front end
Dim lcSQLString string
lcSQLString = "declare @idfield int" & vbCRLF &_
              "insert table1 (Description) values ('" & description here & "')"& vbCRLF &_
              "set @idfield = scope_Identity()"
Dim I int
For i = 1 TO attachments count
    lcSQLString = lcSQLString & vbCRLF &_
                  "INSERT INTO tblAttachments (CaseId, AttachmentFilePath) VALUES (@idfield,'" & attachment here & "')"
Next


' Execute that string

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bborissov]

O, I forgot. If you want everything to be OK, I mean If something fail no records to be added in BOTH tables round the whole statement with transaction.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP