locked
vb.net and sql server code require In one Invoice Product can be enter only one time RRS feed

  • Question

  • Required

    invoice(pk) :1

    SERIAL | PRODUCT  | Quantity

    1            |  Apple       |  10

    2             | orange     |  5

    NOW IF USER AGAIN ENTER PRODUCT APPLE CURSOR MOVE TO Quantity column

    In each invoice product all product can come on one time only

    invoice(pk) :2

    SERIAL | PRODUCT  | Quantity

    1            |  Apple       |  10

    2             | orange     |  5


    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    Wednesday, January 23, 2019 7:05 AM

All replies

  • Hello,

    How is the data being entered by the user, in TextBox controls, DataGridView etc.?

    In regards to "if the user again enters product 'Apple' cursor moves to the quantity column" Do you mean if the enter the product 'Apple' again move to the row in a DataGridView where that item has already been entered?

    Also, how are you doing with this question, has it been resolve or not?

    In the mean time here I load a product table from a data class.

    Public Function LoadProducts() As DataTable
        DefaultCatalog = "NorthWindAzure"
        Dim selectStatement As String = "SELECT ProductId, ProductName FROM Products"
        Dim dt As New DataTable
    
        Using cn = New SqlConnection(ConnectionString)
            Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = selectStatement}
                cn.Open()
                dt.Load(cmd.ExecuteReader())
            End Using
        End Using
        Return dt
    End Function

    In the form set the above DataTable to a ComboBox to allow selections. Allow the user to select a product and quantity via a NumericUpDown (can be a TextBox also). Pressing the add button we see if the product has already been added, if not add it while if already in the DataTable move to it and add the quantity to that row.

    Public Class Form3
        Private bsOrder As New BindingSource
        Private dataOperations As New SqlServerOperations
        Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn() With {.ColumnName = "Id",
                              .DataType = GetType(Integer), .AutoIncrement = True})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "ProductName",
                              .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "Quantity",
                              .DataType = GetType(Integer)})
    
            bsOrder.DataSource = dt
            DataGridView1.DataSource = bsOrder
    
            productComboBox.DataSource = dataOperations.LoadProducts
            productComboBox.DisplayMember = "ProductName"
    
        End Sub
    
        Private Sub addButton_Click(sender As Object, e As EventArgs) Handles addButton.Click
            Dim row As DataRow = CType(productComboBox.SelectedItem, DataRowView).Row
            Dim key As Integer = row.Field(Of Integer)("ProductId")
            Dim result = bsOrder.Find("id", key)
            If result = -1 Then
                CType(bsOrder.DataSource, DataTable).Rows.
                    Add(Nothing, productComboBox.Text, CType(quantityNumericUpDown.Value, Integer))
                bsOrder.MoveLast()
            Else
                bsOrder.Position = result
                Dim currentQty = CType(bsOrder.Current, DataRowView).Row.Field(Of Integer)("Quantity")
                CType(bsOrder.Current, DataRowView).Row.
                    SetField(Of Integer)("Quantity", currentQty + CType(quantityNumericUpDown.Value, Integer))
            End If
    
        End Sub
    End Class

    In this case three products have been added, the while one two attempts were made but using the above code we moved to the first record that was already there and updated it.

     


    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


    Wednesday, January 23, 2019 11:19 AM
  • hi

    i have Northwind sample database.  i am using  vb.net and sql server.  for entering the data i use the text box which take value from database,  i tried to apply your code i found error two places

     Private dataOperations As New SqlServerOperations

    Error 1 Type 'SqlServerOperations' is not defined. C:\Users\Public\Documents\Khawaja Pharma\vb application\Northwind\Northwind\Form1.vb 9 35 Northwind

    2nd error

    DefaultCatalog = "NorthWindAzure"

    Error 2 'DefaultCatalog' is not declared. It may be inaccessible due to its protection level. C:\Users\Public\Documents\Khawaja Pharma\vb application\Northwind\Northwind\Module1.vb 10 9 Northwind


    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    Wednesday, January 23, 2019 5:48 PM
  • SqlServerOperations is a class that contains the following

    Public Function LoadProducts() As DataTable
        DefaultCatalog = "NorthWindAzure"
        Dim selectStatement As String = "SELECT ProductId, ProductName FROM Products"
        Dim dt As New DataTable
    
        Using cn = New SqlConnection(ConnectionString)
            Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = selectStatement}
                cn.Open()
                dt.Load(cmd.ExecuteReader())
            End Using
        End Using
        Return dt
    End Function
    DefaultCatalog comes from here.


    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

    Wednesday, January 23, 2019 7:59 PM
  • i follow your code i found following error in following line when you declaration portion.

    Private dataOperations As New SqlServerOperations


    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    Thursday, January 24, 2019 12:45 AM
  • i follow your code i found following error in following line when you declaration portion.

    Private dataOperations As New SqlServerOperations


    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    What you believe is an error is not an error, I provided a conceptual example that you could see the bigger picture of the LoadProducts method but if you want the code here it is and think about what it does and if something does not work right don't wait for me, developers are meant to solve problems :-)

    Public MustInherit Class BaseSqlServerConnection
        ''' <summary>
        ''' This points to your database server
        ''' </summary>
        Protected DatabaseServer As String = "KARENS-PC"
        ''' <summary>
        ''' Name of database containing required tables
        ''' </summary>
        Protected DefaultCatalog As String = "ForumExample"
        Public ReadOnly Property ConnectionString() As String
            Get
                Return $"Data Source={DatabaseServer};Initial Catalog={DefaultCatalog};Integrated Security=True"
            End Get
        End Property
    End Class
    
    Public Class SqlServerOperations
        Inherits BaseSqlServerConnection
        Public Function LoadProducts() As DataTable
            DefaultCatalog = "NorthWindAzure"
            Dim selectStatement As String = "SELECT ProductId, ProductName FROM Products"
            Dim dt As New DataTable
    
            Using cn = New SqlConnection(ConnectionString)
                Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = selectStatement}
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
                End Using
            End Using
            Return dt
        End Function
    End Class
    


    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

    Thursday, January 24, 2019 1:02 AM