I spent all day trying to figure out why I can enter Where in statement in my sql query and grid populates properly. When I have a tectbox load contents from a checkbox list and enter my where in statement it returns a blank grid.
This is a cp from my query:
Dim myComm As New SqlCommand("SELECT gl_journal_entry.transaction_date AS Trans_date, gl_journal_entry_detail.account_id AS Account, gl_journal_entry_detail.reference_number AS" _
& " Reference_no, gl_journal_entry_detail.amount AS Amount, gl_journal_entry_detail.description AS Description, CASE WHEN gl_journal_entry_detail.account_id = '100100331010'" _
& " THEN 'LA' WHEN gl_journal_entry_detail.account_id = '120100331010' THEN 'SD' WHEN gl_journal_entry_detail.account_id = '130100331010'" _
& " THEN ' SA' WHEN gl_journal_entry_detail.account_id = '140100331010' THEN 'NY' WHEN gl_journal_entry_detail.account_id = '150100331010'" _
& " THEN 'WY' WHEN gl_journal_entry_detail.account_id = '160100331010' THEN 'MN' WHEN gl_journal_entry_detail.account_id = '120100331010' THEN '" _
& "MO' WHEN gl_journal_entry_detail.account_id = '170100331010' THEN 'DE' WHEN gl_journal_entry_detail.account_id = '180100331010'" _
& " THEN '802' WHEN gl_journal_entry_detail.account_id = '190100331010' THEN 'MI' WHEN gl_journal_entry_detail.account_id = '110100331010'" _
& " THEN 'CN' WHEN gl_journal_entry_detail.account_id = '101100331010' THEN 'MX' WHEN gl_journal_entry_detail.account_id = '111100331010'" _
& " THEN 'KS' WHEN gl_journal_entry_detail.account_id = '112100331010' THEN 'FL' WHEN gl_journal_entry_detail.account_id = '121100331010'" _
& " THEN 'AL' WHEN gl_journal_entry_detail.account_id = '122100331010' THEN 'MS' WHEN gl_journal_entry_detail.account_id = '161100331010'" _
& " THEN 'OK' WHEN gl_journal_entry_detail.account_id = '151100331010' THEN 'OR' END AS BRANCH FROM gl_journal_entry" _
& " INNER JOIN gl_journal_entry_detail ON gl_journal_entry.journal_entry_id = gl_journal_entry_detail.journal_entry_id WHERE" _
& " (gl_journal_entry.transaction_date BETWEEN @bdate AND @edate) AND (gl_journal_entry_detail.account_id IN (@branch) " _
& " ) ORDER BY Account, Trans_date", myConn)
myConn.Open()
myComm.Parameters.Add("@bdate", SqlDbType.DateTime).Value = Format(Me.Datefrom.Text, "short date")
myComm.Parameters.Add("@edate", SqlDbType.DateTime).Value = Format(Me.Dateto.Text, "short date")
myComm.Parameters.AddWithValue("@branch", TextBox1.Text)
Me.TextBox2.Text = Me.TextBox1.Text
I added a textbox 2 to see if data had not been erased on postback, this returns a blank grid
If I paste this result from the textbox
(('151100331010'),('122100331010'),('170100331010'))
Over (@branch) grid populates.
Please help.