how to save selected item from checkedlistbox to mysql?

[shohoku]

i want to select the empname from checkedlistbox and save into mysql
can anybody pls tell me how to do?

i find out some code but quite don't understand what it means..

For i = 0 To lvlist.Items.Count - 1

If lvlist.Items .Checked Then

GROUPCHK="select * from task where username='"& lvitem .item.text &"'"

groupcmd.Connection = conn
groupcmd.CommandText = GROUPCHK
groupadp.SelectCommand = groupcmd
groupa

groupadp.SelectCommand = groupcmd
groupadp.Fill(grouptable)

me.datagrid.datasource=grouptable
end if
next

 

[jmcilhinney]

The CheckedListBox has a CheckedItems property. It is a collection that contains all the items in the list that are checked. Similarly it has an Items property and a SelectedItems property.

I don't really understand what it is you're asking, though. Save them how? Are they existing records that you are editing, or new records altogether? Is it the empname that is displayed in the CheckedListBox, and how does it relate to the rest of a record?

 

[kulrom]

this is for checked items ... for selected item just replace collection to
SelectedItems i.e. Me.CheckedListBox1.SelectedItems

Dim[/color][/size][size=2] xList [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Object

[/color][/size][size=2][/size][size=2][color=#0000ff]Try

[/color][/size][size=2]oledbcon.Open()

[/size][size=2][color=#0000ff]For[/color][/size][size=2] [/size][size=2][color=#0000ff]Each[/color][/size][size=2] xList [/size][size=2][color=#0000ff]In[/color][/size][size=2] [/size][size=2][color=#0000ff]Me[/color][/size][size=2].CheckedListBox1.CheckedItems

strSQL = "INSERT INTO Table1 (Forma) VALUES ('" & xList & "')"

cmd = [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand(strSQL, oledbcon)

cmd.ExecuteNonQuery()

[/size][size=2][color=#0000ff]Next

[/color][/size][size=2][/size][size=2][color=#0000ff]Catch[/color][/size][size=2] ex [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception

MessageBox.Show(ex.Message)

[/size][size=2][color=#0000ff]Finally

[/color][/size][size=2]oledbcon.Close()

MsgBox("Saved !")

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Try

Cheers

 

[shohoku]

Kulrom...
i feels sorry that i'm quite no understand abt ur code...
i'm new in VB.NET
can u change it by using odbc??
sorry for waste ur time..

 

[shohoku]

i'm doing a scheduling app
now i want to choose only few ppl to attend a group meeting
The name inside the checkedlistbox is retrieve from database based on how many user in database.
now let say in checkedlistbox got ten user name
but i only choose three user
and when i click save..
i need to record the time,venue,meeting title in those three user's timetable
i'm using the VB.NET + MySQL..
and i'm using ODBC connect

 

[kulrom]

Well, oledbcon is the connection name that i'm using there but you can use another one it really doesn't matter ... means, you are declaring stringConn, declaring connection and ... hmm

Ok i will try to guess for a while ...

[size=2][color=#0000ff]Dim[/color][/size][size=2] con [/size][size=2][color=#0000ff]As [/color][/size][size=2][color=#0000ff]String[/color][/size][size=2] = "Driver= {mySQL};Server=localhost;Option=16834;Database=mydatabase;"[/size]
[size=2][color=#0000ff]Dim[/color][/size][size=2] oledbcon [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbConnection(con)[/size]

[size=2]oledbcon.Open() 'open your conn[/size]
[size=2][color=blue]Dim[/color] strSQL [color=blue]As[/color] String = "SELECT Name FROM Table1"[/size]
[size=2][size=2][color=#0000ff]Dim[/color][/size][size=2] cmd [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbCommand = new OleDBCommand(strSQL, oledbcon)
 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] objRead [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataReader = cmd.ExecuteReader[/size]

[size=2][color=blue]While[/color] Objread.read[/size]
[size=2][color=blue]Me[/color].CheckedListBox.Items.Add(objRead("Name")) 'populate the checkListBox[/size]
[size=2][color=blue]End While[/color][/size]
[size=2]Objread.Close()

[/size]

now just use the code from my previous post ... to insert selected items in another table or whenever you want

Cheers
[/size]

 

[shohoku]

This is the code that how i add item into checkedlistbox....

		Dim myCommand As New OdbcCommand
		Dim myData As New DataTable
		Dim SQL As String
		Dim myAdapter As New OdbcDataAdapter
		Dim conn As OdbcConnection


		CheckedListBox1.Items.Clear()
		conn = New OdbcConnection
		conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
					   "SERVER=localhost;" & _
					   "DATABASE=seas;" & _
					   "UID=root;" & _
					   "PASSWORD=;" & _
					   "OPTION=3;"

		SQL = "SELECT Employee_ID, Empname from empdetail"

		conn.Open()
		myData.Clear()
		myCommand.Connection = conn
		myCommand.CommandText = SQL
		myAdapter.SelectCommand = myCommand
		myAdapter.Fill(myData)
		For i = 0 To myData.Rows.Count - 1
			id(i) = Convert.ToString(myData.Rows(i)(0))
			name1(i) = Convert.ToString(myData.Rows(i)(1))

		Next

		conn.Close()

		Dim myCommand1 As New OdbcCommand
		Dim myData1 As New DataTable
		Dim SQL1 As String
		Dim myAdapter1 As New OdbcDataAdapter
		Dim conn1 As OdbcConnection

		conn1 = New OdbcConnection
		conn1.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
					   "SERVER=localhost;" & _
					   "DATABASE=seas;" & _
					   "UID=root;" & _
					   "PASSWORD=;" & _
					   "OPTION=3;"

		SQL1 = "SELECT Employee_ID, Empname from empdetail"

		conn1.Open()
		myData1.Clear()
		myCommand1.Connection = conn1
		myCommand1.CommandText = SQL1
		myAdapter1.SelectCommand = myCommand1
		myAdapter1.Fill(myData1)


		For i = 0 To 9
			CheckedListBox1.Items.Add(name1(i))
		Next

 

[kulrom]

And now, what is the problem? Sorry if i miss something

Cheers

 

[shohoku]

above is the code i retrieve the data into checkedlistbox...
than now incheckedlistbox got 8 name
aaa
bbb
ccc
ddd
eee
ffff
ggg
hhh

i select 3 names inside(aaa,ccc,ggg)
when i click save
will save to a table name timetable
example:
ID Name Event
101 aaa group meeting
103 ccc group meeting
106 ggg group meeting

than others don't have to save
can u understand my poor language??
keke~~

 

[kulrom]

Well, post 3 demonstrates how to insert all checked items within new table ... just maybe if needs to be adapted for a while. Feel free to ask id there is a part you don't quite understand ...

Cheers

 

[shohoku]

can u check for me pls...
is it correct?
wat the forma actually put?
before that i put in Empname but pop out some error
"ERROR[42000][MySQL][ODBC 3.51 Driver][mysql-4.1.11-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near')VALUES('Tai) at line 1 "
in checkedlistbox1, i choose Tai, Lee, santi

		Dim myCommand As New OdbcCommand
		Dim myData As New DataSet
		Dim SQL As String
		Dim myAdapter As OdbcDataAdapter
		Dim conn As OdbcConnection
		Dim acc As Data.DataRow
		Dim a As OdbcCommandBuilder


		conn = New OdbcConnection
		conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
					   "SERVER=localhost;" & _
					   "DATABASE=seas;" & _
					   "UID=root;" & _
					   "PASSWORD=;" & _
					   "OPTION=3;"

		Dim xList As Object
		Try
			conn.Open()
			For Each xList In Me.CheckedListBox1.CheckedItems
			    SQL = "INSERT INTO timetable3 (Forma)) VALUES ('" & xList & "')"
			    myCommand = New OdbcCommand(SQL, conn)
				myCommand.ExecuteNonQuery()
			Next
		Catch ex As Exception
			MessageBox.Show(ex.Message)
		Finally
			conn.Close()
			MsgBox("Saved !")
		End Try

 

[shohoku]

how about if i use this code??
but it still come out some error...
seems is sql problem

		Dim myCommand As New OdbcCommand
		Dim myData As New DataSet
		Dim SQL As String
		Dim myAdapter As OdbcDataAdapter
		Dim conn As OdbcConnection
		Dim acc As Data.DataRow
		Dim a As OdbcCommandBuilder


		conn = New OdbcConnection
		conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
					   "SERVER=localhost;" & _
					   "DATABASE=seas;" & _
					   "UID=root;" & _
					   "PASSWORD=;" & _
					   "OPTION=3;"


		For i = 0 To CheckedListBox1.Items.Count - 1

			If CheckedListBox1.GetItemChecked(i) = True Then
			    SQL = "insert into timetable3 values Empname ='" & CheckedListBox1.GetItemChecked(i) & "'"
				myCommand.Connection = conn
				myCommand.CommandText = SQL
				myAdapter.SelectCommand = myCommand
				myAdapter.Fill(myData)
			End If
		Next

 

[kulrom]

I'm not sure what are you trying to accomplish there as ones you are reading from DB and another time you want to update or insert DB ... really confusing if i may

Could you clarify the question?

Cheers

 

[shohoku]

i'm doing a scheduling app
there all two type of user(manager and employee)
now the manager want hold a group meeting which include only part of employees
so i need to do like the manager can choose the ppl who are need to attend the meeting
so i use a checkedlistbox that get all the employee name from database(this part no problem)

now my problem is: when manager choose the certain ppl(maybe 3 out of 10) and click save
This three ppl's timetable must add in the record(like meeting date,meeting time, EmpID,Empname)Employee_ID id primary key
if i only choose two name in the box....than only two ppll will be record

timetable's table
Employ_ID
Date
Starting_Time
Reason
Note

 

[shohoku]

________________
|O Lee |
|O Dee |
|O santi |
|O xue |
|O Gan | <checklistbox
--------------------

Now i choose LEE, santi, xue and i click save
in database timetable3 must add in the record
Employee_ID Date Starting_time Reason Note
1001 12-07-05 9:00AM G.meeting Don't be late
1003 12-07-05 9:00AM G.meeting Don't be late
1004 12-07-05 9:00AM G.meeting Don't be late

other ppl no effect.

the what i want to ask is when i choose this 3 ppl in checkedlistbox...
how can i save them into timetable3??
can u understand what i means?