?
Solved

edit column in  datagrid

Posted on 2006-07-21
20
Medium Priority
?
482 Views
Last Modified: 2010-08-05
Hi Experts,
I am reading  data in datagrid through data adpater from sql 2000

user needs to enter/change values in some columns

after editing . how we can submit changes  back to sql server. means only update the columns or row we have edit or need to submit the whole datagrid.

Regards,
Bilal
0
Comment
Question by:bsheikh
  • 11
  • 7
18 Comments
 

Author Comment

by:bsheikh
ID: 17156229
well i was not aware of this fact.
i will stay with Question # 2 here
and will open new tickets for rest of 2 questions




Regards
Bilal
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17157555
It's the DataTable that is the DataSource of your datagrid which actually holds the data: the datagrid is just a display/editing mechanism.  That's rather a pedantic point, but it really holds the answer to your question.

What is "submitted" in any updating operation is the datatable, not the grid, and the submission is done by the dataadapter's .Update method.  For that to work your dataadapter will need to have appropriate update commands but those can be automatically generated - if you have not already done this - with

    Dim cb As New SqlCommandBuilder(mydataadapter)

any time after you have initiated the dataadapter with its SelectCommand.

When any field/cell is changed in a DataTable that row is marked by a flag.  The dataadapter works, when it receives an .Update instruction, by checking those flags and it only sends through to the database those rows which have changed.  So, although the right command to issue is the generic

    dataadapter.Update(datatable) 'or dataadapter.update(dataset)

the actual "submission" will be restricted, without any further intervention by you, just to those rows which have changed.

One final point.  The flags I've referred to are only set when the system recognises that editing on any particular row/record has ended.  With a datagrid this automatically happens if the user moves to another row/record.  But it is usually safer to precede an .Update command with code to make sure that all changes have been committed to the DataTable.  That requires a line like

    BindingContext(datatable).EndCurrentEdit

Roger
0
 

Author Comment

by:bsheikh
ID: 17167628
Roger  i need some line of code  if possible.

I am filling  my datagrid with stored procedure.  user needs to edit 1/2 colums
which i need to save back in databse.



Regards
SMB


0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 34

Expert Comment

by:Sancler
ID: 17167737
I'm not really an SQL expert.  OleDb is where most of my experience is.  The principles are the same - which is why I felt able to answer your "in principle" question - but the syntax can be different.  

It looks to me as though, because you are using a stored procedure to fill the datatable, you are going to have to code your own UpdateCommand.  I don't think the automatic creation of that with the CommandBuilder will work from a stroed procedure.  That is why I think syntax will be important.

So, if you don't mind, I'll leave this for an hour or so in the hope that someone with a greater depth of SQL knowledge than I have will pick it up.  If that doesn't happen, I'll give it a crack myself.

Roger

Chaosian - are you about?
0
 

Author Comment

by:bsheikh
ID: 17168078
yup make sense. i am also working side by side.

thanks
SMB
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17172165
As all has been silence, here's my promised further contribution.  Essentially, as the CommandBuilder will not work, you will need yourself to create a new SQLCommand, giving it

    CommandText
    Parameters
    Connection

and set it as your dataadapter's UpdateCommand.  Provided that all that is being done is amendments - no Inserts or Deletions - calling

    BindingContext(datatable).EndCurrentEdit
    dataAdapter.Update(datatable)

should then work.  The detail of the CommandText and the Parameters will depend on field/column names that I don't know but if you use the example from this

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

as a model, you shouldn't go far wrong.  If you are sure that only two columns are going to be updated then it should be sufficient - even if your datatable/datagrid has more columns than that - if your command text and parameters only cover the two fields/columns that are changing: plus, of course, a key column, in the WHERE clause, so that it is clear which amendments are supposed to go in which records.

I hope that helps

Roger
0
 

Author Comment

by:bsheikh
ID: 17178058
Hi I have tried ths example mentioned in above link.
am getting error @ship parameter value not supplied

here is my code


 Private Sub btUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btupdate.Click
        Try

       

        Dim adapter As SqlDataAdapter = New SqlDataAdapter
      Dim command As SqlCommand = New SqlCommand( _
                "UPDATE ProgItems SET PiShopFrom = @Ship" & _
                " WHERE PiProgId =@Progd  AND Piupc=@UPC", cnsqlserv)

           
                command.Parameters.Add("@Ship", SqlDbType.NVarChar, 2, "PiShopFrom")



            Dim param As SqlParameter = command.Parameters.Add( _
                     "@Ship", SqlDbType.NVarChar, 2, "PiShipFrom")
            param.SourceVersion = DataRowVersion.Original


            Dim param1 As SqlParameter = command.Parameters.Add( _
                    "@Progd", SqlDbType.NVarChar, 15, "PiProgId")
            param1.SourceVersion = DataRowVersion.Original

         
            Dim param3 As SqlParameter = command.Parameters.Add( _
                  "@UPC", SqlDbType.Float, 18, "PiUPC")
        param3.SourceVersion = DataRowVersion.Original

        adapter.UpdateCommand = command

        BindingContext(dsdataset.Tables(0)).EndCurrentEdit()
        adapter.Update(dsdataset.Tables(0))

       Catch ex As SqlException
            MsgBox(ex.Message)


       End Try
   


 End Sub
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17178537
Get rid of these lines

            Dim param As SqlParameter = command.Parameters.Add( _
                     "@Ship", SqlDbType.NVarChar, 2, "PiShipFrom")
            param.SourceVersion = DataRowVersion.Original

You have (a) already declared that parameter with this line

               command.Parameters.Add("@Ship", SqlDbType.NVarChar, 2, "PiShopFrom")

and (b) as this is the value you want to update, you don't want it's SourceVersion to be .Original.  You want to leave it at its default, which is .Current.  The single line you already have does that.

Apart from that, it looks to me like it should work OK.  The only comment I have is that you are only updating one field - PiShopFrom - whereas you originally said "user needs to edit 1/2 colums".  Is this correct?

Roger
0
 

Author Comment

by:bsheikh
ID: 17178699
yeah just for simplicity i am updating only 1 column now .

i did with repsted to ur last suggestions.
and getting message that i didnt  supple the parameter @Ship .

SMB
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17180303
What columns are in the datatable that is returned by your stored procedure?  Is there a column named "PiShipFrom"?

Roger
0
 

Author Comment

by:bsheikh
ID: 17181168
this is the only column i want to edit in that particularly selected record.

SMB
0
 
LVL 34

Accepted Solution

by:
Sancler earned 450 total points
ID: 17182458
I made a dummy sql database and table, as consistent with yours as I could.  I then created a form with one button and one datagridview (called dgv1).  Here is the whole of the code, copied and pasted exactly as it stands.

Imports System.Data.SqlClient
Public Class Form1
    Dim constr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Test\BSheikh.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
    Dim con As New SqlConnection(constr)
    Dim sqlstr As String = "SELECT PiShopFrom, PiProgId, Piupc FROM ProgItems"
    Dim da As New SqlDataAdapter(sqlstr, con)
    Dim dt As New DataTable

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        da.Fill(dt)
        DGV1.DataSource = dt
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try



            Dim adapter As SqlDataAdapter = New SqlDataAdapter
            Dim command As SqlCommand = New SqlCommand( _
                      "UPDATE ProgItems SET PiShopFrom = @Ship" & _
                      " WHERE PiProgId =@Progd  AND Piupc=@UPC", con)


            command.Parameters.Add("@Ship", SqlDbType.NVarChar, 2, "PiShopFrom")



            'Dim param As SqlParameter = command.Parameters.Add( _
            '         "@Ship", SqlDbType.NVarChar, 2, "PiShipFrom")
            'param.SourceVersion = DataRowVersion.Original


            Dim param1 As SqlParameter = command.Parameters.Add( _
                    "@Progd", SqlDbType.NVarChar, 15, "PiProgId")
            param1.SourceVersion = DataRowVersion.Original


            Dim param3 As SqlParameter = command.Parameters.Add( _
                  "@UPC", SqlDbType.Float, 18, "PiUPC")
            param3.SourceVersion = DataRowVersion.Original

            adapter.UpdateCommand = command

            BindingContext(dt).EndCurrentEdit()
            adapter.Update(dt)

        Catch ex As SqlException
            MsgBox(ex.Message)


        End Try


    End Sub
End Class

The only differences that I can see are the connection string, the fact that the data was loaded with a text query rather than a stored procedure, and that I put the data in a free standing datatable rather than in a table in a dataset.  I also did it in VB.NET 2005 rather than 2003.  None of those, however, should affect in any way the issue that you are now reporting.

It works for me.

Roger
0
 

Author Comment

by:bsheikh
ID: 17183683
I will back to u after two hours its still asking me for "@ship" parameter was not supplied.


regards
bilal
0
 

Author Comment

by:bsheikh
ID: 17183886
this is the exact error message i am getting

Prepared statement '(@Ship NVarchar (2),@Progd nvarchar (15), @UPC float (18))UPDATE ProgItems' Expectes Parametr @Ship whuich was not supplied



Regards
Bilal
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17184060
Sorry, Bilal, I can't help any further.  As I said early on, I'm really an OleDb man, not SQL.  That error message - with its reference to "Prepared statement" - looks to me like the database is trying to execute a StoredProcedure.  The code you've posted, and I've tried out, (a) does not reference any stored procedure (b) would certainly be correct for OleDb and (c), as my post before this one shows, worked OK for me in SQL.  So I can neither understand why the error is occurring nor suggest what you might do to fix it.

Again, sorry

Roger
0
 

Author Comment

by:bsheikh
ID: 17184205
I beleive somehow it is not picking @Ship Value  
i dont know why
0
 

Author Comment

by:bsheikh
ID: 17184224
I appriciate your efforts in order to help em out . i 'll get back to you as soon as i make any progress


Regards
bilal
0
 

Author Comment

by:bsheikh
ID: 17185233
I just solved the problem

it was displaying the right message . as it was unable to pick the values.

here is the right code for assignign values

 command.Parameters.Add("@Ship", SqlDbType.NVarChar, 2)
                command.Parameters("@Ship").SourceColumn = "Ship"

                MsgBox(command.Parameters("@Ship").SourceColumn = "Ship")




            Dim param1 As SqlParameter = command.Parameters.Add( _
                    "@Progd", SqlDbType.NVarChar, 15)
            param1.Value = DataRowVersion.Original
                param1.SourceVersion = DataRowVersion.Original
                param1.SourceColumn = "Progd"

            Dim param2 As SqlParameter = command.Parameters.Add( _
            "@selected_prog_number", SqlDbType.Float, 8)
                param2.SourceVersion = DataRowVersion.Current
            param2.Value = TextBox2.Text



            Dim param3 As SqlParameter = command.Parameters.Add( _
                   "@UPC", SqlDbType.NVarChar, 18)
                param3.SourceVersion = DataRowVersion.Current
                param3.Value = DataRowVersion.Current
                param3.SourceColumn = "UPC"


now it is updating perfectly .

thanks for ur help and time

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 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