Save all items of Listbox to access Database Table in VB.Net

Question

1.00/5 (1 vote)

See more:

Hello friends
My name is Sarfaraz. I am developing a project in VB.Net and MS Access 2007 where I am trying to save the details of a patient like name ,age, Ticket No and Lab investigations in the database.
I have two listboxes on the form one is having all the available investigations which can be done and the other is the selected investigations. When I click on the save button i want to save all the selected investigations to be stored in the database table.
Please help me in that. Actually i have done it in vb6 but no idea in VB.Net.
To load the available investigations from the database i use the following code which is working fine.

Sub loadinvestigations()
       Con.Open()
       Dim cmd As New OleDbCommand("select * from investigation", Con)
       Dim dr As OleDbDataReader = cmd.ExecuteReader
       While dr.Read
           ListBox1.Items.Add(dr(0).ToString.ToUpper)
       End While
       dr.Close()
       Con.Close()
   End Sub
Thank you

Posted 25-Apr-14 6:52am

sarfarazbhat

Updated 25-Oct-17 20:06pm

Richard C Bishop

v2

Add a Solution

Comments

[no name] 25-Apr-14 14:23pm

So what have you tried? What is the problem with the code that you have tried? You have code to get data why are you unable to write the code to insert into the database? After all it's just getting the items from your listbox and writing a query.

2 solutions

Solution 2

I have tried the following code and that worked correctly

<br />
 Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click<br />
        Dim Str As String<br />
        Try<br />
            Dim i As Integer<br />
            con.Open()<br />
            For i = 0 To ListBox2.Items.Count - 1<br />
                Str = "insert into login values('" + ListBox2.Items.Item(i) + "')"<br />
<br />
<br />
<br />
<br />
                Dim cmd As OleDbCommand = New OleDbCommand(Str, con)<br />
                cmd.ExecuteNonQuery()<br />
                '  Dim Dst As DataSet<br />
<br />
                Dim dad As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM login ORDER BY username", con)<br />
                ' Dad.Fill(Dst, "login")<br />
            Next<br />
            MsgBox("Record inserted successfully...")<br />
            ';   con.Close()<br />
        Catch ex As Exception<br />
            MessageBox.Show("Could Not Insert Record!!!")<br />
            MsgBox(ex.Message & " -  " & ex.Source)<br />
<br />
            con.Close()<br />
        End Try<br />
<br />
<br />
<br />
        Con.Close()<br />
<br />
    End Sub<br />

Posted 28-Apr-14 1:03am

sarfarazbhat

Comments

RDBurmon 30-Apr-14 9:19am

So Is this resolved your query? Do you have any other query?
I would like to help you.

sarfarazbhat 1-May-14 23:08pm

Thank you 
I more query, I am trying to save the records to database which is working just fine but at the same time i need to print that record (actually a receipt) I am writing the details in a text file But: 
I am not able to write the text at proper positions in that text file as a result printing is not properly aligned on a paper: 
I am using the code as:

<br><br><br><br>
 Dim file As System.IO.StreamWriter<br><br><br><br>
            file = My.Computer.FileSystem.OpenTextFileWriter("text.txt", False)<br><br><br><br>
            file.Write(lbldate.Text & vbTab & vbTab & vbTab)<br><br><br><br>
            file.Write(lbltime.Text)<br><br><br><br>
            file.WriteLine()<br><br><br><br>
            file.Write(txtreceiptno.Text & vbTab & vbTab & vbTab)<br><br><br><br>
            file.Write(txtopd.Text)<br><br><br><br>
            file.WriteLine()<br><br><br><br>
            file.Write(txtname.Text & vbTab & vbTab & vbTab)<br><br><br><br>
            file.WriteLine()<br><br><br><br>
 file.Write("______________________________________")<br><br>
            file.WriteLine()<br><br>
            file.Close()<br><br>
Dim p As New Process<br>
        Dim info As New ProcessStartInfo<br>
        info.FileName = "text.txt"<br>
        info.Verb = "print"<br>
        p.StartInfo = info<br>
        p.Start()<br>

Also when i print the receipt the name of the text file also gets printed. I do not know why.
Thank you

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Accepted Answer · 2014-04-26T04:40:00

Solution 1

Here is the answer :

VB.NET

Imports System.Data.OleDb
Public Class Form1

    Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click


        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PatientLabInvestigations.accdb;")

        Dim cmd As New OledbCommand

        Dim var1 As String

        con.Open()

        cmd.Connection = con

        For l_index As Integer = 0 To lstlabinvestigations.Items.Count - 1
            var1 = String.Concat("'", txtpatientname.Text.ToString(), "','", txtpatintage.Text.ToString(), "','", txtpatientticketno.Text.ToString(), "','", CStr(lstlabinvestigations.Items(l_index)).ToString(), "'")
            cmd.CommandText = "INSERT INTO tblLabInvestigations(PatientName,Age,TicketNo,LabInvestigation) VALUES(" + var1 + ")"
            cmd.ExecuteNonQuery()
        Next
        MessageBox.Show("Record saved successfully")
    End Sub

End Class

Hope this helps. Try and if does work then accept and vote this answer

Posted 26-Apr-14 4:40am

RDBurmon

Comments

Mike Meinz 26-Apr-14 12:44pm

Please use OLEDBParameter objects to pass values to the SQL statement. Using a concatenated string as demonstrated in Solution 1 will allow an SQL Injection Attack. Also, If the patient's name contains an apostrophe (e.g. James O'Toole) your sample code will fail. Use of OLEDBParameter objects would prevent that issue.

sarfarazbhat 28-Apr-14 8:48am

Yes if I try to insert name as Bhat's it is giving me an error.Missing operator in query.

Mike Meinz 28-Apr-14 9:02am

Here is an example of how to use parameterized SQL statements with an MS Access database. Please note that the order of the parameters in the collection must match the order of the parameters used in the SQL statement.

Microsoft documentation: OleDbParameter Class and OldDbCommand.Parameters Property

sarfarazbhat 28-Apr-14 14:21pm

Thanks for your kind help.

RDBurmon 26-Apr-14 23:10pm

I don't think we can use SQLParamer with Access database. Can we?

sarfarazbhat 28-Apr-14 8:48am

Yes if I try to insert name as Bhat's it is giving me an error.Missing operator in query:::

Mike Meinz 28-Apr-14 9:11am

Thank you for your reply. I overlooked the fact that it was an MS Access database. With MS Access, parameters are passed using the OleDbParameter object.

sarfarazbhat 29-Apr-14 23:59pm

I have figured the answer of select the rate of the individual investigations and save the rate for the corresponding investigation. Here is my code


 Con.Open()
            For l_index = 0 To ListBox2.Items.Count - 1

                query = ("select  Investigation.rate " & _
                        "from Investigation WHERE (((Investigation.Investigation) = '" & ListBox2.Items(l_index) & "'));")

                Dim cmd2 As New OleDbCommand(query, Con)
                'Con.Open()

                Dim reader As OleDbDataReader = cmd2.ExecuteReader

                reader.Read()
                'Console.WriteLine(reader(0))
                txtrate.Text = reader.GetString(0)


                reader.Close()
                'Con.Close()





                ' Con.Close()

                'End If

                Dim SqlString As String = "Insert Into patients (Receipt_No, OPD_MRD,Patientname,Investigation,Receipt_date,Receipt_time,rate) Values (?,?,?,?,?,?,?)"
                Dim cmd As OleDbCommand = New OleDbCommand(SqlString, Con)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("Receipt_No", txtreceiptno.Text.Trim())
                cmd.Parameters.AddWithValue("OPD_MRD", txtopd.Text.Trim())
                cmd.Parameters.AddWithValue("Patientname", txtname.Text.Trim())
                cmd.Parameters.AddWithValue("Investigation", CStr(ListBox2.Items(l_index)).ToString())
                cmd.Parameters.AddWithValue("Receipt_date", lbldate.Text)
                cmd.Parameters.AddWithValue("Receipt_time", lbltime.Text)
                cmd.Parameters.AddWithValue("rate", txtrate.Text.Trim())
                'Con.Open()
                cmd.ExecuteNonQuery()

            Next
            Con.Close()

But it is taking a very long time to perform the task and my system is going in a busy mode for almost 10 seconds. Is there anything wrong in this code Please have a look
Thank you

Mike Meinz 30-Apr-14 5:57am

Ensure that there is an index on this column: Investigation.Investigation

sarfarazbhat 28-Apr-14 9:46am

Please guide me as how to select the rate of the individual investigations and save the rate for the corresponding investigation.
My code is like


 For l_index As Integer = 0 To ListBox2.Items.Count - 1
                query = "select  Investigation.rate " & _
                        "from Investigation WHERE (((Investigation.Investigation) = '" & ListBox2.Items(i) & "'));"
                cmd = New OleDbCommand(query, Con)
                cmd.CommandType = CommandType.Text
/here is the problem i do not know how to get the rate

please help
Thank you

sarfarazbhat 28-Apr-14 14:24pm

What is the VB.Net equivalent of VB6 code of the following:


For i = 0 To List2.ListCount - 1
sql = "patients"
query = "select  Investigation.rate " & _
"from Investigation WHERE (((Investigation.Investigation) = '" & List2.List(i) & "'));"

rs2.Open query, Con, adOpenDynamic
If Not rs2.EOF Then



'Text2.Text = rs!Investigation
Text5.Text = rs2!Rate


End If
'rs2.Close

Set rs = New ADODB.Recordset

rs.Open sql, Con, adOpenDynamic, adLockOptimistic

' Dim X As Long
 'For X = 0 To List2.ListCount - 1
With rs
    .AddNew
        !Receipt_No = Trim(text1.Text)
        !OPD_MRD = Trim(text2.Text)
        !Patientname = Trim(Text3.Text)

     !Investigation = List2.List(i)


        !Receipt_date = Label6.Caption
        !Receipt_time = Label7.Caption
        !Rate = Trim(Text5.Text)

    .Update

End With
rs2.Close
rs.Close
    Next

Actually I need to save the rate of all the items of a listbox. I am able to do it in vb6 as shown above but not in VB.net
Please guide me in this
Thank you