|
No, I doubt you can turn three varchar primary keys into one. Especially as the one has the same length as each of the three. I reckon you can add three foreign keys, one for each, but I'm not sure how effective this primary key is going to be anyhow, three variable length strings ? That's not a quick thing to look up, or an easy thing to index.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
You can take 3 fields (who cares what they are) and put them into one PK constraint. Thats not the problem. You can't make a FK to a non Unique field and non of the fields in the PK are unique; however the combination of the 3 fields is unique. I wan't to know if i can make a FK to a PK Constraint and not just a single field.
Ronald Hahn, CNT - Computer Engineering Technologist
New Technologies Analyst
HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net
|
|
|
|
|
HahnTech wrote:
You can take 3 fields (who cares what they are) and put them into one PK constraint.
I know. I'm just saying that you're making poor choices. Not that they are invalid.
HahnTech wrote:
You can't make a FK to a non Unique field and non of the fields in the PK are unique; however the combination of the 3 fields is unique. I wan't to know if i can make a FK to a PK Constraint and not just a single field.
My point was just that you can't cram 75 characters of field into 25 chars, and the fact that they are varchars makes it even more complex. I dunno if you can have a foreign reference to a multiple primary key, but if you can, it won't work the way your code showed. Personally, I think your table is crying out for an identity field.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
I agree with Chris.
It's better if you set an identity field and make it your FK.
Edbert P.
Sydney, Australia.
|
|
|
|
|
I think an identity field would be the cats ass as well; however i have a boss that dons't aggree with the rest of the world on this one. Will SQL2000 allow a FK refernce to a either a Primary Key or to a group of tables that are constraind unique?
Ronald Hahn, CNT - Computer Engineering Technologist
New Technologies Analyst
HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net
|
|
|
|
|
Instead of stating FOREIGN KEY after the column, you must state it as a separate constraint at the end. You need to say:
CREATE TABLE ThatTable
(
Field_A varchar(25) not null,
Field_B varchar(25) not null,
Field_C varchar(25) not null,
MoreFields varchar(25)
CONSTRAINT FK_ThatTable_ThisTable
FOREIGN KEY (Field_A, Field_B, Field_C)
REFERENCES ThisTable (Field_A, Field_B, Field_C)
) In the parentheses after FOREIGN KEY , state the columns in this table that make up the foreign key. After REFERENCES , put the table containing the corresponding key data and inside the parentheses the fields in that table that make up the referenced key data.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Sweet. I havn't had a chance to test it but it looks like it should work. Thanks
Ronald Hahn, CNT - Computer Engineering Technologist
New Technologies Analyst
HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net
|
|
|
|
|
In the definition of thatTable, you didn't mention whether Field_1 is unique? If it is, then the rest of the table's columns should be added as other columns on ThisTable.
Chris Meech
I am Canadian. [heard in a local bar]
Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]
|
|
|
|
|
hi all this is killing me this statement appears to be correct but not sure
<br />
strSQLUpdate = "UPDATE Software SET ((Name), (Description), (Version), (ProductKey), (Location)) = (['" & n & "'] , ['" & d & "'], ['" & v & "'], ['" & p & "'], ['" & l & "']) WHERE (Name) = {'" & n & "'}"<br />
i have tried with ( ) & with out { } as well
VB6 app to ms access.mdb
please help
thank you
Help is great only if you ask correctly
|
|
|
|
|
Too many brackets for a start. What's going wrong ?
What are you setting Name, Description, Version, etc. to. If nothing, take them out, if something, specify it.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
Correct me if I'm wrong, but I think the syntax you used is only for INSERT (well, not quite, but similiar), not UPDATE.
Try modifying your statement to:
"UPDATE Software SET Name = '" & n & "', Description = '" & d &"', Version = '" & v & "'" WHERE Name = '" & n & "'
Also, you don't need to set the name when it is used as the condition as the name would have the same value anyway.
Hope it helps
Edbert P.
Sydney, Australia.
|
|
|
|
|
that did it
thank you
the insert statement is like this
<bold>INSERT INTO Software <bold>SET (Name, Description, Version, ProductKey, Location )<bold> Values ( blah blah blah)
Help is great only if you ask correctly
|
|
|
|
|
I recommend using parametized queries. See the documentation for the Parameters property of the OleDbCommand /SqlDbCommand classes.
With parametized queries you avoid problems like invalid characters (a quote for example) or someone trying to hijaack your database by inserting SQL statements.
EDIT: See this[^] post.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Hi...
I am trying to save data into an excel sheet. The application is working fine here on my local machine (FAT 32). But when I try to run the same application on server (I think NTFS), following error is generated.
What you say? Permission problem or Rights issue?
Please help !!.. .I am stuck ..
Could not find installable ISAM.Microsoft JET Database Engine at
System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at
System.Data.OleDb.OleDbConnection.InitializeProvider() at
System.Data.OleDb.OleDbConnection.Open() at PROJ.ClientPaymentData.Button1_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\PROJ\ClientPaymentData.aspx.vb:line 67
-------------------
<b>Therez No Place like ... 127.0.0.1</b>
|
|
|
|
|
Hi
Got a Problem with my Data Records. They'll be added double.
By onclick the Add Button the Records are double applied??
What's wrong with my Code?? It's an Access Database.
private void btAdd_Click(object sender, System.EventArgs e)
{
DataAdd();
}
public void DataAdd()
{
OleDbDataAdapter dbAdapter = new OleDbDataAdapter();
conn = new OleDbConnection(ConnectionString);
conn.Open();
string sql ="INSERT INTO tbl_Artikel (Rubrik, Artikel, Regal, Fach, Hersteller, Beschreibung , Preis, Datum, Bestellungsdatum, Lieferungsdatum, Garantie, Seriennr, Mitarbeiter, Verwendung, Lieferant) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
OleDbCommand icmd = new OleDbCommand(sql,conn);
dbAdapter.SelectCommand = icmd;
icmd.Connection=conn;
icmd.Parameters.Add("@Rubrik", OleDbType.VarChar, 50).Value = this.textBoxRubrik.Text;
icmd.Parameters.Add("@Artikel", OleDbType.VarChar, 50).Value =this.textBoxArtikel.Text;
icmd.Parameters.Add("@Regal", OleDbType.VarChar, 50).Value = this.textBoxRegal.Text;
icmd.Parameters.Add("@Fach", OleDbType.VarChar, 50).Value =this.textBoxFach.Text;
icmd.Parameters.Add("@Hersteller", OleDbType.VarChar, 50).Value =this.textBoxHersteller.Text;
icmd.Parameters.Add("@Beschreibung", OleDbType.VarChar, 50).Value =this.textBoxBeschreibung.Text;
icmd.Parameters.Add("@Preis", OleDbType.VarChar, 50).Value =this.textBoxPreis.Text;
icmd.Parameters.Add("@Datum", OleDbType.VarChar, 50).Value =this.textBoxDatum.Text;
icmd.Parameters.Add("@Bestellungsdatum", OleDbType.VarChar, 50).Value =this.textBoxBestellungsdatum.Text;
icmd.Parameters.Add("@Lieferungsdatum", OleDbType.VarChar, 50).Value =this.textBoxLieferungsdatum.Text;
icmd.Parameters.Add("@Garantie", OleDbType.VarChar, 50).Value =this.textBoxGarantie.Text;
icmd.Parameters.Add("@Seriennr", OleDbType.VarChar, 50).Value =this.textBoxSeriennr.Text;
icmd.Parameters.Add("@Mitarbeiter", OleDbType.VarChar, 50).Value =this.textBoxMitarbeiter.Text;
icmd.Parameters.Add("@Verwendung", OleDbType.VarChar, 50).Value =this.textBoxVerwendung.Text;
icmd.Parameters.Add("@Lieferant", OleDbType.VarChar, 50).Value =this.textBoxLieferant.Text;
icmd.ExecuteNonQuery();
DataSet DS = new DataSet();
dbAdapter.Fill(DS, "tbl_Artikel");
conn.Close();
}
|
|
|
|
|
It is because you are calling the INSERT command twice
The first time is here:
icmd.ExecuteNonQuery(); The second time is here:
dbAdapter.Fill(DS, "tbl_Artikel"); The reason it inserts on the Adapter's Fill method is that previously in your code you have set
dbAdapter.SelectCommand = icmd; So, when your adapter attempts to fill a dataset, it actually inserts a row into the database. You should set the SelectCommand property of the DataAdapter to a command that performs a SELECT
Does this help?
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
|
Hi All
im trying to run the below query in Ms-Access
ALTER TABLE cphistory MODIFY unique_number TEXT(50)
which i want to use to chnage the data type of unique_number
but i get a Syntax error
can ne1 help
thanks
si
|
|
|
|
|
|
thanks
si
|
|
|
|
|
Export excel files into sql server using either applications or codings
Eshakarthikeyan
|
|
|
|
|
You can use the DTS wizard that comes with SQL Server to import the data into SQL Server...
Store your favourite bookmarks online: my-faves.co.uk
|
|
|
|
|
Hi
I need some Help. I want to filter Records from one Table.
I got a new Form with two TextBoxes and one DataGrid. The
DataGrid shows the Results after Click on the Button named "Find".
One Textbox is for the DataBase ID. The other TextBox is for common
Text.
Now i need to assign the entered Text from the Textboxes to the sql String.
I hope you know what i mean cause my English is little bit bad sometimes.
I got the following Code and it works.. but how can i assign the text to the
sql Query? So it is static.
private void find ()
{
ds = new DataSet();
string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=...
OleDbConnection conn = new OleDbConnection(ConnectionString);
OleDbCommand cmd = new OleDbCommand("SELECT * FROM tbl_Artikel WHERE ID =88 OR Hersteller LIKE 'Asus' OR Hersteller LIKE ''",conn);
dbAdapter = new OleDbDataAdapter(cmd);
dbAdapter.Fill(ds, "tbl_Artikel");
dataGrid1.DataSource = ds.Tables["tbl_Artikel"].DefaultView;
}
private void btFind_Click(object sender, System.EventArgs e)
{
find();
his.dataGrid1.AlternatingBackColor=Color.Wheat;
}
I tougt something like that, ("SELECT * FROM tbl_Artikel WHERE ID='"+this.textBoxID+"'",conn);
but it don't work furthermore i've heard this is no good solution.
What can I do???
|
|
|
|
|
realmontanakid wrote:
I tougt something like that, ("SELECT * FROM tbl_Artikel WHERE ID='"+this.textBoxID+"'",conn);
but it don't work furthermore i've heard this is no good solution.
That is correct. It is vunerable to SQL Injection Attack. Here is more information about preventing SQL Injection Attacks[^]
What you should do is create parameterised queries.
This means that your command will be something like this:
OleDbCommand cmd = new OleDbCommand("SELECT * FROM tbl_Artikel WHERE ID = ? OR Hersteller LIKE ?",conn);
cmd.Parameters.Add("@id", this.textBoxID.Text);
cmd.Parameters.Add("@hersteller", this.textBoxHersteller.Text);
DISCLAIMER: I am not familiar with exact Access syntax so the above is a guide only. The ideas are correct; the exact implementation may not be correct. For more information on see MSDN: OleDbCommand.Parameters[^]
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
Oracle has got a package called DBMS_SESSION that allows session data to be stored against a connection.
Does SQL Server 2000 have an equivalent feature?
Thanks.
|
|
|
|
|