Solved

edit column in  datagrid

Posted on 2006-07-21
20
445 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
20 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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 150 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 improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

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

21 Experts available now in Live!

Get 1:1 Help Now