none
Using the Data Grid View control with SQL Commands RRS feed

  • Question

  • Hello,

    I have this code snippet in SQL Query Builder:

    INSERT INTO IDNumbers ( IDNumber, IDName )
    SELECT MAX(IDNumber)+1 AS NewID, NULL AS NewIDName
    FROM IDNumbers

    This SQL lines of code refer to the method, InsertQueryIDNumbers()

    And here is the second code snippet:

    Public Class Form1
        Private Sub IDNumbersBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles IDNumbersBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.IDNumbersBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.NamesDBBackupOneDataSet)
    
        End Sub
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'NamesDBBackupOneDataSet.IDNumbers' table. You can move, or remove it, as needed.
            Me.IDNumbersTableAdapter.Fill(Me.NamesDBBackupOneDataSet.IDNumbers)
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Me.IDNumbersTableAdapter.InsertQueryIDNumbers()
    
        End Sub
    End Class

    Anyone can improve this code as they please.  The main goal is to add a record to the data control grid.

    So far, in the properties windows, I do not know which of the choices to select:

    For the command type: The choices in the drop-down menu are: Text, Stored Procedure, and Table Direct.

    And for the execute mode: The choices in the drop-down menu are: scalar, reader, and non query

    Anyone who is willing to help me out would be great.

    Regards,

    JohnDBCTX


    jp

    Friday, August 31, 2018 12:51 PM

All replies

  • You should not attempt to increment a field used as a primary key, especially when using TableAdapter components. Instead having a auto-incrementing primary key e.g.

    CREATE TABLE dbo.IDNumbers
    (IDNumber INT IDENTITY(1, 1) NOT NULL,
     IDName   NVARCHAR(50) NULL,
     CONSTRAINT PK_IDNumbers PRIMARY KEY CLUSTERED(IDNumber ASC)
     WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    )
    ON [PRIMARY];

    The data wizard that creates the data classes will have SQL similar to the following which returns the new primary key to you.

    DECLARE @IDName AS NVARCHAR(50)= 'Somevalue';
    INSERT INTO dbo.IDNumbers(IDName) VALUES(@IDName); SELECT CAST(SCOPE_IDENTITY() AS INT);

    Putting things together when adding a new record and displayed in a DataGridView with the ID column showing before the save the value for ID is a negative value but after the insert is the new ID value e.g.

    TableAdapter components work best when done this way. And if there are multiple inserts done at once your current method is not effective in a multi-user environment.


    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, August 31, 2018 1:41 PM
    Moderator
  • I need to learn more about how Table Adapter components work, instead of the current method; therefore, I should disregard that multi-user method.  Correct?

    Furthermore, can anyone provide me tutorials in regards to Table Adapters in VB.net?

    In the meantime, I'm gonna do more research.

    Regards,

    JohnDBCTX


    jp

    Friday, August 31, 2018 8:51 PM
  • No need to discard the idea of a multi-user application as what I showed works in a multi-user environment.

    I don't have a VB.NET code sample for working with TableAdapter components but do for C#. In the following C# code sample I wrote I think you could learn from it high level.

    There are some starter code samples here that start about half way down the page.

    What I can say is TableAdapters work great if you understand them but if your understanding of TableAdapter components is not pass the basics they will be a challenge to work with.

    What many find appealing with TableAdapter components is there is no need to write a lot of code yet the other methods where you write a good deal of code and have understanding of them provides pretty much complete control of all operations.


    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, August 31, 2018 9:53 PM
    Moderator