|
hai frndz,
i am using MS office 2003.
In MSAccess Database is there any way to retrive autoincremt value just after insertion to the table like
select @@identity or select Scope_identity in MSSQL 2000.
in select @@identity some times it return 0 value.
help me plzzzzzzz
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
Well....are you talking about an 'Autonumber' field? If you are, then if your Autonumber field were something like 'ProductID' then you could say something like
SELECT Max(ProductID) FROM Product
|
|
|
|
|
Hi All,
This is my first post in this message board. I am working in ASP.NET 2.0 project using C#.
I am implementing DataAccess mechanism using 3 tier architecture.
UI:In my aspx page, i am using ObjectDataSource, in which the SELECT method is a method from my BLL(Business Logic Layer).
BLL:In this class, it accesses a method from my DAL(Data Access Layer), whose return type is DataTable.
DAL:It uses a User Stored Procedure (USP) to retrieve the DataTable, and it has some OUTPUT variables also.
The whole logic is works fine. The OUTPUT variable's values are captured in BLL class (during run time).
My Problem : I wish to access the OUTPUT variables from the ObjectDataSource's Selected event handler.How to do this?
Previously, i am directly access my DAL from my ObjectDataSource, that time the stmt e.OutputParameters["output_var"] gives me the result.
Thanks.
Regards,
Jay
|
|
|
|
|
The problem is that i didn't passed the arguments in my method in BLL as Reference Variable.
Now i am used the Reference Variable using ref keyword in C#. Now i got the result value in my ObjectDataSource's Selected event handler using e.OutputParameters["output_var"].
Thanks.
Regards,
Jay
|
|
|
|
|
When trying to get a connection to a SQL server thro ASP.net application, if the SQL server is not running , what error usually it will throw, and how can i trace that error.
thanks,
Harsha
|
|
|
|
|
You will get the error "Sql Server does not exist or access is denied."
Mike Lasseter
|
|
|
|
|
Hi. I've had an issue come up in a program that I am working on with multiple projects in the same solution. The way I handle database connectivity is to encapsulate my database connection and my dataset in two separate class files, clsDatabase and clsQueryResults. clsDatabase evaluates a given connectionString and dataProvider to determine whether the app is using an OLEDB, SQLServer, MySQL, or Oracle database and uses the appropriate connections (i.e. OleDbConnection for Access, SQLConnection for SQLServer)
...Anyway, each of the projects that are being put together into this program were developed discretely, so each of them needed to be able to communicate with the database. Thus, clsDatabase and clsQueryResults are located in each of the programs. This causes a problem in my application because it means that I have to maintain 2 separate instances of clsDatabase (both of which connect to the same db file) which is very undesirable. What I would like to do is pull clsDatabase and clsQueryResults out of the individual project files and place them in their own library. However, for security reasons, the db methods located in clsDatabase have been given access type "internal." I would like to keep this library exposed to my projects while not disclosing it to the UI or Application layer. Does anyone know of any way to accomplish this?
For clarity:
This is how my logic looks:
UI->Application->|Project1->|Project1ClassFile->|clsDatabase->|ADO.NET
|
|Project2->|Project2ClassFile->|clsDatabase->|ADO.NET
This is how I want it to look:
UI->Application->|Project1->|Project1ClassFile->|
| |->clsDatabase->|ADO.NET
|Project2->|Project2ClassFile->|
|
|
|
|
|
Maybe I'm being too simplistic, but if you do not want to build clsDatabase into a shared dll, then can't you just use the same class files in each of the projects? I don't understand why you need to "maintain 2 separate instances of clsDatabase."
Put the shared clsDatabase classes in a separate folder. Add the class files to each project as Linked files. Then, both project1 and project2 will have the same source compiled into the application and you can continue to use the internal modifier.
See: Add Existing Item Dialog Box [^]
Add As Link Adds the existing item as a link from the project to its original location. Linking to files is useful when working in teams on shared resources; many projects can link to a file in one location.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Using the class files in each of the projects is how the application is currently managed. The problem is that inside each of the object files inside each of the projects, linkage to the clsDatabase class is maintained inside the context of the project. So Project1.ObjectClassA 's private database connection object is a Project1.clsDatabase. Project2.ObjectClassB 's private database connection object is a Project2.clsDatabase.
My logical design goes like this:
UI->App Layer|->ObjectAList|-> ObjectClassA |->clsDatabase|->ADO.NET|->DB1
|
|->ObjectBList|-> ObjectClassB |->clsDatabase|->ADO.NET|->DB1
I don't want to have to maintain 2 logical channels to my database. I think I can elaborate better through code
At the Application Layer:
I have a "Mediator" class that handles Network connectivity and Business Logic Management. Mediator contains an instance of "Project1List" and "Project2List"
using Project1;
using Project2;
clsProject1List prjList1;
clsProject2List prjList2;
clsProject1.clsDatabase mDatabase1;
clsProject2.clsDatabase mDatabase2;
public clsMediator()
{
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=mydatabase.mdb;";
string dataProvider = "OLEDB";
mDatabase1 = new clsDatabase(connectionString, dataProvider);
mDatabase2 = new clsDatabase(connectionString, dataProvider);
}
As you can see, I have to fetch two db connections from the connection pool when I should actually only be using one. Imagine if this were a module that aggregated 20 or 30 different busines modules that all connected to the same corporate database. .......WAY too convoluted.
The logical design I desire is:
UI->App Logic |>ObjectAList|>ObjectA|
| |>clsDatabase->ADO.NET->Database
|>ObjectBList|>ObjectB|
|
|
|
|
|
Why not send the clsProjectList and clsProject objects from the App Logic layer to the clsDatabase class, and let the clsDatabase class keep a generic collection of these objects?
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi Friends,
I am facing a big problem. whenever i create a table in my SQL Server 2000 database, it is created of the type "System". I am not able to delete that table again.
Can anyone tell me the reason for this and how can i get the table type back to "User"
Sushant Duggal.
|
|
|
|
|
Sushant Duggal wrote: whenever i create a table in my SQL Server 2000 database, it is created of the type "System".
I've never seen that happen before. I wonder if "sys" is a reserved prefix for table names. Of course, you might not be naming your table with "sys...". You didn't actually give much information that could be useful. For example: How are you logged in? How did you create the "system" table? What permissions does your logged in user have?
|
|
|
|
|
hi all
i want to bind a radiobutton in the datagrid to a cloumn in the dB of datatype BIT and i want to make it checked if the bit col value is false and vice-versa
thank
abdelhameed81
|
|
|
|
|
I think a check box would be a better option. Radio buttons are generally used when there is more than two options and only one is possible. Where check boxes are used for true/false values.
Mike Lasseter
|
|
|
|
|
Hi,
I am using SQL Server 2005.
I have a script that was generated to create all my tables.
How do I create a script (INSERT) that takes all the data in my current tables, and creates INSERT statement for each. I want to take all my data that I have locally and insert it into the online database.
Regards,
ma se
|
|
|
|
|
I usually write some SQL like this:
SELECT 'INSERT INTO MyTable(Column1, Column2) VALUES ('+Column1+', '+Column2+')'
FROM MyTable
And then I run it (using a text output, rather than the grid) and then I can copy and paste it back into the query window.
|
|
|
|
|
Thanks for this.
Please explain what you mean by +Column1+, is this the coloumn name?? Or a value of a column?
Does this create a whole bunch of Insert statements?oes it work?
You have my MyTable listed twice, is this correct?
|
|
|
|
|
ma se wrote: Please explain what you mean by +Column1+, is this the coloumn name??
Yes - It is the column name. I don't know what your column names are so I had to use something.
ma se wrote: Or a value of a column?
No. It is the name of the column.
ma se wrote: Does this create a whole bunch of Insert statements?
Yes.
ma se wrote: Does it work?
Why would I answer this if I didn't know it would work?
ma se wrote: You have my MyTable listed twice, is this correct?
Yes - As a learning exercise tell me why they are both needed.
The only other comment I have is that the +Colunm1+ may have to be CAST or CONVERTed to coerce the value of a column into a string, or replace a null column value with a string that says 'null'. Again I don't know your data model to show you how to do that.
|
|
|
|
|
Thanks for the answers, and I want to apologise for "Does it work?" It was supposed to be "How does it work?"
I'll try it soon and get back to you soonest to let you know.
|
|
|
|
|
Hi,
I haven't tried this out yet, going to do so soon, but I was wondering if there wasn't a faster method? What happens if I have like 500 tables (for example)??
The reason why I want to go this way is because I always import/export my data, but then the Identity Specification is set back to No. How can I export my tables and stored procedures to the online database with setting the Identity Specification to No, and keeping it Yes?
Regards
ma se
|
|
|
|
|
ma se wrote: I haven't tried this out yet, going to do so soon, but I was wondering if there wasn't a faster method? What happens if I have like 500 tables (for example)??
You could always create a DTS (in SQL Server 2000) or SSIS (in SQL Server 2005) package - but that isn't an "INSERT script".
If you have 500 tables then you could always come up with a program to automate the generation of the script by having it examine the schema of the tables and auto generate something.
ma se wrote: How can I export my tables and stored procedures to the online database with setting the Identity Specification to No, and keeping it Yes?
Use SET IDENTITY_INSERT[^] as appropriate.
|
|
|
|
|
Hello,
I'm trying to gather the schema information for a tables columns using ADO.Net with OleDb (an Access Database) and Sql (a SQL Server database) and found some problems:
For OleDb/Access:
method 1 to get the MetaData Collections
OleDbConnection currentconnection = ...;<br />
DataTable schema = currentConnection.GetSchema("COLUMNS");
method 2 to get the schema for the commands result:
OleDbCommand cmd = ...;<br />
OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);<br />
DataTable schema = reader.GetSchemaTable();
Both give me the correct list of columns, but the values for IsNullable (from method 1) and AllowDBNull (method 2) differ!?
I'm using the Northwind database for example purposes and for one table (Order Details) I get IsNullable=true for the first foreign key and IsNullable=false for the second foreign key.
I also get IsNullable=false for the other three columns that have a Column_Default set.
Method 2 seems to give the correct values: AllowDBNull=false for the two foreign keys and AllowDBNull=true for the default-value-columns.
In another table (Categories) method 1 returns IsNullable=false for a VarChar column and method 2 returns AllowDBNull=true for the same column, while this time I think method 1 gives the correct answer because SQL Server says IsNullable=false and AllowDBNull=false for this column.
How comes that? Am I missing a difference in this two values? Because I saw many websites using them as synonyms, e.g. http://www.koders.com/csharp/fid74A751F1A6F8ACEB258469216B7C263679DAD88A.aspx
The other problem is when using the SQL Server (again using northwind database). This time both methods return identical values, if IsNullable=false then AllowDBNull=false too.
But: It returns AllowDBNull=false for default-value-columns too? How can i then do an Insert without setting these fields for using the default values?
Sorry for this long text, I'm looking forward for help,
regards J.Schumann
|
|
|
|
|
Did no one got this problem before?
Can somebody help me please!
Thank you in advance,
J.Schumann
|
|
|
|
|
I am drawing a blank on this and i have non eo fmy samples where i am, so i cant for the life of me remembwer how to do this, but...
When doing an Insert into a table that has Identity Seed turned on for the primary key, how do you return the id of the new row?
i know you end the insert statement, then it is somehting like this:
Insert Into dbtable_x (Stuff) Values(@stuff); @selectID
I knwo im not that far off the mark, but computers are some damn picky about syntax....
oooookie dokie.... select @@Identity
At least i figured it out...
-- modified at 13:00 Monday 2nd October, 2006
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
You're safer with using SCOPE_IDENTITY() instead of @@Identity
Below is an excerpt from the SQLTeam:
The gist: Use scope_identity() rather than @@IDENTITY or you might not get what you're looking for.
"ManyTSQL books show you how to use @@Identity to get the identity of the most recently added row. Many articles online, or in magazines show the same. What you might not know is that it is potentially a source for some very hard to trace bugs in your application.
@@Identity is potentially a very, very bad thing! In almost every case, you should use scope_identity() instead.
Why? @@Identity returns the most recently created identity for your current connection. When you first use it, it might be fine. Until someone adds a trigger. If the trigger causes another identity to be created, guess which identity you'll get in your call to @@Identity? Not nice.
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
