none
Typed DataSet in VS 2013, insert query RRS feed

  • Question

  • First I need to start with, I don't know what forum I should ask this question in.

    I am working with a typed dataset and would like to use the VS2013 query builder to write the SQL that inserts rows into my table. I have one table with data that needs to be inserted into another table, in the typed dataset. I opened the query builder through the dataset designer but I have hit a wall. It is not as intuitive as I thought it would be so I don't know how to make the insert query work for me. 

    Does anyone have pointers? I am not afraid to read and learn what I don't know. My google searches have yielded nothing.

    Thanks


    Hmm... I feel like I have more questions than answers. If you find my reply helpful please mark as Answer below. Thanks.


    • Edited by Trewaters Saturday, August 16, 2014 11:51 PM wrong title
    • Moved by Amanda Zhu Monday, August 18, 2014 2:30 AM better forum
    Saturday, August 16, 2014 11:47 PM

Answers

  • Hello Trewaters,

    From your description, do you mean that you want to run sql statement as below in the typed dataset:

    insert into TableY(TableYID, SomeValue,  SomeValueToJoin ) select TableX.TableXID,TableX.SomeValue, TableX.SomeValueToJoin from TableX

    What item do you create in the designer model? If you create the DataTable and DataTableApater, this is not impossible, the two items could not do what you want. The way is that in the designer model, adding a query and select the insert sql query type as:

    Then you could write your insert sql statement which is similar with mine in the next step, it would create an adapter named QueriesTableAdapter by default if you do not modify its name, we use this object to run the generated insert method:

    QueriesTableAdapter da = new QueriesTableAdapter();
    
    
                int affectrows = da.InsertQuery();
    
    If this does not work for you, please let me know freely.

    Best Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Trewaters Monday, August 18, 2014 10:33 PM
    Monday, August 18, 2014 3:19 AM
    Moderator
  • Alright this is what I came up with. Thanks to Fred Boa for letting me know the SQL Builder wouldn't help me.

    I am still learning how to code this stuff. So I am sure there is a much better way to do this. If you have a better way please share what works for you.

    'For Each Loop reads every row in tblShutOff, which is my important data
                                For Each vDataRow As DataRow In DhA_sampleDataSet1.tblShutOff.Rows
    
                                    'create variable for new row
                                    Dim vTblWDNewRow As DataRow = DhA_sampleDataSet1.tblWorkingData.NewRow()
    
                                    'My idea of mapping table rows, each vTblWDNewRow(#) takes data from the other table
                                    vTblWDNewRow(0) = vDataRow(0)
                                    vTblWDNewRow(1) = vDataRow(1)
                                    vTblWDNewRow(2) = vDataRow(2)
                                    vTblWDNewRow(3) = vDataRow(3)
                                    vTblWDNewRow(4) = vDataRow(4)
                                    vTblWDNewRow(5) = vDataRow(5)
                                    vTblWDNewRow(6) = vDataRow(6)
                                    vTblWDNewRow(7) = vDataRow(7)
                                    vTblWDNewRow(8) = vDataRow(8)
                                    vTblWDNewRow(9) = vDataRow(9)
                                    vTblWDNewRow(10) = vDataRow(10)
                                    vTblWDNewRow(11) = vDataRow(11)
                                    vTblWDNewRow(12) = vDataRow(12)
                                    vTblWDNewRow(13) = vDataRow(13)
                                    vTblWDNewRow(14) = vDataRow(14)
                                    vTblWDNewRow(15) = "None"
                                    vTblWDNewRow(16) = vDataRow(22)
                                    vTblWDNewRow(17) = vDataRow(23)
                                    vTblWDNewRow(18) = vDataRow(24)
                                    vTblWDNewRow(19) = 0
                                    vTblWDNewRow(20) = 0
                                    vTblWDNewRow(21) = #1/1/1900#
                                    vTblWDNewRow(22) = #1/1/1900#
                                    vTblWDNewRow(23) = #1/1/1900#
                                    vTblWDNewRow(24) = vDataRow(16)
                                    vTblWDNewRow(25) = vDataRow(18)
                                    vTblWDNewRow(26) = vDataRow(19)
                                    vTblWDNewRow(27) = vDataRow(20)
                                    vTblWDNewRow(28) = vDataRow(21)
                                    vTblWDNewRow(29) = vDataRow(15)
                                    vTblWDNewRow(30) = vDataRow(17)
    
    'add new row to tblWorking data 
    DhA_sampleDataSet1.tblWorkingData.Rows.Add(vTblWDNewRow)
    Next vDataRow


    Hmm... I feel like I have more questions than answers. If you find my reply helpful please mark as Answer below. Thanks.

    • Marked as answer by Trewaters Monday, August 18, 2014 10:32 PM
    Monday, August 18, 2014 10:32 PM

All replies

  • Sunday, August 17, 2014 3:43 PM
  • Hello Trewaters,

    From your description, do you mean that you want to run sql statement as below in the typed dataset:

    insert into TableY(TableYID, SomeValue,  SomeValueToJoin ) select TableX.TableXID,TableX.SomeValue, TableX.SomeValueToJoin from TableX

    What item do you create in the designer model? If you create the DataTable and DataTableApater, this is not impossible, the two items could not do what you want. The way is that in the designer model, adding a query and select the insert sql query type as:

    Then you could write your insert sql statement which is similar with mine in the next step, it would create an adapter named QueriesTableAdapter by default if you do not modify its name, we use this object to run the generated insert method:

    QueriesTableAdapter da = new QueriesTableAdapter();
    
    
                int affectrows = da.InsertQuery();
    
    If this does not work for you, please let me know freely.

    Best Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Trewaters Monday, August 18, 2014 10:33 PM
    Monday, August 18, 2014 3:19 AM
    Moderator
  • Thank you both for your responses.

    pvdg42 - I have read that link and the answer still alludes me.

    Fred Bao - Your query is what I wanted to do in Query Builder. It sounds like that isn't possible. I am trying to follow the second part of your answer but I am doing something wrong. 

    When I created the "new" insert query I called it "InsertTblSO", but since I don't fully understand your answer my VB.Net code isn't working.

    The code I am trying is 

    TblWorkingTableAdapter da = new TblWorkingTableAdapter()
    
    int affectrows = da.InsertTblSO()

    Intellisense doesn't like this and I can't follow it either. What am I missing?

    I try to add something more like this. Where "TblWorkingDataTableAdapter" is my Typed Dataset, "tblWorkingData" is my table, and "InsertTblSO" is the new insert query I created.

    TblWorkingDataTableAdapter.InsertTblSO(DhA_sampleDataSet1.tblWorkingData)

    and Intellisense still doesn't like it.

    Thanks again for the answer but I am not quite getting it to work for me. I think I need some more guidance.


    Hmm... I feel like I have more questions than answers. If you find my reply helpful please mark as Answer below. Thanks.

    Monday, August 18, 2014 3:02 PM
  • I would not use a dataset for this case. Simply create an SQL script and run it using a query manager

    This is the forum therefore, using a dataset for this is like first going to the moon.

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql


    Success
    Cor

    Monday, August 18, 2014 6:06 PM
  • Alright this is what I came up with. Thanks to Fred Boa for letting me know the SQL Builder wouldn't help me.

    I am still learning how to code this stuff. So I am sure there is a much better way to do this. If you have a better way please share what works for you.

    'For Each Loop reads every row in tblShutOff, which is my important data
                                For Each vDataRow As DataRow In DhA_sampleDataSet1.tblShutOff.Rows
    
                                    'create variable for new row
                                    Dim vTblWDNewRow As DataRow = DhA_sampleDataSet1.tblWorkingData.NewRow()
    
                                    'My idea of mapping table rows, each vTblWDNewRow(#) takes data from the other table
                                    vTblWDNewRow(0) = vDataRow(0)
                                    vTblWDNewRow(1) = vDataRow(1)
                                    vTblWDNewRow(2) = vDataRow(2)
                                    vTblWDNewRow(3) = vDataRow(3)
                                    vTblWDNewRow(4) = vDataRow(4)
                                    vTblWDNewRow(5) = vDataRow(5)
                                    vTblWDNewRow(6) = vDataRow(6)
                                    vTblWDNewRow(7) = vDataRow(7)
                                    vTblWDNewRow(8) = vDataRow(8)
                                    vTblWDNewRow(9) = vDataRow(9)
                                    vTblWDNewRow(10) = vDataRow(10)
                                    vTblWDNewRow(11) = vDataRow(11)
                                    vTblWDNewRow(12) = vDataRow(12)
                                    vTblWDNewRow(13) = vDataRow(13)
                                    vTblWDNewRow(14) = vDataRow(14)
                                    vTblWDNewRow(15) = "None"
                                    vTblWDNewRow(16) = vDataRow(22)
                                    vTblWDNewRow(17) = vDataRow(23)
                                    vTblWDNewRow(18) = vDataRow(24)
                                    vTblWDNewRow(19) = 0
                                    vTblWDNewRow(20) = 0
                                    vTblWDNewRow(21) = #1/1/1900#
                                    vTblWDNewRow(22) = #1/1/1900#
                                    vTblWDNewRow(23) = #1/1/1900#
                                    vTblWDNewRow(24) = vDataRow(16)
                                    vTblWDNewRow(25) = vDataRow(18)
                                    vTblWDNewRow(26) = vDataRow(19)
                                    vTblWDNewRow(27) = vDataRow(20)
                                    vTblWDNewRow(28) = vDataRow(21)
                                    vTblWDNewRow(29) = vDataRow(15)
                                    vTblWDNewRow(30) = vDataRow(17)
    
    'add new row to tblWorking data 
    DhA_sampleDataSet1.tblWorkingData.Rows.Add(vTblWDNewRow)
    Next vDataRow


    Hmm... I feel like I have more questions than answers. If you find my reply helpful please mark as Answer below. Thanks.

    • Marked as answer by Trewaters Monday, August 18, 2014 10:32 PM
    Monday, August 18, 2014 10:32 PM