troubleshooting Question

Write out datatable to a database

Avatar of running32
running32 asked on
Visual Basic.NET
4 Comments1 Solution263 ViewsLast Modified:
I want to be able to get the select value from a list box and write it out to a table.  I need to write out the selected value to the database not the displayed value the list box.  My code will not write out the the database can you please tell me what is wrong.

Thanks
mports SqlParametersDB.Name
Imports VB = Microsoft.VisualBasic
Imports System.Data.SQLClient
 
 
Public Class frmagency
    Inherits System.Windows.Forms.Form
    Dim countprogram
    Dim Connection1 As New SqlClient.SqlConnection(basGlobals.sCon)
#Region " Windows Form Designer generated code "
 
    Public Sub New()
        MyBase.New()
 
        'This call is required by the Windows Form Designer.
        InitializeComponent()
 
        'Add any initialization after the InitializeComponent() call
 
    End Sub
 
    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub
 
    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer
 
    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox
    Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents DsAgency1 As SqlParametersDB.DSAgency
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents ListBox1 As System.Windows.Forms.ListBox
    Friend WithEvents SqlDataAdapter2 As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents SqlSelectCommand2 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlInsertCommand2 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents DspaReferal1 As SqlParametersDB.DSPAReferal
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Label1 = New System.Windows.Forms.Label
        Me.ComboBox1 = New System.Windows.Forms.ComboBox
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.DsAgency1 = New SqlParametersDB.DSAgency
        Me.Label2 = New System.Windows.Forms.Label
        Me.Label3 = New System.Windows.Forms.Label
        Me.ListBox1 = New System.Windows.Forms.ListBox
        Me.SqlDataAdapter2 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlInsertCommand2 = New System.Data.SqlClient.SqlCommand
        Me.SqlSelectCommand2 = New System.Data.SqlClient.SqlCommand
        Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
        Me.DspaReferal1 = New SqlParametersDB.DSPAReferal
        CType(Me.DsAgency1, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.DspaReferal1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'Label1
        '
        Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", 10.0!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label1.Location = New System.Drawing.Point(8, 8)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(336, 16)
        Me.Label1.TabIndex = 5
        Me.Label1.Text = "Description"
        '
        'ComboBox1
        '
        Me.ComboBox1.Location = New System.Drawing.Point(8, 48)
        Me.ComboBox1.Name = "ComboBox1"
        Me.ComboBox1.Size = New System.Drawing.Size(336, 21)
        Me.ComboBox1.TabIndex = 4
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=JUSTINECLEARY;packet size=4096;user id=sa;data source=""BC-BIZTALK""" & _
        ";persist security info=True;initial catalog=HealthDistrict;password=bristleconef" & _
        "ox"
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tblLkpAgency", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("strAgency", "strAgency"), New System.Data.Common.DataColumnMapping("lngAgencynum", "lngAgencynum")})})
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO tblLkpAgency(strAgency, lngAgencynum) VALUES (@strAgency, @lngAgencyn" & _
        "um); SELECT strAgency, lngAgencynum FROM tblLkpAgency"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@strAgency", System.Data.SqlDbType.NVarChar, 50, "strAgency"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@lngAgencynum", System.Data.SqlDbType.Int, 4, "lngAgencynum"))
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT strAgency, lngAgencynum FROM tblLkpAgency"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'DsAgency1
        '
        Me.DsAgency1.DataSetName = "DSAgency"
        Me.DsAgency1.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'Label2
        '
        Me.Label2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label2.Location = New System.Drawing.Point(8, 32)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(136, 16)
        Me.Label2.TabIndex = 8
        Me.Label2.Text = "Agency Filter"
        '
        'Label3
        '
        Me.Label3.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label3.Location = New System.Drawing.Point(8, 80)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(200, 16)
        Me.Label3.TabIndex = 9
        Me.Label3.Text = "Select Agencies for Referral"
        '
        'ListBox1
        '
        Me.ListBox1.Location = New System.Drawing.Point(8, 96)
        Me.ListBox1.Name = "ListBox1"
        Me.ListBox1.SelectionMode = System.Windows.Forms.SelectionMode.MultiExtended
        Me.ListBox1.Size = New System.Drawing.Size(336, 160)
        Me.ListBox1.TabIndex = 10
        '
        'SqlDataAdapter2
        '
        Me.SqlDataAdapter2.DeleteCommand = Me.SqlDeleteCommand1
        Me.SqlDataAdapter2.InsertCommand = Me.SqlInsertCommand2
        Me.SqlDataAdapter2.SelectCommand = Me.SqlSelectCommand2
        Me.SqlDataAdapter2.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tblContactType", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("lngContactType", "lngContactType"), New System.Data.Common.DataColumnMapping("strTypeDesc", "strTypeDesc")})})
        Me.SqlDataAdapter2.UpdateCommand = Me.SqlUpdateCommand1
        '
        'SqlDeleteCommand1
        '
        Me.SqlDeleteCommand1.CommandText = "DELETE FROM tblContactType WHERE (lngContactType = @Original_lngContactType) AND " & _
        "(strTypeDesc = @Original_strTypeDesc OR @Original_strTypeDesc IS NULL AND strTyp" & _
        "eDesc IS NULL)"
        Me.SqlDeleteCommand1.Connection = Me.SqlConnection1
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_lngContactType", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "lngContactType", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_strTypeDesc", System.Data.SqlDbType.NVarChar, 25, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "strTypeDesc", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlInsertCommand2
        '
        Me.SqlInsertCommand2.CommandText = "INSERT INTO tblContactType(lngContactType, strTypeDesc) VALUES (@lngContactType, " & _
        "@strTypeDesc); SELECT lngContactType, strTypeDesc FROM tblContactType WHERE (lng" & _
        "ContactType = @lngContactType) ORDER BY strTypeDesc"
        Me.SqlInsertCommand2.Connection = Me.SqlConnection1
        Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@lngContactType", System.Data.SqlDbType.Int, 4, "lngContactType"))
        Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@strTypeDesc", System.Data.SqlDbType.NVarChar, 25, "strTypeDesc"))
        '
        'SqlSelectCommand2
        '
        Me.SqlSelectCommand2.CommandText = "SELECT lngContactType, strTypeDesc FROM tblContactType WHERE (bytFlag = 0) ORDER " & _
        "BY strTypeDesc"
        Me.SqlSelectCommand2.Connection = Me.SqlConnection1
        '
        'SqlUpdateCommand1
        '
        Me.SqlUpdateCommand1.CommandText = "UPDATE tblContactType SET lngContactType = @lngContactType, strTypeDesc = @strTyp" & _
        "eDesc WHERE (lngContactType = @Original_lngContactType) AND (strTypeDesc = @Orig" & _
        "inal_strTypeDesc OR @Original_strTypeDesc IS NULL AND strTypeDesc IS NULL); SELE" & _
        "CT lngContactType, strTypeDesc FROM tblContactType WHERE (lngContactType = @lngC" & _
        "ontactType) ORDER BY strTypeDesc"
        Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@lngContactType", System.Data.SqlDbType.Int, 4, "lngContactType"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@strTypeDesc", System.Data.SqlDbType.NVarChar, 25, "strTypeDesc"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_lngContactType", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "lngContactType", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_strTypeDesc", System.Data.SqlDbType.NVarChar, 25, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "strTypeDesc", System.Data.DataRowVersion.Original, Nothing))
        '
        'DspaReferal1
        '
        Me.DspaReferal1.DataSetName = "DSPAReferal"
        Me.DspaReferal1.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'frmagency
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(352, 262)
        Me.Controls.Add(Me.ListBox1)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.Label1)
        Me.Controls.Add(Me.ComboBox1)
        Me.Name = "frmagency"
        Me.Text = "Agency"
        CType(Me.DsAgency1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DspaReferal1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)
 
    End Sub
 
#End Region
 
    Private Sub frmagency_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        DspaReferal1.Clear()
        Try
            countprogram = (SqlDataAdapter2.Fill(DspaReferal1))
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ListBox1.DataSource = DspaReferal1.Tables("tblContactType")
        ListBox1.DisplayMember = "strTypeDesc"
        ListBox1.ValueMember = "lngContactType"
 
        DsAgency1.Clear()
        Try
            countprogram = (SqlDataAdapter1.Fill(DsAgency1))
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ComboBox1.DataSource = DsAgency1.Tables("tbllkpAgency")
        ComboBox1.DisplayMember = "strAgency"
        ComboBox1.ValueMember = "lngAgencynum"
 
    End Sub
 
    Private Sub ComboBox1_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.DropDown
        Dim countprogram
        DsAgency1.Clear()
        Try
            countprogram = (SqlDataAdapter1.Fill(DsAgency1))
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ComboBox1.DataSource = DsAgency1.Tables("tbllkpAgency")
        ComboBox1.DisplayMember = "strAgency"
        ComboBox1.ValueMember = "lngAgencynum"
    End Sub
 
    Private Sub frmagency_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Leave
        Dim table As DataTable = Me.ListBox1.DataSource
 
        Dim newTable As DataTable
        newTable = table.Clone()
 
        'Add each selected item to the newTable
        For Each row As DataRowView In Me.ListBox1.SelectedItems
 
            newTable.Rows.Add(row.Row.ItemArray())
 
        Next
        'write out from datatable
        Dim cmd As New SqlCommand
        Dim newrow As DataRow
        With cmd
            .Connection = Connection1
            .CommandText = "INSERT INTO tblPArefer (cola, colb) VALUES (@cola, @colb)"
            With .Parameters
                .Add("@cola", SqlDbType.NVarChar)  'typesize is optional for fixed size datatypes like integer, datetime, etc.
                .Add("@colb", SqlDbType.NVarChar)
 
            End With
        End With
 
        For Each newrow In newTable.Rows
            cmd.Parameters("@cola").Value = newrow("cola")
            cmd.Parameters("@colb").Value = newrow("colb")
 
            Try
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show("unable to insert record: ")
            Finally
                If cmd.Connection.State = ConnectionState.Open Then
                    cmd.Connection.Close()
                End If
            End Try
        Next
 
 
    End Sub
End Class
ASKER CERTIFIED SOLUTION
mankowitz

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros