Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

visual basic update record while retaining old data

Posted on 2003-10-21
12
Medium Priority
?
415 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
[X]
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
  • 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 Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
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…
Six Sigma Control Plans

722 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