save listview data to database
save listview data to database
save listview data to database
save listview data to database
save listview data to database
save listview data to database save listview data to database save listview data to database save listview data to database save listview data to database save listview data to database save listview data to database save listview data to database
save listview data to database save listview data to database
save listview data to database
Go Back  Xtreme Visual Basic Talk > > > save listview data to database


Reply
 
Thread Tools Display Modes
  #1  
Old 11-16-2011, 07:05 PM
sigridish sigridish is offline
Newcomer
 
Join Date: Nov 2011
Posts: 14
Unhappy save listview data to database


hello all! i have here a code to save the data in my listview to my database but its not working. kindly help me please? its not saving in my database

Code:
Dim lvitem As Object
        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!")
Reply With Quote
  #2  
Old 11-16-2011, 08:16 PM
jwshepherd jwshepherd is offline
Freshman
 
Join Date: May 2009
Location: West Texas
Posts: 45
Default

What is the error you are getting, that would help tremendously.

Most if the time , an error will occur before he sql call in the initialization of a variable or in the sql string itself.

i would start by changing the variables to include values when initialized:
Code:
Dim iCount As Integer=0
        Dim iLoop As Integer=0
Then check and make sure that all of the items in the database are infact strings and not numbers. Most databases are designed to reference numbers for repeated data like salesgroup and customertype should each have their own table even as simple as
Code:
Salesgroup
____________
id  | type_name
0   | External
1   | New Mexico
Then in your [wer] table you would use the id of the atual salesgroup. This is what makes databases efficient, because instead of spelling out and compressing 'New Mexico' a thousand times in a database, it just compresses a single digit that references the name.

Also, you do not have an option in there to check if the name already exists in the database, to use an update query instead of insert.

Finally put a try catch loop around the entire sub and see what errors are generated if any are caught.
Reply With Quote
  #3  
Old 11-16-2011, 08:19 PM
sigridish sigridish is offline
Newcomer
 
Join Date: Nov 2011
Posts: 14
Default

all of them anre varchar. im using mysql query browser. there is no error. its just that the data is not saved in my database
Reply With Quote
  #4  
Old 11-16-2011, 08:21 PM
jwshepherd jwshepherd is offline
Freshman
 
Join Date: May 2009
Location: West Texas
Posts: 45
Default

mysql is very particular about casing. to mysql CustomerType and customertype are two completely different fields.

Do you see the message at the end ? "Record saved."?
Reply With Quote
  #5  
Old 11-16-2011, 08:26 PM
sigridish sigridish is offline
Newcomer
 
Join Date: Nov 2011
Posts: 14
Default

yes i do. the message box "record saved" appears
Reply With Quote
  #6  
Old 11-16-2011, 08:26 PM
jwshepherd jwshepherd is offline
Freshman
 
Join Date: May 2009
Location: West Texas
Posts: 45
Default

wait. it Looks like you never open the mysql connection , you just define it.
Reply With Quote
  #7  
Old 11-16-2011, 08:28 PM
jwshepherd jwshepherd is offline
Freshman
 
Join Date: May 2009
Location: West Texas
Posts: 45
Default

Reply With Quote
  #8  
Old 11-16-2011, 08:36 PM
sigridish sigridish is offline
Newcomer
 
Join Date: Nov 2011
Posts: 14
Default

i placed query3.connection.open after the query3.connection and this error appeared

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Reply With Quote
  #9  
Old 11-16-2011, 09:37 PM
jwshepherd jwshepherd is offline
Freshman
 
Join Date: May 2009
Location: West Texas
Posts: 45
Default

Have you installed the mysql connector for .Net?
Reply With Quote
  #10  
Old 11-16-2011, 09:46 PM
sigridish sigridish is offline
Newcomer
 
Join Date: Nov 2011
Posts: 14
Default

yes i already have mysql connector. my version is 6.3.7
Reply With Quote
  #11  
Old 11-16-2011, 09:59 PM
jwshepherd jwshepherd is offline
Freshman
 
Join Date: May 2009
Location: West Texas
Posts: 45
Default

do you have the mysql import statement .
Code:
Imports mysql.data.mysqlclient
Reply With Quote
  #12  
Old 11-16-2011, 10:12 PM
sigridish sigridish is offline
Newcomer
 
Join Date: Nov 2011
Posts: 14
Default

yes sir i have it already.
Reply With Quote
  #13  
Old 11-16-2011, 10:21 PM
jwshepherd jwshepherd is offline
Freshman
 
Join Date: May 2009
Location: West Texas
Posts: 45
Default

do you also have the
Code:
imports system.data.sqlclient
Reply With Quote
  #14  
Old 11-16-2011, 10:29 PM
sigridish sigridish is offline
Newcomer
 
Join Date: Nov 2011
Posts: 14
Default

i also have it sir. i'll just post the whole code. what im doing sir is that my program has a browse button to import excel file in my listview. then save it to database


Code:
Imports System.Data.OleDb
Imports System.Runtime.InteropServices
Imports Microsoft.Office.Interop
'Imports Excel
Imports System.ComponentModel
Imports System.Drawing.Point
Imports MySql.Data.MySqlClient
Imports System.Data.SqlClient

Code:
Public Class form1

    Public Class listview_NoFlicker
        Inherits ListView
        Public Sub New()
            MyBase.New()
            Me.DoubleBuffered = True
        End Sub
    End Class
    Public Const WM_NCLBUTTONDBLCLK As Integer = &HA3
    Protected Overrides Sub WndProc(ByRef m As System.Windows.Forms.Message)
        If m.Msg = WM_NCLBUTTONDBLCLK Then Return
        MyBase.WndProc(m)
    End Sub
    Public path As String
    Dim connString As String
    Dim conn As OleDbConnection
    Dim tablename As String
    Dim dt As New DataTable
    Dim names As DataTable
    Dim selectedTable As String
    Dim query As String = ""
    Dim lvExcel As New listview_NoFlicker
    Dim template As DataTable
    Dim team As String = ""
    Dim monthArray() As String
    Dim month As String
    Dim lvDataDB As New listview_NoFlicker
    Dim teamDB As String = ""
    Dim tableNameDB As New DataTable
    Dim searchDT As New DataTable
    Private Sub clearAll()
        viewdata.Enabled = False
        table.Enabled = False
        status.Text = "-------------------"
        table.Items.Clear()
        table.Text = ""
        lvExcel.Clear()
    End Sub


browse button

Code:
 Private Sub browse1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles browse.Click
        clearAll()
        Dim dlg As New OpenFileDialog
        dlg.Filter = "Excel Worksheets|*.xls; *.xlsx"

        If dlg.ShowDialog = DialogResult.OK Then
            path = dlg.FileName
            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties='Excel 12.0;HDR=YES'"
            conn = New OleDbConnection(connString)
            status.Text = "Please Wait"
            status.Text = "Fetching"

            Dim bgTableName As New BackgroundWorker
            AddHandler bgTableName.DoWork, AddressOf bgwTableName_DoWork
            AddHandler bgTableName.RunWorkerCompleted, AddressOf bgwTableName_RunWorkerCompleted
            bgTableName.RunWorkerAsync()
        End If

    End Sub
view data button
Code:
Private Sub view_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles viewdata.Click
        lvExcel.Clear()
        selectedTable = ""
        lvExcel.Clear()
        status.Text = "Gathering Data"
        status.Refresh()
        browse.Enabled = False
        viewdata.Enabled = False
        table.Enabled = False
        selectedTable = table.SelectedItem.ToString
        Dim dbView As New BackgroundWorker
        AddHandler dbView.DoWork, AddressOf view
        dbView.RunWorkerAsync()
    End Sub

save button
Code:
 Dim lvitem As Object
        Dim iCount As Integer
        Dim iLoop As Integer
        Dim query3 = New MySqlCommand
        query3.Connection = New MySqlConnection("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!")
Reply With Quote
  #15  
Old 11-16-2011, 11:08 PM
jwshepherd jwshepherd is offline
Freshman
 
Join Date: May 2009
Location: West Texas
Posts: 45
Default

replace the Save Button with this. I think you might have to add as password = Line in the connection string even if it is blank
Code:
 Dim lvitem As Object
        Dim iCount As Integer
        Dim iLoop As Integer
	Dim connstring as string ="SERVER=localhost;UID=root;DATABASE=test"     
	DIM conn as new MySqlConnection(connstring)
	dim cmd as new MySqlCoammand() 
	conn.open
            iCount = lvLogs.Items.Count()
        If Not lvLogs.Items.Count = 0 Then
            Do Until iLoop = lvLogs.Items.Count
                lvitem = lvLogs.Items.Item(iLoop)
                With lvitem
		
                   cmd.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 & "')"
                    cmd.ExecuteNonQuery()

                End With
                iLoop = iLoop + 1
                lvitem = Nothing
            Loop
	conn.close

        End If
        MessageBox.Show("Record Saved!")
Reply With Quote
  #16  
Old 11-17-2011, 05:43 PM
sigridish sigridish is offline
Newcomer
 
Join Date: Nov 2011
Posts: 14
Default

good day to you sir! i tried the code that you gave me. its still the same. message box pops out but no data is saved in my database
Reply With Quote
  #17  
Old 11-18-2011, 09:42 AM
jwshepherd jwshepherd is offline
Freshman
 
Join Date: May 2009
Location: West Texas
Posts: 45
Default

At this point it will probably be easiest to just attach the project. There is something in the connection string or the opening of the database connection that is incorrect.
Reply With Quote
  #18  
Old 11-22-2011, 07:19 PM
sigridish sigridish is offline
Newcomer
 
Join Date: Nov 2011
Posts: 14
Default

here it is sir
Attached Files
File Type: zip browse.zip (163.1 KB, 6 views)
Reply With Quote
  #19  
Old 12-30-2011, 11:23 PM
cigarette cigarette is offline
Newcomer
 
Join Date: Mar 2010
Location: Pinas
Posts: 8
Default

Declare your connection for db
Code:
db.Open("Provider=microsoft.jet.oledb.4.0;data source=YOUR DATABASE LOCATION;OLE DB Services = 0")
Then,
Code:
Dim i As Integer
                For i = 0 To ListView1.Items.Count - 1
                    rs1.Open("YOUR TABLE NAME", db, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)
                    rs1.AddNew()
                    With rs1
                        .Fields("fieldname1").Value = ListView1.Items.Item(i).Text
                        .Fields("fieldname2").Value = ListView1.Items.Item(i).SubItems(1).Text
                        .Fields("fieldname3").Value = ListView1.Items.Item(i).SubItems(2).Text
                    End With
                    rs1.Update()
                    rs1.MoveNext()
                    rs1.Close()
                Next
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
save listview data to database
save listview data to database
save listview data to database save listview data to database
save listview data to database
save listview data to database
save listview data to database save listview data to database save listview data to database save listview data to database save listview data to database save listview data to database save listview data to database
save listview data to database
save listview data to database
 
save listview data to database
save listview data to database
 
-->