Hello All,
I have two tables that can't be changed. One contains Stock codes / country codes, the other has country code / description for each code.

I have a combobox with all the country descriptions that can be added to the stock code table. my problems is i can't figure out how to get the selected Country description to get the right country code and add that too the Stock code table.

My idea was to add another hidden data grid view with the country code/description and search that for the correct row and go over a column to take the correct country code into a hidden text box to then be added to the stockcode/ country code table when i hit the add button with existing SQL code. I was going to put the code in at line #141

here is the code so far.

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Data
Imports System.Text
Imports System.Windows.Forms
Imports System.Xml
Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class UserControl1
    Dim DataSet1 As New DataSet
    Public Event SysProGetInfo(ByRef Parm1 As String, ByRef Parm2 As String)
    Public Function LinkedFromForm(ByVal FormParm1 As String, ByVal FormParm2 As String)
        Try
            Dim MyArray() As String
            Tbx_StkCode.Text = FormParm2
            MyArray = FormParm1.Split("_")
            If MyArray(1).ToLower = "stock code" Then
                BindDataNutr()
                AddCountry()
            Else
                Return ""
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function
    Public Sub BackFromForm(ByVal FormPara1, ByVal FormPara2)
        Try
            Dim XmlOut As New XmlDocument
            MsgBox(FormPara1)
        Catch ex As Exception
        End Try
    End Sub
    Private Sub btnAdd_Click() Handles btn_Add.Click
        Dim check As Integer
        Dim conn As OleDbConnection
        Dim cmdNutr As New OleDbCommand
        Dim cmdNutr1 As New OleDbCommand
        Dim daNutr As New OleDbDataAdapter
        Dim dsNutr As New DataSet
        Dim dtNutr As New DataTable
        If MsgBox("Are you sure you want to add  " & Trim(cbx_Country.Text) & " to " & Trim(tbx_StkCode.Text) & " ?", MsgBoxStyle.OkCancel, "Input confirm") = MsgBoxResult.Cancel Then
        Else
            Try
                conn = New OleDbConnection(SysproSqlString())
                conn.Open()
                cmdNutr = conn.CreateCommand
                cmdNutr.CommandText = "SELECT * From dbo.SBSI_CountryOfOrigin where StockCode = '" & Trim(tbx_StkCode.Text) & "' AND CountryCode = '" & Trim(tbx_COAcode.Text) & "'"
                daNutr.SelectCommand = cmdNutr
                daNutr.Fill(dsNutr)
                dtNutr = dsNutr.Tables(0)
                If (dtNutr.Rows.Count > 0) Then
                    MsgBox(Trim(cbx_Country.Text) & " already in database", MsgBoxStyle.OkOnly, "Message:")
                Else
                    cmdNutr1 = conn.CreateCommand
                    cmdNutr1.CommandText = "INSERT INTO dbo.SBSI_CountryOfOrigin (StockCode, CountryCode) VALUES ('" & Trim(tbx_StkCode.Text) & "', '" & Trim(tbx_COAcode.Text) & "')"
                    check = cmdNutr1.ExecuteReader.RecordsAffected()
                    If check > 0 Then
                        MsgBox(Trim(cbx_Country.Text) & " succesfully to added", MsgBoxStyle.OkOnly, "Message :")
                    Else
                        MsgBox(Trim(cbx_Country.Text) & " Failure  to added", MsgBoxStyle.OkOnly, "Message :")
                    End If
                    BindDataNutr()
                    conn.Close()
                End If
            Catch ex As Exception
                MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error !!")
            End Try
        End If
    End Sub
    Private Sub btnDelete_Click() Handles btn_remove.Click
        Dim check As Integer
        Dim conn As OleDbConnection
        Dim cmdNutr As New OleDbCommand
        Dim daNutr As New OleDbDataAdapter
        Dim dsNutr As New DataSet
        If cbx_Country.Text <> "" Then
            If MsgBox("Are you sure you want to remove  " & Trim(cbx_Country.Text) & " from " & Trim(tbx_StkCode.Text) & " ?", MsgBoxStyle.OkCancel, "Delete confirm") = MsgBoxResult.Cancel Then
            Else
                conn = New OleDbConnection(SysproSqlString())
                Try
                    conn.Open()
                    cmdNutr = conn.CreateCommand
                    cmdNutr.CommandText = "DELETE FROM dbo.SBSI_CountryOfOrigin WHERE StockCode ='" & Trim(tbx_StkCode.Text) & "' AND CountryCode ='" & Trim(tbx_COAcode.Text) & "'"
                    check = cmdNutr.ExecuteReader.RecordsAffected
                    If check > 0 Then
                        MsgBox(Trim(cbx_Country.Text) & " Succesfully Deleted", MsgBoxStyle.OkOnly, "Message :")
                    Else
                        MsgBox(Trim(cbx_Country.Text) & " Failure To Delete", MsgBoxStyle.OkOnly, "Message :")
                    End If
                    BindDataNutr()
                    conn.Close()
                Catch ex As Exception
                    MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error !!")
                End Try
            End If
        Else
            MsgBox("What would you like to Delete?", MsgBoxStyle.OkOnly, "Info Data")
        End If
    End Sub
    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv_list.CellContentClick
        If dgv_list.SelectedCells.Count = 1 Then
            cbx_Country.Text = dgv_list.Item(2, e.RowIndex).Value
            tbx_COAcode.Text = dgv_list.Item(1, e.RowIndex).Value
        End If
    End Sub
    Private Function SysproSqlString()
     'my SQL conn string :)
    End Function
    Private Sub BindDataNutr()
        Dim conn As OleDbConnection
        Dim SqlStr As String
        Dim dsNutr As New DataSet
        Dim daNutr As New OleDbDataAdapter
        conn = New OleDbConnection(SysproSqlString())
        conn.Open()
        SqlStr = "SELECT SBSI_CountryOfOrigin.StockCode, SBSI_CountryOfOrigin.CountryCode, SBSI_CountryCodes.CountryDesc FROM  SBSI_CountryOfOrigin INNER JOIN SBSI_CountryCodes ON SBSI_CountryOfOrigin.CountryCode = SBSI_CountryCodes.CountryCode where StockCode = '" & Trim(tbx_StkCode.Text) & "'"
        daNutr = New OleDbDataAdapter(SqlStr, conn)
        daNutr.Fill(dsNutr)
        dgv_list.DataSource = dsNutr.Tables(0)
        dgv_list.Columns("CountryCode").Visible = False
        dgv_list.Columns("StockCode").Visible = False
    End Sub
    Private Sub AddCountry()
        Dim conn As OleDbConnection
        Dim SqlStr As String
        Dim dsNutr As New DataSet
        Dim daNutr As New OleDbDataAdapter
        conn = New OleDbConnection(SysproSqlString())
        conn.Open()
        SqlStr = "select * From dbo.SBSI_CountryCodes"
        daNutr = New OleDbDataAdapter(SqlStr, conn)
        daNutr.Fill(dsNutr)
        cbx_Country.DataSource = dsNutr.Tables(0)
        dgv_COA.DataSource = dsNutr.Tables(0)
        cbx_Country.DisplayMember = "CountryDesc"
    End Sub
    Private Sub cbx_Country_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbx_Country.SelectedIndexChanged
        '******************************************************
    End Sub
End Class

Create a table that will contain data from both table.

select tbl1.stock_code, tbl2.description
from tbl1, tbl2
where tbl1.country_code = tbl2.country_code

bind this table to your combobox and then have description as display member and stock_code as value member.

thank you :)

so i just added:

cbx_Country.ValueMember = "CountryCode"

and in the SQL code i added cbx_Country.SelectedValue

BOOM!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.