Question about pivot - Database Discussion Boards

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 Confused | :confused:

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

Thyme In The Country
Interacx
My Blog

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

Thyme In The Country
Interacx
My Blog

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

Thyme In The Country
Interacx
My Blog

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

Thyme In The Country
Interacx
My Blog

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

can you post query

sqlserver have the 2 tables 1 st table have the same strcture with the second table ,when we insert the first table it will also update the second table and when we update the first table it will also update the second table

raja

Values which you insert on first table insert and in second table too, same way use for update.

I Love T-SQL

Use Triggers for this

Happy programming

Dont Get Paid for the Hours you worked, Get Paid for the Work You Have Done in an Hour.

Hi all,

I made a very simple program to manipulate with DB as below
1. I have a SQL Server DB. The database contains just only 2 tables
1.1 TEST1(KEYCODE, SEIHIND, TEXT1, TEXT2, TEXT3), KEYCODE is primary key, auto increase
1.2 TEST2(TEXT1, TEXT2, TEXT3) no key
2. I make a new MDB by MS Access 2007 and created link table to these above SQL DB via ODBC(SQL Server driver)
3. My program uses DAO 3.6 to insert data to SQL via MS Access
3.1 The bussiness is to select data from TEST2 and insert to TEST1
3.2 SQL is like
INSERT INTO TEST1(SEIHIND, TEXT1,TEXT2,TEXT3) SELECT '1', TEXT1, TEXT2, TEXT3 FROM TEST2
4. To obtain above, i made a simple program like below:

Private Sub Command1_Click()
Dim defWsk As DAO.Workspace
Dim dbs As DAO.Database
Dim sqlStr As String
Set defWsk = DBEngine.Workspaces(0)
Set dbs = defWsk.OpenDatabase("F:\Tin\PQMC.MDB")
defWsk.BeginTrans
sqlStr = "INSERT INTO TEST1(SEIHIND, TEXT1,TEXT2,TEXT3) " & _
"SELECT '1', TEXT1, TEXT2, TEXT3 " & _
"FROM TEST2 "
Debug.Print sqlStr
dbs.Execute (sqlStr)
defWsk.CommitTrans
End Sub

5. Issue: The problem when execting above code is "data is not inserted to TEST1, TEST1 is blocked(I could not execute SELECT * FROM TEST1 in SQL Analyser)" When i close program, table is not locked(I could execute SELECT * FROM TEST1 in SQL Analyser)

Notes: I take above SQL and execute in SQL Analyser. it's OK

Anyone know how to solve this problem, please help me. Thank you very much for your support.

Tin Le,

Hi all,
I am Savtri here.I am having 3 field in table,Slnumber,Name, and PhoneNumber.Slnumber is of type Autonumber, and Name and phonenumber are text.when I add New element then it is incrementing.but when i am deleting it is not decrementing it means if i have 5 values like 1,2,3,4,5.if delete 3rd value then my table cantains 4 values.so please tell me How Shall i do it.PLEASE TELL ME.HELP ME.

Thanks in Advance,
savitri Confused | :confused:

this is what I suggest you:

Don't set column SInumber as AutoNumber, set value by code, when you insert row get max of SInumber and increase by 1.

When you delete one row reset values of column SInumber by this method to get regular numbers e.g:1,2,3,4, and so on:

declare @i as int<br />
set @i =  0<br />
declare @rowCount as int<br />
set @rowCount =(select count(SInumber) from myTable) <br />
<br />
while @i <= @rowCount <br />
begin<br />
     set @i = @i +1<br />
      <br />
     if(select count(SInumber) from myTable where SInumber =@i)=0 <br />
     begin<br />
          Update myTable set SInumber = @i where SInumber =@i+1          <br />
     end      <br />
end

I Love T-SQL

That is the way autonumbers work - if you think about it has to. What if you had used the Slnumber as a key in a related table? Changing the numbers in the first tabe would orphan records in the second table.

Bob
Ashfield Consultants Ltd

How can i write a program to execute a project by scheduling?
For eg.
I need to write a program which will run in a defined time. also it should stop the execution in a given time.

Thankyou,
Yesuprakash

Good luck with finding any SQL statement that will do that for you.

[Hint]You're in the wrong forum[/Hint]

Deja View - the feeling that you've seen this post before.

My blog | My articles

Maybe collaborate with this poster clickety
He had the same question as you.

Guy Hmmm | :|

Database

Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)