Asked by:
Visual basic write values to sql server table

-
I have a checkedlist box that I want to write those values to a sql server table. Can anyone help?
Alan
- Moved by Sara LiuMicrosoft contingent staff Tuesday, April 11, 2017 7:47 AM
Question
All replies
-
depends how the data is used in data base. By that I mean, how each item in checkedlist box is related to a field in database.
See if this helps
Mark Answered, if it solves your question and Vote if you found it helpful.
Rohit Arora- Proposed as answer by Cor Ligthert Tuesday, April 11, 2017 8:07 AM
-
Hi Alan,
Welcome to the MSDN forum.
Refer to your description, your issue is more related to the development of VB. Since our forum is to discuss Visual Studio WPF/SL Designer, Visual Studio Guidance Automation Toolkit, Developer Documentation and Help System, and Visual Studio Editor, I will help you move this thread to this appropriate forum: Visual Studio Languages > Visual Basic , you will get a more professional support and thank you for your understanding.
Best regards,
Sara
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. -
Hi anaylor01,
I do a simple that you can refer to. I put one CheckListBox and Button Control in the Form.
Imports mshtml Imports System.Data.SqlClient Public Class Form18 Private Sub Form18_Load(sender As Object, e As EventArgs) Handles MyBase.Load CheckedListBox1.SuspendLayout() Dim dt As New DataTable Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Example(VB)\DataBase1.mdf;Integrated Security=True;Connect Timeout=30" Using conn As New SqlConnection(str) Dim sql As String = "select * from CheckBox" Using cmd As New SqlCommand(sql, conn) conn.Open() dt.Load(cmd.ExecuteReader) End Using End Using Dim lastindex As Integer = 0 Try For Each row As DataRow In dt.Rows CheckedListBox1.Items.Add(row.Field(Of String)("Item")) lastindex = CheckedListBox1.Items.Count - 1 If row.Field(Of Boolean)("CheckState") Then CheckedListBox1.SetItemChecked(lastindex, True) End If Next CheckedListBox1.SelectedIndex = 0 Catch ex As Exception MessageBox.Show(ex.Message) Finally CheckedListBox1.ResumeLayout() End Try End Sub Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim affected As Integer = 0 Dim errorcount As Integer = 0 Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Example(VB)\DataBase1.mdf;Integrated Security=True;Connect Timeout=30" Using conn As New SqlConnection(str) Dim sql As String = "Update CheckBox set Itemindex=@Itemindex, checkstate=@checkstate where Itemindex=@ItemIndex" Using cmd As New SqlCommand(sql, conn) cmd.Parameters.Add("@Itemindex", SqlDbType.Int) cmd.Parameters.Add("@CheckState", SqlDbType.Bit) cmd.Parameters.Add("@ItemIndex", SqlDbType.Int) conn.Open() For index As Integer = 0 To (CheckedListBox1.Items.Count - 1) cmd.Parameters(0).Value = index If CheckedListBox1.GetItemChecked(index) Then cmd.Parameters(1).Value = True Else cmd.Parameters(1).Value = False End If cmd.Parameters(2).Value = index Try ' ' Good form to check if the query actually worked ' affected = cmd.ExecuteNonQuery If affected <> 1 Then errorcount += 1 End If Catch ex As Exception errorcount += 1 End Try Next End Using End Using End Sub End Class
Hope It is helpful to you.
Best Regards,
Cherry Bu
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.- Proposed as answer by Cherry BuMicrosoft contingent staff, Moderator Thursday, April 20, 2017 7:11 AM
-
Hello,
See my MSDN code sample. All the basic operations are shown in the ProductsCodeSample for loading a CheckedListBox and updating data.
What I don't show is a single method for updating all items at once but here is code that will do the trick for the ProductsCodeSample.
Add a button to the form and place the following code into it.
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim Items As List(Of CheckListBoxItem) = ( From item In checkedListBox1.Items.Cast(Of CheckListBoxItem) Select item ).ToList End Sub
Then in Operations class add the following method
Public Sub UpdateAll(Items As List(Of CheckListBoxItem)) Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString} Using cmd As SqlCommand = New SqlCommand With {.Connection = cn} cmd.CommandText = "UPDATE [dbo].[Products] " & "SET [Quantity] = @Quantity, CheckedStatus = CheckedStatus WHERE id = @Id" cmd.Parameters.Add(New SqlParameter() With {.ParameterName = "@id", .SqlDbType = SqlDbType.Int}) cmd.Parameters.Add(New SqlParameter() With {.ParameterName = "@Quantity", .SqlDbType = SqlDbType.Int}) cmd.Parameters.Add(New SqlParameter() With {.ParameterName = "@CheckedStatus", .SqlDbType = SqlDbType.Bit}) cn.Open() For Each item As CheckListBoxItem In Items cmd.Parameters("id").Value = item.PrimaryKey cmd.Parameters("@Quantity").Value = item.Quantity cmd.Parameters("@CheckedStatus").Value = item.Checked cmd.ExecuteNonQuery() Next End Using End Using End Sub
Note that I used a class for all of the database operations as this is a best practice to separate code from user interface which allows you to use said code in more than one form or project and keeps form code clean.
Since the checked state is remembered in the CheckedListBox each time the form is opened the checked state is the same as last time the form was opened. If this is not desirable you can elect not to use that part simply be removing the following line in form load.
checkedListBox1.SetItemChecked(LastIndex, row.Field(Of Boolean)("CheckedStatus"))
Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator
- Edited by KareninstructorMVP, Moderator Tuesday, April 11, 2017 10:30 AM Added code