asked on
Write out datatable to a database
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
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
Visual Basic.NET
Last Comment
mankowitz
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
How can I assign cmd.Parameters("@colb").Va
ASKER
Thanks
Not sure if you figured it out, but newrow contains an itemarray of values. You want to assign only one of those values