|
here it is:
select distinct [name],<br />
(select top 1 a1.reg from myTable as a1 where a1.name = myTable.name order by a1.reg asc)<br />
from myTable
I Love T-SQL
modified on Monday, April 28, 2008 3:57 AM
|
|
|
|
|
Thanks for ur help,
Its working fine now..
|
|
|
|
|
You are welcome.
I Love T-SQL
|
|
|
|
|
If you are using SQL Server 2005, you should avoid subselects, because their performance is poor.
I'd rather use:
SELECT
NAME
, MIN(REG)
FROM dbo.MYTABLE
GROUP BY
NAME
|
|
|
|
|
thanks but your reply is not for me.
I Love T-SQL
|
|
|
|
|
When I run this code I get this error message "Syntax error in INSERT INTO statement". Can anyone explain why my command is a syntax error? Thanks.
rsGuest.Open("Guest", ADOConnection, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)<br />
Dim command As String<br />
<br />
<br />
command = "INSERT INTO Guest(Guest ID) VALUES('1111')"<br />
ADOConnection.Execute(command, rsGuest)
|
|
|
|
|
There are two possible reasons I can see.
Firstly, your syntax is wrong:
command = "INSERT INTO Guest(Guest ID) VALUES('1111')"<br />
Should be
command = "INSERT INTO Guest<big>[</big>Guest ID<big>]</big> VALUES('1111')"
Also, is [Guest ID] an identity (autonumber) column?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
column name Guest ID is having space so it cannot be used directly
use it like this
command = "INSERT INTO Guest([Guest ID]) VALUES('1111')"
Regards
KP
|
|
|
|
|
Sorry, thats what I meant - you need to put Guest ID in square brackets, I didn't mean to lose the () round it. Old age and failing eyesight to blame 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ok, I know vb.net and I've used ms access before and I understand the basic concepts of what a recordset and cursor and other stuff are but I have never used ADO before to program a database and I need to learn. Specifically I am trying to use vb.net 2005 to program ms access 2007.
I've opened the database and opened a recordset successfully. This is the code I used.
Private ADOConnection As New ADODB.Connection 'the database<br />
Private rsGuest As New ADODB.Recordset 'the recordset<br />
<br />
'code for opening the database and recordset<br />
ADOConnection.Open("provider=microsoft.ace.oledb.12.0;" & "Data Source=j:\junk.accdb")<br />
rsGuest.Open("Guest", ADOConnection, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
What I need to know is what is the syntax for adding data to a recordset. If someone could give me an example it would be very helpfull. Thanks in advance.
Mike
ps I have googled the hell out of this but every example I find is either a different version of access or vb, or is an example of using sql server, or it is an example of vba within access. In any case there seem to subtle incompatabilities that mean the examples never seem to work.
|
|
|
|
|
check this[^]
I Love T-SQL
|
|
|
|
|
You really shouldn't be using ADODB from VB.Net. ADODB is an older COM technology and was replaced in .Net with the System.Data.Oledb namespace classes. Oledb uses Datasets, Datatables, DataReaders and DataAdapters rather than Recordsets. It performs better than ADODB and is easier to use, and is an integrated set of managed classes.
|
|
|
|
|
hello
I am new and i have just started learning sql 2005 and i have create small table student with 3 columns studentID,firstname and lastname it's works fine i can insert,delete without any problem but when i delete any antry it dosent refresh the page i mean it delete record from sql database but it shows deleted entry in text boxes but if i close application and restart it it dosent show deleted entry any help this is the code for delete
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click<br />
Dim SQL As String = Nothing<br />
SQL = "DELETE FROM Student WHERE StudentID = " & TextBox3.Text<br />
If MessageBox.Show("Are you sure you want to delete", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = MsgBoxResult.Yes Then<br />
conStudent = New Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\bablu\My Documents\Visual Studio 2005\Projects\SQL DataApplication\SQL DataApplication\Student.mdf;Integrated Security=True;User Instance=True")<br />
conStudent.Open()<br />
Try<br />
commStudent = New SqlClient.SqlCommand(SQL, conStudent)<br />
commStudent.ExecuteNonQuery()<br />
<br />
MsgBox("Itemdeleted successfully", MsgBoxStyle.Information)<br />
Catch ex As Exception<br />
MsgBox(ex.Message)<br />
End Try<br />
conStudent.Close()<br />
conStudent.Dispose()<br />
Else<br />
Exit Sub<br />
End If<br />
End Sub
any help 
waiting for your kind rep.
have a nice time
thanks
|
|
|
|
|
after you delete row then close and open connection
conStudent.Close()<br />
conStudent.Open()
for more information check this[^]
I Love T-SQL
|
|
|
|
|
hello
first of all thanks for your rep.
i have tried it but it's not working
this is the code
conStudent.Open()<br />
Try<br />
commStudent = New SqlClient.SqlCommand(SQL, conStudent)<br />
commStudent.ExecuteNonQuery()<br />
conStudent.Close() <------:confused:<br />
conStudent.Open() <------:confused:<br />
MsgBox("Item deleted", MsgBoxStyle.Information)<br />
Catch ex As Exception<br />
MsgBox(ex.Message)<br />
End Try<br />
conStudent.Close()<br />
conStudent.Dispose()
is this what you are saying or some thing else
waiting for your kid rep.
have a nice day
|
|
|
|
|
hi ,
in try block after query exection just assign textbox.text="";
i think this solves u r problem .
if this is in webapplication we can set viewstate as false .
bye .
|
|
|
|
|
hello
thansk for your kind rep.
yes i have tried that before but it's not working well it's clear the text box but it also shows empty textbox when you move next or previous so i dont know what to do
still waiting for help 
have a nice day
|
|
|
|
|
Perhaps pivot isn't what I'm looking for, but here's what I'm trying to accomplish (pardon the XML).
I have a table of species:
<TableDef IsPooled="false" Name="Species">
<Fields>
<TableFieldDef Length="0" DataType="Integer" IsPK="true" IsIdentity="true" Name="ID" />
<TableFieldDef Length="0" DataType="Integer" PKTable="Family" PKField="ID" Name="FamilyID" />
<TableFieldDef Length="50" Name="Name" />
<TableFieldDef Length="50" Name="CommonName" />
</Fields>
</TableDef>
and a table of samples:
<TableDef IsPooled="false" Name="Sample">
<Fields>
<TableFieldDef Length="0" DataType="Integer" IsPK="true" IsIdentity="true" Name="ID" />
<TableFieldDef Length="0" DataType="Date" Name="SampleDate" />
<TableFieldDef Length="0" DataType="Integer" PKTable="SamplingSite" PKField="ID" Name="SamplingSiteID" />
<TableFieldDef Length="0" DataType="Integer" PKTable="Species" PKField="ID" Name="SpeciesID" />
<TableFieldDef Length="16" AllowNull="true" Name="AbundanceRank" />
<TableFieldDef Length="16" AllowNull="true" Name="PercentCover" />
<TableFieldDef DefaultValue="false" Length="0" DataType="Boolean" Name="Present" />
</Fields>
</TableDef>
which references the sampling sites table:
<TableDef IsPooled="false" Name="SamplingSite">
<Fields>
<TableFieldDef Length="0" DataType="Integer" IsPK="true" IsIdentity="true" Name="ID" />
<TableFieldDef Length="0" DataType="Integer" PKTable="Location" PKField="ID" Name="LocationID" />
<TableFieldDef Length="0" DataType="Integer" PKTable="Subhabitat" PKField="ID" Name="SubhabitatID" />
<TableFieldDef Length="0" DataType="Integer" PKTable="GPSCoord" PKField="ID" Name="GPSID" />
<TableFieldDef Length="512" Name="Descr" />
</Fields>
</TableDef>
What I'd like is a vertical list of species crossed with a horizontal list of sampling sites in which, for each species at that sampling site, the percent cover is given (qualified by date or a master sample ID so we can guarantee that there's only sample per species-sample site, but that's not the question, I can figure that part out).
So, is this this species-sample matrix done with a pivot or some sort of cross apply?
Marc
|
|
|
|
|
You could do this using PIVOT but you would need to supply fixed/known columns (Sampling Sites). Dynamic cross-tabulation of data in the way that you require is handled by Analysis Services or by importing the data into Excel and using the pivot functions there.
Ian
|
|
|
|
|
i.j.russell wrote: Dynamic cross-tabulation of data in the way that you require is handled by Analysis Services or by importing the data into Excel and using the pivot functions there.
Thanks for the reply. After researching the matter some more, I came to the same conclusion.
Funny thing is though, I'm constructing the data so that I can export the result to Excel.
Marc
|
|
|
|
|
I've got this query:
select
Species.Name as Species_Name,
Species.CommonName as CommonName,
Family.Name as Family_Name,
NativeView.NativeCode
from Species
left join NativeView on SpeciesID=Species.ID
inner join Family as Family on Species.FamilyID=Family.ID
That uses this view:
create view NativeView as
select
Species.ID as SpeciesID,
SpeciesNativeTypeOptionList.Code as NativeCode
from Species
inner join SpeciesTypeInfo as SpeciesNative on Species.ID = SpeciesNative.SpeciesID
inner join SpeciesTypeOptionList as SpeciesNativeTypeOptionList on SpeciesNative.SpeciesTypeOptionListID=SpeciesNativeTypeOptionList.ID
inner join SpeciesTypeList as SpeciesNativeTypeList on SpeciesNativeTypeOptionList.SpeciesTypeListID=SpeciesNativeTypeList.ID and SpeciesNativeTypeList.Name='Native'
But what I'd like to do is formulate the query without using a view at all.
The problem comes in on the "left join" above, because I want "NativeCode" to return null when the species ID does not have a matching row in the query that makes up the NativeView.
Is it possible to achieve that (get rid of the view)?
Thanks!
Marc
|
|
|
|
|
Here:
select
Species.Name as Species_Name,
Species.CommonName as CommonName,
Family.Name as Family_Name,
v.NativeCode
from Species
left join
(select
Species.ID as SpeciesID,
SpeciesNativeTypeOptionList.Code as NativeCode
from Species
inner join SpeciesTypeInfo as SpeciesNative on Species.ID = SpeciesNative.SpeciesID
inner join SpeciesTypeOptionList as SpeciesNativeTypeOptionList on SpeciesNative.SpeciesTypeOptionListID=SpeciesNativeTypeOptionList.ID
inner join SpeciesTypeList as SpeciesNativeTypeList on SpeciesNativeTypeOptionList.SpeciesTypeListID=SpeciesNativeTypeList.ID and
SpeciesNativeTypeList.Name='Native'
) v
on v.SpeciesID=Species.ID
inner join Family as Family on Species.FamilyID=Family.ID
The thing I was missing was the alias for the inner select. I've been learning that when SQL says there's an error with ')', it means I'm missing an alias name.
Marc
|
|
|
|
|
in sql server have the 2 tables first table have the 5 rows and second table have the 10 rows
when we apply the left outer join the tables . whats the no of rows selected in best case and worst case can u explain this?
raja
|
|
|
|
|
Post your query and we will see what's wrong.
I Love T-SQL
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
