|
see if you can autogenerate your primary key, instead of allowing to enter it manually. This would make sure that the data you are gonna insert would not be duplicate.
to answer your query,
one way of checking if primary key already exists is to use select count(*) query.
Bhaskara
|
|
|
|
|
From a design point of view, it is generally a bad idea to use data which has business meaning to a primary key. This is because data that has meaning has a nasty habit of changing - and changing primary keys can be a messy procedure.
That aside, you would face the same situation if you had a unique index on the column, so I will try and answer your question...
I have found that for problems that have a low likelihood of occurring (such as inserting duplicate data), it is simply best to try and do it, and then handle any resulting error. This is because you're going to have to handle errors anyway, and it does not make sense to spend a lot of time programming around it.
So, I would have a single SP that does the insert, and if the insert returns an error indicating a uniqueness constraint, then I would handle that in a user-friendly manner.
|
|
|
|
|
Hello Steve,
You are right there about using business-sensitive data as primary key. However, in my app, this primary key is a transaction number (for every voucher), that once fed in, can only be cancelled, and the rest of the fields modified - I am not allowing any change in the value of the transaction number _itself_.
Since I wanted the user to be able to make changes to the other contents of the voucher (such as date, amount transacted, accounts to be affected, etc.), I provided a single button - for fetching the transaction if the number entered by him exists in the database, or providing a blank fieldset - and another button that does the Posting of the transaction in the database (besides other buttons for delete/exit, etc.)
The code in that button had to take a decision on whether the transaction already existed or not, and then call INSERT or UPDATE. And since .net already seems to be doing a lot of hardwork internally, i was wondering if it was also providing a simple method/event that one had to perhaps, ping, in order to get to this information ! 
Regards,
Sanjay.
|
|
|
|
|
Hi,
I'm building an Application using C# and ADO.NET. As for now, I'd simply like to open a SqlConnection but the Compiler complains about multiple definitions of SqlConnection...
Here the error:
core\DBConnection.cs(49,4): warning CS1595: 'System.Data.SqlClient.SqlConnection' is defined in multiple places; using definition from 'c:\WINNT\Microsoft.NET\Framework\v1.1.4322\System.Data.dll'
Any ideas?
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
With the following SQL an exception is thrown in .NET which is entirely expected. However I'm not sure why the number is not set to the number in my RAISERROR
["..." have been included for brevity]
INSERT topic(topic_name...)
VALUES(@topic_name...)
SELECT @last_error = @@error
IF @last_error <> 0
BEGIN
ROLLBACK TRANSACTION
IF @last_error = 2627
BEGIN
RAISERROR(50300, 16, 1, @topic_name) WITH SETERROR
RETURN
END
END
With a certain error I want to report out of this stored procedure my own error and I want to be able to pick this up in my C# application with
catch(SqlException SqlEx)
{
if (SqlEx.Number == 50300)
{
}
}
If my RAISERROR is not the result of a previous error then everything is fine, the number I put in RAISERROR gets through to my SqlException.
Does anyone have any ideas of how to get this to work the way I want?
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
My Blog
|
|
|
|
|
My understanding of SQL errors is that they stack up, in a collection. At least that was the way it was when I last worked with SQL errors, (in plain old ADO).
A quick glance at the docs tells me it is the same in .NET - take a look at the docs for the About SQLException class...there is an Errors collection.
|
|
|
|
|
Steven Campbell wrote:
A quick glance at the docs tells me it is the same in .NET - take a look at the docs for the About SQLException class...there is an Errors collection
 I can't believe I missed SqlException.Errors[^]
Thanks.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
My Blog
|
|
|
|
|
Hi!
I need some advice how to retrieve data from an existing MySQL database using .NET Framework.
I’ve earlier retrieved data from an Acces database and then used OleDbConnection successfully. I’ve also used SqlConnection to contact a MS SQL 2000 Server.
In which way should I do the suff with my MySQL database?
Thanks
...and justice for all
APe
|
|
|
|
|
|
Hi,
I am using sql server 7.0 in that i am having a table of 3 feild i.e.
empName, Logindate, HoursWorked
select * from tablename
Result:
empName LoginDate HoursWorked
------- ------------ ------------
Amit 06/03/04 13:44:45
Amit 06/04/04 14:44:45
now i want the sum of the field "HoursWorked"
Can any body help?
|
|
|
|
|
Your results are a little confusing. What is the data type of HoursWorked?
|
|
|
|
|
Actually im taking 'LoginTime' and 'LogoutTime' Datatype
'DateTime' from "login" table and subtraction of this two field is strored in field 'TimeDiffrence' datatype varchar
Query i m using is
"convert(varchar(8),(logoutTime-loginTime),14)Timediffrence"
now i want the sum of the field "Timediffrence"
|
|
|
|
|
hi, i got a question about published the report in access. I use the following code to open the report: DoCmd.OpenReport "Result", acViewPreview, , Query1, acWindowNormal it is working.
But when I using the following code instead: DoCmd.OpenReport "Result", acViewPreview, , "SELECT * FROM Customers WHERE Customers.CustomerID LIKE 'A*'", acWindowNormal it is not running. I just want to use SQL statement instead of query object (Query1). Does anybody knows it problem?
Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Hi all,
This is a query about the usage of SqlDataAdapter class - I would like to know the base or bare minimum requirements that need to be fulfilled in order to use this class.
I have two text boxes - brCode and brName. On the Update button, I have the following code :
dim brDA as new SqlDataAdapter()
.
I declare a brDA.InsertCommand(), and specify the following -
1. Parameters "@brCode" and "@brName" as input parameters. Their .value are set to the text property of the respective textboxes.
2. The .CommandType as a stored procedure that simply does the following -
update <db>
set <fldname> = @parm1
where <fldname> = @parm2
With this, when brDA.Update is called, it expects datatable / row / dataset. In my simple example,
do i still need to create a dataset and populate it with the field values in order to proceed with the Update ? Or should i take an altogether different route instead of the data-adapter ?
Any help shall be appreciated.
Sanjay.
|
|
|
|
|
You can create a SQLCommand object and set the properties to it including CommandText as your SP name, CommadType as 'SP' and add parameters. In your case, params are going to be the two textbox values and then just use SqlCommand.ExecuteNonQuery() which will just execute the SP which will update the underlying table
Bhaskara
|
|
|
|
|
Bhaskara,
Thanks. Will try it out.
Another issue I was struggling with was - in case I _am_ using a data table, and I want to use the DA.Update(), then I just have to initialize the .InsertCommand and am through with the database updation. But if I have to modify an already existing row in the database, then the DA _insists_ that I initialize a .UpdateCommand, _as_ _well_ _as_ a .InsertCommand.
Is that the case, or am I missing some point here ?
Sanjay.
|
|
|
|
|
Yes, you need to set XXXCommands of a DataAdapter. These commands are wisely used by DataAdapter, based on the status of the row in DataSets. Eg. If the row is newly inserted it will use InsertCommand etc.
I suggest you to go thru MSDN. I find it very useful to get more info.
Bhaskara
|
|
|
|
|
Hi
I'm trying to select the latest date entry record for a property out of a table and I thought I could use MAX(productdate), but this only works on it's own, not with other fields in the SELECT from that record. I know I can select all records for that property and sort by date descending but surely there's a better way to do it in SQL to pull all the record details out explicitly without a sort?
Hope you can help,
Patrick Collins
|
|
|
|
|
Hi,
What about this:
SELECT * FROM sales WHERE productdate = (SELECT max(productdate) FROM sales)
HTH,
Thea
|
|
|
|
|
Thank you very much, Thea
Everything & Nothing
http://www.lostsheep.com
|
|
|
|
|
SELECT TOP 1 * FROM table WHERE condition ORDER BY productdata DESC
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
My Blog
|
|
|
|
|
Thanks for for that too Colin
Everything & Nothing
http://www.lostsheep.com
|
|
|
|
|
I'm accessing Oracle data using oo4o VB. Here's the problem:
I'd like to update the unique keys like below. How would I
implement it using oo40 oradynaset. If it can't be done,
how to implement it in ADO?
Ukey (Original -> Target Data)
----
10 -> 11
11 -> 12
12 -> 10
Have tried it but there's a unique key violation error
after I call the Oradynaset.Update method for each row.
I use begintrans/committrans.
|
|
|
|
|
I am a little unclear about why you would be doing this from code. Updating primary keys should never be a part of an application, unless it is a one-time fixup of data.
That said, the problem is that the database will not allow 2 items to share a primary key value, even within a transaction. So, the solution would be do it in two steps, e.g.
Step 1
<br />
10 -> 1011<br />
11 -> 1012<br />
12 -> 1010
Step 2
<br />
1011 -> 11<br />
1012 -> 12<br />
1010 -> 10<br />
You could still keep the above in a single transaction.
|
|
|
|
|
Now the database I have has relationships defined through the tables, albiet the design is more implied in the design of the fields and tables. Realizing that MS Access is nothing more than a front end to the Jet Database, is it worth it to setup relationships in MS Access through the Tools/Relationships menu option? That is, it seems in using this menu option it shows subsheets for the tables that have relationships and perhaps you have a design look and feel in the Relationship window, but on a programmical scale is it worth setting this up? What are the benefits in using that menu option?
Thanks!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
