|
Which database system? SQL Server 2012?
You want to get the second highest? Or both the highest and second-highest in one row?
You'll never get very far if all you do is follow instructions.
modified 23-Apr-14 13:12pm.
|
|
|
|
|
Database system is SQL Server 2012. I just want to get the second highest in a column Date.
|
|
|
|
|
You could use ROW_NUMBER and select the one with a value of 2.
Or something like:
SELECT TOP(1) * FROM (
SELECT TOP(2) StartTime FROM dbo.RunSummary ORDER BY StartTime DESC
) T ORDER BY StartTime
Neither seems very elegant.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
Tqvm 
|
|
|
|
|
Syafiqah Zahirah wrote: what can I do?
..SELECT TOP 2
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
yes..it working! 
|
|
|
|
|
You try to do this by the following code:
select max(Actuators.Date)
from table1
where Actuators.Date<(select max(Actuaors.Date) from t1);
i hope it can help you.
|
|
|
|
|
I'm not sure if this is even possible. I'm trying to create a auto increment column for FID in this database file.
I was going to use SQL Server Express, but decided to just stick with the same format of the original program Account Mate for DOS, running low on quoted time for the project.
Any insight would be appreciated!
Dim m_path As String = Nothing
Dim dwXCode As Integer = registry_shared.read_HKCU_dataPath(m_path)
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & m_path & "; Extended Properties=dBASE IV"
Dim queryString As String = _
"CREATE TABLE ARCEMJN " & _
"(" & _
" FID AUTOINCREMENT " & _
" , FINVNO VARCHAR(10) " & _
" , FCUSTNO VARCHAR(10) " & _
" , FTRANSDATE DATE " & _
" , FCONTACT1 VARCHAR(80) " & _
" , FEMAIL1 VARCHAR(254) " & _
" , FSTATUS BIT " & _
" , FERRCODE VARCHAR(10) " & _
" , FERRMESS VARCHAR(80) " & _
" , FPDFPATH VARCHAR(254) " & _
") "
|
|
|
|
|
Some info[^] on how to use AutoInc fields in dBase.
Sorry, forget that, wrong version.
modified 23-Apr-14 1:45am.
|
|
|
|
|
Did those exist in DBase IV?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Oh bugger. I forgot to check the version.
|
|
|
|
|
I am developing a windows form application using c#. I want to connect to sql server 2008 which is in the server using windows authentication. Can anyone please provide me the connection string?
Note:
1. domain is created in my server.
2. Remote access is enabled for the sql server
3. TCP/IP port is also enabled.
Please help me. I am struggling for this since 3 days.
|
|
|
|
|
|
Hi,
I am currently using DevExpress Reports. I just wanted to know if anyone used RDLC?
Would you recommend it?
Thanks,
Technology News @ www.JassimRahma.com
|
|
|
|
|
RDLC is the embedded version of SSRS. We have used these extensively in winforms and asp environment. There is no Silverlight RDLC viewer (well there is on but it is bloody expensive). We use the server version for our enterprise reporting needs.
SSRS is one of the premier reporting platforms, but then so is DevExpress!
Just don't get involved with crystal reports spit
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
who's using crystal reports now! 
so let me ask you if you had to choose between DevExpress reports and RDLC, which one you'll choose and why?
Also let me tell you about my scenario..
I am using MySQL backend with WinForm and from application, I want to allow the user to design their own reports or change existing reports using report designer.
Thanks,
Jassim
Technology News @ www.JassimRahma.com
|
|
|
|
|
I would choose RDLC but only because of familiarity. Possibly the support and examples may be more widespread.
While I have deployed the report designer (many years ago) in 2 organisations they were NEVER used, the users always came back to the developer (me) to build the reports. The closest I ever got to user design was where they would dump a set of data to excel, fiddle with it and then get me to build the report.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Great..
Thanks Mycroft 
Technology News @ www.JassimRahma.com
|
|
|
|
|
I've never worked on these old dbase/clipper database files for Account Mate DOS, and I'm having trouble with multiple joins. Says I'm missing a arithmetic operator in the statement. I can't find a reference to study to get an idea of how it should be written. I really don't want to make 2 calls, because it really slows down the load time.
This is the error message
Syntax error (missing operator) in query expression '(c.FCUSTNO = a.FCUSTNO) LEFT JOIN ARCEM01.dbf e ON (c.FCUSTNO = e.FCUSTNO)'.
"SELECT c.FINVNO, c.FCUSTNO, c.FCOMPANY, c.FSALESPN, c.FSHIPVIA, c.FORDDATE, c.FSHIPDATE, c.FNTAXAMT, a.FADDR1 " & _
"FROM ARINV01.dbf c " & _
"LEFT JOIN ARCUS01.dbf a ON (c.FCUSTNO = a.FCUSTNO) " & _
"LEFT JOIN ARCEM01.dbf e ON (c.FCUSTNO = e.FCUSTNO) " & _
"WHERE c.FORDDATE=@startDate"
|
|
|
|
|
If I remember correctly (which might not be the case) dbase only allows one outer join. But this doesn't seem to be a real problem as you're not using ARCEM01.dbf for anything.
The solution otherwise would be to use subqueries in the select. Like this:
SELECT c.FINVNO
,c.FCUSTNO
,c.FCOMPANY
,c.FSALESPN
,c.FSHIPVIA
,c.FORDDATE
,c.FSHIPDATE
,c.FNTAXAMT
,(SELECT a.FADDR1 FROM ARCUS01.dbf a WHERE c.FCUSTNO = a.FCUSTNO) as FADDR1
FROM ARINV01.dbf c
WHERE c.FORDDATE=@startDate
|
|
|
|
|
I kind of thought that.
I didn't put the e.FEMAIL1 in for experimenting yet until I got some concrete information on it.
Thanks!
|
|
|
|
|
That Works! Thanks Jorgen
SELECT c.FINVNO
,c.FCUSTNO
,c.FCOMPANY
,c.FSALESPN
,c.FSHIPVIA
,c.FORDDATE
,c.FSHIPDATE
,c.FNTAXAMT
,(SELECT a.FADDR1 FROM ARCUS01.dbf a WHERE c.FCUSTNO = a.FCUSTNO) AS FADDR1
,(SELECT e.FEMAIL1 FROM ARCEM01.dbf e WHERE c.FCUSTNO = e.FCUSTNO) AS FEMAIL1
FROM ARINV01.dbf c
WHERE c.FORDDATE=@startDate
|
|
|
|
|
Hye, I having a problem with this sql query..
string sql = "SELECT * FROM Environmnets INNER JOIN TDevice ON Environmnets.Deviceid = TDevice.id INNER JOIN TMushroomHouse ON TDevice.MushroomHouseId = TMushroomHouse.id WHERE (Environmnets.DateT =(SELECT MAX(DateT) AS Expr1 FROM Environmnets AS Environmnets_1)) AND TMushroomHouse.Name ='"+ DropDownList1.Text + "'";
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand(sql, con);
// SqlDataReader dr = cmd.ExecuteReader();
try
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
HouseTextBox.Text = dr["House"].ToString();
DateTTextBox.Text = dr["DateT"].ToString();
TempInTextBox.Text = dr["TempIn"].ToString();
TempOutTextBox.Text = dr["tempOut"].ToString();
HumidityTextBox.Text = dr["Humidity"].ToString();
}
}
There is no error occur when I run this code, but when I select home from dropdownlist, the data from database didn't display in the textbox. Anybody can help me?
|
|
|
|
|
The first problem is that your code is susceptible to SQL Injection[^]. You should be using a parameterized query instead.
While we're fixing that, the SqlConnection, SqlCommand and SqlDataReader objects all implement IDisposable, so they should be wrapped in using blocks to make sure their resources are freed.
We'll also get rid of the SELECT *, since you only need five columns.
string sql = "SELECT House, DateT, TempIn, tempOut, Humidity FROM Environmnets INNER JOIN TDevice ON Environmnets.Deviceid = TDevice.id INNER JOIN TMushroomHouse ON TDevice.MushroomHouseId = TMushroomHouse.id WHERE (Environmnets.DateT = (SELECT MAX(DateT) AS Expr1 FROM Environmnets AS Environmnets_1)) AND TMushroomHouse.Name = @MushroomHouseName";
using (SqlConnection con = new SqlConnection(strConnString))
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@MushroomHouseName", DropDownList1.Text);
con.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
HouseTextBox.Text = dr["House"].ToString();
DateTTextBox.Text = dr["DateT"].ToString();
TempInTextBox.Text = dr["TempIn"].ToString();
TempOutTextBox.Text = dr["tempOut"].ToString();
HumidityTextBox.Text = dr["Humidity"].ToString();
}
}
}
Now, if the code is definitely executing, and there's no error, but you're not seeing anything in the textboxes, then that means there were no records returned by your query. If I had to guess, I'd say it's probably connected to the DateT filter:
WHERE (Environmnets.DateT = (SELECT MAX(DateT) AS Expr1 FROM Environmnets AS Environmnets_1))
I suspect you want the last record for a specific TMushroomHouse, but you're actually only looking for the last record overall, and only returning it if it matches the selected house.
If that's the case, try changing your query to:
SELECT TOP 1 House, DateT, TempIn, tempOut, Humidity FROM Environmnets INNER JOIN TDevice ON Environmnets.Deviceid = TDevice.id INNER JOIN TMushroomHouse ON TDevice.MushroomHouseId = TMushroomHouse.id WHERE TMushroomHouse.Name = @MushroomHouseName ORDER BY Environmnets.DateT DESC
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Tq..it working right now  And I have one more question, mushroomhousename is not the same table with environmnets..how i want to call mushroomhousename and put it in a textbox?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
Hangover? Last 3 days're holidays.