SaleID  (Primary Key)
SaleTotalAmount
SalePaidAmount
SaleDiscount
SaleNetBalance
SaleDate

SaleTotalProfit
SaleTotalLoss

quote:

---

Originally posted by Abid

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 (the client has started working upon the application)into this newly structure tables because this newly structured tables will have more or less columns than the previous one. Please guide me that how to solve it.

My table for the time being is:

SaleID  (Primary Key)
SaleTotalAmount
SalePaidAmount
SaleDiscount
SaleNetBalance
SaleDate

And fields have to incorporate are:

SaleTotalProfit
SaleTotalLoss

I ran the alter query and it worked fine for me. But the problem is that If i give this new DB to client, so how i will incorporate the old data into this newly altered DB?

Let me briefly explain my application. I developed a standalone PC application where both DB and application are installed on a single PC.

---

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 & "")
            
            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

#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

ProdID (Primary Key)
ProdName
Description
Manufacturer
Store

ProductPrice.

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")
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cnSql.Close()
End Try
End Sub

quote:

---

Originally posted by MIK_2008

if you want how to do it via VB code I dont know .. neither this forum is for VB coding.

But if you want to update via SQL, I think I've already given you the answer for this. For hundreds of records you need to have hundreds of values for each record of the ProductInfo table. Once you have this information. Create a temporary table (ProdID, Price) and load it with required information and then use following query to get the ProductInfo table updated in one go

UPDATE A
SET Price=tempTable.Price
FROM ProductInfo A
INNER JOIN tempTable on tempTable.ProdID=A.ProdID

Cheers
MIK

---