sql server - Query works in Sql but returns empty datatable -

edit

i'm confused, changed if statement:

 if data.rows.count > 0  'do voucher things in here else txtvoucher.text = "sorry, invalid voucher" end if 

to display query voucher checking function outputting 1 of voucher's string properties, this:

else txtvoucher.text = voucher.vouchername end if 

and works fine! if change error message... datatable returns no rows. haven't changed else code, goes textbox if row count 0. shouldn't row count same regardless of send textbox afterward?

end edit

i making basic online voucher function webpage , having issues voucher checking query.

i have query, checks string entered textbox against sql table match.

public shared function checkforvoucher(byval strvouchername string) datatable     dim connect new sqlconnection      dim data new datatable    'connection works, finds number of vouchers in db match either code or id. id used randomly generated vouchers.      connect.connectionstring = "server = server-sql01; trusted_connection=yes; database=pcsql"     connect.open()      dim query string     dim search string     search = strvouchername     if search.length >= 20         query = "select * pcsql.dbo.voucher_details vid='" + search + "' "     else         query = "select * pcsql.dbo.voucher_details vouchername='" + search + "' "     end if      dim command = new sqldataadapter(query, connect)     command.fill(data)     connect.close()      return data end function 

the query works fine in sql manager, can replace keyword search of voucher names have in list , returns correct result, same goes if replace search keyword in vb.net , force query check specific voucher no matter entered in textbox (e.g. testvoucher2).

i have page set check results below.

protected sub lbvouchercheck_click(byval sender object, byval e system.eventargs) handles lbvouchercheck.click      'voucher checking code go here      dim data datatable = voucher.checkforvoucher(txtvoucher.text)     if data.rows.count > 0         dim row datarow         row = data.rows(0)          'set voucher properties in voucher.vb using datarow result.         voucher.voucherid = row.item("vid").tostring().trim()         voucher.vouchername = row.item("vouchername").tostring().trim()         voucher.expirydate = row.item("expirydate")         voucher.validuses = row.item("validuses")         voucher.currentuses = row.item("currentuses")         voucher.discounttype = row.item("discounttype").tostring().trim()         voucher.appliesto = row.item("appliesto").tostring().trim()         voucher.numberof = row.item("numberof").tostring().trim()         voucher.amount = row.item("amount")         voucher.noofitems = row.item("noofitems")         voucher.category = row.item("category").tostring().trim()         voucher.freebieid = row.item("freebieid").tostring().trim()         voucher.discountamount = row.item("discountamount")          'lbvouchercheck.text = data.tostring()           'step one: check expiry date         dim count int32         count = 0         dim expiry datetime = voucher.expirydate         dim today datetime = date.today()         count = ((expiry - today).days)         if count <= -1             txtvoucher.text = "voucher expired"         else             txtvoucher.text = "expires in " + count.tostring() + " days."         end if     else         txtvoucher.text = data.rows.count     end if end sub 

when run query based off txtvoucher.text input returns "0", indicating hasn't found anything. if rig query voucher name returns correct expiry result.

i have strong feeling it's not getting right information txtvoucher.text voucher.checkforvoucher(txtvoucher.text) function.