Populating per like statement

Populating per like statement

• Johnn manc

  I found the query builder was doing it....i edited the sql line and it took it out..however it did put brackets around the text part...WHERE (Word LIKE txtFind.[Text])...

  ...it seems i'm getting closer as it went further.........seems now it is hung up on my click event to populate the datagrid and says: OleDBException is unhandled. it is kicking on the;

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

  Me.DictionaryTableAdapter.Fill(Me.CrossWordDataSet.Dictionary) <- here it dies...i've tried different things...

  ' Me.Refresh()

  'Me.DictionaryTableAdapter.Update(Me.CrossWordDataSet.Dictionary)

  thanks for your help...i'd won't have got this far without your help............. :>)

  
  

• Rusty Deschenes

  i'm getting nothing......

  here's what i have:

  Imports System.Data.Odbc

  .....

  on the click event...

  Dim da As OdbcDataAdapter = New OdbcDataAdapter()

  Dim cmd As OdbcCommand

  Dim parm As OdbcParameter

  cmd = New OdbcCommand("SELECT Word FROM Dictionary " & _

  "WHERE Word like txtfind.text")

  cmd.Parameters.Add("@Word", OdbcType.NVarChar, 15)

  Me.DictionaryTableAdapter.Fill(Me.CrossWordDataSet.Dictionary)

  i'm lost.....thanks for helping........

  
  

• VJV

  What's going wrong is that you have txtfind.text in quotes somewhere, I would think. One it's SQL, it needs the quotes, it just needs the text from your textbox, instead of the name of it in there.

  You're also using the wrote characters to populate your like, if you're using and *. Here's an extract from the SQL books online:

  Wildcard	Meaning
  %	Any string of zero or more characters.
  _	Any single character.
  [ ]	Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).
  	Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).

  Perhaps if you post all code that refers to that text property

  
  
  

• Clas

  To be honest... i want to do it anyway i can!... i don't know any other way...i've searched and read and tried it dozens of ways and can't get b to this....this is the basic to a lot of programming i do....if you have any method that will work...i would certainly appreciate the assistance...thanks again.......
  

• namo21

  Thanks...i forgot about the symbols....i still can't get it to work though.....so the like statement appears okay i thought it was to be without quotes, unless it was a literal....there's not alot of code ..so i don't know what's wrong.........
  

• Jon Irish

  You didn't say what you were using before, but I thought that Access supported stored procs Even if you generated the SQL yourself instead of using a stored proc, using those objects will give you more control than with the stuff you're using now.

  
  
  

• grr grr grr

  Well, if your database supports stored procedures, you can write the SQL as a stored proc, and then you can call the procedure using the SQLCommand object.

  http://www.startvbdotnet.com/ado/sqlserver.aspx

  that provides a half decent overview, from the look of it.

  
  
  

• adcworks

  i appreciate the tip...but the sample is using sqlserver and not access....i'll never figure that out...but let me read on the datareader... i'll try anything....it seems such a simple thing to do...i'd have 50 of these done in access.......thanks again..........
  

• Digant

  OleDBException is unhandled. it is kicking on the;

  is what you said, I assumed it was 'kicking on the ;' :-)

  
  
  

• Steve_B

  whew. I tried to tell him this yesterday ( that the name of the variable was being passed in, instead of the value ).

  I'm glad someone with more knowledge of Access than me was able to provide a clear answer for him.

  
  
  

• Jian Zeng

  Yes, it's still a string, so I'd expect it to be in quotes. You can test it in query analyzer, that's the way to go with a problem here, test your SQL, and if it works then the problem is in how it's being passed/handled.

  But, if you have LIKE 'txtbox.text' in your SQL string, that's obviously where the problem is. How is the string getting in there

  
  
  

• dimaki

  What semicolon What's the exception say

  To be honest, I don't use these new adapter things, I prefer to write stored procedures and call them. Perhaps you're finding limitations in the adapters

  
  
  

• IAN1903

  Hi, Bill,

  Sorry it took 12 unsuccessfull replies before I found your issue.

  Your are using the Query Builder, which is just creating the query in plain text. When you typed txtFind.Text in the filter, it is taking txtFind.Text literally. Query Builder does n`t know about objects and properties.

  You have basically 2 options that I know (maybe someone else might know another trick or two):

  1) Since you are unisng MS Access, you will use the question mark ( ) as the filter value. In Access, the works as a parameter.
  Then, in code, you have to instanciate a Parameter object and provide the txtFind.Text as the Value of the parameter. From MSDN Library:

  Dim da As OdbcDataAdapter = New OdbcDataAdapter()
  Dim cmd As OdbcCommand
  Dim parm As OdbcParameter

  ' Create the SelectCommand.
  cmd = New OdbcCommand("SELECT * FROM Customers " & _
  "WHERE Country = ", conn)
  cmd.Parameters.Add("@Country", OdbcType.NVarChar, 15)

  2) The other trick would be to modify the adapter.SelectCommand property on the fly.
  Try this code:

  ' The CommandText of the Command object is of type String.
  yourCommandInstance.CommandText = _ yourCommandInstance.CommandText.Replace("txtFind.Text", txtFind.Text.Trim())

  Let me know if this helped you. Good luck!

  
  

• Jorge Loureiro

  what semicolon/ - i was referring to it dying at this line:Me.DictionaryTableAdapter.Fill(Me.CrossWordDataSet.Dictionary) ...this is where it got the exception...........
  
• Populating per like statement