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