Crazy Query string RSS

• • Reply
  

  mattcase

  Member

  374 Points

  518 Posts

  Crazy Query string

  Feb 25, 2012 02:54 AM|LINK

  Hi,

  UGH!!!!

  I am soooo frustrated with this issue!

  I have the query string below that populates a GridView.  However, it is not populating the Gridview with any data even though I KNOW FOR SURE that there are 2 records that should show up.

  However when I change the <WHERE T1.Col2 = ''> to <WHERE T1.Col3 = ''> then it works perfectly (of course it does not pull the records I am really wanting in my original query with Col2).  I feel that I have tried everything.

  Does anyone know what else I could try?

  Thanks.

  Dim sqlStr As String = "SELECT T1.Col1, T1.Col2, T1.Col3, T2.Col1, T2.Col2, T1.Col4 AS Name4, CONVERT(CHAR(12), T1.Col5, 107) AS Name5, T1.Col6 AS Name6 FROM Table1 T1 LEFT OUTER JOIN Table2 T2 ON T1.Col2 = T2.Col3 WHERE T1.Col2 = '" & HttpContext.Current.User.Identity.Name & "' ORDER BY T1.Col5 DESC"

  sql select

  Matt
• • Reply
  

  sandeepmitta...

  Contributor

  6767 Points

  1057 Posts

  Re: Crazy Query string

  Feb 25, 2012 03:27 AM|LINK

  You mean to say that in both col2 and col3 there is username stored and when filtered on col3 it works fine but when filtered on col2 there are records but not filtering.

  Example

  col1 col2   col3
  1    user1  user3
  2    user1  user1
  3    user2  user1
  4    user2  user2

  WHERE T1.Col3 = 'user1' giving you 2 records (2 & 3)

  WHERE T1.Col2 = 'user1' not giving you any record, however you should get 2 records (1 & 2)

  If this is the case, Please check the datatype and data of col2. it might have spaces in the end

  try using TRIM in that case LTRIM(RTRIM(T1.Col2))

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
• • Reply
  

  mattcase

  Member

  374 Points

  518 Posts

  Re: Crazy Query string

  Feb 28, 2012 02:21 AM|LINK

  Thanks for your reply.

  I did as you recommended and my datatype and data are all correct.  In addition, using TRIM did not resolve the issue.

  However, I do have an additional part to this where I am querying two different database instances.

  Here is the entire part:

  Dim sqlStr As String = "SELECT T1.Col1, T1.Col2, T1.Col3, T2.Col1, T2.Col2, T1.Col4 AS Name4, CONVERT(CHAR(12), T1.Col5, 107) AS Name5, T1.Col6 AS Name6 FROM Table1 T1 LEFT OUTER JOIN Table2 T2 ON T1.Col2 = T2.Col3 WHERE T1.Col2 = '" & HttpContext.Current.User.Identity.Name & "' ORDER BY T1.Col5 DESC"
              Dim dt As New DataTable
              Dim dataAdapter As New SqlClient.SqlDataAdapter(sqlStr, connstr)
              dataAdapter.Fill(dt)
              dataAdapter.Dispose()
  
              Dim Keyconnstr As String = ConfigurationManager.ConnectionStrings("String2").ToString()
              Dim sqlStrKey As String = "SELECT Col1, Col2 FROM Table3 WHERE UserName = '" & HttpContext.Current.User.Identity.Name & "'"
              Dim dtKey As New DataTable
              Dim dataAdapterKey As New SqlClient.SqlDataAdapter(sqlStrKey, Keyconnstr)
              dataAdapterKey.Fill(dtKey)
              dataAdapterKey.Dispose()
  
              Dim query = From t1 In dt.AsEnumerable() Join t2 In dtKey.AsEnumerable() On t1.Field(Of String)("Col1") Equals _
                  t2.Field(Of String)("Col1") Select New With { _
                          .Col1 = t1.Field(Of String)("Col1"), _
                          .Col2 = t1.Field(Of String)("Col2"), _
                          .Col3 = t1.Field(Of String)("Col3"), _
                          .Col4 = t1.Field(Of String)("Col4"), _
                          .Col5 = t1.Field(Of String)("Col5"), _
                          .Col6 = t1.Field(Of String)("Col6"), _
                          .Col7 = t1.Field(Of String)("Col7"), _
                          .Col8 = t2.Field(Of String)("Col8")}
  
              MyGrid.DataSource = query.ToList()

  Matt
• • Reply
  

  mattcase

  Member

  374 Points

  518 Posts

  Re: Crazy Query string

  Feb 28, 2012 05:04 AM|LINK

  Resolved!!!!

  I fixed it!!!  It appears that I had an error on my second DB.

  Thanks.

  sql select

  Matt
  • Marked as answer by mattcase on Feb 28, 2012 05:04 AM