Ado.net Dataset table relationships

I need to create a dataset/datatable filtering the records based on those records being in another table.  In Vb6 I was able to do an inner join, iterate the recordset, and update the records that I needed.

I created a datable in ado.net with the same join, and of course, I can't do the update.  I get "Dynamic SQL generation is not supported against multiple base table".  If I change my select statement to include only 1 table, it runs.

So, I am trying to creating a dataset with both tables.   I think that is what I need to do ???

The tables have a 1 to 1 relationship based on the cust_po_no field.  Basically, I need a dataset  (datatable) of all the records in ptt table (see code) that have a record in pick table.

I am just learning vb.net and ado.net, so I need your answer in clear and simple terms that I can understand.  Thank you.
Sub updateGroup(ByVal stype, ByVal iPrintCounter)
'-------------------------------------------------
        Dim Conn As New SqlConnection(sSQLConn)
        Conn.Open()
        Dim dt As DataTable
        Dim sqry As String = ""
        Dim i As Integer = 0
        Dim sFormat As String = ""
	
         sqry = "SELECT ptt.GroupID, ptt.Ctr, ptt.RecordID " & _
        "FROM ptt " & _
        "INNER JOIN pick ON ptt.CUST_PO_NO = pick.CUST_PO_NO " & _
        "WHERE ptt.SubmittedMis Is Null"
 
        oDb = New dbFac()
        dt = oDb.GetTableForUpdate(sqry, Conn)
 
        Try
            With dt
                For i = 0 To dt.Rows.Count - 1
                    sFormat = Format(iGroupCounter.ToString, "000000")
                    .Rows(i)!Ctr = Trim(Str(iGroupCounter))
                    .Rows(i)!GroupID = stype & sFormat
                    If iPrintCounter < imax Then
                        iPrintCounter += 1
                    Else
                        iGroupCounter += 1
                        iPrintCounter = 1
                    End If
                Next
                da.Update(dt)
            End With
            UpdateGroupCounter(stype)
 
        Catch exp As System.Exception
            MessageBox.Show(exp.Message, "updateGroup", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            Conn.Close()
            Conn.Dispose()
        End Try
    End Sub
 
'-------------------------------------------------
Public Class dbFac
'-------------------------------------------------
  Public Function GetTableForUpdate(ByVal sqry As String, ByVal Conn As SqlConnection) As DataTable
        
        Dim cmd As New SqlCommand(sqry, Conn)
        da = New SqlDataAdapter(cmd)
        Try
            Dim db As New SqlCommandBuilder(da)
            dt = New DataTable
            da.Fill(dt)
        
        Catch exp As System.Exception
            MessageBox.Show(exp.Message, "GetTableForUpdate", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            cmd = Nothing
        
        End Try
        GetTableForUpdate = dt
    End Function
End Class

Open in new window

Delta7428Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shahprabalCommented:

The commandbuilder can't create the select, update, insert, delete statements since you have joins. So you have to set the dataadapter's SelectCommand , UpdateCommand, etc manually.

check out :
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.selectcommand.aspx
for example
0
Delta7428Author Commented:
"The commandbuilder can't create the select, update, insert, delete statements since you have joins>"

Yes, I had already concluded that.

Thanks for the link.  I'm not sure I can figure out what I need to do to make it work.  I will look at it.
0
Delta7428Author Commented:
shahprabal,
I have no idea what to do with that.  I am a beginner with ado.net and vb.net
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

shahprabalCommented:
" I'm not sure I can figure out what I need to do to make it work.  I will look at it."
Answer:
So you have to set the dataadapter's SelectCommand , UpdateCommand, etc manually.
and had given you link to the sample code:
 http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.selectcommand.aspx
I understand that you are learning vb.net... You will learn faster if you put some effort into figuring things out instead of using the code that I post here. If you just want the code then I can post that as well.
0
shahprabalCommented:
I saw your second post after posting... will get you the code you need soon...
0
Delta7428Author Commented:
Do I call the CreateCustoemerAdapter from my function that creates the Datatable?  Where do I send the parameters to and how does it know if I'm doing an insert, update, etc.

0
Delta7428Author Commented:
I don't necessarily need you to code it for me, but sample would help.  I've researched this all over the internet.  I just need better direction.
0
shahprabalCommented:
CreateCustomerAdapter is a function for demonstration purposes... you can use it in your code... but you will need to change the sql for the commands. Also to update the datatable you
" Where do I send the parameters to "
 When you set the value it knows which column it belongs to :
.Rows(i)!Ctr  
"and how does it know if I'm doing an insert, update, etc."
da.Fill(dt) uses the select statement to populate the dt
da.Update uses the update statement to update the sql table.
This is a much simpler and complete example :
 http://msdn.microsoft.com/en-us/library/33y2221y.aspx
 All you are missing is the update command for the dataadapter. Once you add that your code should work.
0
shahprabalCommented:
Change :

When you set the value it knows which column it belongs to :
.Rows(i)!Ctr  
to

When you set the value it knows which column it belongs to :
.Rows(i)!Ctr = Trim(Str(iGroupCounter))

What I was saying is that when you assign a value to a datacell (as above) and execute the dataadapter's update command, the update command containing a parameter for @Ctr column will run that sql statement and pass the value that you set to the parameter.
0
Delta7428Author Commented:
Do I keep my function that returns the datatable based on the joined tables?  ... Add the UpdateCommand and parameters there?
0
Delta7428Author Commented:

Public Function GetTableForUpdate(ByVal sqry As String, ByVal Conn As SqlConnection) As DataTable
        
        Dim cmd As New SqlCommand(sqry, Conn)
        da = New SqlDataAdapter(cmd)
        Try
            Dim db As New SqlCommandBuilder(da)
            dt = New DataTable
            da.Fill(dt)
        
        Catch exp As System.Exception
            MessageBox.Show(exp.Message, "GetTableForUpdate", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            cmd = Nothing
        
        End Try
        GetTableForUpdate = dt
    End Function

Open in new window

0
shahprabalCommented:
In your code you are declaring da in the GetTableForUpdate but using it in updateGroup Sub. Do you have another da declared somewhere? I would just declare da at the updateGroup level, pass it by reference to the GetTableForUpdate where you create the select and update commands and set them to da.selectcommand and da.updatecommand. Once you get the da and dt from that function, you don't need to change rest of your code in updateGroup.
0
shahprabalCommented:
Scratch that... you have dbFac which would need changing as well. So where is the da that you are using in the updateGroup Sub declared?
 
0
Delta7428Author Commented:
The are public in a project module.

Public da As SqlDataAdapter
Public dt As DataTable

I have been using GetTableForUpdate function generically throughout the app to return a datatable when I need it.  So I will need to leave it in tact and do this differently.
0
shahprabalCommented:
in that case I would change :
         sqry = "SELECT ptt.GroupID, ptt.Ctr, ptt.RecordID " & _
        "FROM ptt " & _
        "INNER JOIN pick ON ptt.CUST_PO_NO = pick.CUST_PO_NO " & _
        "WHERE ptt.SubmittedMis Is Null"

        oDb = New dbFac()
        dt = oDb.GetTableForUpdate(sqry, Conn)
 
to:
         sqry = "SELECT ptt.GroupID, ptt.Ctr, ptt.RecordID " & _
        "FROM ptt " & _
        "INNER JOIN pick ON ptt.CUST_PO_NO = pick.CUST_PO_NO " & _
        "WHERE ptt.SubmittedMis Is Null"

        oDb = New dbFac()
        dt = oDb.GetTableForUpdate(sqry, Conn)
         Dim sel_cmd As New SqlCommand(sqry, Conn)
        da.selectcommand = sel_cmd
         sqry = <update statement with parameters>
        Dim upt_cmd as New SqlCommand(sqry,Conn)
        da.UpdateCommand = upt_cmd

0
shahprabalCommented:
your update sql should be something like :
 

UPDATE ptt
SET GroupID= @grp_id, Ctr= @ctr
FROM ptt
INNER JOIN pick
ON ptt.CUST_PO_NO = pick.CUST_PO_NO 

Open in new window

0
Delta7428Author Commented:
Thank you.  I'm getting ready to leave for the rest of the afternoon.  I will test this tonight or in the morning.
0
Delta7428Author Commented:
ok, I have modified the GetTableForUpdate function.  I am getting "must declare the scalar variable @grp_id" when da.Update is called.
Module Module1
   Public oDb As dbFac
    Public da As SqlDataAdapter
    Public dt As DataTable
End Module
 
Sub updateGroup(ByVal stype, ByVal iPrintCounter)
        Dim Conn As New SqlConnection(sSQLConn)
        Conn.Open()
        Dim dt As DataTable
        Dim sqry As String = ""
        Dim i As Integer = 0
        Dim sFormat As String = ""
 
       sqry = "SELECT ptt.GroupID, ptt.Ctr, ptt.RecordID " & _
        "FROM ptt " & _
        "INNER JOIN pick ON ptt.CUST_PO_NO = pick.CUST_PO_NO " & _
        "WHERE ptt.SubmittedMis Is Null"
 
        oDb = New dbFac()
        dt = oDb.GetTableForUpdate(sqry, Conn)
        Try
            With dt
                For i = 0 To dt.Rows.Count - 1
                    .Rows(i)!Ctr = Trim(Str(iGroupCounter))
                    sFormat = Trim(Str(iGroupCounter))
                    sFormat = Format(iGroupCounter, "000000")
 
                    .Rows(i)!GroupID = stype & sFormat
                    If iPrintCounter < imax Then
                        iPrintCounter = iPrintCounter + 1
                    Else
                        iGroupCounter = iGroupCounter + 1
                        iPrintCounter = 1
                    End If
                    'da.Update(dt)
                Next
                da.Update(dt)
            End With
            UpdateGroupCounter(stype)
 
        Catch exp As System.Exception
            MessageBox.Show(exp.Message, "updateGroup", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            Conn.Close()
            Conn.Dispose()
        End Try
    End Sub
 
Public Function GetTableForUpdate(ByVal sqry As String, ByVal Conn As SqlConnection) As DataTable
        Dim sel_cmd As New SqlCommand(sqry, Conn)
        da = New SqlDataAdapter(sel_cmd)
 
        Try
            da.SelectCommand = sel_cmd
            sqry = "UPDATE ptt " & _
            "SET GroupID= @grp_id, Ctr= @ctr " & _
            "FROM ptt INNER JOIN pick ON ptt.CUST_PO_NO = pick.CUST_PO_NO"
 
            Dim upt_cmd As New SqlCommand(sqry, Conn)
 
            upt_cmd.Parameters.Add("@grp_id", SqlDbType.NVarChar, 15, "GroupID")
            upt_cmd.Parameters.Add("@ctr", SqlDbType.NVarChar, 15, "Ctr")
            da.UpdateCommand = upt_cmd
 
            dt = New DataTable
            da.Fill(dt)
 
        Catch exp As System.Exception
            MessageBox.Show(exp.Message, "GetTableForUpdate", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            sel_cmd = Nothing
 
        End Try
        GetTableForUpdate = dt
    End Function

Open in new window

0
shahprabalCommented:
change :
 upt_cmd.Parameters.Add("@grp_id", SqlDbType.NVarChar, 15, "GroupID")
to :
 upt_cmd.Parameters.Add("@GroupID", SqlDbType.NVarChar, 15, "GroupID")
0
Delta7428Author Commented:
I'm still getting the error when updateGroup tries to update the data adapter.

da.Update  >> Must declare the scalar variable "@grp_id"

The code snippet below shows my select statement as is in my code.  I had shortened it and the table name to make it more concise on my post.  But I don't see an issue there.
Module Module1
   Public oDb As dbFac
    Public da As SqlDataAdapter
    Public dt As DataTable
End Module
 
 Sub updateGroup(ByVal stype, ByVal iPrintCounter)
        Dim Conn As New SqlConnection(sSQLConn)
        Conn.Open()
        Dim dt As DataTable
        Dim sqry As String = ""
        Dim i As Integer = 0
        Dim sFormat As String = ""
 
        sqry = "SELECT pttGrpPrinted.GroupType, pttGrpPrinted.GroupID, pttGrpPrinted.Ctr, " & _
    "pttGrpPrinted.DatePrinted, pttGrpPrinted.SubmittedMis, pttGrpPrinted.RecordID  " & _
    "FROM pttGrpPrinted INNER JOIN pick ON pttGrpPrinted.CUST_PO_NO = pick.CUST_PO_NO " & _
    "WHERE (((pttGrpPrinted.GroupType)= '" & stype & "') AND ((pttGrpPrinted.Ctr) Is Null Or (pttGrpPrinted.Ctr)='') " & _
    "AND ((pttGrpPrinted.SubmittedMis)=0 Or (pttGrpPrinted.SubmittedMis) Is Null)) " & _
    "ORDER BY pick.Image desc, pttGrpPrinted.CUST_PO_NO"
 
        oDb = New dbFac()
        dt = oDb.GetTableForUpdate(sqry, Conn)
        Try
            With dt
                For i = 0 To dt.Rows.Count - 1
                    .Rows(i)!Ctr = Trim(Str(iGroupCounter))
                    sFormat = Trim(Str(iGroupCounter))
                    sFormat = Format(iGroupCounter, "000000")
 
                    .Rows(i)!GroupID = stype & sFormat
                    If iPrintCounter < imax Then
                        iPrintCounter = iPrintCounter + 1
                    Else
                        iGroupCounter = iGroupCounter + 1
                        iPrintCounter = 1
                    End If
 
                Next
                da.Update(dt)
 
            End With
            UpdateGroupCounter(stype)
 
        Catch exp As System.Exception
            MessageBox.Show(exp.Message, "updateGroup", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            Conn.Close()
            Conn.Dispose()
        End Try
    End Sub
 
Public Function GetTableForUpdate(ByVal sqry As String, ByVal Conn As SqlConnection) As DataTable
        Dim sel_cmd As New SqlCommand(sqry, Conn)
        da = New SqlDataAdapter(sel_cmd)
 
        Try
            da.SelectCommand = sel_cmd
            sqry = "UPDATE pttGrpPrinted " & _
            "SET GroupID= @grp_id, Ctr= @ctr " & _
            "FROM pttGrpPrinted INNER JOIN pick ON pttGrpPrinted.CUST_PO_NO = pick.CUST_PO_NO"
 
            Dim upt_cmd As New SqlCommand(sqry, Conn)
 
            upt_cmd.Parameters.Add("@GroupID", SqlDbType.VarChar, 50, "GroupID")
            upt_cmd.Parameters.Add("@Ctr", SqlDbType.VarChar, 50, "Ctr")
            da.UpdateCommand = upt_cmd
 
            dt = New DataTable
            da.Fill(dt)
 
 
        Catch exp As System.Exception
            MessageBox.Show(exp.Message, "GetTableForUpdate", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            sel_cmd = Nothing
 
        End Try
        GetTableForUpdate = dt
    End Function

Open in new window

0
shahprabalCommented:
Forgot to tell you to change this :
           sqry = "UPDATE pttGrpPrinted " & _
            "SET GroupID= @grp_id, Ctr= @ctr " & _
            "FROM pttGrpPrinted INNER JOIN pick ON pttGrpPrinted.CUST_PO_NO = pick.CUST_PO_NO"

to :
           sqry = "UPDATE pttGrpPrinted " & _
            "SET GroupID= @GroupID, Ctr= @ctr " & _
            "FROM pttGrpPrinted INNER JOIN pick ON pttGrpPrinted.CUST_PO_NO = pick.CUST_PO_NO"
 
0
Delta7428Author Commented:
I changed that too ... no help, so I changed it back.  I will try again to make sure.
0
Delta7428Author Commented:
Error now is:

The parameterized query '(@GroupID varchar(50),@Ctr varchar(50))UPDATE pttGrpPrinted SET' expects the parameter '@GroupID', which was not supplied.
 Public Function GetTableForUpdate(ByVal sqry As String, ByVal Conn As SqlConnection) As DataTable
        Dim sel_cmd As New SqlCommand(sqry, Conn)
        da = New SqlDataAdapter(sel_cmd)
 
        Try
            da.SelectCommand = sel_cmd
            sqry = "UPDATE pttGrpPrinted " & _
            "SET GroupID= @GroupID, Ctr= @Ctr " & _
            "FROM pttGrpPrinted INNER JOIN pick ON pttGrpPrinted.CUST_PO_NO = pick.CUST_PO_NO"
 
            Dim upt_cmd As New SqlCommand(sqry, Conn)
 
            upt_cmd.Parameters.Add("@GroupID", SqlDbType.VarChar, 50, "GroupID")
            upt_cmd.Parameters.Add("@Ctr", SqlDbType.VarChar, 50, "Ctr")
            da.UpdateCommand = upt_cmd
 
            dt = New DataTable
            da.Fill(dt)
 
 
        Catch exp As System.Exception
            MessageBox.Show(exp.Message, "GetTableForUpdate", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            sel_cmd = Nothing
 
        End Try
        GetTableForUpdate = dt
    End Function

Open in new window

0
shahprabalCommented:
are you sure that the code that sets the value for the parameters is executed?
                For i = 0 To dt.Rows.Count - 1 <<<<< if you dont have any rows, the da.update will throw an error
                    .Rows(i)!Ctr = Trim(Str(iGroupCounter))
                    sFormat = Trim(Str(iGroupCounter))
                    sFormat = Format(iGroupCounter, "000000")
 
                    .Rows(i)!GroupID = stype & sFormat
                    If iPrintCounter < imax Then
                        iPrintCounter = iPrintCounter + 1
                    Else
                        iGroupCounter = iGroupCounter + 1
                        iPrintCounter = 1
                    End If
 
                Next
                da.Update(dt)

Open in new window

0
shahprabalCommented:
set a breakpoint at da.update and check the values in dt.
0
Delta7428Author Commented:
ok ... so now I'm really confused.  The dt is being updated.

All records in the are being updated with the same GroupID value and same Ctr value.

I know my logic is working because I have an alternate select statement that does not include a join and it runs perfectly.  The only other difference is GetTableForUpdate uses a CommandBuilder to create the datatable.   This code works with the same logic:
Sub updateGroup1(ByVal stype, ByVal iPrintCounter)
      
 
        Dim Conn As New SqlConnection(sSQLConn)
        Conn.Open()
        Dim dt As DataTable
        Dim sqry As String = ""
        Dim i As Integer = 0
        Dim sFormat As String = ""
       
 
        sqry = "SELECT GroupID, Ctr, RecordID " & _
        "FROM pttGrpPrinted " & _
        "WHERE GroupType= '" & stype & "' AND (Ctr Is Null Or Ctr='') AND " & _
        "(SubmittedMis=0 Or SubmittedMis Is Null) " & _
        "ORDER BY CUST_PO_NO"
 
        oDb = New dbFac()
        dt = oDb.GetTableForUpdate1(sqry, Conn)
 
        Try
            With dt
                For i = 0 To dt.Rows.Count - 1
                    .Rows(i)!Ctr = Trim(Str(iGroupCounter))
                    sFormat = Trim(Str(iGroupCounter))
                    sFormat = Format(iGroupCounter, "000000")
 
                    .Rows(i)!GroupID = stype & sFormat
                    If iPrintCounter < imax Then
                        iPrintCounter = iPrintCounter + 1
                    Else
                        iGroupCounter = iGroupCounter + 1
                        iPrintCounter = 1
                    End If
 
                Next
                da.Update(dt)
 
            End With
            UpdateGroupCounter(stype)
 
        Catch exp As System.Exception
            MessageBox.Show(exp.Message, "-updateGroup1", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            Conn.Close()
            Conn.Dispose()
        End Try
    End Sub
 
Public Function GetTableForUpdate1(ByVal sqry As String, ByVal Conn As SqlConnection) As DataTable
        MsgBox(1)
        Dim cmd As New SqlCommand(sqry, Conn)
        da = New SqlDataAdapter(cmd)
        Try
            Dim db As New SqlCommandBuilder(da)
            dt = New DataTable
            da.Fill(dt)
 
        Catch exp As System.Exception
            MessageBox.Show(exp.Message, "GetTableForUpdate1", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            cmd = Nothing
 
        End Try
        GetTableForUpdate1 = dt
    End Function

Open in new window

0
Delta7428Author Commented:
I also re-tested my select statement with the joined tables.  It is returning all required records.
0
shahprabalCommented:
Cool... so works with commandbuilder....? I just realized that you were creating the dataadapter with the sel_cmd and then adding the update command.:
 Public Function GetTableForUpdate(ByVal sqry As String, ByVal Conn As SqlConnection) As DataTable
        Dim sel_cmd As New SqlCommand(sqry, Conn)
        da = New SqlDataAdapter(sel_cmd)
 
If you change it to :
Dim da as New SqlDataAdapter
da.SelectCommand = sel_cmd
<create update command>
da.UpdateCommand = upt_cmd
it should work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Delta7428Author Commented:
Thanks.  I will try that tonight from home.

But was wondering if there's any chance if the code could be hitting the da.update command before it iterated through the dataset?

Also, do am I updating the data adapater in the right place?  After it updates all the rows?  Instead of after each row?
0
Delta7428Author Commented:
strike the second question.  It is correct as is... cuz it works with the commandbuilder code I tested. :)
0
Delta7428Author Commented:
Now I'm getting:

"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

I tried a number of variations on the update command, but but luck.  Don't I need to take out the join on the update and add a where clause with unique row identifier?

I tried changing the update command to this, but it generates the same error as above:
UPDATE pttGrpPrinted SET GroupID= @GroupID, Ctr= @Ctr WHERE (RecordID = @OriginalRecordID)
-------------------------
upt_cmd.Parameters.Add("@OriginalRecordID", SqlDbType.Int, 18, "RecordID")
    Public Function GetTableForUpdate(ByVal sqry As String, ByVal Conn As SqlConnection) As DataTable
        Dim sel_cmd As New SqlCommand(sqry, Conn)
        Dim da As New SqlDataAdapter
 
        Try
            da.SelectCommand = sel_cmd
 
            sqry = "UPDATE pttGrpPrinted " & _
            "SET GroupID= @GroupID, Ctr= @Ctr " & _
            "FROM pttGrpPrinted INNER JOIN pick ON pttGrpPrinted.CUST_PO_NO = pick.CUST_PO_NO"
 
            'sqry = "UPDATE pttGrpPrinted " & _
            '"SET GroupID= @GroupID, Ctr= @Ctr WHERE (RecordID = @OriginalRecordID)" '& _
 
            Dim upt_cmd As New SqlCommand(sqry, Conn)
            upt_cmd.Parameters.Add("@GroupID", SqlDbType.VarChar, 50, "GroupID")
            upt_cmd.Parameters.Add("@Ctr", SqlDbType.VarChar, 50, "Ctr")
            'upt_cmd.Parameters.Add("@OriginalRecordID", SqlDbType.Int, 18, "RecordID")
            da.UpdateCommand = upt_cmd
 
            dt = New DataTable
            da.Fill(dt)
 
 
        Catch exp As System.Exception
            MessageBox.Show(exp.Message, "GetTableForUpdate", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            sel_cmd = Nothing
 
        End Try
        GetTableForUpdate = dt
    End Function

Open in new window

0
shahprabalCommented:
If it is working with the command builder, put a breakpoint right after the commandbuilder configures the data adapter, and see the check the update command for the data adapter. Specifically the command text and the parameter info.
0
Delta7428Author Commented:
shahprabal, This is working now.  Thanks for your patience.  :-)
The error on the update command was caused by me calling the GetTableForUpdate function by another method when I meant to be calling a different function that uses a CommandBuilder.

I have a last question.  I could of done this another away, quickly and easily, with the knowledge I already had.  

What would be wrong was using a data reader in UpdateGroup instead of a Datatable, then sending parameters to a method that uses an ExecuteNonQuery Command to update the table?
I didnt do it that way because I wanted to break in my learning curve here.  
Any reason not to do this as an alternative & with data reader and ExecuteNonQuery?  What is the benefit of alternately using a data table and data adapter here?
0
shahprabalCommented:
You are right... you can read values one row at a time and then update them one at a time. However the main advantage of doing it this way would be performance. Instead of looping through a lot of lines for every update, this way is more efficient. Also I would think that the database impact is less when you pass it a datable to update but would need to run profiler to verify that (eventhough it is using the update statement in the update command). Second advantage is that you are writing less code to do the same work. Not by much but still.
Since you are interested in best practices... I would look at examples of data layer and tweak the dbFac class. In particular you are declaring dataadapters in the different functions, instead of letting you functions in dbFac accept a dataadpater parameter by reference. You add the select command and pass the same one back to the calling class, same with the update command. Instead of declaring multiple instances and using up more resources and making it harder to debug.
Also, try to have your sql in the datalayer instead of having it everywhere... for eg:
You have a class called box, it has properties : box_type, qty. To fill the box you have a add_item method. Instead of doing the database calls from the box class you can create a boxDB class that box class references. All the boxDB methods are called from box class and only box class methods are called from the GUI or other classes. That way if you need to change the sql in a function you dont need to change all the methods that call that function.
HTH
0
Delta7428Author Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.