Solved

visual basic update record while retaining old data

Posted on 2003-10-21
12
408 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to split this in C++ 4 78
Not needed 13 58
Turning python script into an applet 12 46
Path to Python 9 49
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…
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 …

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now