why is sql query creating duplicates in dataset
for some strange reason this dataset duplicates all of the values
datestart is april first and datestop is april 15 and I get 1 through 15 and then 1 through 15 as second time. the selection is a social security number
I have checked the database, there are no duplicates. I am working in asp.net using vb.
does anyone know why the dataset would create duplicates?
thanks gary
Try
Dim sqlthisPeriod3 As String = "Select * from EmployeeMonthlyrecord where workdate between '" & CDate(datestart) & "' and '" & CDate(datestop) & "' and EMPLOYEEID = '" & Dst.Tables("Dsthireinfo").
Dim adapter35 As SqlDataAdapter = New SqlDataAdapter(sqlthisPeri
adapter35.Fill(Dst, "DstthisPeriod3")
Catch ex As System.Data.SqlClient.SqlE
MsgBox(ex.Message)
End Try
datestart is april first and datestop is april 15 and I get 1 through 15 and then 1 through 15 as second time. the selection is a social security number
I have checked the database, there are no duplicates. I am working in asp.net using vb.
does anyone know why the dataset would create duplicates?
thanks gary
Try
Dim sqlthisPeriod3 As String = "Select * from EmployeeMonthlyrecord where workdate between '" & CDate(datestart) & "' and '" & CDate(datestop) & "' and EMPLOYEEID = '" & Dst.Tables("Dsthireinfo").
Dim adapter35 As SqlDataAdapter = New SqlDataAdapter(sqlthisPeri
adapter35.Fill(Dst, "DstthisPeriod3")
Catch ex As System.Data.SqlClient.SqlE
MsgBox(ex.Message)
End Try
You may need to clear the grid (or even its datasource) before handing the query results to it.
Also, I'd recommend against using BETWEEN for date ranges. See this.
Also, I'd recommend against using BETWEEN for date ranges. See this.
ASKER
first there is no join.
second I am breaking the program the line after the dataset is created and it has two of everything straight out of the sql datatable.
gry
second I am breaking the program the line after the dataset is created and it has two of everything straight out of the sql datatable.
gry
If you put the same query into SQL Server directly (management studio) does the same result occur?
ASKER
David ,no only single entries appear if I run it in the sql server.
the dataset was declared as a global when the webpage opens.
I tried clearing and destroying every table in a dataset on leaving a function, this did not help.
Then I decided to create a different dataset for each function, this worked and the extra lines disappeared. I am thinking I should have declared the dataset In each function and then cleared it on leaving.
the dataset was declared as a global when the webpage opens.
I tried clearing and destroying every table in a dataset on leaving a function, this did not help.
Then I decided to create a different dataset for each function, this worked and the extra lines disappeared. I am thinking I should have declared the dataset In each function and then cleared it on leaving.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
david
yes I know but sometimes one thinks I'll just slip on in here just to make things simple. Lazy usually bites you in the you know where.
yes I know but sometimes one thinks I'll just slip on in here just to make things simple. Lazy usually bites you in the you know where.
The biggest reason duplicates happens is a JOIN between two tables/views where the ON clause does not contain all columns where the two tables are related. OR one of the tables is a one-to-many, there are more than one row for the given value in the first table, and the duplicate is by design.
Is EmployeeMonthlyrecord a table or a view? Since your query only references one object, if it's a table then obviously this shouldn't be happening, and perhaps there is other ASP.NET code that is causing this.
Can't hurt if you do a Debug.Print sqlthisPeriod3 , execute, then post the results into this question.