|
Hi,
I am writing a stored procedure.
I want to update the records in a table.
till i complete the updation of records I want to lock the table and once i complete the updation i want to release the lock on the table.
during the process of updation of records i don't want any other methods to access this table.
How to achieve this
Regards,
Uma
|
|
|
|
|
It happens automatically. As soon as you start making changes the table will get locked until the transaction is completed. Since SQL Server will wrap each statement in its own transaction, if you are updating over several SQL statements use Begin Transaction at the start and Commit Transaction when successfully finished.
|
|
|
|
|
Hi! consider a simple sql query:
SELECT empName FROM tblEmployee
i want the color of text due to result of this query become RED.
i.e.
ram
tom
jenny
bablu
..etc
please help me.
|
|
|
|
|
The database doesn't handle doing text colors. This can only be done by your app after it does the query to the database.
"I'd like to help but I don't feel like Googling it for you."
|
|
|
|
|
i am using vb.net with sql2000
ihave used adodb.connection and adodb.recordset
then i have used a dataadapter and dataset to show resords in datagrid
now i want to update changes made to the data in the datagrid
can any one help me
bhaskar varma
|
|
|
|
|
can be easily found in the MSDN! everything can be done by using the DataAdapter.
example from MSDN:
' Assumes connection is a valid SqlConnection.
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT CategoryID, CategoryName FROM Categories", connection)
adapter.UpdateCommand = New SqlCommand( _
"UPDATE Categories SET CategoryName = @CategoryName " & _
"WHERE CategoryID = @CategoryID", connection)
adapter.UpdateCommand.Parameters.Add( _
"@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")
Dim parameter As SqlParameter = adapter.UpdateCommand.Parameters.Add( _
"@CategoryID", SqlDbType.Int)
parameter.SourceColumn = "CategoryID"
parameter.SourceVersion = DataRowVersion.Original
Dim dataSet As DataSet = New DataSet
adapter.Fill(dataSet, "Categories")
Dim row As DataRow = dataSet.Tables("Categories").Rows(0)
row("CategoryName") = "New Category"
adapter.Update(dataSet, "Categories")
-walter
|
|
|
|
|
Dear friends,
I want to insert a new row in my table which is supposed to be the ditto copy of one of the previous rows. Now the problem is that there is one field in my table having datatype text. This column value is not getting copied from source to destination and i get error from SQL server that it is not allowed to copy text, nvarchar and binary types.
Now if i convert the column value to varchar then it gets coppied. But i dont want to convert it to varchar because varchar has a limit of 8K characters, and my text column may contains more than 8K characters; and they get truncated while converting to varchar.
Please tell me how to copy the text datatype value from one row to the other.
Imtiaz
|
|
|
|
|
Imtiaz Murtaza wrote: I want to insert a new row in my table which is supposed to be the ditto copy of one of the previous rows. Now the problem is that there is one field in my table having datatype text. This column value is not getting copied from source to destination and i get error from SQL server that it is not allowed to copy text, nvarchar and binary types.
With SQL Server 2000:-
Insert Into t4 (f1,f2) Select f1, f2 From t4 Where f1=2
Here f2 field is of type text in t4 table. This query is successfully executing. I guess you are using functions with text type field, which may be a reason for the error.
Imtiaz Murtaza wrote: Now if i convert the column value to varchar then it gets coppied. But i dont want to convert it to varchar because varchar has a limit of 8K characters, and my text column may contains more than 8K characters; and they get truncated while converting to varchar.
Please tell me how to copy the text datatype value from one row to the other.
Try to follow this technique:
Insert Into t4 (f1, f2) Select f1, Substring(f2,1,8000) + Substring(f2,8001,8000) from t4 where f1=3
Hope this may help.
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
Need the steps for importing .dbf file to MS-Access through C# code
Need the steps for exporting MS-Access table to .dbf file(dBaseIII).
|
|
|
|
|
I have merged two datasets using "Dataset.Merge" command, which the data is retrived from two different dBase-III databases of same structure.
Can I create a new database of using dataset, if yes, how...?
Regards,
Kishore.
|
|
|
|
|
i want taht mdb file in vs2005 support utf8 . how? 
|
|
|
|
|
hi all,
first of all Merry Christmas to all of you.
Problem : I'm using MYSQL database, windows forms amd a Grid View. when i'm assigning a DataSet as Datasource to GridView its not working though my dataset has records. Any special configuration is needed ot any property has to be set.
Any type of help will be apprecited.
Regards,
Rahul
|
|
|
|
|
please first check your dataset is data exist or not if yes then you just try it.
I thing it is very use full for you.
DataGrid1.DataSource = objDs.Tables[0].DefaultView();
Enjoy
Pavan Pareta
|
|
|
|
|
I'm not able connect my front end "Borland developer Studio 2006" with postgreSQL 8.1.I'm developing my application with ASP.NET and C#.NET in "Borland developer studio 2006" environment.Please help in this matter by sending the "connection string" and "namespace to use" in db connection..
I'm using layer coding in ASP.NET and C# (code behind).I'm new to postgreSQL 8.1.4,previously i'm using SQL Server 2000,in that i use this codes to connect my front end.
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
namespace suyam
{
public class Election
{
public SqlConnection con=new SqlConnection("initial catalog=election;user id=sa");
public SqlCommand cmd;
public SqlDataReader dr;
DataSet ds=new DataSet();
SqlDataAdapter da=new SqlDataAdapter();
hari
|
|
|
|
|
|
Hi All,
i have 25 XML file. i had make Dataset using below code.
dsObj.ReadXml(strFilePath.Trim());
now i have 25 dsObj. now i need to retrive data from diffrent-diffrent dsObj. so its take a lot of time to read and all values from the dsObj.
So Please Help me that i am make a New One objDataSet that will have
objDataSet.Tables.Count==25
what i am put on this objDataSet Inner Join in this objDataSet. but how ???????/
Pavan Pareta
|
|
|
|
|
Hi
First of all Merry Cristmas all of you.
My question is:
I have an table in my database where one of the columns have default value = GETDATE() and this column also marked as NOT NULL. I.e. when i insert an row in this table by mean of QueryAnalyizer and INSERT statement
INSERT INTO MyTable (Col1, Col2) VALUES (1,2) and suppose what Col3 is the described column. All work fine.
But when i isnsert this row by using DataGridView (this DataGridView have 2 displayed column where may be enetered significant (not default) values for table) and try to save this changes i have exception (Col3 cannot be NULL),
Plaese help: HOW i can avoid this.
Thank
|
|
|
|
|
Actually this is a very good question.
I have assumed that you used typed dataset in your code (that is bounded to the DataGridView).
* The nature of the ADO.NET is a disconnected way of work. Therefore I thought first that we need to define the default property in the DS. When the value of a DateTime column in the DataGridView and the underline DS isn't supplied it is assigned with its default value. So I have tried to accomplish this solution by defining a default value for the DateTime column in the DS's schema. After reading W3C and MSDN definitions of the DateTime constants I have added the following to the schema:
default="2006-01-01T00:00:00"
but surprise, surprise:
I have got the following statement (which actually states that the DS cannot be generated):
"Failed to generate code. Invalid Primitive Type: System.DateTime. Only CLS compliant primitive types can be used. Consider using CodeObjectCreateExpression."
Somehow it seems to be a bug, or maybe the DateTime type should be declared different in the schema when using defaults ??? (Does anybody know something about it ???)
After searching the web a while I have found that
urn:schemas-microsoft-com:xml-msprop can be used. In nutshell it can be used to access the DS's columns that are DBNull.
So this is how the schema looks like now:
<xs:schema
...
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop"
...
>
...
<xs:element name="d" type="xs:dateTime" codegen:nullValue="2006-01-01T00:00:00" minOccurs="0" />
...
But this isn't the end! In order to actually pass the default value to the DB we should assign this value to the desired row by providing the RowChanged event, like follows:
<br />
...<br />
tds.t.tRowChanged += new tRowChangeEventHandler(t_tRowChanged);<br />
...<br />
<br />
private void t_tRowChanged<br />
(object sender, TDS.tRowChangeEvent e)<br />
{<br />
if(e.Action == DataRowAction.Add)<br />
{<br />
e.Row.d = e.Row.d;<br />
}<br />
}<br />
Brrrr... this is a very ugly solution.
* Another way to solve this problem is to avoid to insert values that are NULLs (or DBNulls) i.e. when the values are nulls generate insert sql statement that just omits those values. When omiting the null values the default values of the DB will be applied.
Uri
|
|
|
|
|
Thanks for contribution
Very usefull
Already one question: You wrote
---
* Another way to solve this problem is to avoid to insert values that are NULLs (or DBNulls) i.e. when the values are nulls generate insert sql statement that just omits those values. When omiting the null values the default values of the DB will be applied.
---
How i can accomplish this?
I suppose this may be done just of substition of InsertCommand proerty of adapter class, but this don't work
Already you wrote:
<br />
private void t_tRowChanged<br />
(object sender, TDS.tRowChangeEvent e)<br />
{<br />
if(e.Action == DataRowAction.Add)<br />
{<br />
e.Row.d = e.Row.d;
}<br />
}<br />
---
And another thing, you suppose what dateTime column has default value, but
in my case one actually not default(one defined when row insert in db by GETDATE() function)
Regrats
|
|
|
|
|
Hi,
1. The cleanest way to do it in my opinion is to use SPs. The SP will test using sql whether the DateTime is NULL and if so won't insert it.
<br />
...<br />
SqlCommand insertCommand = new SqlCommand(...);<br />
insertCommand.CommandType = CommandType.StoredProcedure;<br />
insertCommand.CommandText = "YourSPNameHere";<br />
...<br />
2. It seems wrong but it's ok.
if you read about nullVale in urn:schemas-microsoft-com:xml-msprop (with replacement value) you will find that it just provides a mean to access DBNull cell without throwing an exception, but it doesn't assigns that default value to the DS's cell.
Therefore this code just assigns that default value to the desired cell.
3. Yes, you are right.
You can change the code I have provided to be:
<br />
private void t_tRowChanged<br />
(object sender, TDS.tRowChangeEvent e)<br />
{<br />
if(e.Action == DataRowAction.Add)<br />
{<br />
e.Row.d = System.DateTime.Now;<br />
}<br />
}<br />
or to use SP as I have stated early.
Uri
|
|
|
|
|
|
Dear all,
i have tow tables 'Users' and 'Applications'
every user may be has no,one or more applications
how to write a SQL satement that get me every user who hasn't application.
in other word i want user without application...
thanks.
|
|
|
|
|
It would be helpful to answer if you state the field names, type
and with which field both tables are connected.
I think you have to do something like:
select userId from user_table where (Application_table.applCnt = 0 and Application_table.userId = user_table.userId)
if both tables are connected by userId.
Thanks & Regards,
Suman
|
|
|
|
|
select * from Users
where not exists (
select A.AppID from Applications A
where A.AppID = Users.AppID
)
|
|
|
|
|
SELECT DISTINCT Users.IDApp FROM Users WHERE Users.IDApp NOT IN (SELECT Application.IDAp FROM Application)
"My advice to you is to get married. If you find a good wife, you will be happy; if not, you will become a philosopher." Socrates
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
