-
Feb 13th, 2014, 04:41 PM
#1
Thread Starter
Member
[RESOLVED] insert data from listview into ms access database vb.net
i am trying to insert my listview items into my MS access database.
here is the code:
Code:
Private Sub btnNewSMS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewSMS.Click
If cn.State = ConnectionState.Closed Then
cn.Open()
For i = 0 To ListView2.Items.Count
Dim sqlQuery As String = "INSERT INTO InboxTable " & "(Contact_Name,Contact_Number,DateAndTime,Message) " & "VALUES("
With ListView2.Items.Item(i)
sqlQuery = sqlQuery & Val(.SubItems(0)) & "," 'ContactName
sqlQuery = sqlQuery & Val(.SubItems(1)) & "," 'ContactNumber
sqlQuery = sqlQuery & Val(.SubItems(2)) & "," 'Date and Time
sqlQuery = sqlQuery & Val(.SubItems(3)) 'Messages
sqlQuery = sqlQuery & ")"
Dim cmd As New OleDbCommand
With cmd
.CommandText = sqlQuery
.Connection = cn
.ExecuteNonQuery()
End With
MsgBox("Messages Saved")
End With
Next
End If
cn.Close()
End Sub
my error is: Argument 'Expression' cannot be converted to type 'ListViewSubItem'
my problem is in inserting the values. Please Help me... thanks everyone - ChrisTemper
-
Feb 13th, 2014, 06:20 PM
#2
Re: insert data from listview into ms access database vb.net
That code is pretty terrible when it comes to building the SQL code. Firstly, why are you using Val? Do you know what it does? It tries to convert a value to a Double. Do you really want to use a Double as the contact name? If you want the text displayed a subitem then you use the subitem's own Text property. There are several other issues there too, e.g. you have not wrapped the text literals in single quotes or the date/time in hash symbols. You should be using parameters to insert values into your SQL code. To learn why and how, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET. Just note that, if you use AddWithValue to create a parameter then the value must be the correct type, which means converting the date/time from your ListView into an actual DateTime value.
There are numerous other issues too. If you were going to use ExecuteNonQuery then you would just create one command with parameters and then set the values of those parameters each time, not keep creating a new command every time. You wouldn't use ExecuteNonQuery to insert multiple records though. You'd populate a DataTable and then use a data adapter to save the lot in one go. If you're going to use a DataTable though, it should already be populated. If you really must use a ListView then you can assign each DataRow from the DataTable to the Tag of the corresponding item. Better to not use a ListView at all though, but rather use a DataGridView and bind the DataTable in the first place.
-
Feb 13th, 2014, 06:31 PM
#3
Thread Starter
Member
Re: insert data from listview into ms access database vb.net
@jmcilhinney sorry for having a terrible code .. i'm still learning
i changed my code and the error turns into this (Value of '0' is not valid for 'index')
Code:
Public newConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Application.Info.DirectoryPath.ToString() & "\BackUp\Inbox.Accdb;Persist Security Info=False;"
Private Sub btnNewSMS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewSMS.Click
cn.ConnectionString = newConn
If cn.State = ConnectionState.Closed Then
cn.Open()
For x = 0 To ListView2.Items.Count - 1
Dim sqlQuery As String = "INSERT INTO InboxTable (Contact_Name,Contact_Number,DateAndTime,Message) Values ('" & ListView2.Items(x).SubItems(0).Text & "', '" & ListView1.Items(x).SubItems(1).Text & "','" & ListView1.Items(x).SubItems(2).Text & "','" & ListView1.Items(x).SubItems(3).Text & ")"
Dim cmd As New OleDbCommand
With cmd
.CommandText = sqlQuery
.Connection = cn
.ExecuteNonQuery()
End With
MsgBox("Messages Saved")
ListView2.Items.Clear()
'End With
Next
End If
cn.Close()
End Sub
thanks... please help me..
-
Feb 13th, 2014, 07:08 PM
#4
Re: insert data from listview into ms access database vb.net
You don't have to apologise for having terrible code. We all did when we started out. Beginners don't know any better. We do have a responsibility to improve it when provided with additional knowledge though.
You have improved that slightly but only slightly. There's still a long way to go. The immediate issue is that you are using both ListView2 and ListView1 in that line that builds the SQL code. Presumably you should only be using ListView2. We can all make mistakes but the harder your code is to read, the more likely you will make them. If you want to loop through the Items of ListView2 then you should be using a For Each loop, not a For loop. If you had used the proper loop for the situation then you couldn't have made that mistake.
Further, I see no parameters being used there in your SQL. If you're not using parameters then you're doing it wrong. I've told you where you can find information on doing it right.
There are numerous other things I'd change about that code but most of them I mentioned earlier. If you care then you can see them in my previous post. The one last thing I will say is that this:
Code:
Public newConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Application.Info.DirectoryPath.ToString() & "\BackUp\Inbox.Accdb;Persist Security Info=False;"
should be this:
Code:
Public newConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\BackUp\Inbox.Accdb;Persist Security Info=False;"
-
Feb 13th, 2014, 09:38 PM
#5
Thread Starter
Member
Re: insert data from listview into ms access database vb.net
@jmcilhinney.. thank you... its working now
final code:
Code:
Public newConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\BackUp\Inbox.Accdb;Persist Security Info=False;"
Private Sub btnNewSMS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewSMS.Click
cn.ConnectionString = newConn
If cn.State = ConnectionState.Closed Then
cn.Open()
For x = 0 To ListView2.Items.Count - 1
Dim sqlQuery As String = "INSERT INTO InboxTable (Contact_Name,Contact_Number,DateAndTime,Message) Values ('" & ListView2.Items(x).SubItems(0).Text & "', '" & ListView2.Items(x).SubItems(1).Text & "','" & ListView2.Items(x).SubItems(2).Text & "','" & ListView2.Items(x).SubItems(3).Text & "')"
Dim cmd As New OleDbCommand
With cmd
.CommandText = sqlQuery
.Connection = cn
.ExecuteNonQuery()
End With
MsgBox("Messages Saved")
ListView2.Items.Clear()
'End With
Next
End If
cn.Close()
End Sub
but in .Subitems(3) have a problem.. it's saving but not the entire text. In listview2 subitems(3) it has:
lets say:
(if you'll ask why it has (") its because it is from a SMS and my listview is my inbox
and then I try this:
Code:
richtextbox1.text = listview2.FocusedItem.Subitem(3).text
'the result in richtextbox is this
"
Example
'meaning that the (") is above the text
so everytime I'm saving my items in database from listview the message column contain only this (")
How can I remove the string above?.. or in the listview
here is my code:
Code:
Public Sub newrecord()
Dim lineoftext As String
Dim i As Integer
Dim arytextfile() As String
lineoftext = inputData.ToString
arytextfile = Split(lineoftext, "+CMT", , CompareMethod.Text)
For i = 1 To UBound(arytextfile)
Dim input As String = arytextfile(i)
Dim result() As String
Dim pattern As String = "(:)|(,"")|(,"")"
result = Regex.Split(input, pattern)
Dim lvi As New ListViewItem
Dim concat() As String
With ListView2.Items.Add("null")
'for number
Dim my_string, position As String
my_string = result(2)
position = my_string.Length - 2
my_string = my_string.Remove(position, 2)
.SubItems.Add(my_string.Remove(1, 1))
'for Date and time
concat = New String() {result(4) & result(5) & result(6) & result(7) & result(8).Substring(0, 2)}
.SubItems.AddRange(concat)
'for messages
Dim lineoftexts As String
Dim arytextfiles() As String
lineoftexts = arytextfile(i)
arytextfiles = Split(lineoftexts, "32", , CompareMethod.Text)
.SubItems.Add(arytextfiles(i))
End With
Next
End Sub
this how the message look like and then I split them by that code
+CMT : "+63xxxxxxxxxx" , , "06/26/11,23:20:00+40"
Example
(Sorry for my question.. but the only problem is (")-quote above.. please sir help me that thanks)
-
Feb 13th, 2014, 10:12 PM
#6
Re: insert data from listview into ms access database vb.net
OK, now you should apologise. I've already told you twice to use parameters. You still aren't and you still ask for help on how to fix your issues. I'm not big on repeating myself. If you choose to ignore advice then that's your prerogative but I'm not going to keep providing more in that case.
-
Feb 13th, 2014, 10:56 PM
#7
Thread Starter
Member
Re: insert data from listview into ms access database vb.net
now I don't have to apologise. But thanks anyway... The first Problem is solved because of this "The immediate issue is that you are using both ListView2 and ListView1"
thanks to that... You don't need to get angry it is just I don't understand your explanation... thanks
-
Feb 13th, 2014, 11:42 PM
#8
Re: insert data from listview into ms access database vb.net
Originally Posted by chrisTemper
it is just I don't understand your explanation
Have you followed the link I directed you to?
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|