Wrox Programmer Forums
|
BOOK: Visual Basic 2005 Programmer's Reference
This is the forum to discuss the Wrox book Visual Basic 2005 Programmer's Reference by Rod Stephens; ISBN: 9780764571985
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Visual Basic 2005 Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 17th, 2007, 09:19 PM
Registered User
 
Join Date: Oct 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default vs2005 comma delimited file to db

I have created a db using vs2005 (.mdf) and need to read a comma delimited file and save that data in the db (customer table). my vb code and stored proc is attached. Please tell me where I went wrong.
The program is reading all the comma delimited file (all 39000+ records) but does not write to the db.

Public Class BuildCust

    Private Sub CustomerBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Validate()
        Me.CustomerBindingSource.EndEdit()
        Me.CustomerTableAdapter.Update(Me.BuildCustomerDat aSet.Customer)

    End Sub

    Private Sub BuildCust_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'BuildCustomerDataSet.Customer' table. You can move, or remove it, as needed.
        Me.CustomerTableAdapter.Fill(Me.BuildCustomerDataS et.Customer)
        'TODO: This line of code loads data into the 'Get_MoneyDataSet.Customer' table. You can move, or remove it, as needed.
        Me.CustomerTableAdapter.Fill(Me.BuildCustomerDataS et.Customer)

    End Sub


    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strEmpFileName As String
        Dim intEmpFileNbr As Short
        Dim Company As String
        Dim IDNumber As String
        Dim Status As String
        Dim FirstName As String
        Dim LastName As String
        Dim BillingName As String
        Dim Address As String
        Dim City As String
        Dim State As String
        Dim Zip As String
        Dim HomePhone As String
        Dim WorkPhone As String
        Dim JoinDate As String
        Dim ExpDate As String
        Dim RenDate As String
        Dim BillCycle As String
        Dim FormOfPay As String
        Dim RecDues As String
        Dim DOB As String
        Dim Gender As String
        Dim SSN As String
        Dim BillIdNo As String
        Dim NxtBillDate As String
        Dim MemberType As String
        Dim ContractBillAmt As String
        Dim BankRoute As String
        Dim AcctNo As String
        Dim ChkSav As String
        Dim CCExp As String
        Dim effectivedate As String
        strEmpFileName = My.Application.Info.DirectoryPath & "\gm_customers.txt"
        intEmpFileNbr = FreeFile()

        FileOpen(intEmpFileNbr, strEmpFileName, OpenMode.Input)
        Dim count As Integer
        count = 0
        Do While Not EOF(intEmpFileNbr)
            count = count + 1
            'If count = 10 Then
            'Exit Do
            'End If
            Input(intEmpFileNbr, Company)
            Input(intEmpFileNbr, IDNumber)
            Input(intEmpFileNbr, Status)
            Input(intEmpFileNbr, FirstName)
            Input(intEmpFileNbr, LastName)
            Input(intEmpFileNbr, BillingName)
            Input(intEmpFileNbr, Address)
            Input(intEmpFileNbr, City)
            Input(intEmpFileNbr, State)
            Input(intEmpFileNbr, Zip)
            Input(intEmpFileNbr, HomePhone)
            Input(intEmpFileNbr, WorkPhone)
            Input(intEmpFileNbr, JoinDate)
            Input(intEmpFileNbr, ExpDate)
            Input(intEmpFileNbr, RenDate)
            Input(intEmpFileNbr, BillCycle)
            Input(intEmpFileNbr, FormOfPay)
            Input(intEmpFileNbr, RecDues)
            Input(intEmpFileNbr, DOB)
            Input(intEmpFileNbr, Gender)
            Input(intEmpFileNbr, SSN)
            Input(intEmpFileNbr, BillIdNo)
            Input(intEmpFileNbr, NxtBillDate)
            Input(intEmpFileNbr, MemberType)
            Input(intEmpFileNbr, ContractBillAmt)
            Input(intEmpFileNbr, BankRoute)
            Input(intEmpFileNbr, AcctNo)
            Input(intEmpFileNbr, ChkSav)
            Input(intEmpFileNbr, CCExp)
            Input(intEmpFileNbr, effectivedate)
            Me.Validate()
            Me.CustomerBindingSource.EndEdit()
            Me.CustomerTableAdapter.Update(Me.BuildCustomerDat aSet.Customer)
        Loop
        MsgBox(count)
    End Sub
End Class
ALTER PROCEDURE dbo.InsertCustomer
(
@Company varchar(6) ,
@IDNumber varchar(12) ,
@Status varchar(1) ,
@FirstName varchar(25) ,
@LastName varchar(30) ,
@BillingName varchar(50) ,
@Address varchar(40) ,
@City varchar(40) ,
@State varchar(2) ,
@Zip varchar(10) ,
@HomePhone varchar(12) ,
@WorkPhone varchar(12) ,
@JoinDate datetime ,
@ExpirationDate datetime ,
@RenewalDate datetime ,
@BillingCycle varchar(2) ,
@FormOfPay varchar(1) ,
@RecurringDues money ,
@DateOfBirth datetime ,
@Gender varchar(1) ,
@SocialSecurity varchar(11) ,
@BillingIDNumber varchar(12) ,
@NextBillingDate datetime ,
@MemberType varchar(2) ,
@ContractBillAmount money ,
@BankRoute varchar(9) ,
@AccountNumber varchar(12) ,
@ChkSav varchar(50) ,
@Ccexp varchar(20) ,
@Aging30 money ,
@Aging60 money ,
@Aging90 money ,
@Aging120 money ,
@Aging150 money ,
@Aging180 money ,
@Aging210 money ,
@Aging210Up money ,
@LateFees money ,
@PayAll money ,
@NowDue money ,
@PromiseDate datetime ,
@PromiseAmount money ,
@CallBack datetime ,
@DateAssigned datetime ,
@Collector varchar(40) ,
@LastWorked datetime ,
@OurStatus varchar(12) ,
@Email varchar(50) ,
@Modified timestamp output ,
@CustomerID int output
)
as set nocount on;
insert into customer
(
Company,
IDNumber,
Status,
FirstName,
LastName,
BillingName,
Address,
City,
State,
Zip,
HomePhone,
WorkPhone,
JoinDate,
ExpirationDate,
RenewalDate,
BillingCycle,
FormOfPay,
RecurringDues,
DateOfBirth,
Gender,
SocialSecurity,
BillingIDNumber,
NextBillingDate,
MemberType,
ContractBillAmount,
BankRoute,
AccountNumber,
ChkSav,
Ccexp,
Aging30,
Aging60,
Aging90,
Aging120,
Aging150,
Aging180,
Aging210,
Aging210Up,
LateFees,
PayAll,
NowDue,
PromiseDate,
PromiseAmount,
CallBack,
DateAssigned,
Collector,
LastWorked,
OurStatus,
Email
)
values
(
@Company,
@IDNumber,
@Status,
@FirstName,
@LastName,
@BillingName,
@Address,
@City,
@State,
@Zip,
@HomePhone,
@WorkPhone,
@JoinDate,
@ExpirationDate,
@RenewalDate,
@BillingCycle,
@FormOfPay,
@RecurringDues,
@DateOfBirth,
@Gender,
@SocialSecurity,
@BillingIDNumber,
@NextBillingDate,
@MemberType,
@ContractBillAmount,
@BankRoute,
@AccountNumber,
@ChkSav,
@Ccexp,
@Aging30,
@Aging60,
@Aging90,
@Aging120,
@Aging150,
@Aging180,
@Aging210,
@Aging210Up,
@LateFees,
@PayAll,
@NowDue,
@PromiseDate,
@PromiseAmount,
@CallBack,
@DateAssigned,
@Collector,
@LastWorked,
@OurStatus,
@Email
);

if @@rowcount>0 and @@error=0
select @customerID = customerID,
@modified = modified
from customer
where (customerID = scope_identity());

ALTER PROCEDURE dbo.UpdateCustomer
(
@Company varchar(6) ,
@IDNumber varchar(12) ,
@Status varchar(1) ,
@FirstName varchar(25) ,
@LastName varchar(30) ,
@BillingName varchar(50) ,
@Address varchar(40) ,
@City varchar(40) ,
@State varchar(2) ,
@Zip varchar(10) ,
@HomePhone varchar(12) ,
@WorkPhone varchar(12) ,
@JoinDate datetime ,
@ExpirationDate datetime ,
@RenewalDate datetime ,
@BillingCycle varchar(2) ,
@FormOfPay varchar(1) ,
@RecurringDues money ,
@DateOfBirth datetime ,
@Gender varchar(1) ,
@SocialSecurity varchar(11) ,
@BillingIDNumber varchar(12) ,
@NextBillingDate datetime ,
@MemberType varchar(2) ,
@ContractBillAmount money ,
@BankRoute varchar(9) ,
@AccountNumber varchar(12) ,
@ChkSav varchar(50) ,
@Ccexp varchar(20) ,
@Aging30 money ,
@Aging60 money ,
@Aging90 money ,
@Aging120 money ,
@Aging150 money ,
@Aging180 money ,
@Aging210 money ,
@Aging210Up money ,
@LateFees money ,
@PayAll money ,
@NowDue money ,
@PromiseDate datetime ,
@PromiseAmount money ,
@CallBack datetime ,
@DateAssigned datetime ,
@Collector varchar(40) ,
@LastWorked datetime ,
@OurStatus varchar(12) ,
@Email varchar(50) ,
@customerID int,
@Modified timestamp output
)
as
set nocount on;
update customer set
Company = @Company,
IDNumber = @IDNumber,
Status = @Status,
FirstName = @FirstName,
LastName = @LastName,
BillingName = @BillingName,
Address = @Address,
City = @City,
State = @State,
Zip = @Zip,
HomePhone = @HomePhone,
WorkPhone = @WorkPhone,
JoinDate = @JoinDate,
ExpirationDate = @ExpirationDate,
RenewalDate = @RenewalDate,
BillingCycle = @BillingCycle,
FormOfPay = @FormOfPay,
RecurringDues = @RecurringDues,
DateOfBirth = @DateOfBirth,
Gender = @Gender,
SocialSecurity = @SocialSecurity,
BillingIDNumber = @BillingIDNumber,
NextBillingDate = @NextBillingDate,
MemberType = @MemberType,
ContractBillAmount = @ContractBillAmount,
BankRoute = @BankRoute,
AccountNumber = @AccountNumber,
ChkSav = @ChkSav,
Ccexp = @Ccexp,
Aging30 = @Aging30,
Aging60 = @Aging60,
Aging90 = @Aging90,
Aging120 = @Aging120,
Aging150 = @Aging150,
Aging180 = @Aging180,
Aging210 = @Aging210,
Aging210Up = @Aging210Up,
LateFees = @LateFees,
PayAll = @PayAll,
NowDue = @NowDue,
PromiseDate = @PromiseDate,
PromiseAmount = @PromiseAmount,
CallBack = @CallBack,
DateAssigned = @DateAssigned,
Collector = @Collector,
LastWorked = @LastWorked,
OurStatus = @OurStatus,
Email = @Email
where
(customerid = @customerid) and (modified = @modified);
if @@rowcount>0 and @@error=0
select @modified = modified
from customer
where (customerID = CustomerID);
else
raiserror 50000 'concurrency violation on table Customer'



 
Old October 19th, 2007, 05:35 PM
Rod Stephens's Avatar
Wrox Author
 
Join Date: Jan 2006
Posts: 647
Thanks: 2
Thanked 96 Times in 95 Posts
Default

I don't think I understand how this code is supposed to work. It looks like you read the values from the file into routine-local variables but I don't see how those variables are associated with the database. The EndEdit and Update calls make it seem like you're thinking the data in is text boxes and other controls that are bound to the database but I don't think that's the case. The data is read into variables not controls.

I don't do enough scripting like this to be much help with that part but I would normally skip the script and let VB insert the data. I would make a parameterized Command object to insert the values. Then change the parameter values each time you read a line from the file.

This example uses a parameterized query rather than a command but it should give you the idea:

http://www.vb-helper.com/howto_net_d...zed_query.html

HTH,

Rod
RodStephens@vb-helper.com

Check out my book:
"Expert One-on-One Visual Basic 2005 Design and Development"
http://www.vb-helper.com/one_on_one.htm

Sign up for the free VB Helper Newsletters at http://www.vb-helper.com/newsletter.html





Similar Threads
Thread Thread Starter Forum Replies Last Post
Generating a comma-delimited data in XSLT dude153 XSLT 2 November 30th, 2007 12:12 PM
Comma delimited file to Excel Sheet vinod_yadav1919 VB How-To 0 August 5th, 2005 10:09 AM
Comma delimited text files bmurrin Beginning VB 6 8 February 26th, 2004 02:07 PM
Comma Delimited Update? mariakovacs Access ASP 6 October 13th, 2003 03:49 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.