Solved

visual basic update record while retaining old data

Posted on 2003-10-21
12
409 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mapShare challenge 13 106
I need an assist with a programming logic math question. 5 64
Advice in Xamarin 21 79
Query Syntax 17 34
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
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…

770 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