473,215 Members | 1,214 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,215 software developers and data experts.

Adding tables and fields to database using vb.net

Hi everyone,

I need some help. I'm placing text files into a created database using
vb.Net. The problem is that, i need two seperate sql statements to add
both files because they are in different loops. My output comes out to
be as Follows:
TABLE
----------------------------------------------------
Field1 Field2
outputfile1
outputfile1
outputfile1
outputfile1
outputfile1
outputfile2
outputfile2
outputfile2
outputfile2
-----------------------------------------------------
I declared both fields Nullable so I wouldn't get an error, complaining

that the fields cannot be Null.
I need the "outputfile2" lines to be adjacent to the "outputfile1"
files. I understand why this is happening, I just can't think of
another way of adding the fields.
My code is as follows:

---------------------------------------------------------------------------------------------------
Sub ReadFiles()
File1 = "intDirectory.txt"
'Open the first file
sr = IO.File.OpenText(File1)
'Initialize counter
'Loop through Directory Listing file to load filepaths into an
array
Do While sr.Peek <-1
lineFile1 = sr.ReadLine
Dim sb As New System.Text.StringBuilder(lineFile1)
sb.Replace("C:", "")
sb.Replace("x:", "")
sb.Replace("\", "/")
sb.Replace("wwwroot", "")
lineFile1 = sb.ToString()
'INSERT INTO TABLE
sSQL = "INSERT INTO Comparison(Internet_Directory)
VALUES('" & lineFile1.ToString & "')"
CreateStoredProc(sSQL)
counter += 1
Loop
sr.Close()
File2 = "IISLog.txt"
sr = IO.File.OpenText(File2)
sSQL = ""
Do While sr.Peek <-1
lineFile2 = sr.ReadLine
'String Manipulation (Replace some unneeded text)
Dim sb2 As New System.Text.StringBuilder(lineFile2)
sb2.Replace("C:", "")
sb2.Replace("x:", "")
sb2.Replace("\", "/")
sb2.Replace("wwwroot", "")
lineFile2 = sb2.ToString()
'INSERT INTO TABLE
sSQL = "INSERT INTO Comparison(IIS_Logs) VALUES('" &
lineFile2.ToString & "')"
CreateStoredProc(sSQL)
counter2 += 1
Loop
sr.Close()
End Sub
---------------------------------------------------------------------------------------------------

Oct 27 '06 #1
2 3151
Justin Fancy wrote:
I need some help. I'm placing text files into a created database using
vb.Net. The problem is that, i need two seperate sql statements to add
both files because they are in different loops.
I don't begin to claim I know what code you need in .Net, but your 1st query
can be an append query and the 2nd query must be an update query for Field2.
How you're going to line up the right records unless you have a primary key
beats me.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

Oct 27 '06 #2
Assuming that the data in the two files is ordered identically and that the
data in Line1
of FileA belongs with data in Line1 of FileB then:

Open both files under different file handles; walk down both files at the
same time(one loop);
store the data from FileA in a variable called DataA and the data from FileB
in a variable
called DataB; have your Sql statement use both variables to append to each
field in one append; procede down the files until you hit (hopefully at the
same time) the EOF.

"Justin Fancy" <ju*********@gmail.comwrote in message
news:11*********************@b28g2000cwb.googlegro ups.com...
Hi everyone,

I need some help. I'm placing text files into a created database using
vb.Net. The problem is that, i need two seperate sql statements to add
both files because they are in different loops. My output comes out to
be as Follows:
TABLE
----------------------------------------------------
Field1 Field2
outputfile1
outputfile1
outputfile1
outputfile1
outputfile1
outputfile2
outputfile2
outputfile2
outputfile2
-----------------------------------------------------
I declared both fields Nullable so I wouldn't get an error, complaining

that the fields cannot be Null.
I need the "outputfile2" lines to be adjacent to the "outputfile1"
files. I understand why this is happening, I just can't think of
another way of adding the fields.
My code is as follows:

---------------------------------------------------------------------------------------------------
Sub ReadFiles()
File1 = "intDirectory.txt"
'Open the first file
sr = IO.File.OpenText(File1)
'Initialize counter
'Loop through Directory Listing file to load filepaths into an
array
Do While sr.Peek <-1
lineFile1 = sr.ReadLine
Dim sb As New System.Text.StringBuilder(lineFile1)
sb.Replace("C:", "")
sb.Replace("x:", "")
sb.Replace("\", "/")
sb.Replace("wwwroot", "")
lineFile1 = sb.ToString()
'INSERT INTO TABLE
sSQL = "INSERT INTO Comparison(Internet_Directory)
VALUES('" & lineFile1.ToString & "')"
CreateStoredProc(sSQL)
counter += 1
Loop
sr.Close()
File2 = "IISLog.txt"
sr = IO.File.OpenText(File2)
sSQL = ""
Do While sr.Peek <-1
lineFile2 = sr.ReadLine
'String Manipulation (Replace some unneeded text)
Dim sb2 As New System.Text.StringBuilder(lineFile2)
sb2.Replace("C:", "")
sb2.Replace("x:", "")
sb2.Replace("\", "/")
sb2.Replace("wwwroot", "")
lineFile2 = sb2.ToString()
'INSERT INTO TABLE
sSQL = "INSERT INTO Comparison(IIS_Logs) VALUES('" &
lineFile2.ToString & "')"
CreateStoredProc(sSQL)
counter2 += 1
Loop
sr.Close()
End Sub
---------------------------------------------------------------------------------------------------

Oct 30 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Dee | last post by:
I have a form which contains basics of a customer's order from table . I placed a tab control on this form which is still empty. I'm trying to design a project management database and would like...
11
by: Bobbak | last post by:
Hello All, I have these tables (lets call it ‘EmpCalls', ‘EmpOrders', and ‘Stats') that each contain the list of EmployeeIDs, I want to be able to create a Module in which I could call in my VB...
3
by: LouD | last post by:
I have 5 tables in my db and when i bring up the linked form I get my product lists Tbls are tbStock, which contain unitcost, sellprice n qty tblProduct, contains ID, medicalname n genericname...
5
by: Sami | last post by:
Please bear with me, and if you answer this question, please do it step by step. I am new at Access, not at all sophisticated. I am using Office XP. This will need to be read in Access for...
1
by: RC | last post by:
I have an Access 2002 database with many tables and forms (but just to keep things simple, let's say the DB has one Table "Table1" and one Form "Form1"). I have managed to cobble together so much...
12
by: Art | last post by:
Hi everyone I was hoping someone might be able to help me with this. I'm just starting to try to work with MS Access tables through VB.net. In Access I can take an existing table and add a new...
19
by: Shwetabh | last post by:
Hi, I have two tables: Code and Color. The create command for them is : create table Color( Partnum varchar(10), Eng_Color char(10), Span_Color char(20), Frch_Color char(20), CONSTRAINT...
2
by: Steve | last post by:
I have zero experience with ODBC. If I have an Access frontend connected to a SQL Database using ODBC, are the tables connected like a frontend/backend Access database where the the tables you see...
1
by: sherifffruitfly | last post by:
Hi all, I'm tasked with adding a few fields to our database, and all of the stuff that goes along with it (updating c# UI code, etc.). The database is a few hundred tables, with all of the...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.