0

I've gotten the queries in my form working and am going back for a general round of cleanup and to parameterize queries and am having trouble with one query that is using the LIKE keyword along with a wildcard (%). The queries are obtaining info from or acting on an Access database. Note that SQL Injection attacks are not an issue as the location where this form is run has no connection to the outside world (none, zero, zip). So SQL Injection trolls please refrain.

Also note, I am not an experienced VB'er or .NET'er so am doing what I can based on searching through StackOverFlow (which has proven to be extremely helpful on numerous occasions, thanks all!)

  1. The values from Me.cmboAssemblyNum.Text are of the format X31-D104518-1-00101-**.
  2. There will always be a alpha character at the beginning
  3. The last characters will range from double asterisk to 2 alpha characters (it is the revision of a part/drawing)
    • I know the double asterisks were a bad choice but this is what I have to work with and it cannot change
  4. The field being filtered with LIKE and % will contain something like:
    • Added to X31-D104518-1-00101-**
    • Removed from X31-D104518-1-00101-**

It is kind of ugly to view but the original, working query text is below. Note that I formatted for readability but it works so any typos are not an issue.

"SELECT HistoryID, InventoryID, SerialNumber, QuantityChange, TransactionAction, Notes, TransactionDate, TransactionTime
FROM InventoryHistory
WHERE ((((InventoryHistory.TransactionAction) Like '%" + Me.cmboAssemblyNum.Text + "')
AND ((InventoryHistory.Location) = 'INVENTORY'))
OR (((InventoryHistory.SerialNumber) = '" + Me.cmboAssemblyNum.Text + "') AND ((InventoryHistory.TransactionAction) = 'Assembly is complete'))
OR ((InventoryHistory.Location) = '" + Me.cmboAssemblyNum.Text + "'))"

I have parameterized the query in the following manner using @cmboAssemblyNum but it doesn't work. I do not get any errors but the query is not returning the expected values. Again, I formated for readability and all I did in the code was to replace " + Me.cmboAssemblyNum.Text + " with @cmboAssemblyNum.

I've also tried the following with and without single quotes, e.g. '%@cmboAssemblyNum'.

"SELECT HistoryID, InventoryID, SerialNumber, QuantityChange, TransactionAction, Notes, TransactionDate, TransactionTime
FROM InventoryHistory
WHERE (((InventoryHistory.TransactionAction Like %@cmboAssemblyNum)
AND ((InventoryHistory.Location) = 'INVENTORY'))
OR (((InventoryHistory.SerialNumber) = @cmboAssemblyNum)
AND ((InventoryHistory.TransactionAction) = 'Assembly is complete'))
OR ((InventoryHistory.Location) = @cmboAssemblyNum))"

Full code for executing this query is.

Dim ds As New DataSet
Dim da As New OleDb.OleDbDataAdapter

'If an assembly has been started but not completed, get list of items already added
strSQL = "SELECT HistoryID, InventoryID, SerialNumber, QuantityChange, TransactionAction, Notes, TransactionDate, TransactionTime " & _
"FROM InventoryHistory " & _
"WHERE (((InventoryHistory.TransactionAction Like %@cmboAssemblyNum) AND ((InventoryHistory.Location) = 'INVENTORY')) OR (((InventoryHistory.SerialNumber) = @cmboAssemblyNum) AND ((InventoryHistory.TransactionAction) = 'Assembly is complete')) OR ((InventoryHistory.Location) = @cmboAssemblyNum))"

Try
   'run strSQL statement fills tbl_ExistingTransactions with resulting dataset
   da.SelectCommand = New OleDb.OleDbCommand(strSQL, Conn_Backend)
   da.SelectCommand.Parameters.AddWithValue("@cmboAssemblyNum", Me.cmboAssemblyNum.Text)
   da.Fill(ds)
   tbl_ExistingTransactions = ds.Tables(0)

Catch ex As Exception
    Me.txtStatus.Text = "Caught exception when running strSQL the first time in UpdateDataGridView"

End Try

Any help or guidance is appreciated. I've searched StackOverFlow as well as other sites and have not found anything that works to date.

I do not believe I can include a sample of the tables but if I am able I will do so.

Improve this question
0

3 Answers 3

Reset to default
3

Try with the % wildcard character in the parameter value, instead of in the SQL:

Dim ds As New DataSet
Dim da As New OleDb.OleDbDataAdapter

'If an assembly has been started but not completed, get list of items already added
strSQL = "SELECT HistoryID, InventoryID, SerialNumber, QuantityChange, TransactionAction, Notes, TransactionDate, TransactionTime " & _
"FROM InventoryHistory " & _
"WHERE (((InventoryHistory.TransactionAction Like @cmboAssemblyNum) AND ((InventoryHistory.Location) = 'INVENTORY')) OR (((InventoryHistory.SerialNumber) = @cmboAssemblyNum) AND ((InventoryHistory.TransactionAction) = 'Assembly is complete')) OR ((InventoryHistory.Location) = @cmboAssemblyNum))"

Try
   'run strSQL statement fills tbl_ExistingTransactions with resulting dataset
   da.SelectCommand = New OleDb.OleDbCommand(strSQL, Conn_Backend)
   da.SelectCommand.Parameters.AddWithValue("@cmboAssemblyNum", "%" & Me.cmboAssemblyNum.Text)
   da.Fill(ds)
   tbl_ExistingTransactions = ds.Tables(0)

Catch ex As Exception
    Me.txtStatus.Text = "Caught exception when running strSQL the first time in UpdateDataGridView"

End Try
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Just tried this, it did not work. I'm starting to think it may not be the "...Like..." portion where the problem is. With the lack of an error it seemed logical because I've been successful parameterizing other queries but they did not have the "...Like..." portion.
0

Try this:

WHERE (((InventoryHistory.TransactionAction Like '%' + @cmboAssemblyNum)
Improve this answer

Comments

0

I think I sorted it out although I am unable to explain why this works. I created an INI file and included the following in the INI which is what I originally tried in my code (i.e. replacing " + Me.cmboAssemblyNum.Text + " with @cmboAssemblyNum). I read the INI file using GetPrivateProfileString and stored the value in a string.

sql_tblExistingTransactions="SELECT HistoryID, InventoryID, SerialNumber, QuantityChange, TransactionAction, Notes, TransactionDate, TransactionTime FROM InventoryHistory WHERE ((((InventoryHistory.TransactionAction) Like '%@cmboAssemblyNum.Text') AND ((InventoryHistory.Location) = 'INVENTORY')) OR (((InventoryHistory.SerialNumber) = @cmboAssemblyNum.Text) AND ((InventoryHistory.TransactionAction) = 'Assembly is complete')) OR ((InventoryHistory.Location) = @cmboAssemblyNum.Text))"

I then ran my code as-is in my question:

   da.SelectCommand = New OleDb.OleDbCommand(strSQL, Conn_Backend)
   da.SelectCommand.Parameters.AddWithValue("@cmboAssemblyNum", Me.cmboAssemblyNum.Text)
   da.Fill(ds)
   tbl_ExistingTransactions = ds.Tables(0)

and it worked as expected.

We use the INI file for queries anyway but do development outside to avoid in-service issues while updates are being made so this works out.

Can anyone come up with reasoning as to why it would work when a string is being read from an INI file but not work when using in-line SQL statement in the code?

I'm going to try some other things but I don't think I will sort out why this works.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.