Solved

Ado.net Dataset table relationships

Posted on 2009-07-02
35
596 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 19
  • 16
35 Comments
 
LVL 14

Expert Comment

by:shahprabal
ID: 24765136

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
ID: 24765368
"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
ID: 24765452
shahprabal,
I have no idea what to do with that.  I am a beginner with ado.net and vb.net
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 14

Expert Comment

by:shahprabal
ID: 24765459
" 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
ID: 24765477
I saw your second post after posting... will get you the code you need soon...
0
 

Author Comment

by:Delta7428
ID: 24765505
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
ID: 24765514
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
ID: 24765638
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
ID: 24765677
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
ID: 24766034
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
ID: 24766038

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
ID: 24766131
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
ID: 24766148
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
ID: 24766205
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
ID: 24766394
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
ID: 24766438
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
ID: 24766484
Thank you.  I'm getting ready to leave for the rest of the afternoon.  I will test this tonight or in the morning.
0
 

Author Comment

by:Delta7428
ID: 24780414
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
ID: 24781822
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
ID: 24788513
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
ID: 24788577
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
ID: 24788585
I changed that too ... no help, so I changed it back.  I will try again to make sure.
0
 

Author Comment

by:Delta7428
ID: 24788631
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
ID: 24788917
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
ID: 24788924
set a breakpoint at da.update and check the values in dt.
0
 

Author Comment

by:Delta7428
ID: 24789179
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
ID: 24789191
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
ID: 24789394
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
ID: 24789641
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
ID: 24789686
strike the second question.  It is correct as is... cuz it works with the commandbuilder code I tested. :)
0
 

Author Comment

by:Delta7428
ID: 24797592
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
ID: 24798318
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
ID: 24803580
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
ID: 24804103
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
ID: 24804858
Thank you!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

724 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