Solved

Updating A Datagrid

Posted on 2006-06-16
11
263 Views
Last Modified: 2012-05-05
I am trying to update a Dataset from editted data from a datagrid. If there is no change of the data when the update is performed there is no problem, However if there is a change in the data within the datagrid the code fails. Could some one tell what is wrong with this code?

Thanks,

BB

    Sub UpdateDataDS(ByRef sSlctCust As String, ByRef sSlctRprt As String)
        Dim strSQL As String
        Dim daData As SqlClient.SqlDataAdapter

        strSQL = "Select CUSTNO, PROJECT, LAB_NO, CYLINDER, [LOAD], POUNDS, REQUIRED," & _
                "AGE, SLUMP, AIR, AMBIENT, CONCRETE, DATE_TEST from LLCOND" & sLocation & " where CUSTNO = " & _
                "'" & sSlctCust & "'" & " And LAB_NO = " & "'" & sSlctRprt & "'" & " Order by CYLINDER Asc"

        daData = New SqlClient.SqlDataAdapter(strSQL, cn)
        daData.Fill(dsData, "LLCOND" & sLocation)

        daData.UpdateCommand = New SqlClient.SqlCommand("UPDATE LLCOND" & sLocation & " where CUSTNO = " & sSlctCust & " And LAB_NO = " & sSlctRprt, cn)

        Try
            'WHEN AN UPDATE OCCURS THIS LINE CAUSES AN ERROR ONLY IF THERE WAS A CHANGE IN DATA
            daData.Update(dsData, "LLCOND" & sLocation)

        Catch ex As Exception
            MsgBox(ex.ToString, MsgBoxStyle.Information)
        End Try



    End Sub
0
Comment
Question by:bbarnette
  • 6
  • 5
11 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 16926008
Your Update statement is incomplete.  Besides saying which table to update, which is this bit

   "UPDATE LLCOND" & sLocation

and which record/s within that table to update, which is this bit

   " where CUSTNO = " & sSlctCust & " And LAB_NO = " & sSlctRprt

an update statement must say which Values have to go in which Fields.  It needs a "set" clause, which goes between the "update" clause and the "where" clause.  It can be either in the form

   SET FieldName1 = Value1, FieldName2 = Value2

if the actual values are put into the statement, on the lines

   SET FirstName = 'Roger', LogInName = 'Sancler'

or in the form

   SET FieldName1 = @parameter1, FieldName2 = @parameter2

if the update command needs to get the values from somewhere, and then the command also has to have the necessary parameters added to its Parameters collection so that it knows where to look for the necessary values.  Have a look at this for a worked example

   http://msdn2.microsoft.com/en-us/system.data.sqlclient.sqldataadapter.updatecommand.aspx

A second problem is likely to be with datatypes.  The where clause in your Select statement puts single quotes round sSlctCust and sSlctRprt, but the where clause in your Update statement doesn't.

But it looks to me as though your code might have problems even if you get the Update statement right.  This is because the sub that you quote (presumably in full) goes straight from a .Fill of the datatable with records that match the CUSTNO and LAB_NO tests to an .Update of the same records.  But, having just been loaded, those records will be regarded by the dataadapter as unchanged, so it is unlikely that the .Update command will have any effect.

The normal approach would be to declare your dataadapter/s with scope that makes it/them available both to the sub with which you initially load the data for the purpose of filling your datagrid and to the sub by which you update the database after any changes made via the datagrid.  Then you would .Fill in one sub and .Update in the other but with the same dataadapter.  I imagine - given that you appear to be changing the table name - both for the purposes of the database and the dataset - with reference to your sLocation variable, that that "normal" approach might not be totally appropriate for you.  But I don't really think things will work quite as you have them at the moment.

Roger
0
 

Author Comment

by:bbarnette
ID: 17010401
Ok, I think I get it, but how do you read the values from the datagrid to update the data adapter?

I have no problem with this if I am updating from a textbox, this is pretty straight forward.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17010819
You don't read the values from the datagrid.  With a dataadapter you tell that where to get them from in the datatable that is the datasource for the datagrid.  This is from the link I referred you to last time

>>
    ' Add the parameters for the UpdateCommand.
    command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
    command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")
<<

The fourth arguments in those declarations - "CustomerID" and "CompanyName" - specify the columns in the datatable from which the values for the parameters are to come.

Roger
0
 

Author Comment

by:bbarnette
ID: 17017769
Keeping in mind that now the DS and DA are public. I made the following changes to the code, but it still does not do the update. No errors unless I uncomment the last line. What appears to be wrong with this?

Sub UpdateDataDS(ByRef sSlctCust As String, ByRef sSlctRprt As String)
        Dim command As SqlClient.SqlCommand

        command = New SqlClient.SqlCommand("UPDATE LLCOND" & sLocation & _
            " Set CYLINDER = @CYLINDER, POUNDS = @POUNDS where CUSTNO = " & "'" & sSlctCust & "'" & _
            " And LAB_NO = " & "'" & sSlctRprt & "'", cn)


        command.Parameters.Add("@CYLINDER", SqlDbType.NVarChar, 6, "CYLINDER")
        command.Parameters.Add("@POUNDS", SqlDbType.NVarChar, 6, "POUNDS")

        Dim parameter As SqlClient.SqlParameter = command.Parameters.Add( _
        "@CYLINDER", SqlDbType.NVarChar, 6, "CYLINDER")

        parameter = command.Parameters.Add( _
        "@POUNDS", SqlDbType.NVarChar, 6, "POUNDS")

        parameter.SourceVersion = DataRowVersion.Original

        daData.UpdateCommand = command

        '       daData.Update(dsData, "LLCOND" & sLocation)

    End Sub
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17018636
As you don't say what the error was if you left the last line uncommented, I can't be sure, but it looks to me as if it should work if you comment out the three lines marked below

Sub UpdateDataDS(ByRef sSlctCust As String, ByRef sSlctRprt As String)
        Dim command As SqlClient.SqlCommand

        command = New SqlClient.SqlCommand("UPDATE LLCOND" & sLocation & _
            " Set CYLINDER = @CYLINDER, POUNDS = @POUNDS where CUSTNO = " & "'" & sSlctCust & "'" & _
            " And LAB_NO = " & "'" & sSlctRprt & "'", cn)


        command.Parameters.Add("@CYLINDER", SqlDbType.NVarChar, 6, "CYLINDER")
        command.Parameters.Add("@POUNDS", SqlDbType.NVarChar, 6, "POUNDS")

'''        Dim parameter As SqlClient.SqlParameter = command.Parameters.Add( _
        "@CYLINDER", SqlDbType.NVarChar, 6, "CYLINDER")

'''        parameter = command.Parameters.Add( _
        "@POUNDS", SqlDbType.NVarChar, 6, "POUNDS")

'''        parameter.SourceVersion = DataRowVersion.Original

        daData.UpdateCommand = command

        '       daData.Update(dsData, "LLCOND" & sLocation)

    End Sub

Roger
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:bbarnette
ID: 17020800
If I comment out the three lines you suggested, and un-comment the last line (daData.Update(dsData, "LLCOND" & sLocation)), it does work, but if I make a change in the CYLINDER column in any record it updates all records in the CYLINDER column with the same value. Any idea's?
0
 
LVL 34

Accepted Solution

by:
Sancler earned 125 total points
ID: 17021247
What the message that your update command is sending to the database is saying is this.

Find every record in the table called LLCONDsLocation in which CUSTNO = 'sSlctCust' and LAB_NO = 'sSlctRprt' and, in every such record that you find, set CYLINDER to this value (which I got from the CYLINDER column of the datatable) and POUNDS to this value (which I got from the POUNDS column of the datatable).

The dataadapter reads through all the records in the application's datatable and it sends that command once for each row that it encounters where any alteration has been made (that is, in technical terms, where the .RowState is 'Modified'.  The values that it passes are that from the relevant columns in just that row.

Now, if you have only actually altered one row, the dataadapter should only be sending that message once, with the new values from the altered row.  But it looks very much as though all the records are being found (and so altered in accordance with the message) by the database because all of them meet the requirements that CUSTNO = 'sSlctCust' and LAB_NO = 'sSlctRprt'.  Is that so?  I had assumed that one or other or the combination of CUSTNO and LAB_NO was a Unique Key: not necessarily the Primary Key of the relevant table but a value which uniquely identified the record that had been altered and that therefore needed updating.  If that is not the case then you will need to add such a unique key into your update command and add a parameter for it.

I cannot suggest the precise code because I do not know what the unique key might be.  But the pattern you would need to follow would be like your present code.  Your commandtext would have to added to it something like

   " AND UniqueKey = @UniqueKey"

and the parameter would need adding on these lines

        command.Parameters.Add("@UniqueKey", SqlDbType.NVarChar, 6, <Name of column holding unique key>) 'an the datatype may need changing

Roger
0
 

Author Comment

by:bbarnette
ID: 17050486
OK, I think I have pretty much got it now. However, I had one minor problem with one of the columns in the table. In order to select (read) the data from the column it must be in brackets. When I try this in the parameter line it gives me a syntax error. What is the correct syntax for this in the parameter line?

command.Parameters.Add("@[LOAD]", SqlDbType.NVarChar, 8, "[LOAD]")

The problem appears to be in the @[LOAD] and not in the [LOAD]. If in the update command I replace the @LOAD with a numerical value for test purposes, the code works fine.

BB
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17050806
Use a name for the Parameter other than @[LOAD].  There is no rule that the parameter name needs to reflect, or be related in any way, to the column from which its value is going to come.  That's just for convenience af us humans matching things up when reading the code.  So far as the system is concerned, it can be any name so long as (a) it starts with @ and (b) it is unique in the parameters collection.  And obviously, now ;-), (c) it doesn't contain square brackets.

Obviously you need to change it both in the SQL statement and in the parameters collection.

Roger
0
 

Author Comment

by:bbarnette
ID: 17053632
FYI, the only thing that i found that Ithought was unusual is that the "[LOAD]" field needed brackets in the Update command, but they were not needed in the parameters for the update command.

Everything is working perfectly now. Thanks for all your help.

BB
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17053726
Square brackets are normally needed when a fieldname is a "reserved" word for the database/driver concerned.  I don't know for certain, but I can quite imagine that "LOAD" would be.

Roger
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

16 Experts available now in Live!

Get 1:1 Help Now