Advertisement

04.11.2008 at 09:07AM PDT, ID: 23315573
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
8.0

Write out datatable to a database

Zone: Microsoft Visual Basic.Net
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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:

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
Start your free trial to view this solution
Question Stats
Zone: Programming
Question Asked By: running32
Solution Provided By: mankowitz
Participating Experts: 1
Solution Grade: A
Views: 9
Translate:
Loading Advertisement...
04.11.2008 at 09:25AM PDT, ID: 21335699
mankowitz:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.11.2008 at 09:38AM PDT, ID: 21335807
running32:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
20080236-EE-VQP-29 / EE_QW_EXPERT_20070906