Showing posts with label vb6. Show all posts
Showing posts with label vb6. Show all posts

Friday, March 9, 2012

Help w/ unique identity

Not sure if I have the right term in my Subject, but I have written an
application using VB6, which stores employee transactions in a SQL database.
I had originally designed the app for independent use in a standalone PC in
our remote offices but was then asked to expand its use to our corporate
office and import the transactions from all the locations into one database.
I have an Identity column in my table labeled "TransactionID" with the
Identity Seed and Indentity Increment set to the default of 1 and
automatically increments. The TransactionID number ensures that each
transaction is unique because other pieces of data collected in the table
could be the same such as StoreNumber, EmployeeNumber, etc.
All this is working fine, but now I want to import the transactions from the
remote location(s) into the SQL database at the corporate office. Now, there
is the very real possibility that a TransactionID from any of the remote
locations will match. Plus, when I import the records into the corporate
database, won't the master database want to reassign it a TransactionID as
well?
Since the TransactionID is unique at each store/office level, I was thinking
of formatting the TransactionID to something like "1234.01" where "1234" is
the next row in the table and "01" is the store number. This would be
separated by a decimal point. The store number would never exceed 2 digits
and the corporate office could be "00" or "99".
Is this possible or is there a better way to do this? I need to preserve the
TransactionID from the store/office because I use it to create an audit
trail if a transaction is ever modified.
Thanks,
BarryYou can add StoreID to the table and keep current Identity column as it (int
).
Perayu
"BCS" wrote:

> Not sure if I have the right term in my Subject, but I have written an
> application using VB6, which stores employee transactions in a SQL databas
e.
> I had originally designed the app for independent use in a standalone PC i
n
> our remote offices but was then asked to expand its use to our corporate
> office and import the transactions from all the locations into one databas
e.
> I have an Identity column in my table labeled "TransactionID" with the
> Identity Seed and Indentity Increment set to the default of 1 and
> automatically increments. The TransactionID number ensures that each
> transaction is unique because other pieces of data collected in the table
> could be the same such as StoreNumber, EmployeeNumber, etc.
> All this is working fine, but now I want to import the transactions from t
he
> remote location(s) into the SQL database at the corporate office. Now, the
re
> is the very real possibility that a TransactionID from any of the remote
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
> Since the TransactionID is unique at each store/office level, I was thinki
ng
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
> Is this possible or is there a better way to do this? I need to preserve t
he
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
> Thanks,
> Barry
>
>|||I suggest you add another field in the table called store id
modify the unique constraint or the Pk to a composite (PK) or constraint
a composite pk is composed of at least two or more fields. In that way
you still preserve uniquesness of the data without violation atomicity of
the column
or better known as the domain integrity
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"BCS" wrote:

> Not sure if I have the right term in my Subject, but I have written an
> application using VB6, which stores employee transactions in a SQL databas
e.
> I had originally designed the app for independent use in a standalone PC i
n
> our remote offices but was then asked to expand its use to our corporate
> office and import the transactions from all the locations into one databas
e.
> I have an Identity column in my table labeled "TransactionID" with the
> Identity Seed and Indentity Increment set to the default of 1 and
> automatically increments. The TransactionID number ensures that each
> transaction is unique because other pieces of data collected in the table
> could be the same such as StoreNumber, EmployeeNumber, etc.
> All this is working fine, but now I want to import the transactions from t
he
> remote location(s) into the SQL database at the corporate office. Now, the
re
> is the very real possibility that a TransactionID from any of the remote
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
> Since the TransactionID is unique at each store/office level, I was thinki
ng
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
> Is this possible or is there a better way to do this? I need to preserve t
he
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
> Thanks,
> Barry
>
>|||> Since the TransactionID is unique at each store/office level, I was thinkingd">
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
I can not understand well because you are mixing names here.
My recommendation is to add a new column to the transaction table, to store
the [store] from where the transaccion was made. Do not use the formatting
approach (1234.01), then you will have to parse it into a select statement
and this will avoid sql server from considering it as a search argument.
Also, you can not create indexes based on a formula unless you create a
computed column and base the index on this column.
avoid this:
select c1,..., cn
from dbo.[transactions]
where right(transactionid, 2) = '01'
AMB
"BCS" wrote:

> Not sure if I have the right term in my Subject, but I have written an
> application using VB6, which stores employee transactions in a SQL databas
e.
> I had originally designed the app for independent use in a standalone PC i
n
> our remote offices but was then asked to expand its use to our corporate
> office and import the transactions from all the locations into one databas
e.
> I have an Identity column in my table labeled "TransactionID" with the
> Identity Seed and Indentity Increment set to the default of 1 and
> automatically increments. The TransactionID number ensures that each
> transaction is unique because other pieces of data collected in the table
> could be the same such as StoreNumber, EmployeeNumber, etc.
> All this is working fine, but now I want to import the transactions from t
he
> remote location(s) into the SQL database at the corporate office. Now, the
re
> is the very real possibility that a TransactionID from any of the remote
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
> Since the TransactionID is unique at each store/office level, I was thinki
ng
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
> Is this possible or is there a better way to do this? I need to preserve t
he
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
> Thanks,
> Barry
>
>|||I already have a StoreNumber column, so I guess I just need to find some
info on constructing a Composite Constraint. Any good links?
Thanks,
Barry
<Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
news:EF3BF399-EDA4-4652-BC12-4321B73497D2@.microsoft.com...
> I suggest you add another field in the table called store id
> modify the unique constraint or the Pk to a composite (PK) or constraint
> a composite pk is composed of at least two or more fields. In that way
> you still preserve uniquesness of the data without violation atomicity of
> the column
> or better known as the domain integrity
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "BCS" wrote:
>
database.
in
database.
table
the
there
as
thinking
is
digits
the|||
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
>
You didn't mention, but I'm assuming that records in this table are only
inserted at the store level and are consolidated at the corporate level for
accounting and reporting purposes. The corporate data model can have the
same basic column layout as the store level model, but do not use the
identity property for TransactionID, becuase there is no need for it and
would create problem when importing.

> Since the TransactionID is unique at each store/office level, I was
> thinking
> of formatting the TransactionID to something like "1234.01" where "1234"
> is
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
>
Identity columns (in this case TransactionID) must be integers, and you
cannot alter their format.

> Is this possible or is there a better way to do this? I need to preserve
> the
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
>
The combination of TransactionID and StoreID would make each transaction
unique at the corporate level, and this would be a good candidate for the
primary key at both the store and corporate level. When your primary key is
an identoty key, you should attempt to identify a natural key (ex: StoreID,
RegisterID, TransactionDateTime) and place a unique constraint on it.

> Thanks,
> Barry
>|||He probably means that the primary key should be composed of more than 1
column. For example:
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
(
[StoreNumber],
[TransactionID]
)
"BCS" <bswedeen@.tayloroil.com> wrote in message
news:rT_Oe.36020$es2.553871@.twister.southeast.rr.com...
>I already have a StoreNumber column, so I guess I just need to find some
> info on constructing a Composite Constraint. Any good links?
> Thanks,
> Barry
> <Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
> news:EF3BF399-EDA4-4652-BC12-4321B73497D2@.microsoft.com...
> database.
> in
> database.
> table
> the
> there
> as
> thinking
> is
> digits
> the
>

Help using custom VB6 Function in Crystal

I have a function for a date format in VB6 that converts to a Long Integer. I then use the opposing function below to convert to a readable date. Can anyone help me implement the function in Crystal 10.

Public Function fDateLong(plngDate As Long) ' Gets Date from DB format YYYYMMDD (DB date is Long) ,
Dim sDate, sYYYY, sMM, sDD As String
sDate = Trim(CStr(plngDate)) ' trim a Converted Long Date
sYYYY = Left(sDate, 4) ' get YYYY from left
sMM = Mid(sDate, 5, 2) 'get MM from middle
sDD = Right(sDate, 2) ' get DD from right
sDate = sMM & "/" & sDD & "/" & sYYYY ' reassemble
fDateLong = CDate(sDate) ' Convert to Date

End Function

Right now my date is reporting as " 20060612". Any help in implementing this function in Crystal would be appreciated.What is your expected output?|||I would like to take it from the DB format (long) 20060615 to a date format like either June 15 2006 or even 6-15-2006 (or 15-06-2006).

Thanks for you response.|||Create a formula having this code and drag that in the report

Numbervar y:=0;
Numbervar m:=0;
Numbervar da:=0;

y:=Tonumber(left(replace(totext(20060615 ),",",""),4));
m:=Tonumber(mid(replace(totext(20060615 ),",",""),5,2));
da:=Tonumber(mid(replace(totext(20060615 ),",",""),7,2));

monthname(m)+" "+totext(da,0)+ " "+replace(totext(y,0),",","")|||Madhi,
Appreciate the help but I still have no bloody where to put this?
Can you advise?|||As I told you create new formula. Put that code. Save it. Drag it to the details section|||Madhi,

Great stuff; problem solved.

Thanks very much