locked
SQL Insert to multiple tables RRS feed

  • Question

  • User1772043685 posted
    Here is my problem.  I have a relational Access database where I need to insert data to Table 1 (parent) as a new record.  I also need to insert data into Table 2 (child).

    > I can get the insert to work for the Parent, but a record also needs
    > to be inserted into the Child.   Below is an example of my tables.  I
    > get Table 1 to insert no problem.  What I need is table 2 to populate
    > based on the catID from table 1 which is primarty key and selected
    > imgID from table 3.  Since the catID is Autonumber in Table 1, I need to pass that to Table 2 in the Insert  The imgID should not be an issue since it is coming from a dropdown in the form>
     
    BTW, this is an Access db using Visual Studio 2005 Pro.  I am using the formview with an AccessDataSource.
     
    How do I get the data into table 2?

    > EX.
    >
    > Table 1
    >  catID
    >  imgID
    >  venID
    >
    >
    > Table 2
    >  catID
    >  imgID
    >
    >
    > Table 3
    >  imgID
    >  imgName
     
     
    Thanks, TRU
    Thursday, April 12, 2007 12:18 PM

Answers

  • User-821857111 posted
    Yeah, you're right.  Don't close the connection after the first command is executed.  Obviously, it won't need to be opened prior to the second one being executed.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 15, 2007 2:56 PM

All replies

  • User-821857111 posted
    In the FormView's ItemInserted event, you will need to run "SELECT @@IDENTITY" against the database to get the newly added record's ID.  Once you have this, you can run another Insert statement to enter the data into your second table.
    Thursday, April 12, 2007 2:35 PM
  • User1772043685 posted

    Thanks, but could you share an example of how this would look in the code as I am very new to .NET and use of custom sql statements.

     

    TRU

    Thursday, April 12, 2007 8:52 PM
  • User-821857111 posted
    Which language are you using? VB or C#?  And why is ImgID in Table1?  You appear to be relating it to table1 already through the CatId in table 2.
    Friday, April 13, 2007 2:37 AM
  • User1772043685 posted

     

     I am using VB.  This is my first project with Visual Studio Pro, and the last obstacle to make this project complete is to create the submission for to add records to the database.

     Tables 1,2,3 actually looks more like this below.  I believe the reason imgID is in Table 1 (Catalog) is to make the relationship between Items and Images.  Many Images are related to many Items.  I have 2 other tables that are irrelevant for this task Vendor and Category

    CATALOG
    catID
    Item
    Description
    Price
    imgID
    venID
    subID

    CATALOGIMAGE
    catID
    imgID


    IMAGES
    imgID
    imgName

    Thanks,

     TRU

     

     

    Which language are you using? VB or C#?  And why is ImgID in Table1?  You appear to be relating it to table1 already through the CatId in table 2.

    Friday, April 13, 2007 8:10 AM
  • User1772043685 posted

     

     I am using VB.  This is my first project with Visual Studio Pro, and the last obstacle to make this project complete is to create the submission for to add records to the database.

     Tables 1,2,3 actually looks more like this below.  I believe the reason imgID is in Table 1 (Catalog) is to make the relationship between Items and Images.  Many Images are related to many Items.  I have 2 other tables that are irrelevant for this task Vendor and Category.  As I mentioned before, my insert to Catalog works, but I need to carry the data to CATALOGIMAGE table.

    CATALOG
    catID
    Item
    Description
    Price
    imgID
    venID
    subID

    CATALOGIMAGE
    catID
    imgID


    IMAGES
    imgID
    imgName

    Thanks,

     TRU

     

     

    Which language are you using? VB or C#?  And why is ImgID in Table1?  You appear to be relating it to table1 already through the CatId in table 2.

    Friday, April 13, 2007 8:11 AM
  • User-821857111 posted

    I would get rid of imgID in CATALOG.  Images will be related to items in the CATALOG table through the bridging table - CATALOGIMAGE, which is the correct design for many-to-many relationships.

    Right, where were we? Oh yes.  VB. [:D]

     

        Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs) Handles FormView1.ItemInserted

    'Declare & Define Connection string Dim strConn As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" < path_to_db > ";" 'Declare & Define SQL Dim strSQL As String = "SELECT @@Identity" 'Create connection object with connection string Dim Conn As New OleDbConnection(strConn)
    'Create command object with SQL and connection oject Dim Cmd As New OleDbCommand(strSQL, Conn)
    'Declare & Define variable for new id value Dim newId As Integer Conn.Open() 'Execute the command and pas the returend result to newId newId = Cmd.ExecuteScalar() Conn.Close() 'Declare and Define new SQL statement using parameter marker (?)
    Dim strSQL2 As String = "Insert into CATALOGIMAGE (ImgID, CatID) Values (?," & newId & ")" Dim Cmd2 As New OleDbCommand(strSQL2, Conn)
    'Add the parameter to the command, and specify its source
    'This is the dropdownlist's selectedValue
    Cmd2.Parameters.AddWithValue("", ImageDropDown.SelectedValue)
    Conn.Open()
    'Execute the SQL to insert the values Cmd.ExecuteNonQuery() Conn.Close() End Sub

    You will need to import System.Data.OleDb, which you can do by simply adding Imports System.Data.OleDb at the top of the code-behind.

     

    Friday, April 13, 2007 8:44 AM
  • User1772043685 posted

    Thanks... I will give this a try and let you know how it goes.

     

    TRU

    Friday, April 13, 2007 8:48 AM
  • User1772043685 posted

    Ok.. well I tried it out has you have the code and having a problem.

     When running the page I get the following error.

    Compiler Error Message: BC30451: Name 'ImageDropDown' is not declared

     Line 32:         Cmd2.Parameters.AddWithValue("", ImageDropDown.SelectedValue)

     The only modification I did you your code is the connection string path

    Dim strConn As String = Server.MapPath("\App_Data\Database.mdb")

     

    One more thing I noticed that does not relate to the above problem, but could cause a problem once the above is resolved.

     Your code shows

    Dim strSQL2 As String = "Insert into CATALOGIMAGE (ImgID, CatID) Values (?," & newId & ")"

     in my table the fields are reversed in order.

    Should it read...

    Dim strSQL2 As String = "Insert into CATALOGIMAGE (CatID, ImgID) Values (" & newId & ",?)"


    Thank you for your time.

     TRU

    Friday, April 13, 2007 9:08 PM
  • User-821857111 posted

    Replace "ImageDropDown" with the ID of your actual dropdown control.  I don't know what your is called, so I just put something descriptive in there for you to change.  Same with the database connection string - which you noticed.

    The order in which the fields are listed in the INSERT statement is irrelevant, so long as the values follow the same order as the fields in the statement, and you AddWithValue() the parameters in the same order as well (when there are more than one).   Your version will work just as well as mine.

    Saturday, April 14, 2007 3:48 AM
  • User1772043685 posted

    I already had the ImageDropDown ID correct.  Your choice was the same as mine.

     Should there be reference in the code to the location of ImageDropDown as it is located in the InsertItemTemplate?

    Time to sleep now... Catch you in a few hours. [8-)]

     

    TRU

    Saturday, April 14, 2007 3:56 AM
  • User-821857111 posted

    I can't believe I overlooked the fact that the dropdown is in a FormView!  You need to use FindControl to access it:

     

        Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs) Handles FormView1.ItemInserted

    'Declare & Define Connection string Dim strConn As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" < path_to_db > ";" 'Declare & Define SQL Dim strSQL As String = "SELECT @@Identity" 'Create connection object with connection string Dim Conn As New OleDbConnection(strConn)
    'Create command object with SQL and connection oject Dim Cmd As New OleDbCommand(strSQL, Conn)
    'Declare & Define variable for new id value Dim newId As Integer Conn.Open() 'Execute the command and pas the returend result to newId newId = Cmd.ExecuteScalar() Conn.Close() 'Declare and Define new SQL statement using parameter marker (?) Dim strSQL2 As String = "Insert into CATALOGIMAGE (ImgID, CatID) Values (?," & newId & ")" Dim Cmd2 As New OleDbCommand(strSQL2, Conn)
    'Add the parameter to the command, and specify its source
    'Reference the DropDownList using FindControl()
    DropDownList ddl = CType(FormView1.FindControl("ImageDropDown"), DropDownList)
    'This is the dropdownlist's selectedValue Cmd2.Parameters.AddWithValue("", ddl.SelectedValue)
    Conn.Open()
    'Execute the SQL to insert the values Cmd.ExecuteNonQuery() Conn.Close() End Sub

    Sleep??!? I've had mine[:D] Been up a couple of hours now....

    Saturday, April 14, 2007 4:55 AM
  • User1772043685 posted

    Still not working[:'(]

     

    First off, Visual Studio  changes this line from

     DropDownList ddl = CType(FormView1.FindControl("ImageDropDown"), DropDownList)

     to this

    DropDownList(ddl = CType(FormView1.FindControl("ImageDropDown"), DropDownList))

    So I did the edit in notepad to keep VS from making edits.

     Either way I get the following error when running

    Compiler Error Message: BC30108: 'DropDownList' is a type and cannot be used as an expression.

    Source Error:

    Line 31:         'Add the parameter to the command, and specify its source
    Line 32:         'Reference the DropDownList using FindControl()
    Line 33:         DropDownList(ddl = CType(FormView1.FindControl("ImageDropDown"), DropDownList))
    

    Also in VS  editor I get ddl is not declared.

    Are we getting closer the solution?  What did you overlook this time? [;)][;)][;)]

     

     TRU

    Saturday, April 14, 2007 12:32 PM
  • User-821857111 posted

     DropDownList ddl = CType(FormView1.FindControl("ImageDropDown"), DropDownList)

     to this

    DropDownList(ddl = CType(FormView1.FindControl("ImageDropDown"), DropDownList))


    Oh poo.  Sorry.  I rarely use VB and gave you a mess betwen VB and C# code.

    Dim ddl As DropDownList = CType(FormView1.FindControl("ImageDropDown"), DropDownList)

     

    Are we getting closer the solution?  What did you overlook this time? [;)][;)][;)]

    Well, you shoud have spotted that!!! [:P]

    [:D] 

    Saturday, April 14, 2007 12:40 PM
  • User1772043685 posted

    Well Good News and Bad News

     The Good first.   The page load with no errors. YIPPEEEE!  [:D]

     The Bad...  Data is still not inserted into the bridge table CatalogImage. [:'(]

    Below is the entire code behind.  I am going to bang on this a couple more hours.  But difficult to work from something that creates no errors.

    Let me know if you have any new ideas.

    Imports System.Data.OleDb

    Partial Class admin_CatalogAdd

    Inherits System.Web.UI.Page

    Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs) Handles FormView1.ItemInserted

     

    'Declare & Define Connection string

    Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("~/App_Data/Database.mdb")

    'Declare & Define SQL

    Dim strSQL As String = "SELECT @@Identity"

    'Create connection object with connection string

    Dim Conn As New OleDbConnection(strConn)

    'Create command object with SQL and connection oject

    Dim Cmd As New OleDbCommand(strSQL, Conn)

    'Declare & Define variable for new id value

    Dim newId As Integer

    Conn.Open()

    'Execute the command and pass the returend result to newId

    newId = Cmd.ExecuteScalar()

    Conn.Close()

    'Declare and Define new SQL statement using parameter marker (?)

    Dim strSQL2 As String = "Insert into CATALOGIMAGE (CatID, ImgID) Values (" & newId & ",?)"

    Dim Cmd2 As New OleDbCommand(strSQL2, Conn)

    'Add the parameter to the command, and specify its source

    'Reference the DropDownList using FindControl()

    Dim ddl As DropDownList = CType(FormView1.FindControl("ImageDropDown"), DropDownList)

    'This is the dropdownlist's selectedValue

    Cmd2.Parameters.AddWithValue("", ddl.SelectedValue)

    Conn.Open()

    'Execute the SQL to insert the values

    Cmd.ExecuteNonQuery()

    Conn.Close()

    End Sub

    End Class

     

     TRU

    Saturday, April 14, 2007 10:52 PM
  • User1772043685 posted

    Mike,

    After further thought and more reading on this @@Identity topic, I am not sure this will work how we have been trying.

    As it is now, the data connection closes after submission to Table 1.  It is after the submission that the code behind kicks in with the Select @@Identity.  Shouldn't all be done in the same connection?

    So... submission is made to Table 1 connection closes.

    With code behind we try to open another connnection, but the Select @@Identity has nothing to work with since the connection was closed.

    Correct me if I am wrong, or if there is a way to get this code to work in onc connection?

    It is that time again......[|-)] [|-)] [|-)] [|-)]

     TRU

    Sunday, April 15, 2007 2:40 AM
  • User-821857111 posted
    Yeah, you're right.  Don't close the connection after the first command is executed.  Obviously, it won't need to be opened prior to the second one being executed.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 15, 2007 2:56 PM
  • User1772043685 posted

     Mike,

    Well i was not able to get your solution due to my own ineptitude. [*-)]

    I moved away from the Formview and went to a simple form.  My solution is below is my code behind.

    Thanks to my college course instructor MZ for helping me on this solution.

    Thanks to you Mike for your assistance and time.  Sorry I was not able to get your solution to work.

     I hope my solution and Mike's solution is valuable to somebody else.

    If you feel there is a way to improve this... please share.

     Final solution.....

     

    Partial Class CatalogAdd

    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

    CatalogDataSource.InsertParameters("catNum").DefaultValue = catNumTxt.Text

    CatalogDataSource.InsertParameters("catPrice").DefaultValue = catPriceTxt.Text

    CatalogDataSource.InsertParameters("catDescription").DefaultValue = CatDescriptionTxt.Text

    CatalogDataSource.InsertParameters("imgID").DefaultValue = ImageDropDown.SelectedValue

    CatalogDataSource.InsertParameters("venID").DefaultValue = VendorDropDown.SelectedValue

    CatalogDataSource.InsertParameters("subID").DefaultValue = CategoryDropDown.SelectedValue

    CatalogDataSource.Insert()

    Dim catID As Integer

    'create datasource object, point to db, set select statement

    'mz - select changed...

    Dim CatalogImageDataSource As New AccessDataSource("~/app_data/Database.mdb", "select max(catid) from [catalog] ")

    ' we are simply reading data, use simpler option

    CatalogImageDataSource.DataSourceMode = SqlDataSourceMode.DataReader

    ' go and read the database... point to results in myReader object

    Dim myReader As Data.IDataReader = CType(CatalogImageDataSource.Select(DataSourceSelectArguments.Empty), Data.IDataReader)

    'be sure something was read

    If myReader.Read Then

    'grab the value of the first column of the query...

    catID = myReader(0)

    End If

    Dim strSQL As String = "Insert into CATALOGIMAGE (catID, imgid) Values (" & catID & ", " + ImageDropDown.SelectedValue + ")"

    'new code - mz

    'define the specific command you want to execute for insert..

    CatalogImageDataSource.InsertCommand = strSQL

    CatalogImageDataSource.InsertCommandType = SqlDataSourceCommandType.Text

     

    ' end - mz

    CatalogImageDataSource.Insert()

    End Sub

    End Class

     

    Regards,

     

    TRU 

     

    Thursday, April 19, 2007 12:39 AM
  • User-821857111 posted

     

    Thanks to my college course instructor MZ for helping me on this solution.

    .....

    Dim CatalogImageDataSource As New AccessDataSource("~/app_data/Database.mdb", "select max(catid) from [catalog] ")

    I really hope course instructors are not advocating the use of Select Max() for returning the most recently created identity value.  

    Thursday, April 19, 2007 2:48 AM
  • User1772043685 posted

    Why do you see this as a problem?

     Since it is an autonumber, it does return the required data.

     TRU

    Thursday, April 19, 2007 7:56 AM
  • User-821857111 posted

    It will return the required number nearly every time.  If you have concurrent inserts going on, though, you may get the wrong number returned.  It's highly unlikely to happen, but not impossible.  That's why Microsoft introduced @@Identity with version 4.0 of the Jet provider.

    In your case, if you only have one person entering data, this isn't an issue, but you should be aware of it if there is any possibility that more than one person could hit their submit button at the same time. 

    Thursday, April 19, 2007 11:57 AM
  • User219737613 posted

    Hello,

     I came across a similar problem while trying to insert a primary key into a table as a foreign key. i somewhat had success running the following code:

    it appears that after closing a connection, you would have to dispose of it completely to retain the value (not sure if you actually need to close connection before dispose).

    Dim cb As String = "insert into tblProduction (AcctNo) values (" & Me.txtAcctNo.Text & ")"
                Dim perp As New OleDbCommand(cb, conn)
                Dim ProductionIDGET As String = "select @@identity"
                Dim nan As New OleDbCommand(ProductionIDGET, conn)
    
                conn.Open()
                perp.ExecuteNonQuery()
                KeyID = nan.ExecuteScalar()
                conn.Close()
    
                '
                'fetch identity key from tblProduction to be inserted as foreign Key
                'must dispose of connection so that KEYID can retain value for insertion
                'as foreign Key into different tables.
                '
                conn.Dispose()

     ******

    then i redeclare conn and run my next insert SQL with KeyID as my foreign key.

    i am new at picking up VB so please correct me if my terminology is not the correct terminology

     

    Friday, October 24, 2008 12:12 PM
  • User-821857111 posted

    This thread was over and done with more than a year and a half ago, so should really be left dead. However, you are new to the forums, so you weren't to know. [:D]

    It's not actually necessary to even close the connection, let alone dispose of it before you can use the value passed into KeyID.  However, it is good practice to close AND dispose of connections when you are done with them.  As it happens, a couple of months after this thread appeared, I put together a little piece on getting newly created autonumber values: http://www.mikesdotnetting.com/Article.aspx?ArticleID=54.  You will notice that connections are created within a Using block.  This automatically takes care of closure and disposal.

     

    Friday, October 24, 2008 2:21 PM