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'
vs2005 comma delimited file to db
