|
Sanjeewani wrote: I need to know the steps to connect to the Access database from my C#.Net code written using VS 2005.
I typed "C# Connection Access Database" into a search engine and this[^] was on the first page of hits.
|
|
|
|
|
Hello Frnds,
I'm making a Store procedure in which i'm passing a parameter of string type have hotelid like this('1,3'). i want to compare these hotelid from hotel table which have hotelid in int in different records.
like this
-----------------------
hotelid Hotelme
1 Xyz
2 ABC
3 KHL
4 PQR
5 RST
I have hotelid like this (3,5).
I want to get the Hotel Name by store procedure. how can i compare hotelid from string to int while i have hotel id in hotel table is of type INT.
please give me some suggestion.
thanx and regards
Dotnet
|
|
|
|
|
for converting string to int, in SQL Server there are two function.
CAST(<value> AS <datatype>)
CONVERT(<datatype>, <value>)
in your case you can use ...
cast(hotelid AS int)
or
convert(int, hotelid)
|
|
|
|
|
Hello, I'm new to attributes. Attributes seem to solve a problem I have with my data representation. However, how are attributes stored in a database?
Specifically, I'm using VS'03 to build a typed data schema, wherein I create and populate some elements. I can use the SqlDataAdapter to load/retrieve from the dB, no problem. Now, I add attributes to the schema, how do I get them into the dB and what is the representation?
Thanks.
NYC Traveler
|
|
|
|
|
I'm trying to use the SqlDataAdapter to map 2 of 5 columns into a DataTable object and then update changes made to the local table back to the table in the database. Every time the Update command is called, I get DBConcurrency errors. I know this is not because of an actual concurrency issue because I am the only one accessing this database and I've tried this code in a separate program to isolate it from anything else I might be doing. I've read in a few places that this can be caused by an invalid update string, but I can't figure out what's wrong with mine. The code I'm using is below.
string sc = "";
string uc = "";
// The following chooses the proper select and update
// commands based on the value of MethodNumber. This
// is where I choose the column in the original table
// that I want to work with and call it OrderPoint in
// the local table. SSID is the PK.
if(MethodNumber == 1)
{
sc += "Select SSID, Method1OrderPoint As OrderPoint From OrderPoints";
uc += "Update OrderPoints Set Method1OrderPoint = @OP Where SSID = @SSID";
}
else
{
sc += "Select SSID, Method2OrderPoint As OrderPoint From OrderPoints";
uc += "Update OrderPoints Set Method2OrderPoint = @OP Where SSID = @SSID";
}
// Create a new DataTable and SqlDataAdapter, fairly standard
DataTable TableOp = new DataTable("TableOp");
SqlDataAdapter AdapterOp = new SqlDataAdapter(sc, Connection);
// Add parameters to the Update command. I've checked the
// SqlRowUpdatingEventHandler and these are being passed properly
// at run time
SqlCommand UpdateCommand = new SqlCommand(uc, Connection);
UpdateCommand.Parameters.Add("@SSID", SqlDbType.NChar, 14, "SSID");
UpdateCommand.Parameters.Add("@OP", SqlDbType.Int, 1, "OrderPoint");
AdapterOp.UpdateCommand = UpdateCommand;
// Add the custom table/column mappings. I've tried every conceivable
// permutation for every possible value I can think of to put in these,
// but no luck so far.
DataTableMapping MappingOp = AdapterOp.TableMappings.Add("OrderPoints", "TableOp");
MappingOp.ColumnMappings.Add("SSID", "SSID");
MappingOp.ColumnMappings.Add("Method1OrderPoint", "OrderPoint");
MappingOp.ColumnMappings.Add("Method2OrderPoint", "OrderPoint");
// Fill the schema and the table. These work fine. The TableOp
// looks and behaves exactly as I would expect.
AdapterOp.FillSchema(TableOp, SchemaType.Mapped);
AdapterOp.Fill(TableOp);
// Change TableOp.
foreach(DataRow CurrentRow in TableOp.Rows)
{
CurrentRow["OrderPoint"] = 1;
}
// Here's the update command.
AdapterOp.Update(TableOp);
This throws the following exception:
System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 records.
I've also tried using a DataSet object instead of the single DataTable with no luck.
I'm using Sql Server Express 2005 with Visual Studio 2003. I've also tried using their visual interface for building an SqlDataAdapter and it fails to generate update and delete commands for a mapped table.
Is it even possible to do this, and if not, what exactly is the point of table/column mapping?
Thanks
-Matt
|
|
|
|
|
My delete trigger will rollback the operation under certain conditions, however even when it does, the statement still returns a count > 0 of records affected (using SQLRowCount). Anybody know of a way to find out the real number of records affected?
|
|
|
|
|
Suppose I have 3 or 4 triggers on a table for UPDATE. Is there a way to control the execution order of them?
|
|
|
|
|
Not really, not on one table. I assume that you are talking about the same type of trigger here. If they are different trigger types, e.g. DELETE and INSERT, then they are fired based on the operation performed on the table.
Why would you want to have multiple triggers fired anyway? I'm not sure I can see what benefit you would get out of this design.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Because, ON UPDATE i need to do several different things on the table.
some are logging things.
some are denormalization for performance.
some are maintenance issues...
but I need then in certain order.
I know, i stick them all in one trigger... But still, I was wondering if it could be done in 3 triggers
|
|
|
|
|
Does anyone know if there is a list of all the sp_ (built in)procedure and their purpose?
Thanks,
Ankur Bakliwal
|
|
|
|
|
Have a look at Books Online. They list all of the procedures, along with descriptions of what they do.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
check this site
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
may help you
|
|
|
|
|
Hi all,,
i got some problem ,, there is not data in my dabase but its properties shows the size of the data base about to 2gb,, why is that , i have trunated the data base and there is not data in the data base , i am so tense ,, what to do can any body help me out please ?
hello
|
|
|
|
|
If ur database is SQL Server 2000,
Shrink db then take Backup of Transaction log
this will empty log db and bring down the space occupied
KP
|
|
|
|
|
Is there any site that provides free space to make and use sql database online
?
Thanks
|
|
|
|
|
BACKUP DATABASE [db] TO DISK = N'D:\Classes' WITH NOFORMAT, INIT, NAME = N'db-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
i use this query to backup my db with sql express 2005.
it's fine.but when i use direct partition(eg. C:\) or desktop it's giving error as os error and access denied
anyone can help me
|
|
|
|
|
Probably because the account that SQL Server is running in does not have access to those drives. (NOTE: This is the account the SQL Server process is using, not the account you use to log in to SQL Server)
|
|
|
|
|
thanks
if i try to backup to "c:\" it's giving that error .but if i backup to "c:\new folder" it's ok.i dont know why's that and what'll i do.i think that's not sql account problem.
|
|
|
|
|
I'm curios what's the best option to clear a database that contains a large number of tables?
Do we need to pick every table and issue delete statements (except those that have cascade rule set)?
Does anybody had any similar issue?
company, work and everything else @ netis
|
|
|
|
|
Another way is simply to extract the schema and create a new database from it.
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
There are three ways I would approach this:
The cleanest and fastest way if it's a database that contains a lot of data is to issue a drop statement against the entire database. I.E. remove the database completely and then recreate it.
The advantage of this is that it's clean and fast if you have to empty a database that already has a lot of data in it. The disadvantage is that you have to recreate the database from code, but it's usually a good idea to have code do that in any case which makes installations etc easier.
The second way depends on the database server itself many SQL servers have some kind of syntax for quickly emptying tables. In MS SQL server you can use something like "truncate table" (oracle too I think) it will delete much faster because it won't log each row deletion, however it will still respect referential integrity so if you have foreign keys etc you will have to either a) Be careful what order you remove the tables in always removing the ones at the outermost "branches" of the relationships first or b) drop the relationship then recreate it after you've truncated.
The third way is to issue delete statements against tables with the same caveats about referential integrity as the second way, but it's much slower than using truncate table.
In my own software I use the second *and* third way because for the areas that need to clean out the datbase they need to keep some of the data in some of the tables. And I have to support Firebird as well as mssql which doesn't have a truncate table command. If this weren't the case I'd go with option 1 every time, nothing is faster or cleaner than that.
|
|
|
|
|
I'm using Firebird and I also need to keep (in some cases) data in some of the tables.
I did go with the third option, however it is just getting more and more complicated 
(and I don't know if it's a good idea to reset the generators also)
company, work and everything else @ netis
|
|
|
|
|
Resetting the generators depends on a lot of things:
Let's say you are using them for a number that is visible to the user of the program and may print on reports etc. If they erase the data and start again it's possible that they might have old documents with the old number on them, in which case it's a bad idea to reset it as it won't be unique.
If they are only used internally and not to any of the unerased data then it's not a problem
I don't use generators for unique record ID's in Firebird because I support more than one type of database and my app generates it's own unique GUID values for records so in Firebird I just use the GUID's instead.
This stuff is always complicated, I have hundreds of tables in my app with some linked 4 deep.
If it was easy they wouldn't need anyone to write software. 
|
|
|
|
|
John Cardinal wrote: This stuff is always complicated, I have hundreds of tables in my app with some linked 4 deep.
If it was easy they wouldn't need anyone to write software.
Exactly 
I guess I'm going on with the 3rd option
company, work and everything else @ netis
|
|
|
|
|
For my code for the 3rd option I just made a helper method to delete the tables with the option of passing a generic List of ID's, then I can just pass that method a table name, it takes care of emptying it and optionally a list with one or more id's which it then constructs into a different format delete to keep the records id'd.
I know it's messy and not really programming to have to keep track of and work with the database schema, but there's no getting around it. You really do have to take the time to determine the correct order to delete those tables and always keep it in mind when adding new tables etc. It's easy to add a new table or feature to a program and then find out later from the end user that the erase database function is now broken. It helps to put it all in one place, the code that creates the schema updates and the code that does the table deletion and also make sure you have a schema level version number in your database itself so that you can handle properly database schema versionsing between your code and the database.
This means you can then put delete and table creation blocks in regions flagged with the db schema version number.
It means nothing at first release, but it's critical down the road for updates.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
