|
There're was an article on MSDN covering this issue, but I cannot find it.
What it suggests is to configure your EquipmentID to use negative autoincrement values:
<br />
ds.Tables["Equipment"].Columns["EquipmentID"].AutoIncrementSeed = -1;<br />
ds.Tables["Equipment"].Columns["EquipmentID"].AutoIncrementStep = -1;<br />
...and to modify your insert SP (or insert code if you use Access) to return the newly created identity value. SP would look like this:
<br />
CREATE PROCEDURE dbo.daInsertEquipment<br />
(<br />
@EquipmentID int OUT,<br />
@EquipmentDescription nvarchar(200)<br />
)<br />
AS<br />
INSERT INTO Equipment<br />
(EquipmentID, EquipmentDescription)<br />
VALUES (@EquipmentID, @EquipmentDescription) <br />
<br />
SET @id = SCOPE_IDENTITY()<br />
HTH
Alexandre Kojevnikov
MCP (SQL2K)
Leuven, Belgium
|
|
|
|
|
How to backup StoreProcedure, View, database... in Oracle.
Help me please!!!
Thank you.
***Hung***
|
|
|
|
|
Oracle has a myriad of ways to backup the database. I suggest you check out the Backup & REcovery Concepts guide for details on all of them.
Unfortunately there is no wasy way for me to tell you how to do this without knowing your environment in detail. Basically your options are: Export, Traditional cold backup, and backup using recovery manager (or EBU if you're on 7.X). The export is the simplest but not real efficient and can be costly in resources. Traditional works fine, but requires downtime and is also costly in resources. Rman is relatively efficient, but fairly complex to use and setup.
If yo need a quick backup, most dbas use export when in a pinch if there aren't any other options.
Mark Conger
Sonork:100.28396
|
|
|
|
|
I have problems when i use the #import directive to import the msado and msadox DLLs in the same application.
#import <C:\Archivos de programa\Archivos comunes\System\ado\msado15.dll> no_namespace rename ("EOF", "adoEOF")
#import <C:\Archivos de programa\Archivos comunes\System\ado\msadox.dll> no_namespace
Has anyone found this problem? and the solution?
Thanks in advance.
|
|
|
|
|
Don't use < and > between the path, use:
#import "msado15.dll" no_namespace rename("EOF", "adoEOF")
Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!
|
|
|
|
|
Hi all
I have a typed DataSet called MyDataSet and I populate it by calling Fill on the adapter eg
MyDataSet mds = new MyDataSet();
myAdapter.Fill(mds);
now I use this DataSet to populate a listbox eg
myListbox.DataSource = mds;
myListBox.DataTextField = "Text";
myListBox.DataValueField = "ID";
myListBox.DataBind();
but I don't really like this - what if my DataSet changes (as would happen early on in a project)
I may lose the ID or Text columns and the only time I would notice would be at runtime.
Is there any Information in the typed dataset that contains my column names? so I could do something like
myListbox.DataSource = mds;
myListBox.DataTextField = mds.Tables[0].colText.ColumnName;
myListBox.DataValueField = mds.Tables[0].colID.ColumnName;
myListBox.DataBind();
so that if say Text is remved from my DataSet schema then on next compilation colText would no longer exists and the compilation would break and then it can be fixed.
I see that the columns are in there but made internal - arrgghhhhh
Please help
Shaun Wilde
|
|
|
|
|
Shaun Wilde wrote:
Is there any Information in the typed dataset that contains my column names?
mydataset.Tables["mytablename"].Columns[index].ColumnName;
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
true - I did look at this before I posted -
but that still requires me to understand what info is in what column - if my column disapeared or moved (changed index) then it would still compile but it would go wrong at runtime - what I was hoping for was a more strongly typed solution (if one exists) that would be caught at compile time rather than runtime.
|
|
|
|
|
How about
myListBox.DataTextField = mds.TheTable.TextColumn.ColumnName;
myListBox.DataValueField = mds.TheTable.IDColumn.ColumnName;
Replacing TheTable with whatever your table is named.
It seems to work in the quick test app I threw together.
DataSet1 o = new DataSet1();
MessageBox.Show( o.Staging.DescriptionColumn.ColumnName );
Displays a message box with 'Description' in it.
Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.
|
|
|
|
|
true - but as I said before the custom tool makes them internal which is great if you are using them
in the same project but no good if you wish to use them from a library eg as an object in the toolbox - I can't see any reason for them to be made internal other than to annoy people.
you can change the access specifiers but when the wizard runs again it will overwrite them 
thanks
Technically speaking the dictionary would define Visual Basic users as programmers.
But here again, a very generalized, liberal definition is being employed and it's wrong
- just plain wrong - Tom Archer 5/12/02
|
|
|
|
|
Ah, I see the problem now. That is annoying
Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.
|
|
|
|
|
How can I pass,number of column instead of its name in my SLELECT statement.Like this:
Select * from mytable WHERE COLUMNNUMBER5='something'
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
Hi Mazdak
Mazdak wrote:
How can I pass,number of column instead of its name in my SLELECT statement.Like this:
Select * from mytable WHERE COLUMNNUMBER5='something'
I'm not aware of any database engines that allow you to directly do what you have asked for. However you should be able to persuade .Net to help you out with something like the following:
OleDbCommand comm = new OleDbCommand("mytable", conn) ;<br />
comm.CommandType = CommandType.TableDirect ;<br />
OleDbDataReader reader = comm.ExecuteReader(CommandBehaviour.SchemaOnly) ;<br />
DataTable dt = reader.GetSchemaTable() ;<br />
reader.Close() ;
The dt datatable should now contain details of each column in the table. Use the following to show the details on a grid:
this.dataGrid1.SetDataBinding(dt) ;
You should now be able to fabricate a new SQL Select statement by reading the appropriate column details (the ColumnName and DataType) from the datatable.
Hope this helps.
Andy
|
|
|
|
|
Thnkas Andy. Thats what I want. 
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
I'm working on a video rental system. I have a table of data that looks something like this:
DateRented RentalType
----------------------- -----------
11/11/2002 11:21:47 AM VHS
11/11/2002 11:44:28 AM VHS
11/13/2002 11:37:05 AM VHS
11/13/2002 10:54:14 PM DVD
11/14/2002 2:57:58 AM DVD
11/14/2002 2:58:31 AM DVD
11/14/2002 3:39:19 PM DVD
11/15/2002 10:38:32 AM VHS
11/15/2002 10:49:06 AM DVD
What I need is a query that will return three values--the date in question (day only), a count of all items that were rented, and a count of all DVDs that were rented. I have tried using the GROUP BY clause, but I can't seem to get it right. See, I don't care about the time in the date field. I just want a count for all items and DVD items for a particular day. So my output should look like this:
DateRented TotalRentals DVDRentals
-------------- --------------- ----------
11/11/2002 2 0
11/13/2002 2 1
11/14/2002 3 3
11/15/2002 2 1
Thanks in advance.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Try something like:
SELECT
DateRented,
COUNT(1) As TotalRentals,
SUM(DVD) As DVDRentals
FROM
(
SELECT
CAST(
CAST(Year(DateRented) As varchar(4))
+ '/' + CAST(Month(DateRented) As varchar(2))
+ '/' + CAST(Day(DateRented) As varchar(2))
As datetime) As DateRented,
CASE
WHEN RentalType = 'DVD' THEN 1
ELSE 0
END As DVD
FROM
Rentals
) As R
GROUP BY
DateRented
ORDER BY
DateRented
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
Matt
The SQL statement is likely to depend upon the database that you are using. For Oracle, the statement would be:
select trunc(DateRented) DateRented,<br />
count(*) TotalRentals,<br />
sum(decode(RentalType, 'VHS', 1, 0)) VHSRentals,<br />
sum(decode(RentalType, 'DVD', 1, 0)) DVDRentals,<br />
from Rentals<br />
group by trunc(DateRented)
The trunc() function gets rid of the time portion of a date column. If there isn't a similar function for SQL-Server then I know that there is a function that allows you to convert the date-portion of a date column into a string.
The decode() function above returns 1 if the rental is a video, and 0 if the rental isn't. By summing the 1's, you get the total number of video rentals. Other database engines normally provide a case or iif function that would give you the same effect.
Hope this helps.
Andy
|
|
|
|
|
I'm actually toying with what database I should use. I was given the data in Access, though, so I'm starting there. The query functionality is not very powerful, so I don't think it has the functionality you describe. I will most likely end up moving this over to SQL Server, but I am trying to get something quick and dirty in the near term by just using what Access offers. Oy!!
Thanks for your help.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hi Matt
I got the follow statement to run through the MS-Access query painter:
SELECT DateValue(rentaldate) AS RentalDay,<br />
COUNT(*) AS TotalRentals,<br />
sum(IIf(rentaltype='VHS',1,0)) AS VhsRentals,<br />
sum(IIf(rentaltype='DVD',1,0)) AS DvdRentals<br />
FROM Rentals<br />
GROUP BY DateValue(rentaldate);
Andy
|
|
|
|
|
You are the man!!! Thank you. This is perfect!! I really appreciate it. By the way, do you know of any good source for SQL syntax in Access? I've found a few tutorial sites, but can't seem to find more of a guide on syntax and the differences between SQL Server SQL, ANSI SQL, and Access SQL. Any ideas?
Thanks again.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
The basic SQL syntax (select, insert, etc) is the same for all SQL databases. They start to differ in the following areas:
o Functions that can be used within SQL expressions (e.g. IIF).
o Outer joins.
o Available data-types.
o Whether stored-procedures and triggers are available.
o Security.
I tend to use the MS-Access query-painter to get the basic SQL syntax, then switch over to the SQL-Syntax view for the nitty-gritty stuff. The online help provides details of the available functions (if you look hard enough).
Regards
Andy
PS: If you start to use MS-Access in a production networked environment then make sure you have a copy of the JETCOMP.EXE utility available (from the MS support website). If your MDB database file get corrupted then JetComp may save your bacon.
PPS: If you want an alternative then the MSDE database engine is included on the MS-Access installation CD (you have to install it manually). This is a cut-down version of the SQL-Server product (so you should be able to use the .Net SQL-Server database classes).
|
|
|
|
|
You've used the term "MS-Access query-painter" a couple of times. Is this something different from the Design View of a query? I've used the expression builder a couple of times too, which is somewhat helpful. I just thought it would be nicer to have a decent reference. Seems like most of the books on the subject are more about the basic functionality available and less about the details of the SQL syntax that differs from other DBs.
As far as using this in a production environment, there's just no way. I don't trust Access. The company that built the application that generates and uses this DB does use access, but they provide no queries for reporting. I just decided that rather than maintaining two sets of data (the original in Access and then copying it to SQL Server), I would just learn how to use the query tool on Access. I will eventually be automating the generation of reporting (charting more than anything) in Excel with Office Automation. I guess I would call learning Access a necessary evil.
Thanks again.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
You're right - I ment the "Design View" and "SQL View" for Queries. I didn't have Access open when I wrote my previous email.
Andy
|
|
|
|
|
Hi all,
I am new in ASP and ADO
when I call recordset.open "select * from db where ID='aa'"
the returned record set seems wrong sometimes. i can not fetch the fields data. if i use this "rs.fields("PIN")" it gives an execption
sometimes it 's fine.
why?
|
|
|
|
|
Hi,
I was wondering whether it is possible to register for a DataTable events from a remote machine.
Lets say i have machine A , which has a DataSet , and let say also that i have machine B, which has a copy ( clone or cluster , i don’t know the right term for it ) and what I’m looking for is a way to know when something in the DataTable in machine A is occurred so i would update the DataTable in machine B.
I thought of registering machine B for the DadaTable events in machine A but i don't know how.
Do you think on a better solution for my problem?
Amir Harel
Why we always search for the generic solution for a specific problem...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
