|
I can't undestand well your question but down below is what i undestand from your question.
asha_s wrote: i have many room that have the same featuer and i want to chose the room that are its size greater than the sumCap
I think this fits to your need.
select sum(capacity) as CapacitySum,feature,<br />
(select top 1 t1.roomid from myTable as t1 where t1.feature = myTable.feature order by t1.roomid desc) as roomID<br />
from myTable <br />
group by feature<br />
order by CapacitySum desc
asha_s wrote: ( i have many room that are grater than the sumCap and i want to chose the one that have the minman capacity from them )
the second fit for you.
select top 1 sum(capacity) as CapacitySum,feature,<br />
(select top 1 t1.roomid from myTable as t1 where t1.feature = myTable.feature order by t1.roomid desc) as roomID<br />
from myTable <br />
group by feature<br />
order by CapacitySum asc
I Love T-SQL
|
|
|
|
|
thanks but i do not want the sum
sumcap:is variable that contain value
i want the room than have the manmam capacity from a set of rooms that are their capacity greater than sumcap
because i have a set of room that have capacity greater than sumcap and
have the same feature and i want to select the one that have the manimam capacity
i hop you understand
please help 
|
|
|
|
|
By manmam you mean minimum? (I hope I had understand you even if i didn't got it 100%)
select top 1 min(roomID) as RoomID,feature from myTable where roomid >=sumCap<br />
group by roomid,feature<br />
having (feature='1100')<br />
order by roomID asc<br />
I Love T-SQL
|
|
|
|
|
yes i mean minimum
can i want the minimum capicity can i write
select top 1 min(Capacity ) as Capacity,feature from myTable where Capacity >=sumCap<br />
group by roomid,feature<br />
having (feature='1100')<br />
order by Capacity asc<br />
thanks so much
|
|
|
|
|
I guess you got right solution, right?
I Love T-SQL
|
|
|
|
|
yes ,thanks so much 
|
|
|
|
|
Cool, you are welcome. 
I Love T-SQL
|
|
|
|
|
i'm using SQL Server 2005, Windows XP SP2, Visual Studio .Net 2003, AVG anti-virus, no firewall, no proxy.
i configure IIS (virtual directory) and SQL Server (publication) as described in Miscrosoft web site.
i installed Microsoft SQL Server 2005 Mobile Edition and configured for web sycronization. i tried everything which mentioned in many blogs, forms and msdn but i recieve the error "Header Information is either is corrupted or missing..."
When i type "http://[Server IP]/[Virtual Directory]/sqlcesa30.dll", it return a valid result but i cannot connect to SQL Server for Pull or Push or SubmitSql process.
here is my code
string rdaOleDbConnectString = "Provider=SQLOLEDB;" +
"Data Source=" + MainForm.ServerIP + ";" +
"User Id=" + MainForm.DBUserID + ";" +
"Password=" + MainForm.DBPassword + "; " +
"Initial Catalog=" + MainForm.DBName + ";";
SqlCeRemoteDataAccess rda = null;
try {
rda = new SqlCeRemoteDataAccess();
rda.InternetLogin = MainForm.WebUserID;
rda.InternetPassword = MainForm.WebPassword;
rda.InternetUrl = "http://" + MainForm.ServerIP + "/" + MainForm.WebDir + "/sqlcesa30.dll";
rda.SubmitSql(txtQuery.Text, rdaOleDbConnectString);
MessageBox.Show("The query execution is successfull");
}
catch (SqlCeException exc){
MessageBox.Show(exc.Message);
return;
}
finally{
rda.Dispose();
}
is there any suggestions?
|
|
|
|
|
abdurrahman o wrote: is there any suggestions?
Yes, read about this error.[^]
I Love T-SQL
|
|
|
|
|
i read and applied them all. Nothing was changed.
I can access http://Server/Term/sqlcesa30.dll.
but i cannot access the same object "sqlcesa30.dll" in C#, gives an error "Header information is either corrupted or missing..."
|
|
|
|
|
I recently wrote an application in C# 2005 and used MSSQL as the backend. After successful completion, I added an Access .mdb file to the app so that I could make it portable. I changed the code appropriately from SQL Command to OLEDB Command, but it does not work. This is the rub: I do not get an error and it will not return results when it is run from inside the program, however, when I copy the command line that fails and paste it directly into the Access Database query tool and run it, it returns a perfect result. I am baffled! I have the feeling that there is a syntax difference between the MSSQL and Access, but I cannot discover it, nor would that explain why it works correctly when it is pasted into the program directly. Following is the SQL that comes directly from the program:
"SELECT DISTINCT T.Trans_Name AS Transmission, M.Manufacturer, M.Concern, M.Years, M.Description, M.ATB " +
" FROM Master AS M INNER JOIN Trans AS T ON M.Trans = T.Trans WHERE M.Manufacturer = @Manufacturer "
Any insight would be greatly appreciated.
Pat
|
|
|
|
|
What do the code to your command object looks like? Maybe you're not connecting to the file correctly. Do a select * for one of the table and see if you get results.
|
|
|
|
|
Thank you for your reply, Youtea.
The application returns results from query's that are built on a series of "if" statements. These are created from the request of the user.
//For Access Query
string Sql1 = "SELECT DISTINCT T.Trans_Name AS Transmission, M.Manufacturer, M.Concern, M.Years, M.Description, M.ATB " +
" FROM Master AS M INNER JOIN Trans AS T ON M.Trans = T.Trans WHERE ";
string Sql2 = " AND ";
string Sql3 = " T.Trans_Name = @Trans ";
string Sql4 = " M.Manufacturer = @Manufacturer ";
string Sql5 = " M.Concern = @Concern ";
string Sql6 = " M.Years = @Year ";
if (textBoxMans.Text != "" && textBoxTrans.Text == "" && textBoxCons.Text == "" & textBoxYears.Text == "")
{
Sql = Sql1 + Sql4;
}
The resulting query string that comes from a selection using the text box "textboxmans.text" is as follows:
"SELECT DISTINCT T.Trans_Name AS Transmission, M.Manufacturer, M.Concern, M.Years, M.Description, M.ATB " +
" FROM Master AS M INNER JOIN Trans AS T ON M.Trans = T.Trans WHERE "
" M.Manufacturer = @Manufacturer ".
This should return results and returns perfectly when pasted into the Access program manually, however, using the C# application, it connects properly but says that there are no matching results for the query, which is both incorrect and also does not match the SAME query when it is pasted into both the Access program or the SQL query analyzer using MS SQL 2000 (exact same table names apply to both MSSQl and ACCESS).Also, if I leave off the added statements and run just Sql1 WITHOUT the "WHERE" clause, it works! I seem to think there is syntax error, but if so, then why does it work when the resulting query, as assembled by the application, is copied and pasted into Access? I am BAFFLED!!!
Appreciate your reply. Any other ideas?....Pat
|
|
|
|
|
Hmm... Can't I can't say much, the code looks pretty good. If you haven't tried it, a print on Sql and see if your connection or logic is correct.
|
|
|
|
|
You are likely having problems with parameter order in the query. Unlike the SQl Data classes, Oledb essentially ignores parameter names, and relies exclusively on parameter position in the query. It seems likely that you might be depending on matching the parameter names (rather than their order in the query string) before executing the query (although I can't tell that from the code provided...).
|
|
|
|
|
Rob,
Thank you for your thoughtful answer. Yes, It has been so long since I used OLEDB that I forgot that, but in this case, it was not the problem. I mentioned in the beginning that I was suspicious that it had something to do with the syntax passing the parameter. I found an obscure piece of code on the net yesterday that showed me the problem. It had to do with passing the single quotes. The solution is pasted below in the event that it might also help some others:
//For Access Query
string Sql1 = "SELECT DISTINCT T.Trans_Name AS Transmission, [M.Manufacturer] AS Manufacturer, [M.Concern] AS Concern, " +
"[M.Years] AS Years, [M.Description] AS Description, [M.ATB] AS Bulletin " +
" FROM Master AS M INNER JOIN Trans AS T ON M.Trans = T.Trans WHERE ";
string Sql2 = " AND ";
string trans = textBoxTrans.Text;
string Sql3 = " T.Trans_Name = \'" + trans + " \'";
Thanks to you and "Youtea" for your input, Pat
|
|
|
|
|
Access does not support named parameters (e.g. @Manufacturer). It only supports positional parameters indicated with the ? character.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Hi
My Sql query is returing below resultset.
SNO Id Count
1 101
2 102
3 104
4 102
5 106
6 102
7 104
I want if Id is repeating like 102 and 104 is repeating in above resulset .it should display count as:
SNO Id Count
1 101 1
2 102 1
3 104 1
4 102 2
5 106 1
6 102 3
7 104 2
Please help how i write query for calculating clount value.
Its urgent
Plz help asap.
Thanks
|
|
|
|
|
Hi
Try this:
SELECT <br />
SNO,<br />
Id,<br />
(<br />
SELECT <br />
(COUNT(*) + 1) <br />
FROM <br />
table1 b <br />
WHERE <br />
b.SNO < a.SNO AND <br />
a.Id = b.Id)<br />
FROM <br />
table1 a<br />
OR
<br />
SELECT <br />
SNO,<br />
Id,<br />
(<br />
SELECT <br />
COUNT(*)<br />
FROM <br />
table1 b <br />
WHERE <br />
b.SNO <= a.SNO AND <br />
a.Id = b.Id)<br />
FROM <br />
table1 a<br />
Harini
|
|
|
|
|
Hi, I am trying to upload a database (which I have no problem doing), then I need to take that database and append it to a master database with the same tables and structure. I am using Access and visual studio .net, C#. I have tried everything, I am new to C# and .net and so maybe I am not putting things in the correct order or something?? Specifically, I have to use two different connections to get to the data, one connection where the data is located (conn1), and then append that data to the master database, (conn2). Please help if you can!   
|
|
|
|
|
Please suppy at least enough code snippets for someone else to understand what you've tried, and which part is not working. Your present question is sufficiently vague that no one is likely to respond.
|
|
|
|
|
as I said, I am new to this so here is what I have done;
<br />
private bool uploadData(string myPath, string txtUpload)<br />
{<br />
bool bNoError = true;<br />
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +<br />
"Data Source=" +myPath +txtUpload +";";<br />
<br />
OleDbConnection con = new OleDbConnection(strConnection);<br />
con.Open();<br />
<br />
<br />
try<br />
{<br />
DataSet dsEWEtmp = new DataSet();<br />
<br />
OleDbDataAdapter daEWEForm = new OleDbDataAdapter("SELECT * FROM Some_Form", con);<br />
<br />
<br />
int updates = daEWEForm.Fill(dsEWEtmp, "Some_Form");<br />
foreach (DataRow dataRow in dsEWEtmp.Tables)<br />
{<br />
dsEWEtmp.Tables[0].ImportRow(dataRow);<br />
dsEWEtmp.Tables[0].AcceptChanges();<br />
}<br />
<br />
updates = daEWEForm.Update(dsEWEtmp);<br />
<br />
DataTable tblEWEForm;<br />
tblEWEForm = dsEWEtmp.Tables[0];<br />
<br />
OleDbCommandBuilder objCommandBuilder = new OleDbCommandBuilder(daEWEForm);<br />
daEWEForm.Update(dsEWEtmp, "Some_Form");<br />
}<br />
catch(Exception err)<br />
{<br />
string str = err.Message;<br />
bNoError = false;<br />
<br />
}<br />
finally<br />
{<br />
if (con.State.ToString() =="Open")<br />
{<br />
con.Close();<br />
}<br />
}<br />
return bNoError;
As I stated I have the file that I upload; then if there is an uploaded file, it is "supposed" to read that file into a dataset and write it to the master database. In my web.config file, the master database resides at strConnection2, the uploaded file is at strConnection. I am really confused how to access them both, one to get the data from the uploaded file and two connect to the master database and write to it.
Is that better? Please help if you can. Thanks!
|
|
|
|
|
You grab your data from strConnection and stored it in the DataSet dsEWEtmp. Now go ahead and open up strConnection2 and insert the data. I'm pretty sure you can find how to insert from dataset very easily from this site.
|
|
|
|
|
Wow, I can't believe someone gave me 1 point because I didn't put the codes up. Isn't this a C# question anyways?
|
|
|
|
|
It probably is a C# question, but as I said, I am new to all this and for me; it has to do with databases as well as the code behind. I don't know who gave you one point, but a little code for pointing in the right direction would have helped. Thanks.  
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
