inserting data into multiple tables

Results 1 to 6 of 6

Thread: inserting data into multiple tables

  1. #1
    hopeless Guest

    Default inserting data into multiple tables

    access database. have form that i want to insert data into 3 tables. how do you do multiple insert statements from same form?

  2. #2
    Heaven's Martini Guest

    Default RE: inserting data into multiple tables

    Do you have<BR>a processing page?<BR><BR>All you have to do is this.<BR>create connection<BR>open connection<BR><BR>sql = "insert into smoking (blah) Values (&#039;"& blahvar &"&#039;)<BR>connection.execute(sql)<BR><BR>sql = "insert ...<BR>conn......<BR><BR>sql = "

  3. #3
    hopeless Guest

    Default data integrity

    If you use individual insert statements how do you maintain the referential integrity between the data.<BR>ie.<BR><BR>The 3 tables are related accordingly.<BR>OwnerInfo maintains a list of all registered owners. <BR>PropertyInfo maintains basic property info ie adress, etc<BR>PropertyDetails maintains more advanced set of details about each property.<BR><BR>Each owner may have several properties (one to many)<BR>Each property only has one set of details (one to one)<BR><BR>When I go to retrieve this info I need to put these all together again.

  4. #4
    Heaven's Martini Guest

    Default integrity:something disregarded by politicians

    Well,<BR><BR>I am missing the point.<BR><BR>You can use the ado transaction stuff.<BR>so you call<BR>connection.begintrans<BR><BR>.. sql calls<BR><BR>check for errors<BR>rollback<BR>or committrans.<BR><BR>why not just insert the foreign keys with it.<BR><BR>...just came to me.<BR>read the faq about retrieving the just inserted record id.<BR>that must be what you mean. <BR><BR>Or use a sp.

  5. #5
    hopeless Guest

    Default RE: integrity:something disregarded by politicians

    I&#039;m sorry let me try to clarify what I&#039;m trying to do. I tried to use a sp but keep getting error message. Too few parameters. Expected 2. <BR><BR>In the code below you see I call the CheckOwnerProc procedure. The CheckOwnerProc has two parameters that are needed to execute. <BR>I know how to pass the parameters for the fields requested by <BR>InsertPropProc but is it possible to pass the parameters first to the CheckOwnerProc so that the InsertPropProc may execute correctly. Shortened versions of code below. This error is not happening because fields are unequal or spelled wrong. I don&#039;t know how to pass the parameters through to the CheckOwnerProc from the InsertPropProc or if this is even possible. <BR><BR>SQL Code: <BR>INSERTPROPPROC <BR>INSERT INTO CheckOwnerProc[**** Requires two parameters****] <BR>( PropertyAddress, City, State, ... ) <BR>SELECT [@PropertyAddress], [@City], [@State], [@ZipCode], ... <BR><BR>CHECKOWNERPROC <BR>SELECT OwnerInfo.UserName, OwnerInfo.UserPassword,... <BR>WHERE OwnerInfo.UserName = [(@UserName)] AND <BR>OwnerInfo.UserPassword = [(@UserPassword)]; <BR><BR>ASP Code: <BR>Set cmdObj = Server.CreateObject("ADODB.Command") <BR>cmdObj.CommandText = "PropertyInsertProc" <BR>cmdObj.CommandType = &H0004 <BR><BR>Is it possible to create the parameters for the CheckOwnerProc <BR>through the parameters created for PropertyInsertProc? <BR>i.e. <BR>cmdObj.Parameters.Append objCommand.CreateParameter <BR>("OUserName", 200, &H0001, 20) <BR>cmdObj.Parameters.Append objCommand.CreateParameter("OUserPassword", 200, &H0001, 20) <BR>

  6. #6
    Heaven's Martini Guest

    Default RE: integrity:something disregarded by politicians

    Hmm<BR><BR>getting out of my bailiwick unfortunately.<BR><BR>I haven&#039;t used sp&#039;s for much yet.<BR><BR>You have to be able to do what you are saying.<BR>msdn should have something on that.<BR><BR>sorry i couldn&#039;t be of more help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts