troubleshooting Question

ObjectDataSource's and Changing Table Definitions

Avatar of nate_0000
nate_0000 asked on
ASP.NETVisual Basic.NETMicrosoft SQL Server 2005
6 Comments1 Solution278 ViewsLast Modified:
I'm using an ObjectDataSource to interface an edit-able DetailsView control with an MS SQL Table.  I understand the fundamentals of how this works, and have a lot of experience with using at least the select part of this method.  I'm confident that I could have update, insert & delete functions working fine too, except for one thing - The fields in this table are constantly changing.  For example, a week from now they may add a column, then a month later take one away.  Obviously changing the code to work with this every single time the table is changed would be quite frustrating and undesirable.  How would I go about making the "writing" functions change with the table definition?

Here's the function I've been experimenting with:

Public Sub nonQuery(ByVal lsQueryType As String, ByVal loDT As DataTable)
            Try
                Dim lsSql As String = ""
                Select Case lsQueryType
                    Case "insert"
                        Dim loTmpRow As DataRow
                        loTmpRow = loDT.Rows(0)
                        lsSql &= "INSERT INTO " & loDT.TableName & " ("
                        Dim loTmpCol As DataColumn
                        For Each loTmpCol In loDT.Columns
                            lsSql &= loTmpCol.ColumnName & ", "
                        Next

                        lsSql = Left(lsSql, lsSql.Length - 2)

                        lsSql &= ") VALUES ("

                        Dim i As Integer

                        For i = 1 To loTmpRow.ItemArray.Length
                            lsSql &= loTmpRow.Item(i - 1) & ", "
                        Next

                        lsSql = Left(lsSql, lsSql.Length - 2)

                        lsSql &= ");"
                    Case "update"
                        Dim loTmpRow As DataRow
                        loTmpRow = loDT.Rows(0)

                        lsSql &= "UPDATE " & loDT.TableName & " SET "

                        Dim loTmpCol As DataColumn
                        Dim j As Integer = 0
                        For Each loTmpCol In loDT.Columns
                            lsSql &= loTmpCol.ColumnName & " = '" & _
                            loTmpRow.Item(j) & "', "
                            j += 1
                        Next

                        lsSql = Left(lsSql, lsSql.Length - 2)

                        lsSql &= " WHERE vcLocationNum = '" & _
                        loTmpRow.Item("vcLocationNum") & "';"
                    Case "delete"
                        lsSql &= "DELETE FROM " & loDT.TableName & _
                        "WHERE vcLocationNum = '" & _
                        loDT.Rows(0).Item("vcLocationNum") & "'"
                End Select

                Throw New Exception(lsSql)

                loDA.ExecuteNonQuery( _
                ConfigurationManager.ConnectionStrings("EmploymentConnStr").ConnectionString, _
                CommandType.Text, _
                lsSql)
            Catch ex As Exception
                ExceptionMgr.Publish(ex, Err.Number, Err.Description, "LocationsManagerDataObj", "query")
            End Try
        End Sub
ASKER CERTIFIED SOLUTION
nate_0000

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros