I am now getting seriously into VB.Net development as a front end to SQL
server. After working about 20 yrs with DBF's and various xBase
programs (dBase, FoxPro, Clipper, etc), I am now convinced VB/SQL is the
way to go
I have a few questions:
I've successfully imported DBF files into SQL server (MSDE) but I can't
seem to create a data adapter (in VB) unless I have a primary key
defined. Do tables require primary keys be defined prior to dragging
them onto a forms and working with datagrids?
How can I create a primary key on a compound field? For a simple key
(one field), I know to open the server explorer, go into design mode,
then right click the field and set primary key. This works well ... but
... what about a 'details' table where the only unique key is a
combination of many fields (might be mixed type too), ex: customer
(char) + item (numeric) + color (char). How does one do that?
Thanks in advance
Richard Fagen
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
In the table design high-light each column name you want to make PK and
click the PK icon (a gold key symbol) in the toolbar. You high-light
each column by holding down the control key and clicking the square to
the left of the column name.
You could also use a DDL statement to create a PK:
ALTER TABLE table_name ADD CONSTRAINT PK_table_name PRIMARY KEY (col1,
col2, col3)
The coln are the column names that will comprise the PK.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQbTkE4echKqOuFEgEQI7uACg3KvX6c2lJy7M9loRVsXQbs 732YwAnRKD
6CSubCv+n/TA2EUvULyWke2E
=qfj3
--END PGP SIGNATURE--
Richard Fagen wrote:
> Hi everyone,
> I am now getting seriously into VB.Net development as a front end to SQL
> server. After working about 20 yrs with DBF's and various xBase
> programs (dBase, FoxPro, Clipper, etc), I am now convinced VB/SQL is the
> way to go
> I have a few questions:
> I've successfully imported DBF files into SQL server (MSDE) but I can't
> seem to create a data adapter (in VB) unless I have a primary key
> defined. Do tables require primary keys be defined prior to dragging
> them onto a forms and working with datagrids?
> How can I create a primary key on a compound field? For a simple key
> (one field), I know to open the server explorer, go into design mode,
> then right click the field and set primary key. This works well ... but
> ... what about a 'details' table where the only unique key is a
> combination of many fields (might be mixed type too), ex: customer
> (char) + item (numeric) + color (char). How does one do that?
|||Hi,
Thanks for your reply. I never noticed the gold key icon, I was using
the right click popup menu
While it appears to work, when I try tosave the changes to the file, I get the same error message...
'PRODUCT' table
- Unable to modify table.
ADO error: Cannot insert the value NULL into column 'PREF', table
'sterilite.dbo.Tmp_PRODUCT'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I imported the DBF files via SQL servers' tools. I thought everything
was ok as in the server explorer, I see the database with all the tables
(foxpro files) inside it. I can also browse the data. I did notice the
'allow nulls' column was checked. I tried unchecking it but I get the
same error.
The foxpro file originally had a 'pref' C4 (for the 'prefix' code, a
character field, width 4) and I don't know what to try next.
In DBF syntax, the unique fields are:
pref, C4 (prefix of item)
item, C4 (item code)
color, C2 (color code)
cu_code, C4 (customer code)
All these fields get translated into 'varchar' type with the appropriate
widths and the allow nulls checked.
Any ideas?
Thanks
Richard
MGFoster wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> In the table design high-light each column name you want to make PK and
> click the PK icon (a gold key symbol) in the toolbar. You high-light
> each column by holding down the control key and clicking the square to
> the left of the column name.
> You could also use a DDL statement to create a PK:
> ALTER TABLE table_name ADD CONSTRAINT PK_table_name PRIMARY KEY (col1,
> col2, col3)
> The coln are the column names that will comprise the PK.
>
|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
In RDBMS (SQL dbs) each row's Primary Key columns require data, i.e.,
they cannot be NULL. The error you're getting means that the PREF
column has NULL values in some, or all, of the imported rows. This
means the columns you've selected for PKs are not a good choice, or you
need to fix the data so there is a value in the PREF column for each
row.
It would seem that the columns item, color, cu_code, would be good
candidates for PK if they always have values per row, and, the 3 column
values, taken together, represent a unique value in the table.
Is the PREF column useful as part of a unique identifier (PK)? If so,
then it should have data.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQbUGaoechKqOuFEgEQK43gCg4pund4SFw+VpPdFrYlcLoG iwV5wAnjzV
aFvBbWaz/Xm1Y1XKxbpzG7wQ
=rrhn
--END PGP SIGNATURE--
Richard Fagen wrote:[vbcol=seagreen]
> Hi,
> Thanks for your reply. I never noticed the gold key icon, I was using
> the right click popup menu
While it appears to work, when I try to> save the changes to the file, I get the same error message...
> 'PRODUCT' table
> - Unable to modify table.
> ADO error: Cannot insert the value NULL into column 'PREF', table
> 'sterilite.dbo.Tmp_PRODUCT'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> I imported the DBF files via SQL servers' tools. I thought everything
> was ok as in the server explorer, I see the database with all the tables
> (foxpro files) inside it. I can also browse the data. I did notice the
> 'allow nulls' column was checked. I tried unchecking it but I get the
> same error.
> The foxpro file originally had a 'pref' C4 (for the 'prefix' code, a
> character field, width 4) and I don't know what to try next.
> In DBF syntax, the unique fields are:
> pref, C4 (prefix of item)
> item, C4 (item code)
> color, C2 (color code)
> cu_code, C4 (customer code)
> All these fields get translated into 'varchar' type with the appropriate
> widths and the allow nulls checked.
> Any ideas?
> Thanks
> Richard
>
>
> MGFoster wrote:
|||That must be it! Yes, some values of Pref are null. Strange, in the
DBF format it was a blank character field, but when it was imported in
SQL, it became <null>
While I might be able to get around this by excluding 'pref' from the
key, I'll have that problem for other fields as some items don't have
colours and they also appear with <null> in SQL format.
They would still be unique as if an item only comes in one colour, the
DBF file had a blank. Can I globally change all <null> to " " (blank
characters) to fix this.
Thanks for figuring out what the problem was!
Richard
MGFoster wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> In RDBMS (SQL dbs) each row's Primary Key columns require data, i.e.,
> they cannot be NULL. The error you're getting means that the PREF
> column has NULL values in some, or all, of the imported rows. This
> means the columns you've selected for PKs are not a good choice, or you
> need to fix the data so there is a value in the PREF column for each
> row.
> It would seem that the columns item, color, cu_code, would be good
> candidates for PK if they always have values per row, and, the 3 column
> values, taken together, represent a unique value in the table.
> Is the PREF column useful as part of a unique identifier (PK)? If so,
> then it should have data.
>
|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You can change all values of one column, but that will not fix your
problem. For the PK, what you need to do is pick one, or more, columns
whose values uniquely identify the row. Each of these columns MUST have
data for every row. There cannot be rows where these columns have equal
values. E.g. (the 3 columns that make up the PK):
item colour cu_code
1 2 9988
1 3 9988
1 2 9988 <- PK constraint violation 'cuz equals 1st
row.
It would seem that this table would have a Date column. You could
include the date column in the PK & have something like this:
item colour cu_code order_date
1 2 9988 20040115
1 3 9988 20040115
1 2 9988 20040120 <- now OK 'cuz order_date makes
row different from 1st row.
If the columns you pick as the PK have rows w/o data then you will have
to do, what is known as, "Data Clean Up." Which should be done by your
client since they are the ones who created this mess to begin with.
I suggest you get a good book on database design to help you figure out
construction of primary keys, foreign keys, indexes, etc. I've
recommended _Database Design for Mere Mortals_ by Hernandez.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQbVhPIechKqOuFEgEQLJGQCgjejMWSmlyiNAELht8FvPb2 BluRsAoORW
6ofwJ8Gby1KYNurkiHCb1NM+
=Ode3
--END PGP SIGNATURE--
Richard Fagen wrote:[vbcol=seagreen]
> That must be it! Yes, some values of Pref are null. Strange, in the
> DBF format it was a blank character field, but when it was imported in
> SQL, it became <null>
> While I might be able to get around this by excluding 'pref' from the
> key, I'll have that problem for other fields as some items don't have
> colours and they also appear with <null> in SQL format.
> They would still be unique as if an item only comes in one colour, the
> DBF file had a blank. Can I globally change all <null> to " " (blank
> characters) to fix this.
> Thanks for figuring out what the problem was!
> Richard
> MGFoster wrote:
|||Hi,
Thanks for your prompt reply. I will definitely check out that book. I
have collected quite a library over the years and I'm always reading
many tech books at once
I'm aware of the requirement of a unique value for each record, it is
just some records, when imported from DBF (Foxpro/DOS) have null values.
In DBF format, spaces were stored. I'm not sure why importing the
files into SQL tables cause them to change to <nulls> Maybe I didn't
check off some option during the import process?
Ex:
ItemColourCu_code... more fields here
11Walmart
12Walmart
13Walmart
2<null>Walmart <- problem here, it was blank in the DBF,
some items have a single record that is blank,
it will still be unique, just one " " value
for that cu_code/item combination
31Walmart
32Walmart
Your idea to incorporate another field is a great idea. You are right,
I will have the same problem elsewhere unless I add an orderdate (or
invoice #) to the key. I don't know how to get rid of the <nulls> for
the blank DBFs that were imported into SQL/
I was thinking of replacing the blanks in the DBF with a dummy symbol
(say *) before importing into SQL format. Then I'd use OSQL to replace
the dummy with " ". Is there an easier way?
Thanks again.
Richard
MGFoster wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> You can change all values of one column, but that will not fix your
> problem. For the PK, what you need to do is pick one, or more, columns
> whose values uniquely identify the row. Each of these columns MUST have
> data for every row. There cannot be rows where these columns have equal
> values. E.g. (the 3 columns that make up the PK):
> item colour cu_code
> 1 2 9988
> 1 3 9988
> 1 2 9988 <- PK constraint violation 'cuz equals 1st
> row.
> It would seem that this table would have a Date column. You could
> include the date column in the PK & have something like this:
> item colour cu_code order_date
> 1 2 9988 20040115
> 1 3 9988 20040115
> 1 2 9988 20040120 <- now OK 'cuz order_date makes
> row different from 1st row.
> If the columns you pick as the PK have rows w/o data then you will have
> to do, what is known as, "Data Clean Up." Which should be done by your
> client since they are the ones who created this mess to begin with.
> I suggest you get a good book on database design to help you figure out
> construction of primary keys, foreign keys, indexes, etc. I've
> recommended _Database Design for Mere Mortals_ by Hernandez.
>
|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
It would seem that replacing the "*" with a space would be redundant,
since you're just inserting a "holding character" until you get valid
data, and that holding character can be an asterisk as easily as a
space.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQbYol4echKqOuFEgEQIcRACg0oDX4sr3G7MWGLBjVSDoTU 2tUjgAn3UM
Cg//imB7G3nJKMWBWBZRffvf
=Is8Z
--END PGP SIGNATURE--
Richard Fagen wrote:[vbcol=seagreen]
> Hi,
> Thanks for your prompt reply. I will definitely check out that book. I
> have collected quite a library over the years and I'm always reading
> many tech books at once
> I'm aware of the requirement of a unique value for each record, it is
> just some records, when imported from DBF (Foxpro/DOS) have null values.
> In DBF format, spaces were stored. I'm not sure why importing the
> files into SQL tables cause them to change to <nulls> Maybe I didn't
> check off some option during the import process?
> Ex:
> Item Colour Cu_code ... more fields here
> 1 1 Walmart
> 1 2 Walmart
> 1 3 Walmart
> 2 <null> Walmart <- problem here, it was blank in the DBF,
> some items have a single record that is blank,
> it will still be unique, just one " " value
> for that cu_code/item combination
> 3 1 Walmart
> 3 2 Walmart
> Your idea to incorporate another field is a great idea. You are right,
> I will have the same problem elsewhere unless I add an orderdate (or
> invoice #) to the key. I don't know how to get rid of the <nulls> for
> the blank DBFs that were imported into SQL/
> I was thinking of replacing the blanks in the DBF with a dummy symbol
> (say *) before importing into SQL format. Then I'd use OSQL to replace
> the dummy with " ". Is there an easier way?
> Thanks again.
> Richard
> MGFoster wrote:
|||Can't a blank value (" ") be valid if it is part of a unique compound
key?
There must be some OSQL statement that I can use.
In anycase, is there a simple way to change all the <null> values to
blank spaces? I know I can use the server explorer to browse and edit
values, but certain tables have 1000's of <null> values in certain columns.
Thanks
Richard
MGFoster wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> It would seem that replacing the "*" with a space would be redundant,
> since you're just inserting a "holding character" until you get valid
> data, and that holding character can be an asterisk as easily as a
> space.
>
|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You can change NULLs to another character(s) using an update like this:
UPDATE table_name
SET column_name = '*'
WHERE column_name IS NULL
I'd use an actual character, 'cuz some query results thru other
applications (MS Access) will seem to be a space (blank), but, in fact,
will be a NULL.
What I was trying to get at is this may not enable a PK to be
established on the columns you want to make a PK, because, putting a
character in the column may not make a unique PK. E.g.:
BEFORE:
item colour cu_code
NULL 1 9998
2 NULL 9998
NULL 1 9998
AFTER:
item colour cu_code
* 1 9998
2 * 9998
* 1 9998 <- violates PK constraint 'cuz equals row 1.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQbY6KoechKqOuFEgEQKgvACfeROn2fzUzR88HCv9HxPmvY Pl3YcAoMM6
lDykLHrwNeYAAUzf6vFHbpmb
=r+De
--END PGP SIGNATURE--
Richard Fagen wrote:[vbcol=seagreen]
> Can't a blank value (" ") be valid if it is part of a unique compound
> key?
> There must be some OSQL statement that I can use.
> In anycase, is there a simple way to change all the <null> values to
> blank spaces? I know I can use the server explorer to browse and edit
> values, but certain tables have 1000's of <null> values in certain columns.
> Thanks
> Richard
>
> MGFoster wrote:
sql
No comments:
Post a Comment