locked
How to commit changes from datagridview linq to sql RRS feed

  • Question

  • I've created an SQL database and in Visual basic have created a .dbml LinqtoSQL and am using datagridview to view/edit. What I can't seem to find or figure out is how to commit the changes in the datagridview back to the database. Everything I find refers to using a TableAdapter, but this is LinqtoSQL. Using the context.submitchanges() does not work, I am assuming because it doesn't know there are any changes.

    Any help/guidance/direction appreciated!!

    Sunday, January 26, 2014 1:24 PM

Answers

  • Now you have me wondering, I gave you the method to save via DataContext class which is the main entry point for LINQ to SQL.

    The majority of the time developers take advantage of creating their classes via the Object Relational Designer in the IDE, some might consider SQLMetal and the one that want to will create code by hand which Microsoft does not recommend. For more see here.

    With that said, I gave you a recommendation based on best practices to use the IDE designer to create classes so you can write LINQ/SQL. Have no clue why your code is not working and not sure how you are coding so I have nothing else to offer you.

    Reference: SubmitChanges from LINQ/SQL 101 examples.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Marked as answer by Carl Cai Friday, January 31, 2014 3:31 PM
    Monday, January 27, 2014 12:43 AM

All replies

  • Sunday, January 26, 2014 1:39 PM
  • Hello,

    Look at DataContext.SubmitChanges Method.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Proposed as answer by Cor Ligthert Sunday, January 26, 2014 6:02 PM
    Sunday, January 26, 2014 1:41 PM
  • Bassicaly that is easy. In DataSets was the acceptchanges created which gave a lot of misunderstanding.

    In Linq to SQL is Submit changes, which do in fact what everybody thought. It does the updates to the database.

    http://msdn.microsoft.com/en-us/library/bb399378(v=vs.110).aspx


    Success
    Cor

    Sunday, January 26, 2014 6:01 PM
  • None of this seems to work but all looks correct. I have added a BindingSource to my form, I have set the datasource to my SQL database and set my datagridview to the bindingsource. I can access and retrieve data, update the datagridview etc. but when I execute bindingsource.endedit and context.submitchanges() it does NOT update the database??

    What am I missing??

    Sunday, January 26, 2014 7:15 PM
  • Look into the dataset designer file, under project properties select "Show all files". Open the designer file, search for something like the following (which is a partial line of code) which was generated for my database using a DataConext class.

    Me._adapter.UpdateCommand.CommandText = "UPDATE

    If you find this then you should also see something like the following, look at the where condition, this is where a problem would be thinking regular sql.

                Me._adapter.UpdateCommand.CommandText = "UPDATE [dbo].[Categories] SET [CategoryName] = @CategoryName, [Description] = @De"& _ 
                    "scription, [Picture] = @Picture WHERE (([CategoryID] = @Original_CategoryID) AND"& _ 
                    " ([CategoryName] = @Original_CategoryName));"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&"SELECT CategoryID, CategoryName, D"& _ 
                    "escription, Picture FROM Categories WHERE (CategoryID = @CategoryID)"
                Me._adapter.UpdateCommand.CommandType = Global.System.Data.CommandType.Text
                Me._adapter.UpdateCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@CategoryName", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "CategoryName", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
                Me._adapter.UpdateCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@Description", Global.System.Data.SqlDbType.NText, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "Description", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
                Me._adapter.UpdateCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@Picture", Global.System.Data.SqlDbType.Image, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "Picture", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
                Me._adapter.UpdateCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@Original_CategoryID", Global.System.Data.SqlDbType.Int, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "CategoryID", Global.System.Data.DataRowVersion.Original, false, Nothing, "", "", ""))
                Me._adapter.UpdateCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@Original_CategoryName", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "CategoryName", Global.System.Data.DataRowVersion.Original, false, Nothing, "", "", ""))
                Me._adapter.UpdateCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@CategoryID", Global.System.Data.SqlDbType.Int, 4, Global.System.Data.ParameterDirection.Input, 0, 0, "CategoryID", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
    
    If you don't find any of the above this is the problem, a update command was a) not generated b) the SQL is incorrect.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Sunday, January 26, 2014 10:12 PM
  • I'm not seeing a dataset designer file. This is just plain weird behavior. I can set queries and do updates/adds etc. directly from any other area in my app, but the datagridview is kicking my butt.
    Sunday, January 26, 2014 10:28 PM
  • Did you follow my instructions when looking for the designer file i.e. "Show All Files" ?

    This is what I see for a demo project I created. The image below is from viewing the project in Solution Explorer in Visual Studio. Without using "Show All Files" you would not see the underlying file as in the designer file.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Sunday, January 26, 2014 11:25 PM
  • I am using Linq to SQL, these files are not created under this scenario. Again, am I missing something?

    As I understand it (but being wrong isn't new to me) the linq to SQL does not use the dataset model, but is handles a s 'link' between the SQL and VB. As I said, it works perfectly when I am making calls directly through Linq, but once I got into the datagridview (and this seems the best method without a ton of coding) to do the job, I just can't get the data from the grid to the SQL.


    Sunday, January 26, 2014 11:32 PM
  • Now you have me wondering, I gave you the method to save via DataContext class which is the main entry point for LINQ to SQL.

    The majority of the time developers take advantage of creating their classes via the Object Relational Designer in the IDE, some might consider SQLMetal and the one that want to will create code by hand which Microsoft does not recommend. For more see here.

    With that said, I gave you a recommendation based on best practices to use the IDE designer to create classes so you can write LINQ/SQL. Have no clue why your code is not working and not sure how you are coding so I have nothing else to offer you.

    Reference: SubmitChanges from LINQ/SQL 101 examples.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Marked as answer by Carl Cai Friday, January 31, 2014 3:31 PM
    Monday, January 27, 2014 12:43 AM