Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

visual basic update record while retaining old data

Posted on 2003-10-21
12
Medium Priority
?
416 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 200 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Starting up a Project

926 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