Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

visual basic update record while retaining old data

Posted on 2003-10-21
12
410 Views
Last Modified: 2010-05-18
I have a listbox that I am wanting to add to a database field

I want the data in the listbox to look like this:

Betterbasic
Visual basic
C#

the listbox data looks like this:
Betterbasic
Visual basic
C#


A vbcrlf will have to be added to make the text go to the next line in the access database.

this is what I have but it is not retaining the data in that field.

For i = 0 To lstWindows3.ListCount - 1
  If TheCount > 0 Then
   'it exists so update if necessary
 Else
   
  Dim objCon As New ADODB.Connection, s As String, rs As Recordset
objCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\windows\data.mdb; Jet OLEDB:Database Password="
  s = "update tablename set Field1= '" & lstWindows3.List(i) & "'  where field1= '" & txtname & "'"

objCon.Execute s

Set objCon = Nothing

 End If
 
Next


if I do this
 s = "update tablename set Field1= '" & lstWindows3.List(i) & vbcrlf & lstWindows3.List(i-1) &"'  where field1= '" & txtname & "'"


this does this
Visual basic
C#

This would not work. Can anyone figure this out please
0
Comment
Question by:bman9111
  • 6
  • 5
12 Comments
 
LVL 2

Expert Comment

by:mingz
ID: 9596649
There may be somthing wrong with this line:
objCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\windows\data.mdb; Jet OLEDB:Database Password="

can you try adding a ODBC record for C:\windows\data.mdb, so that the data source will be like "myData"

etc.:
objCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "myData; Jet OLEDB:Database Password="
0
 
LVL 8

Author Comment

by:bman9111
ID: 9598209
there is nothing wrong with the connection. The problem resides on this line:

 s = "update tablename set Field1= '" & lstWindows3.List(i) & "'  where field1= '" & txtname & "'"


I need a way that it just keeps adding data to an existed field, right now it is just overwrite it with whatever the lstwindows3.list is.


0
 
LVL 4

Expert Comment

by:Sandeepk1999
ID: 9601693
VB.NET
s = "update tablename set Field1= '" & lstWindows3.List(i) & vbcrlf & lstWindows3.List(i-1) &"'  where field1= '" & txtname & "'"

C#
s = "update tablename set Field1= '" + lstWindows3.List(i) + vbcrlf & lstWindows3.List(i-1) + "'  where field1= '" + txtname + "'"
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 8

Author Comment

by:bman9111
ID: 9601838
if I do this
 s = "update tablename set Field1= '" & lstWindows3.List(i) & vbcrlf & lstWindows3.List(i-1) &"'  where field1= '" & txtname & "'"


this outputs this:
Visual basic
C#

please look at my question:

In case it is confusing. The
Betterbasic
Visual basic
C#

is data in a listbox. And the way that was stated and the way I already had it makes the database field look like this


Visual basic
C#

I am writing this in vb 6

sorry I guess I should of used different data that is in my listbox.



0
 
LVL 2

Expert Comment

by:mingz
ID: 9604696
if you run this in a for loop, this part :

where field = '" & txtname &"'"

always update the same row in the database therefore only one row is updated everytime...
whats "txtname" by the way?

Can you tell us what result you want so we can help you :)
0
 
LVL 8

Author Comment

by:bman9111
ID: 9608695
ok txtname will equal the record it needs to grab

meaning primary key record

let's say the data in the actual database looks like this

field0      field1
1             today


I want to add the data in the listbox = (lstWindows3.List) to field1

when I use this command

s = "update tablename set Field1= '" & lstWindows3.List(i) & vbcrlf & lstWindows3.List(i-1) &"'  where field1= '" & txtname & "'"


it loops through the data in the listbox
which the data in it is:
Betterbasic
Visual basic
C#

but when I open the database field1 looks like this

field1
Visual basic C#

where in fact what I want is this:
field1
today Betterbasic Visual basic C#

and if possible have it add vbcrlf so the data looks like this
field1
today
Betterbasic
Visual basic
C#


Make any sense....
0
 
LVL 2

Expert Comment

by:mingz
ID: 9611220
for this result:
field1
today Betterbasic Visual basic C#

'start of code:
Dim objCon As New ADODB.Connection, s As String, rs As Recordset
objCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\windows\data.mdb; Jet OLEDB:Database Password="

s = "update tablename set Field1= '" & date()
For i = 0 To lstWindows3.ListCount - 1
  s = s & " " & lstWindows3.List(i)  
Next
s = s & "'  where field1= '" & txtname & "'"

objCon.Execute s
Set objCon = Nothing
'end of code

and what is "TheCount" for?
0
 
LVL 2

Expert Comment

by:mingz
ID: 9611229
for this result:
field1
today
Betterbasic
Visual basic
C#


'start of code:
Dim objCon As New ADODB.Connection, s As String, rs As Recordset
objCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\windows\data.mdb; Jet OLEDB:Database Password="

s = "update tablename set Field1= '" & date()
For i = 0 To lstWindows3.ListCount - 1
  s = s & vbcrlf & lstWindows3.List(i)  
Next
s = s & "'  where field1= '" & txtname & "'"

objCon.Execute s
Set objCon = Nothing
'end of code
0
 
LVL 8

Author Comment

by:bman9111
ID: 9613697
what is this


& date()


what does that do.
0
 
LVL 2

Expert Comment

by:mingz
ID: 9619544
I thought you want to put the date in??
or did you wanted to put the string "today"?

you can replace:
& date()
with:
& "Today"
0
 
LVL 8

Author Comment

by:bman9111
ID: 9623221
I just wanted to be able to retain the data that is already in that field, just wanted to add to the existing data.

0
 
LVL 2

Accepted Solution

by:
mingz earned 50 total points
ID: 9624261
then the code should be like:

'start of code:
Dim objCon As New ADODB.Connection, s As String, rs As Recordset
objCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\windows\data.mdb; Jet OLEDB:Database Password="

s = "update tablename set Field1= Field1 "
For i = 0 To lstWindows3.ListCount - 1
  s = s & vbcrlf & lstWindows3.List(i)  
Next
s = s & "'  where field1= '" & txtname & "'"

objCon.Execute s
Set objCon = Nothing
'end of code
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
A short article about problems I had with the new location API and permissions in Marshmallow
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question