Results 1 to 35 of 35

Thread: [RESOLVED] How to alter my DB Tables and add the old data into new structure tables

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Resolved [RESOLVED] How to alter my DB Tables and add the old data into new structure tables

    Hi. It is first time that I'm having this problem; I had developed an application which has been deployed on client's computer. Now he requested for some changes in application for which i have to change the tables structure as well as lot of coding too. What i am worry about is that if i make changes to the structure of tables so how i will incorporate the old data into this newly structure tables because this newly structured tables will have more or less columns than the previous one. The client has started working upon the application so how to solve it. Please guide me through...

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to alter my DB Tables and add the old data into new structure tables

    The specifics depend on the specifics. You'll need to execute the appropriate ALTER TABLE statements to add or remove the appropriate columns. New columns will have to accept NULL to begin with because they will be empty. You'll then need to execute appropriate UPDATE statements to populate the new column(s) with appropriate data.

    Where and when you do this is up to you. You could build it into the application itself. At startup you could query the database and check the schema and, if it's not the new schema then update it. That means that you'll be testing the schema every time you startup, which is not a big deal but is an overhead. That's probably no good for a multi-user environment where some users may not have appropriate permissions. Alternatively, you could just provide a script and stipulate that it must be run against the database as part of the update process. The app will throw an exception if it's not run but you can only do so much to protect people from themselves.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    Dearest John, there are so much things i have never faced before... So if you and other precious members are with me so we can reach to the destination.
    Please guide me stepwise that from where do i start now, and gradually we'll finish this soon.

    my table is SaleMsater and i have to incorporate some fields in it.
    Code:
    SaleID  (Primary Key)
    SaleTotalAmount
    SalePaidAmount
    SaleDiscount
    SaleNetBalance
    SaleDate
    The field I've have to enter for the time being is
    Code:
    SaleTotalProfit
    SaleTotalLoss

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to alter my DB Tables and add the old data into new structure tables

    How about, rather than us guiding you stepwise, you do some appropriate research? I said earlier that you should start with an ALTER TABLE statement. Have you made any effort at all to learn about that or did you just think that you may as well let us do all the work for you?

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    Quote Originally Posted by jmcilhinney View Post
    How about, rather than us guiding you stepwise, you do some appropriate research? I said earlier that you should start with an ALTER TABLE statement. Have you made any effort at all to learn about that or did you just think that you may as well let us do all the work for you?
    Nah Nah John, you started guiding me and here i am going to start how to work on alter statement.

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    I found the alter statement and applied it on one my DB as a practice it worked great for me. Now what to do now.
    This is original table:

    Code:
    ContactId
    Father_NIC
    Contact1
    Contact2
    I added Contact3 in it, with alter statement
    Code:
    alter table contact add Contactno3 varchar(15)
    It worked for me.

    Now what to do JMC next.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to alter my DB Tables and add the old data into new structure tables

    Quote Originally Posted by ADQUSIT View Post
    Now what to do JMC next.
    I already told you what to do next in an earlier post so why would I need to tell you again?

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    My dearest John, everytime you answer me, i feel more comfortable that you are with me.. I feel my track is safe to go forward.... ...

    You said that
    You'll then need to execute appropriate UPDATE statements to populate the new column(s) with appropriate data.
    You want me to execute UPDATE statements because the newly inserted columns will be empty and I have to populate those one will suitable data? right?


    I want to explain my application little bit. I developed a standalone PC application, where the actually DB and application both are installed on a single PC right. I installed application and SQL Server 2008 on the client computer and he has started working upon.

  9. #9
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,832

    Re: How to alter my DB Tables and add the old data into new structure tables

    I'm sure you will work through this, post if you are stuck. One word of advice, er three, backups, backups, backups.
    Please remember next time...elections matter!

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    Hello Tyson... Sorry to pick you, but what backups? Do you mean that i take backup of client DB, before altering it?

  11. #11
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: How to alter my DB Tables and add the old data into new structure tables

    Do you mean that i take backup of client DB, before altering it?
    Yes. ALWAYS take a back up before altering a production database.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    Hmm. Well, there is no such option in my app so far to take backup of the application? Should i just copy the .mdf and .ldf files into some safe place?

  13. #13
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: How to alter my DB Tables and add the old data into new structure tables

    You could do that but it's not ideal. Instead you should take a formal backup. The easiest way is to use management studio. You just right click on the database you want to backup and select "backup". It's very easy.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    Oh.. Yess... Okay. I will follow this.

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    So. Here is my altered table after alter statement.
    Name:  ProductInfo.jpg
Views: 265
Size:  24.1 KB
    The table has to be updated now. I tried a simple code, but its very cumbersome way to do it. I cannot make logic for this.

    The scenario is that I have a show all button on my form, when i click on it, so all fields of ProductBasicInfo table loads into DGV. When i double click on any product in DGV, so it is loaded into the fields (textboxes) on form and then the user can update the entire entry.

    Here is my update code:

    Code:
    Try
                updateTable("ProductBasicInfo", "ProdName = '" & txtProdName.Text.Trim & "', Description = '" & txtProdDesc.Text.Trim & "', Manufacturer = '" & txtProdManuf.Text.Trim & "', Store = " & txtStore.Text.Trim & ", ProdPrice = " & txtProdPrice.Text.Trim & "", " ProdName = '" & txtOldValue.Text.Trim & "' and Description = '" & txtOldDesc.Text.Trim & "' and Manufacturer = '" & txtOldManuf.Text.Trim & "' and Store = " & txtOldStore.Text.Trim & " and ProdPrice = " & txtOldProdPrice.Text.Trim & "")
                'updateTable("ProductBasicInfo", " ProdName = '" & txtProdName.Text.Trim & "', Description = '" & txtProdDesc.Text.Trim & "'", " ProdName = '" & txtOldValue.Text.Trim & "' and Description = '" & txtOldDesc.Text.Trim & "' ")
                MessageBox.Show("Record updated successfully")
    
                btnUpdate.Enabled = False
                btnProdSubmit.Enabled = Enabled
    
                txtProdName.Clear()
                txtProdDesc.Clear()
                txtProdManuf.Clear()
                txtStore.Clear()
                txtProdPrice.Clear()
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                cnSql.Close()
            End Try
    and this is update function at module level:

    Code:
    #Region "Updating"
    
        Public Sub updateTable(ByVal tblName As String, ByVal parameter As String, ByVal criteria As String)
    
            Try
                cmdSql1.CommandText = " update " & tblName & " set " & parameter & " where " & criteria & ""
                cmdSql1.Connection = Connect()
                daSql.UpdateCommand = cmdSql1
                daSql.Fill(dsSql, tblName)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
    #End Region
    The problem is that as the ProdPrice filed is empty (null) so my update query does not update the records. Only the first entry can be updated because 60 was entered by me manually. Please guide me that how to do this now.

  16. #16
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to alter my DB Tables and add the old data into new structure tables

    Wow - you have hardwired SQL statements in code - and now you are altering the table structure.

    That can be painful.

    If you were using stored procedures life would be so, so much easier.

    If you want to limit the pain you could create a new table with same PK as the old table and the new fields. Certainly not something I would typically suggest - but you have to measure the return on investment here.

    btw - you should read up on SQL INJECTION - you are way open to attack here.

    Also open to just failed inserts when the user puts SQL delimiters in the data!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  17. #17
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,832

    Re: How to alter my DB Tables and add the old data into new structure tables

    I was under the impression this was a one off and will not be done again. Is that not true?
    Please remember next time...elections matter!

  18. #18
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to alter my DB Tables and add the old data into new structure tables

    Here's some scripts I created recently to alter a table from SSMS

    Code:
    Use Retfiles
    Go
    Exec SP_Rename 'Pay1099_T.TDate','Percentage','COLUMN'
    Go
    Alter Table Pay1099_T Add TDate datetime null
    Go
    Update Pay1099_T Set TDate=Percentage
    Go
    Update Pay1099_T Set Percentage=null
    Go
    Alter Table Pay1099_T Alter Column Percentage int not null
    Update Pay1099_T Set Percentage=0
    I've got a TDATE field that I like to keep as the last field in every one of my tables (bit OCD of me - so what!)

    So that script renamed that field - added a new field - moved the data between the fields and then cleaned up with an UPDATE to set the new field (old TDATE field) to 0

    This next example the table had a couple of EXTRA field so that I could rename one and make use of it

    Code:
    Use Retfiles
    Go
    select * from penstatus_t
    Exec SP_Rename 'PenStatus_T.Extra1','PayGroup','COLUMN'
    Go
    select * from penstatus_t
    Alter Table PenStatus_T Alter Column PayGroup varchar(4)
    Go
    --Drop Table #XYZ Drop Table #ABC
    Select * Into #XYZ From PenMemAct_V
    
    Update PenStatus_T Set PayGroup=(Select Top 1 PayGroup From #XYZ PV Where PV.MasId=PS.MasId and PV.PlanYr<=PS.PlanYr Order by PlanYr Desc)
    	From PenStatus_T PS
    
    Update PenStatus_T Set PayGroup=(Select Top 1 PayGroup From RetDed_T PC Where PC.MasId=PS.MasId Order by PC.PayPeriod Desc)
    	From PenStatus_T PS
    	Where IsNull(PS.PayGroup,'')=''
    
    Select * From PenStatus_T
    	Where IsNull(PayGroup,'')=''
    This next script actually makes a copy of the original table - works it up a bit - and then re-inserts rows from the save table

    Code:
    Use Acctfiles
    Go
    Begin Tran
    
    Select * Into Pay_2012_W2W_T From Pay_W2W_T
    
    Delete From Pay_W2W_T
    
    Alter Table Pay_W2W_T Drop Column FILL6
    Alter Table Pay_W2W_T Drop Column RW_SEI
    Alter Table Pay_W2W_T Drop Column FILL7
    Alter Table Pay_W2W_T Drop Column RW_RPI
    Alter Table Pay_W2W_T Drop Column RW_TPSPI
    Alter Table Pay_W2W_T Drop Column FILL8
    
    Alter Table Pay_W2W_T Add RW_CESHC char(11) null
    Alter Table Pay_W2W_T Add FILL6 char(12) null
    Alter Table Pay_W2W_T Add RW_SEI char(1) null
    Alter Table Pay_W2W_T Add FILL7 char(1) null
    Alter Table Pay_W2W_T Add RW_RPI char(1) null
    Alter Table Pay_W2W_T Add RW_TPSPI char(1) null
    Alter Table Pay_W2W_T Add FILL8 char(23) null
    
    Insert into Pay_W2W_T
    	Select RW_ORDER, RW_TYPE, RW_SSN, RW_FNAME, RW_MNAME, RW_LNAME, RW_SNAME
    			, RW_LADDR, RW_DADDR, RW_CITY, RW_STATE, RW_ZIP, RW_ZIPEXT, FILL1
    			, RW_FSP, RW_FPC, RW_CC, RW_WTOC, RW_FITWH, RW_SSWAGE, RW_SSWH
    			, RW_MEDWT, RW_MEDWH, RW_SSTIPS, RW_AEIC, RW_DCB, RW_401, RW_403
    			, RW_408, RW_457, RW_501, RW_MEBQSCP, RW_N457, RW_ECHSA, RW_NN457
    			, RW_NCP, FILL5, RW_INS, RW_IENSO, RW_DUS409, RW_DRCS401, RW_DRCS403
    			,'' , FILL6
    			, RW_SEI, FILL7, RW_RPI, RW_TPSPI, FILL8
    		From Pay_2012_W2W_T
    
    Select Top 100 * From Pay_W2W_T
    Select Count(*) From Pay_W2W_T
    
    Exec SP_Rename 'Pay_W2T_T.FILL3','RT_CESHC','COLUMN'
    
    --Commit--
    Rollback

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  19. #19

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    Quote Originally Posted by TysonLPrice View Post
    I was under the impression this was a one off and will not be done again. Is that not true?
    You are right Tyson, this won't be done again, but right now, I'm still working on the same (old) project, where i had used the straight sql queries. I"m trying to finish it, but the client everytime asks for somthing new in it.

  20. #20
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,832

    Re: How to alter my DB Tables and add the old data into new structure tables

    Quote Originally Posted by ADQUSIT View Post
    You are right Tyson, this won't be done again, but right now, I'm still working on the same (old) project, where i had used the straight sql queries. I"m trying to finish it, but the client everytime asks for somthing new in it.
    I thought so and I understand that you need it quick but I suggest you follow szlamany's direction.

    How many rows need updated? That could help deciding on a quick and dirty approach.
    Please remember next time...elections matter!

  21. #21

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    I'm regret to say that i did not get what is written in your post sir szlamany. It came into my notice after developing this application that straight queries has so many fatal issues, but now I'm trying to put it to end. Please guide me that how to do my problem solved.

  22. #22
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to alter my DB Tables and add the old data into new structure tables

    If you are trying to bring this project to completion - then you are not going to stop using inline queries right now.

    Just realize that it's a bad idea - start looking at stored procedures. If you do go the SPROC route in the future do not work them into the database directly. Always make a .SQL text file that DROP's and RE-CREATE's SPROCS. Making changes to objects in a database without scripting them is a really bad idea imo (as you see I posted some scripts that changes fields and such)

    Is this thread resolved or do you need further assistance?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  23. #23

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    I'll try to use SPROC, as i have never faced with this. For the time, Yes i cannot stop using the incline queries but i have to complete it. And my thread is not resolved yet. My post # 15 is still unanswered and other things are here too, to discuss.

  24. #24
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to alter my DB Tables and add the old data into new structure tables

    Quote Originally Posted by ADQUSIT View Post
    So. Here is my altered table after alter statement.
    Name:  ProductInfo.jpg
Views: 265
Size:  24.1 KB
    The table has to be updated now. I tried a simple code, but its very cumbersome way to do it. I cannot make logic for this.

    The scenario is that I have a show all button on my form, when i click on it, so all fields of ProductBasicInfo table loads into DGV. When i double click on any product in DGV, so it is loaded into the fields (textboxes) on form and then the user can update the entire entry.

    Here is my update code:

    Code:
    Try
                updateTable("ProductBasicInfo", "ProdName = '" & txtProdName.Text.Trim & "', Description = '" & txtProdDesc.Text.Trim & "', Manufacturer = '" & txtProdManuf.Text.Trim & "', Store = " & txtStore.Text.Trim & ", ProdPrice = " & txtProdPrice.Text.Trim & "", " ProdName = '" & txtOldValue.Text.Trim & "' and Description = '" & txtOldDesc.Text.Trim & "' and Manufacturer = '" & txtOldManuf.Text.Trim & "' and Store = " & txtOldStore.Text.Trim & " and ProdPrice = " & txtOldProdPrice.Text.Trim & "")
                'updateTable("ProductBasicInfo", " ProdName = '" & txtProdName.Text.Trim & "', Description = '" & txtProdDesc.Text.Trim & "'", " ProdName = '" & txtOldValue.Text.Trim & "' and Description = '" & txtOldDesc.Text.Trim & "' ")
                MessageBox.Show("Record updated successfully")
    
                btnUpdate.Enabled = False
                btnProdSubmit.Enabled = Enabled
    
                txtProdName.Clear()
                txtProdDesc.Clear()
                txtProdManuf.Clear()
                txtStore.Clear()
                txtProdPrice.Clear()
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                cnSql.Close()
            End Try
    and this is update function at module level:

    Code:
    #Region "Updating"
    
        Public Sub updateTable(ByVal tblName As String, ByVal parameter As String, ByVal criteria As String)
    
            Try
                cmdSql1.CommandText = " update " & tblName & " set " & parameter & " where " & criteria & ""
                cmdSql1.Connection = Connect()
                daSql.UpdateCommand = cmdSql1
                daSql.Fill(dsSql, tblName)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
    #End Region
    The problem is that as the ProdPrice filed is empty (null) so my update query does not update the records. Only the first entry can be updated because 60 was entered by me manually. Please guide me that how to do this now.
    I have to say I do not understand the problem here. Do you have DGV's that do not know about the new column you added?

    Please explain this a whole lot more - thanks!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  25. #25

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    I post # 24 the first image is my DB image in which The ProdPrice has to be updated, this is what i really want. If the records were few so i would update them one by one by there are hundreds of records, so i cannot update them individually. I want that all records all updated at one time.

    Yes szlamany, The entire table is being loaded into DGV in the image.
    Name:  form1.jpg
Views: 223
Size:  30.8 KB

  26. #26

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    Everytime i double click on any product it gets loaded into fields and then i update the record, but this way is very difficult to do for lots of records. I hope that now the problem will be a bit clearer.

  27. #27
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,832

    Re: How to alter my DB Tables and add the old data into new structure tables

    Quote Originally Posted by ADQUSIT View Post
    Everytime i double click on any product it gets loaded into fields and then i update the record, but this way is very difficult to do for lots of records. I hope that now the problem will be a bit clearer.
    Figure out what is happening step by step when you do it manually. Post it here and I'll try and help. What you do step by step is what needs done to the table enmass. If it is a case by case decision then it may not be pracitible to do it in a batch. So the first step is what is the decision tree to make the updates.

    Sometimes I'll dump the table to a spreadsheet, including the key, and update the spreadsheet, Then I'll update the database with those values.

    I just depends on the logic applied to make the updates. Clear as mud?
    Please remember next time...elections matter!

  28. #28

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    Look Dear Tyson. I have a standalone PC application, where DB and application setup both are residing on same PC. After deployment of my app, the client demanded for some changes, for which I need to alter 3 or 4 tables in my DB. ProductBasicInfo is the first table to be altered, which has the following fields before altering.

    Code:
    ProdID
    ProdName
    Description
    Manufacturer
    Store
    AFter this i altered the table and added one more column:
    Code:
    ProdPrice.
    , after which the table has following data.
    Name:  Table.jpg
Views: 222
Size:  24.7 KB

    Now the ProdPrice entire column is empty and i have to update this column with suitable Price against each Product Record.

    Now, I have a Product Form with textboxes and a DataGridView. I have a showAll button, by pressing which all the record of the ProductBasicInfo is loaded into DGV. I want to update a single record so i double click on any record in DGV and as a result the record is loaded into Textboxes where the user can update the record. Here is my form img.

    Name:  Form2.jpg
Views: 224
Size:  32.2 KB
    In the image i have double clicked on the record and it is loaded into textboxes. The user change the record and press the update button and records gets updated. Here is my update code:
    Code:
    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
            Try
    
    updateTable("ProductBasicInfo", "ProdName = '" & txtProdName.Text.Trim & "', Description = '" & txtProdDesc.Text.Trim & "', Manufacturer = '" & txtProdManuf.Text.Trim & "', Store = " & txtStore.Text.Trim & ", ProdPrice = " & txtProdPrice.Text.Trim & "", " ProdID = " & txtProdID.Text.Trim & "")
    
               MessageBox.Show("Record updated successfully")
    
                btnUpdate.Enabled = False
                btnProdSubmit.Enabled = Enabled
    
                txtProdName.Clear()
                txtProdDesc.Clear()
                txtProdManuf.Clear()
                txtStore.Clear()
                txtProdPrice.Clear()
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                cnSql.Close()
            End Try
        End Sub
    UpdateTable is basically a function at module level, given below:

    Code:
    #Region "Updating"
    
        Public Sub updateTable(ByVal tblName As String, ByVal parameter As String, ByVal criteria As String)
    
            Try
                cmdSql1.CommandText = " update " & tblName & " set " & parameter & " where " & criteria & ""
                cmdSql1.Connection = Connect()
                daSql.UpdateCommand = cmdSql1
                daSql.Fill(dsSql, tblName)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
    #End Region
    The problem is that in this way of updating, i can update only a single record at a time. First i will press the showAll button, data will be loaded into DGV, then double click on record to load it into fields and then change the fields and then press the update button. It is a cumbersome and lengthy way for updating hundreds of records, which are actually residing into Client computer. What i want is that isn't there any way by which i can update the entire records in one attempt. Hope this will be little helping in understanding my scenario and problem both. If not so i can give more info.

  29. #29
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to alter my DB Tables and add the old data into new structure tables

    Wow - that was tons of information about how your app edits a single record - very nice info.

    That info has nothing to do with your goal of updating all the prices in that new column in the table.

    "update the entire records in one attempt" is vague. Do you realize that?

    Are you saying you want to be able to edit the prices in the grid?

    Are you saying you want help with a SQL statement that might load those prices in a single shot?

    I have no idea what you are trying to accomplish.

    Is it a language issue - English not your first language?

    It's been 29 post and 5 days on something that should have been solved in 5 minutes - I'm frustrated - you must be also.

    Thing with me - I can leave the thread. You still need a solution.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  30. #30

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    Quote Originally Posted by szlamany View Post

    That info has nothing to do with your goal of updating all the prices in that new column in the table.
    Hm.. what else then you want me to provide you, sir?

    "update the entire records in one attempt" is vague. Do you realize that?
    Are you saying you want to be able to edit the prices in the grid?
    I've never faced this thing before, so i don't know how to do that. But if i am wrong, i might be, then please tell me that what is the right way to do such things. You've also faced this thing before too, and thousands of developers who altering their DB tables, how do they do this. If updating each record is the ultimate solution so i'm happy to do and same is what the client will do too.

    I have no idea what you are trying to accomplish.
    I've clearly mentioned that i want to update the ProdPrice column, with a single attempt or by one by one (but if it is to be updated one by one so this will take too much time, so therefore, i asked for any easy way, if no way, so i'll be sticky with same individual update way).

    Is it a language issue - English not your first language?
    This can be a reason too that i cannot provide that information which you all needing.
    It's been 29 post and 5 days on something that should have been solved in 5 minutes - I'm frustrated - you must be also.
    you are right sir, but learning never frustates me if someone is guiding me.

    Thing with me - I can leave the thread. You still need a solution.
    Please stay with me until we get it's solution.

  31. #31
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to alter my DB Tables and add the old data into new structure tables

    Quote Originally Posted by szlamany View Post
    Are you saying you want to be able to edit the prices in the grid?

    Are you saying you want help with a SQL statement that might load those prices in a single shot?
    These two questions from my post need to be answered.

    Is #1 a YES or NO?

    Is #2 a YES or NO?

    If #2 is YES then please tell me what TABLE you have that the price is in right now that needs to be used in an UPDATE statement.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  32. #32

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    Dear lamany,
    1). If this is the solution so yes for # 1 option.

    2).
    Are you saying you want help with a SQL statement that might load those prices in a single shot?
    If i am not inferring your statement wrong so it means that a sql command which inserts the data into the ProdPrice entire field in a single shot then YES is answers. The original prices of the product is in ProductBasicInfo table (which i have altered). ProdPrice is the actual field which has the original prices of all products.

    (When i will have all prices in this table then i will use these prices in other forms like sale form and purchase form etc)

  33. #33
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to alter my DB Tables and add the old data into new structure tables

    I can give you an update statement to fill the PRODPRICE field in the PRODUCTBASICINFO table - I just need to know what table to GET that value from.

    Actually - if I look at this thread you have mentioned many tables and many issues. You should really start a new thread with the "single issue" of getting the PRODPRICE field updated.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  34. #34

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    Ok. For solving it i got an idea, i created a new table in which i put the prices (actual prices taken from the client). Here is the table.

    Code:
    TempID (PK)
    ProdID  (FK)
    PriceList
    The price list column has the full prices list.

  35. #35

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: How to alter my DB Tables and add the old data into new structure tables

    HI. My problem has been solved with this query:

    The following inner join query, takes the original values from TempPrice Table and update the ProductBasicInfo table ProdPrice field with those prices of TempPrice Table.

    Code:
    update ProductBasicInfo set ProdPrice = TempPrice.TempProdPrice
    from ProductBasicInfo
    inner join TempPrice on ProductBasicInfo.ProdID = TempPrice.ProdID
    I'm greatly thankful for all those respected members, who gave me their precious time and tried to put me out of this problem. Bundles of thanks for each and everyone, who participated here and made me to learn so many new things in this thread. Thanks to al...

Posting Permissions

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



Click Here to Expand Forum to Full Width