|
i just want to know is there anyway to remove field definition from my below x-query. please tell me is it possible exclude field definition from my query. if yes then please give me a sample.
SELECT CustomerID, CompanyName, City, Country INTO #Customers
FROM OPENXML(@iDoc,'/root/customer',2)
WITH (CustomerID NCHAR(5) 'custid',
CompanyName NVARCHAR(40) 'custname',
City NVARCHAR(15) 'city',
Country NVARCHAR(15) 'country')
tbhattacharjee
|
|
|
|
|
How to select data with alternative row.
eg:
i have a table T1 values -
name flag queue
A....y....1
B....x....2
C....x....3
D....x....4
E....x....5
i want the result like following
B
C
A
D
E
A
Insert the flag 'y' after every 2 row.
Without using stored procedure. I want to bind it to a gridview
Thankyou
YPKI
|
|
|
|
|
I may be missing something, but there does not appear to be any obvious link between the data you have shown and the sequence you require as the outcome, except for the order you have shown them in. Is the pattern 2, 3, 1, 4, 5, 1, 6, 7, 1 the only criteria?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hi, I am presenting the solution in C#. It's a web application
What I have done?
Step 1:
One database table named AlternateTable with the same structure as yours
Name Flag Queue
A Y 1
B X 2
C X 3
D X 4
E X 5
Step 2:
I have a class file which is basically fetching the record from the database table AlternateTable
public class DBConnection
{
string connectionPath = "Data Source=SUMERUSYS135\\SQLEXPRESS;Initial Catalog=test;Integrated Security=True";
string command = string.Empty ;
public DBConnection()
{
}
public DataTable FetchRecord()
{
SqlDataAdapter adapter = new SqlDataAdapter();
SqlConnection connection = new SqlConnection(@connectionPath);
command = "Select * from AlternateTable";
connection.Open();
SqlCommand sqlCom = new SqlCommand(command, connection);
adapter.SelectCommand = sqlCom;
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.Fill(table);
return table;
}
}
Step 3: Drag & drop a gridview in .aspx page
Step 4: I am using datatable as my datasource for the gridview.
Since you want to place the first row after every two alternate rows, so the logic will go like this
Algo: Place alternate row
Step 1: Create a custom datatable(say CustomDt) with the column
names that are to be exposed.
Step 2: Get all the records from the database and place it
inside a datatable(say OriginalDt).
Step 3: Loop thru the second record of the datatable(OriginalDt)
till the end.
Step 4: Check if a even row has come or not from the OriginalDt.
If it is true, then add the first record of the
datasource to the Custom Datatable after adding the even
row from the Original Data Table.
Else, add the other records of the original datasource to
the Custom Datatable.
Step 5: Make the Custom Datatable as the source for the gridview
Step 5: Put the above algo into action
protected void Page_Load(object sender, EventArgs e)
{
DBConnection objdbconnection = new DBConnection();
DataTable OriginalDt = objdbconnection.FetchRecord();
#region Final Custom Datatable
DataTable CustomDt = new DataTable();
CustomDt.Columns.Add("Name");
CustomDt.Columns.Add("Flag");
CustomDt.Columns.Add("Queue");
#endregion
for (int i = 1; i < OriginalDt.Rows.Count; i++)
{
if (i % 2 == 0)
{
CustomDt.Rows.Add(OriginalDt.Rows[i][0], OriginalDt.Rows[i][1], OriginalDt.Rows[i][2]);
CustomDt.Rows.Add("A", "Y", "1");
}
else
{
CustomDt.Rows.Add(OriginalDt.Rows[i][0], OriginalDt.Rows[i][1], OriginalDt.Rows[i][2]);
}
}
grdAlternateRow.DataSource = CustomDt;
grdAlternateRow.DataBind();
}
N.B.~ As you can see that I have hardcoded CustomDt.Rows.Add("A", "Y", "1");. As per your statement, I found it to be constant. If you need it in dynamically, you have to apply a different logic.
Hope it helps.
Vote me please
Niladri Biswas
modified on Tuesday, June 23, 2009 2:13 AM
|
|
|
|
|
WITH ABC
AS
(SELECT NAME,FLAG,QUEUE,ROW_NUMBER() OVER(ORDER BY NAME) AS [RN] FROM T1)
SELECT * FROM ABC WHERE RN % 2<>0
|
|
|
|
|
Hi man,
I hope that by mistake you replied to me instead of replying to the original poster.
Second, the user expects the answer not to be in stored proc.
But your solution is nice
Niladri Biswas
|
|
|
|
|
I have a project (web application) where i have to show hierarchical data in form of a tree.For example A has two children B(left) and C(right). B has two children D(left) and E(right). C has two children F(left) and G(right) and so on... My problem is I dont know the exact SQl query to count the number of all the subsequent children for a particular member. Like "how many children A has to his left or right (depthwise)" also i need to show the initial 15 members in form of a tree. If I do recursing for every node using C# code(which I am using) and then filling the form and going on it is going to take forever. So if someone knows the proper SQL query please help me. Or if anyone has any idea?Please tell me.Thanks
|
|
|
|
|
I assume that you have searched for this, but on the off-chance that you've missed it, here[^] is a link to a relevant article. It also has some interesting links in it.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
I have a datagridview that takes data from a dataset from a table with an ID that is an identity specification number that is supposed to automatically give the next number. When I open a new line it should give me the next number but it doesn't. What am I missing?
|
|
|
|
|
I'm guessing that you won't get a number until the record is saved.
I are troll
|
|
|
|
|
Datagridview is not an SQL table. Until the Record is Saved and the Grid is Refreshed you will not get an Identity number. If you made a gridview to generate the number , it does not mean the Database will do the same thing. If your Application is a multi-user application you will have problems e.g lets say a user assumes that the ID is the 10 and the user user does an insert somewhere and faster that the user that assumed it was 10 and the other user will get the identity of 10 and the assumer will get 11.
Hope it helps
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Hi,
I do agree.. you can add an external col into your grid.
Md. Marufuzzaman
|
|
|
|
|
In some grids I get the right number, in one grid I get a minus number which saves ok afterwards but in the grid I'm working on now I don't get any number at all
|
|
|
|
|
Hi,
I am developing an application in which i use to select data from one grid remove that & display it to the second grid.
I store those data in a local mdb file . when i am selecting rows in bulk(around 50-70 at a time)and then wanted to cut and display in the second grid i am getting this error .
Please help.
|
|
|
|
|
Are you forgetting to close your connections, between operations? Or forgetting to close something that you have opened. A DataReader, or something like that.
Without seeing the code you are using, it is difficult for me to offer more than that. Others might be more helpful.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hi,
I am a newbie in programming and all the IT stuff,currently I am developing a webapplication to enhance my knowledge . I am using visual studio 2008,Sql server management studio 2005;in my application I am simply adding names and passwords in a table in Sql.
1)The application works fine when both Sql and published webappliaction on IIS are on same machine!
2)now I want the Sql database to be on other machine, and my published application on other system so that there are two different servers in LAN one the application server and other the database.
please help!
Thanks
shankbond
|
|
|
|
|
shankbond wrote: he application works fine
So you have a working connection string already in your app, it will probably have "localhost" in the string. Change "localhost" to the remote server name.
You will probably need to change from integrated security (using your windows logon) to a SQL userid and password for the app to connect ans include them in the connection string.
A good research site is connectionstrings.com/[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
Mycroft Holmes wrote: You will probably need to change from integrated security (using your windows logon) to a SQL userid and password for the app to connect ans include them in the connection string.
the above solution will help my application to use a sql id and sql passsword fine
but what other changes(or configure) will I have to do in sql server itself?
Thanks
shank
|
|
|
|
|
"Allow SQL Server to accept remote connections"
Quoted, since it is a common search-phrase on Google
I are troll
|
|
|
|
|
You will need to set up the SQL user with adequate permissions on your database. That's it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You will need to set up the SQL user with adequate permissions on your database. That's it.
ok I m trying; any good resources you know except msdn?
shankbond
|
|
|
|
|
Hi,
Actually when you remotely perform any SQL request (using OPENROWSET , BLOB type data) SQL server
need the AuC first. That you have the permission to perform this operation.
Md. Marufuzzaman
|
|
|
|
|
Hey,
I have this table:
JAN FEB MAR APR
--- --- --- ---
10 10 10 10
Suppose I'm in March now, how would I total the values of the two previous months (ie. JAN and FEB) to have 20
Please help.
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Well, if that was what I wanted to do, I wouldn't start from there.
Has this design been forced on you, or are you free to choose your own?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
It is forced on me, here's the big picture:
Jan Feb Mar Apr
--- --- --- ---
Budget 10 10 10 10
Actual 10 5 10 0
... 0 0 0 0
... 0 0 0 0
... 0 0 0 0
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|