Lost INSERT Statement

Question

Post reply

Lost INSERT Statement

Lawrence Christman

Valued Member

Points: 72
More actions
January 30, 2008 at 12:01 am

#75845

I think I am in abit of trouble here. I wrote a program a couple of months ago that was pretty solid until my development team changed something in the database without me knowing about it.
The problem I am having is, I have a VB6 program that does an insert into my table. This program sucks up values from a tab delimited file and then does an insert. Here is the problem:
Table Definition:
CREATE TABLE tmp_test
(
acct varchar(11),
settlement_amt money,
letter_type varchar(5)
)
The text file that I would load up to fill the table above would look like this (TAB Delimited):
0040-000001 40 DLS
The problem I am having is that the text file has changed to:
0040-000001 $750,40 DLS
So:
INSERT INTO tmp_test SELECT '" & MyField(0) & "'," & MyField(1) & ",'" & MyFields(2) & "'"
So, MyFields(1) is putting an extra comma, tricking the INSERT statement into creating a extra field.
I've tried to think of everything. Insert into a View, INSTEAD OF INSERT trigger. I've tried everything to my knowledge to do something about this.
I wrote the program one night, never saved, and Windows Update restarted my computer and lost the code for this program. Anyone have any ideas? Will I have to try to recreate this program?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lawrence Christman Valued Member Points: 72 More actions · Answer 1

I've edited my post, something about my problem description didn't make sense, it should be more clear now.

Lawrence Christman Valued Member Points: 72 More actions · Answer 2

I tried using a INSTEAD OF INSERT trigger, but I think what is happening is SQL2000 error checking catches the INSERT problem, and doesn't allow the trigger to execute.

Stupeo Hall of Fame Points: 3067 More actions · Answer 3

Is it possible for you to amend the VB6 program to strip out the $ and the , ??

Matt.

Lawrence Christman Valued Member Points: 72 More actions · Answer 4

No, I dont have the source anymore.

If I could get the trigger to fire, my trigger uses the Replace function to strip the unwanted as it got passed in. But like I said, the SQL2000 Error trapping on the insert kicks in before the trigger, thus stopping the trigger from fixing the problem.