Solved

Ado.net Dataset table relationships

Posted on 2009-07-02
35
558 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:Delta7428
  • 19
  • 16
35 Comments
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility

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
 

Author Comment

by:Delta7428
Comment Utility
"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
 

Author Comment

by:Delta7428
Comment Utility
shahprabal,
I have no idea what to do with that.  I am a beginner with ado.net and vb.net
0
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility
" 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
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility
I saw your second post after posting... will get you the code you need soon...
0
 

Author Comment

by:Delta7428
Comment Utility
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
 

Author Comment

by:Delta7428
Comment Utility
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
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility
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
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility
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
 

Author Comment

by:Delta7428
Comment Utility
Do I keep my function that returns the datatable based on the joined tables?  ... Add the UpdateCommand and parameters there?
0
 

Author Comment

by:Delta7428
Comment Utility

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
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility
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
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility
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
 

Author Comment

by:Delta7428
Comment Utility
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
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility
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
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility
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
 

Author Comment

by:Delta7428
Comment Utility
Thank you.  I'm getting ready to leave for the rest of the afternoon.  I will test this tonight or in the morning.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Delta7428
Comment Utility
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
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility
change :
 upt_cmd.Parameters.Add("@grp_id", SqlDbType.NVarChar, 15, "GroupID")
to :
 upt_cmd.Parameters.Add("@GroupID", SqlDbType.NVarChar, 15, "GroupID")
0
 

Author Comment

by:Delta7428
Comment Utility
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
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility
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
 

Author Comment

by:Delta7428
Comment Utility
I changed that too ... no help, so I changed it back.  I will try again to make sure.
0
 

Author Comment

by:Delta7428
Comment Utility
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
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility
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
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility
set a breakpoint at da.update and check the values in dt.
0
 

Author Comment

by:Delta7428
Comment Utility
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
 

Author Comment

by:Delta7428
Comment Utility
I also re-tested my select statement with the joined tables.  It is returning all required records.
0
 
LVL 14

Accepted Solution

by:
shahprabal earned 500 total points
Comment Utility
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
 

Author Comment

by:Delta7428
Comment Utility
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
 

Author Comment

by:Delta7428
Comment Utility
strike the second question.  It is correct as is... cuz it works with the commandbuilder code I tested. :)
0
 

Author Comment

by:Delta7428
Comment Utility
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
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility
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
 

Author Comment

by:Delta7428
Comment Utility
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
 
LVL 14

Expert Comment

by:shahprabal
Comment Utility
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
 

Author Comment

by:Delta7428
Comment Utility
Thank you!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now