SQL Commands in an access Database

SQL Commands in an access Database

• crp2k4

  To search for a specific color in the table:

  Dim stringQuery as String ="SELECT * FROM [" & tableName & "] WHERE [COLOR]=" & yourColor ,con)

  just one of many ways to get specific data..........Renee knows way more about this than I do!!!

  james

  aka:Trucker

  
  

• J M B

  Awww Trucker, you're sweet.... :)

  There are several iterative ways to go about this.

  Private Function ColorSearch(Byval InputColor as string) as datarow

  For each row as datarow in table1.rows

  If row("Colors") = InputColor then return row

  Next

  return nothing

  End function

  
  
  

• Dave_AC

  Hey there, what would be a query string for not equal

  I want to select the fields in the same column where the fields are diferent to"example: %red%"

  how can i do that help!. thanks

  
  

• JamesChapman44

  Re-read my post above. You can open your table, and let a SQL statement read it and return only the rows that contain the color you are searching for. If you are trying to interate thru the string and match that way and ignoring all the other words in the string that don't match a color in your table , then that can be a bit more complex. And slower.

  james

  aka:Trucker

  
  

• Joseph G

  Private Function ColorSearch(Byval InputColor as string) as datarow

  For each row as datarow in table1.rows

  If directcast(row("Colors"),string).contains(InputColor) then return row

  Next

  return nothing

  End function

  
  
  

• Dave Hampe

  Hey there, Well I still have the same problem, It's because all the codes, are looking the color into the string one by one, for the example that I gave you,

  the message, was

  "The dog is running on the green grass, and the sun is yellow"

  example for the "TableColor"

  ID Color

  1 Red

  2 Blue

  3 Yellow

  4 Green

  The code I'm using is something like the next one:

  imports System.Text.RegularExpressions

  imports System.Data.OleDb

  Dim message As String = "The dog is running on the green grass, and the sun is yellow"

  Function getcolors()

  Dim QuerySearch As String = "SELECT Color FROM TableColor"

  Using Connection As New OleDbConnection(ConnectionString)

  Dim Command As New OleDbCommand(QuerySearch, Connection)

  Connection.Open()

  Dim Finds As OleDbDataReader = Command.ExecuteReader

  While Finds.Read

  Dim re As New Regex(Finds.GetString(0), RegexOptions.IgnoreCase)

  Dim m As Match = re.Match(Message)

  If m.Success Then

  'here goes the code when the match is succes, and I have the color matched

  End If

  End While

  End Using

  End Function

  This code compare the color one by one in the table with the message, I'm asking for if there is any way that I can select all the rows where the color is matching into the message, not doing it one by one, something more faster that one by one!!.

  please help and thanks.

  
  

• jen

   

  The command string is always the same:

  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\KnowledgeNavigator.mdb

  You can replace datadirectory with the path if you like.

   

  Dim Connectionstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\Db1.mdb"
  Dim stringQuery As String = "SELECT * FROM Table1"
  Dim con As New OleDbConnection(ConnectionString)
  Con.open

  Using adapter As New OleDbDataAdapter("Select * from [" & tableName & "]" , con)
  Dim Table1 As New DataTable("Table1")
  Table1.Tablename = "Table1"
  

  Try
         adapter.Fill(Table1)
  catch
  end Try

   

   

  
  
  
• SQL Commands in an access Database