Updating A Datagrid

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

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.

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

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

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

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?

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

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

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

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