|
hi
I have created stored procedure for the selection,insertion and updation for updations while excuting the stored procedure it asks for declaring the variables I dont know how to declare variables in the stored procedure .Can anyone help me.
thanks of help in advance
kal2na2
|
|
|
|
|
you can declare in SQL Server procedure like this
DECLARE @local_variable data_type
E.g.
DECLARE
@I INT,
@S VARCHAR(10),
@M MONEY
For more information you could search "DECLARE" in "SQL Server Book Online"
Fariborz Golara
-- modified at 4:33 Monday 12th December, 2005
|
|
|
|
|
How do you set a password on an Access database. Once you do that, how would you access the password using vb .net. Will it be encrypted, will I need to decrypt the password? If so, how would I go about that?
Thanks
|
|
|
|
|
I'm assuming you mean that you want to create some sort of user/password table because if Access was able to give out the password protecting the database then it would, obviously, be a major security hole in Access; There would be many displeased people out there.
Anyway - see the response[^] in my article on SQL Injection Attacks and tips on how to prevent them[^] as it shows how to implement a user table with a salted hash (a type of one-way encryption.) password.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
You set the password in Access under the Tools\Security menu
Once it is set you change your connection string to include the password
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=YourPasswordHere;"
|
|
|
|
|
Hi
Please tell me in .Net Windows application which of the following is better?
1- using only one "DataSet" because we can
set referential between "DataTable" vs. SQL Server referential
2- using several "Dataset" but set referential in SQL Server.
Thanks.
Fariborz Golara
|
|
|
|
|
Regardless of what you do in your application I would set the referential integrity in the database. That is one of its key design features and one of the things it is best at. DataSets are bloated objects that, in my opinion, should generally be avoided if possible.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Colin Angus Mackay wrote: DataSets are bloated objects that, in my opinion, should generally be avoided if possible.
Interesting. As you are of the guys whose SQL and database knowledge I have a great deal of respect for, care to elaborate on what you see as the cons of DataSets so that I can see if I'm making any poor design decisions. (I use DataSets a lot for reading data into my C# apps, don't use them much for writing the data backout though)
Maybe an article or blog entry would be cool.
Michael
CP Blog [^] Development Blog [^]
|
|
|
|
|
Here is an existing blog entry on the subject: http://weblogs.asp.net/fbouma/archive/2003/05/13/6966.aspx[^]
The main reason for me is that DataSets contain way to much functionality for most of what I ever want to do. Generally I only ever want to have one copy of data in memory at once. If I were to use datasets the DataAdapter would use the DataReader to get the information from the database anyway, then it would store it in the DataSet (copy one), which I would most likey transfer to my business objects (copy two) and then propogate to the user interface (copy three). I much prefer getting the information from a DataReader and controlling exacly what I do with it. (Maybe I'm just a control freak).
Generally I don't need to have referential integrity in a DataSet, or know a great detail about the table structure. Often, I will just create the business objects - one per row from the data reader. In fact, most of my SQL queries don't represent actual tables, but some sort of flattened view or the result of some aggregation or calculation. Certainly, there is absolutely no point in using a dataset if the results are going to be immediately streamed out to a file or the like because all you do is get the data, waiting for the dataset to be built, then stream it out.
I would say that if you are using databinding alot they are useful.
A quick way, I think, of determining if you can throw away the dataset is this: Are you creating the dataset, pulling the data into some other structure (your business objects, for example) then throwing away the dataset? If the answer is "yes", then you can get rid of the dataset.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I need a command in MSSQL that will give crete table sql script like in mysql "show create table..." command.
karanba
|
|
|
|
|
In the Enterprise Manager locate the table that you want the create script for and right click on it. Select All Tasks-->Generate SQL Script.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I know that but I need to do that with an sql command...
karanba
|
|
|
|
|
karanba wrote: I know that but I need to do that with an sql command
To my knowledge you cannot.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Please see "CREATE TABLE" in "SQL Server Book Online"
*********************************************
CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }
| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
)
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]
< column_definition > ::= { column_name data_type }
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]
< column_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ON {filegroup | DEFAULT} ] ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}
< table_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ASC | DESC ] [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}
**************************************
CREATE PROCEDURE Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t
GO
CREATE PROCEDURE Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (99)
GO
EXEC Test1
GO
Here is the result set:
(1 row(s) affected)
Test1Col
-----------
1
(1 row(s) affected)
Test2Col
-----------
2
Fariborz Golara
|
|
|
|
|
He was asking how to generate the command via a script from data already in the database rather than create a table from scratch. Looking in BOL will not help him in this case because the answer is not there.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
You can't do that, but you can query the table's schema using the INFORMATION_SCHEMA views. For example, to get the list of columns in a table, you can use
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=N'MyTable' If you have multiple schemas (tables of the same name with different owners in the same database in SQL Server 2000) you should also supply the TABLE_SCHEMA in your select statement.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
tahks for all..I am on search the subject..if any one heard about any sp about this please write here ..
karanba
|
|
|
|
|
What I want to do:
I have a listview, when the user clicks on a row and presses an edit
button, a dialog is shown and the user can change the row in the dataset.
The dialog contains several databound edit controls.
problem:
The dialog should focus on the right row. most of the time,
this is correct. But sometimes it is not. Not sure why, but it happens
when I add, remove rows from the dataset or sort the data.
Is this approach correct?
what should be the problem?
my code:
<br />
Private Sub btnWICustomerEdit_Click(...) ...<br />
Dim key As Integer = getkey()<br />
<br />
'create form<br />
Dim EditData As frmEditDataActivities = New frmEditDataActivities(...)<br />
<br />
Dim rowIndex As Integer = dsCustomers.Tables(0).DefaultView.Find(Key)<br />
If rowIndex = -1 Then<br />
Return<br />
End If<br />
<br />
'focus on the right row<br />
EditData.SetFocusRow(rowIndex, dsCustomers)<br />
EditData.ShowDialog()<br />
<br />
'do update, save changes<br />
End Sub<br />
<br />
'a member from the dialog class<br />
Public Sub SetFocusRow(ByVal rowIndex As Integer, ByRef dsCustomers As dsCustomers)<br />
Dim cm As CurrencyManager<br />
cm = CType(Me.BindingContext(dsCustomers, dsCustomers.Tables(0).TableName), CurrencyManager)<br />
cm.Position = rowIndex<br />
End Sub<br />
<br />
|
|
|
|
|
hi
i want to insert data into sql server (in C#)
one of the attributes (Article) id define as varchar (size 8000 - the MAX)
i want to insert into Article string lets call the variable S (size lower then 8000 and higher than 1000) but its doesnt work, but if S is define in size 100 its working .
if somebody knows what is the problem i will be glad if he will explain to
me .
eyalso
|
|
|
|
|
e_s wrote: if somebody knows what is the problem
What is the error message?
You can create a row with a number of columns that, in total, excede the maximum row size (8006 bytes, if I remember correctly). You will get a warning when you create such a table structure. It is possible that with the other data in the row something has to give.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I am using Visual Studio 2002 (Visual Basic) and an Access Database.
Some tables are read into a Dataset via oledbDataAdapters.
When I read fields from the datasets, I get good values for strings, numbers and dates or times.
When selecting from dataset tables using “myRows = myTable.Select(Filter, Sort)”, I get correct results unless I use a time field in the select criteria. Then 0 rows are returned.
The select clause is properly formatted. As soon as I drop the time field, I get returned rows. I have bypassed the problem by getting the data directly from the database (hard drive), but it is frustrating! A typical WHERE filter clause is:-
"ID = 12 AND StartTime = #07:00:00# AND ReportDate = #10/11/2005# AND ReportArea = 'Area A'"
A, probably related, anomaly is:
Using the “DataAdapter Preview – Fill” method (design mode), the time fields are represented as 30/12/1899 – (a null or base date?). Despite this, I do read times and dates from the dataset/tables correctly in code.
I’m fed up searching the, often overly extensive, help. “Can’t see the forest for the trees”
Any suggestions welcome!!
|
|
|
|
|
i found in documentation such description but i couldnt find any functions or examples of using them or APIs to manage long data block-by-block... can you help me?
"When the ntext, text, and image data values get larger, however, they must be handled on a block-by-block basis. Both Transact-SQL and the database APIs contain functions that allow applications to work with ntext, text, and image data block by block."
|
|
|
|
|
Hi
I had a question abt the Inconsistent Read concurency issue with my application's data access that I am currently developing. Here is my problem. I am using custom business objects that represent my database entities. For example, in one of the web pages displays a list of users in a datagrid bindng it to customcollection that holds user objects (instance of my user class). I am using Enteprise Library to do my dataccess. my DAL would read the data from the database (makes a call to the store procedure) and pass the custom collection of user objects to the UI. When the user clicks on any row of the displayed list, it takes himt o another page that displays further details of that particular user and he may update them. Do I need to use any lock options while reading the data from the database? I mean optimistic concurrency control?
Any suggestions would be greatly appreciated...Please help.
Thanks
|
|
|
|
|
I am pretty much exclusively using SqlDataAdapter.Fill method.... I am getting the dreaded timeout connection pool limit exceeded. MSDN tells me I dont have to exclusively open and close my connections. Yet! under heavy use, i'm getting these errors in my application. What can I do to make sure these connections are getting closed using this method.
|
|
|
|
|
I use
finally block to make sure that clean up is performed
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
