0

Hi

Below is my code. The code is running well if the column name specified below is not exist.

Sql = "ALTER TABLE Update_PaymentDetails ADD ServiceCharge float NULL," _
& "TotalAmount float NULL,Commission1 float NULL," _
& "Commission2 float NULL,DealerCommission float NULL," _
& "PaymentToDealer float NULL,PaymentPay float NULL," _
& "Payment float NULL"

if one of the column exist in the table if gives an error as below and does not update the rest of the columns.
Column names in each table must be unique. Column name 'ServiceCharge' in table 'Update_PaymentDetails' is specified more than once.


How do i do if out of 5 column 2 column exist and the code should be able to update the rest 3 without an error.

Please Help me PLEASE !!!!!!

Try

            Cn.CursorLocation = CursorLocationEnum.adUseServer
            CnStr = "Provider=sqloledb;server=(Local);database=CreditCard;Trusted_Connection=yes"
            Cn.Open(CnStr, "", "", CnMode)

            'Sql = "SELECT COUNT(*) " _
            '    & "FROM sys.objects " _
            '    & "WHERE object_id = OBJECT_ID(N'Update_PaymentDetails')"

            'Rst = Cn.Execute(Sql)

            'If (Rst.Fields(0).Value = 0) Then
            Sql = "ALTER TABLE Update_PaymentDetails ADD ServiceCharge float NULL," _
                      & "TotalAmount float NULL,Commission1 float NULL," _
                          & "Commission2 float NULL,DealerCommission float NULL," _
                          & "PaymentToDealer float NULL,PaymentPay float NULL," _
                          & "Payment float NULL"
            Cn.Execute(Sql)

            'End If

            Cn.Close()

            rtb1.AppendText(vbCrLf & "ALTER TABLE (ADD) Update_PaymentDetails SUCCESSFULLY UPDATED!" & vbCrLf)
            str1 = str1.PadRight(120, "-")
            rtb1.AppendText(str1 & vbCrLf & vbCrLf)
        Catch ex As Exception
            MessageBox.Show("Step 1" & ex.Message)
        End Try
2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by lolafuertes
0

There are almost two ways to get what you want:
1) Do a SELECT * against the table to update (using a 'WHERE 1=2' select clause in order to fill the table with no records) to obtain all the existing fileds, and use it in a data adapter to fill a dataset table. The fields collection of the datatable will give you the names of existing fileds, so you can dinamicaly create the alter sentence adding only those not existing in the table.
2) Split the sentence to be one for each field. Execute one by one and ignore the errors.

Hope this helps

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.