Problemer med sql og dataadapter
Jeg har en søgefunktion hvor som virkede fint, men nu har jeg så lavet en paging funktion, og nu virker det så ikke...Mit bud er at det er fordi DataAdapteren ikke kan håndtere mine Parametere..
Men hvad gør man så lige?
Her er koden...
Private Sub BindTheData()
Dim objConn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim strSQL As String
strSQL = "SELECT * FROM Her_Users WHERE UserActive = '1'"
If Not DDGender.SelectedValue = "12" Then
strSQL = strSQL & " AND Gender = @Gender"
End If
If Not DDSeeks.SelectedValue = "0" Then
strSQL = strSQL & " AND Seeks = @Seek"
End If
If Not TBZip.Text = "" Then
strSQL = strSQL & " AND Zip = @Zip"
End If
If Not TBCity.Text = "" Then
strSQL = strSQL & " AND City = @City"
End If
If TBHeightFrom.Text.Length > 0 And TBHeightTo.Text.Length > 0 Then
strSQL = strSQL & " AND Height BETWEEN @HeightFrom AND @HeightTo"
Else
If TBHeightFrom.Text.Length > 0 Then
strSQL = strSQL & " AND Height BETWEEN @HeightFrom AND '0'"
End If
If TBHeightTo.Text.Length > 0 Then
strSQL = strSQL & " AND Height BETWEEN '0' AND @HeightTo"
End If
End If
If TBWeightFrom.Text.Length > 0 And TBWeightTo.Text.Length > 0 Then
strSQL = strSQL & " AND Weight BETWEEN @WeightFrom AND @WeightTo"
Else
If TBWeightFrom.Text.Length > 0 Then
strSQL = strSQL & " AND Weight BETWEEN @WeightFrom AND '0'"
End If
If TBWeightTo.Text.Length > 0 Then
strSQL = strSQL & " AND Weight BETWEEN '0' AND @WeightTo"
End If
End If
Dim AgeFrom As DateTime
Dim AgeTo As DateTime
If TBAgeFrom.Text.Length > 0 And TBAgeTo.Text.Length > 0 Then
Dim a As New AgeScript
AgeFrom = a.GetAgeDate(TBAgeFrom.Text)
AgeTo = a.GetAgeDate(TBAgeTo.Text)
strSQL = strSQL & " AND Age BETWEEN @AgeTo AND @AgeFrom"
Else
If TBAgeFrom.Text.Length > 0 Then
Dim a As New AgeScript
AgeFrom = a.GetAgeDate(TBAgeFrom.Text)
AgeTo = DateTime.Now
strSQL = strSQL & " AND Age BETWEEN @AgeTo AND @AgeFrom"
End If
If TBAgeTo.Text.Length > 0 Then
Dim a As New AgeScript
AgeTo = a.GetAgeDate(TBAgeTo.Text)
AgeFrom = DateTime.Now
strSQL = strSQL & " AND Age BETWEEN @AgeTo AND @AgeFrom"
End If
End If
strSQL = strSQL & " ORDER by UID asc"
Dim objCmd As New SqlCommand(strSQL, objConn)
Dim dataAdapter As New SqlDataAdapter(strSQL, objConn)
Dim Test As New DataSet
If Not DDSeeks.SelectedValue = "0" Then
Dim paramSeek As SqlParameter
paramSeek = New SqlParameter("@Seek", SqlDbType.VarChar, 50)
paramSeek.Value = DDSeeks.SelectedValue
objCmd.Parameters.Add(paramSeek)
End If
If Not DDGender.SelectedValue = "12" Then
Dim paramGender As SqlParameter
paramGender = New SqlParameter("@Gender", SqlDbType.VarChar, 50)
paramGender.Value = DDGender.SelectedValue
objCmd.Parameters.Add(paramGender)
End If
If Not TBZip.Text = "" Then
Dim paramZip As SqlParameter
paramZip = New SqlParameter("@Zip", SqlDbType.VarChar, 50)
paramZip.Value = TBZip.Text
objCmd.Parameters.Add(paramZip)
End If
If Not TBCity.Text = "" Then
Dim paramCity As SqlParameter
paramCity = New SqlParameter("@City", SqlDbType.VarChar, 50)
paramCity.Value = TBCity.Text
objCmd.Parameters.Add(paramCity)
End If
If TBHeightFrom.Text.Length > 0 And TBHeightTo.Text.Length > 0 Then
Dim parameightFrom As SqlParameter
parameightFrom = New SqlParameter("@WeightFrom", SqlDbType.VarChar, 50)
parameightFrom.Value = TBHeightFrom.Text
objCmd.Parameters.Add(parameightFrom)
Dim paramHeightTo As SqlParameter
paramHeightTo = New SqlParameter("@HeightTo", SqlDbType.VarChar, 50)
paramHeightTo.Value = TBHeightTo.Text
objCmd.Parameters.Add(paramHeightTo)
Else
If TBHeightFrom.Text.Length > 0 Then
Dim paramHeightFrom As SqlParameter
paramHeightFrom = New SqlParameter("@WeightFrom", SqlDbType.VarChar, 50)
paramHeightFrom.Value = TBHeightFrom.Text
objCmd.Parameters.Add(paramHeightFrom)
End If
If TBHeightTo.Text.Length > 0 Then
Dim paramHeightTo As SqlParameter
paramHeightTo = New SqlParameter("@HeightTo", SqlDbType.VarChar, 50)
paramHeightTo.Value = TBHeightTo.Text
objCmd.Parameters.Add(paramHeightTo)
End If
End If
If TBWeightFrom.Text.Length > 0 And TBWeightTo.Text.Length > 0 Then
Dim paramWeightFrom As SqlParameter
paramWeightFrom = New SqlParameter("@WeightFrom", SqlDbType.VarChar, 50)
paramWeightFrom.Value = TBWeightFrom.Text
objCmd.Parameters.Add(paramWeightFrom)
Dim paramWeightTo As SqlParameter
paramWeightTo = New SqlParameter("@WeightTo", SqlDbType.VarChar, 50)
paramWeightTo.Value = TBWeightTo.Text
objCmd.Parameters.Add(paramWeightTo)
Else
If TBWeightFrom.Text.Length > 0 Then
Dim paramWeightFrom As SqlParameter
paramWeightFrom = New SqlParameter("@WeightFrom", SqlDbType.VarChar, 50)
paramWeightFrom.Value = TBWeightFrom.Text
objCmd.Parameters.Add(paramWeightFrom)
End If
If TBWeightTo.Text.Length > 0 Then
Dim paramWeightTo As SqlParameter
paramWeightTo = New SqlParameter("@WeightTo", SqlDbType.VarChar, 50)
paramWeightTo.Value = TBWeightTo.Text
objCmd.Parameters.Add(paramWeightTo)
End If
End If
If TBAgeFrom.Text.Length > 0 Or TBAgeTo.Text.Length > 0 Then
Dim paramAgeFrom As SqlParameter
paramAgeFrom = New SqlParameter("@AgeFrom", SqlDbType.VarChar, 50)
paramAgeFrom.Value = AgeFrom.ToString
objCmd.Parameters.Add(paramAgeFrom)
Dim paramAgeTo As SqlParameter
paramAgeTo = New SqlParameter("@AgeTo", SqlDbType.VarChar, 50)
paramAgeTo.Value = AgeTo.ToString
objCmd.Parameters.Add(paramAgeTo)
End If
Dim dataSet As New DataSet()
dataAdapter.Fill(dataSet)
lblRecordCount.Text = CStr(dataSet.Tables(0).Rows.Count)
dataSet = Nothing
dataSet = New DataSet()
dataAdapter.Fill(dataSet, CInt(lblCurrentIndex.Text), CInt(lblPageSize.Text), "Customers")
DataList1.DataSource = dataSet.Tables("Customers").DefaultView
DataList1.DataBind()
objConn.Close()
ShowCounts()
End Sub
