saving data in mysql query browser using listview

[sigridish]

here is my code

Dim lvitem
        Dim iCount As Integer
        Dim iLoop As Integer
        Dim query3 = New SqlCommand
        query3.Connection = New SqlConnection("SERVER=localhost;UID=root;DATABASE=test")
        iCount = lvLogs.Items.Count()
        If Not lvLogs.Items.Count = 0 Then
            Do Until iLoop = lvLogs.Items.Count

                LvItem = lvLogs.Items.Item(iLoop)
                With LvItem
                    query3.CommandText = "insert into wer(CustomerName,SalesGroup,CustomerType,TypeOfIndustry,RM,SeniorRM) values('" & .SubItems(0).Text & "','" & .SubItems(1).Text & "','" & .SubItems(2).Text & "','" & .SubItems(3).Text & "','" & .SubItems(4).Text & "','" & .SubItems(5).Text & "')"
                    query3.ExecuteNonQuery()
                End With

                iLoop = iLoop + 1
                LvItem = Nothing
            Loop
        End If
        MessageBox.Show("Record Saved!")

but it does not save on my database help me please? :(:'(

[Reverend Jim]

You have to put some effort into the question

1. What is your program supposed to do
2. What is the actual (observed) result of running your code
3. What error message (if any) do you get
4. What is your database structure
5. What fields (data) are you trying to insert

You won't tell us diddly about the environment, the data, the database, yet you expect us to magically tell you what is wrong?

[sigridish]

You have to put some effort into the question

1. What is your program supposed to do
2. What is the actual (observed) result of running your code
3. What error message (if any) do you get
4. What is your database structure
5. What fields (data) are you trying to insert

You won't tell us diddly about the environment, the data, the database, yet you expect us to magically tell you what is wrong?

i want to save the data in my list view to my database. there is no error in my code. whenever i hit save button, message box comes out "record saved!" but whenever i check my database, the record is not saved.

[Reverend Jim]

Reread questions 4 & 5.

[sigridish]

what do u mean by database structure?

im trying to insert all the data in my listview

[Reverend Jim]

I want to create the database on my SQL server. To do that I have to know the structure of the table you are trying to insert into (the field names and types, etc). In order to actually do the insert I have to know what data you are trying to insert. If I know that then I can execute your code on my machine and see if it works here. If it does not then I can try to see what is wrong. If it does then I can ask you more questions to try to determine the source of the problem. But I can't do any of this without some details and if you can't go to the trouble of providing them then we can't help you. In any case, it is now after 11:00 pm so it's time for lights out. I'll check back again in the morning.

[sigridish]

i will attach my file here sir and give you the structure

CustomerName varchar 100
SalesGroup varchar 100
CustomerType varchar 100
TypeOfIndustry varchar 100
RM varchar 100
SeniorRM varchar 100

thats the structure sir.

im using mysql query browser 1.1

[Reverend Jim]

OK. I have the project loaded up and I adjusted for the fact that I am running MS SQL instead od MySQL (minor name changes - not important). All I need now is a copy of the Excel spreadsheet file you are using as your data source.

However, I want to make a few (hopefully constructive) comments while I am waiting.

Your program should ALWAYS include a header that describes what your program does.

You should name your controls to reflect their functions. This makes it easier to associate code with controls when looking at your code later (or when someone else is looking at your code). For example, name your buttons btnBrowse, btnView and btnSave instead of Button1, etc.

Each Sub/Function should have a small header describing what each does.

Years back (many many years) when I was taking my computer science degree, I was a marker for a professor. Anyone who handed in a program without a minimal amount of inline documentation automatically got a zero on the assignment. The language was APL. You are likely unfamiliar with it but trust me - no documentation means essentially undecipherable code (it's a language that I think all programmers should be exposed to).

And my personal pet-peeve - code should be pleasing to the eye as well as functional. YouwouldfinditdifficulttoreadmycommentsifIdidn'tincludesomewhitespace. Get the point? If English is easier to read when formatted and spaced properly then imagine how much easier it will be to read code done the same way.

[sigridish]

thank you sir! i have attached the copy of my excel file

[Reverend Jim]

As a side note - the last time someone called me sir it was preceded by "Please step out of the car". Ok. Just a joke but "Jim" will do just fine.

[Reverend Jim]

If I might ask another question - why are you using BackgroundWorker? Is it a requirement that you run this process async?

[sigridish]

:) thanks jim. are you american by the way?

[sigridish]

honestly, it was given to me by my friend. im really not good in analyzing codes.

[Reverend Jim]

Canadian, actually (Winnipeg).

[Reverend Jim]

This does it for me. By the way, you will have to change the column in the spreadsheet with the header "SENIOR_RM" to "SENIORRM" to match the database column.

Public Class form1

    Private Sub btnLoadData_Click(sender As System.Object, e As System.EventArgs) Handles btnLoadData.Click

        Dim ExcelFile As String = "d:\temp\2011-11-08_CBG&SMECustomerList_perTeam"
        Dim sqlserver As String = "JIM-PC\SQLEXPRESS"
        Dim database As String = "mydb"

        Dim conxls As New ADODB.Connection
        Dim recxls As New ADODB.Recordset

        Dim consql As New ADODB.Connection

        Dim query As String

        conxls.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ExcelFile & ";Extended Properties='Excel 8.0;HDR=YES'")
        consql.Open("Driver={SQL Server};Server=" & sqlserver & ";Database=" & database & ";Trusted_Connection=yes;")

        recxls.Open("select * from [CDSM_A$]", conxls, ADODB.CursorTypeEnum.adOpenStatic)

        Do Until recxls.EOF

            query = MakeInsert("wer", recxls)
            'Debug.WriteLine(query)
            consql.Execute(query)
            recxls.MoveNext()

        Loop

        recxls.Close()
        conxls.Close()
        consql.Close()

    End Sub

    Private Function MakeInsert(table As String, rec As ADODB.Recordset) As String

        Dim fields As String = ""
        Dim values As String = ""

        For i As Integer = 0 To rec.Fields.Count - 1
            fields &= "," & rec.Fields(i).Name
            values &= "," & "'" & rec.Fields(i).Value & "'"
        Next

        Return "insert into " & table & " (" & fields.Substring(1) & ") values(" & values.Substring(1) & ")"

    End Function

End Class

[sigridish]

hi jim! thanks for the help. im a filipina. so i just woke up. i'll try the code later and get back to you. by the way, what database did you use here?

[Reverend Jim]

Glad to help. I like to make the disclaimer that I offer "a" solution, not necessarily the "only" or "best". I have found that in most cases, if it works and it is simple then it is probably good.

I use Microsoft SQL Server Express (free). I prefer to use the ADODB interface. It's what I first learned. It was very easy to coonvert code from one data source (for example, Access) to another (like SQL Server). Also, a lot of the apps I wrote for our system were maintenance and data pumping apps that did not require a GUI so I wrote them in vbScript (there are many reasons why that was better than a compiled app). ADO is available for VB (compiled) as well as vbScript (interpreted). I also prefer it because it is a simpler interface. You can use the ADO as long as you change the connection string.

[sigridish]

will i still use the same form that i gave you? uhm can you attach the file that you made jim if its okay with you

[Reverend Jim]

There is nothing else to include. The GUI is just a form with a button on it and the only code is what I already posted. Keep in mind that my code only loads data from the first worksheet in the workbook. The file you posted has data on more than one sheet so you could put he code into a loop - one iteration for each sheet in the workbook.

[sigridish]

so what would the design look like? can you just edit the save button? because that's the only problem i'm facing right now. :'(

[Reverend Jim]

You already have most of it. You need a button to browse for the Excel file and you need a button to execute the query to load the data from the spreadsheet into the database. In a production environment you would also need to check for errors such as duplicate keys. I presume those errors would go into the listview (log) control which you also have. And you have a button to clear the log. Just a little fiddling with the code and (as I suggested) naming your procedures and variables, and of course adding comments.

[sigridish]

uhm so i would just need 2 button? one for browse and one for saving. would there still be a combo box for choosing a sheet in the file?

[Reverend Jim]

If you want to load data from selected worksheets then yes fot the combobox. The following code will return an array containing the names of all worksheets in a workbook.

Private Function GetWorkSheetNames(ByVal ExcelFile As String) As String()
     
    Dim names As String = ""
    Dim xls As New Excel.Application
    xls.Workbooks.Open(ExcelFile)
     
    For Each sheet As Excel.Worksheet In xls.ActiveWorkbook.Worksheets
        names = names & vbCr & sheet.Name
    Next
     
    xls.Quit()
     
    Return names.Substring(1).Split(vbCr)
     
End Function

[sigridish]

hi jim how are you? another question. am i going to use a module here? if yes, what will i put in my module? and the codes that you gave me, is that to be placed in form or in module if ever? thanks

[Reverend Jim]

I'm not a VB developer and I have never written a complex app in VB. I develop for my own use (book collector app and cryptoquote tool among others) or for my own curiosity, or to try to help out others who are way down on the learning curve. As such, I have never found the need to create modules or even classes (other than the main one required by VB) so I am far from an expert on VB. However, I have been a professional programmer for 35 years so I have some experience in writing code and developing systems of connected apps. Having said that I can only add that it is entirely up to you as to how you use the code in your application. The GetWorkSheetNames function might just as well go into a separate module as it may be of use in other programs. I hope I have been of some assistance. Us retirees like to feel useful from time to time.

[sigridish]

can you just give me the one you created including the forms? please? can you attach it here?

[Reverend Jim]

Can do. Hope it helps.

[sigridish]

thanks jim. if i might ask, what version of vb.net did you use?

[Reverend Jim]

VB 2010 Professional

[sigridish]

thanks. im using 2008 thats why i can't open the file. :|