[RESOLVED] Search button with databases

[Lasering]

Hi!!

I've 4 databases which one have 4 tables. I've a form with a textbox (Searchbox) and a search button (Search). What i want is when u click in search button, he adds to an array all items in the tables of the DB and by colunms by ADO and finaly to seach in the array the text of the textbox and for each found result to add it to a new array also by colunms. U get the point, if not will try to be clear.

Thks!!!

[jmcilhinney]

Sounds like you need to do some reading on ADO.NET. There are numerous tutorials on the subject in my signature.

[Lasering]

Thks jmcilhinney!

But i already know how to conect and use recordset, what i dont know is arrays, arrayslist, multidimensional arrays...

[jmcilhinney]

Recordsets are ADO. You should dump ADO and learn how to use ADO.NET for all your new work. Arrays are a pretty standard part of any programming langauge, so you should do some reading that subject also. The Start VB.NET link in my signature has a section on arrays. An ArrayList is just a simple collection. You create one as you would any other type of object and you call its Add method to add items. You should go to the MSDN library, which you should have installed locally if you're using VB Express or you can use the online version, and read the help topic for the ArrayList class (search for "arraylist class" without quotes). It will give an overview of how to use the class and a link to its member listing for more detailed information.

[Lasering]

Thks for the answer but i prefer ADO even if ADO.NET is best because: with ADO.NET i cant change the conection string in runtime mode, i have to add to the project many table adapters in both forms and i'm working with +/- 40 tables and that only slows me down.

One question with ADO can i make a new database file (mdb) with various tables?

PS: The answer about arrays make me thing and i get to the conclusion that with arrays will only make my work harder so i get another way of solving the problem. Thks again

[sevenhalo]

ADO.NET i cant change the conection string in runtime mode

Yes you can.

i have to add to the project many table adapters

No you don't.

i'm working with +/- 40 tables

Even more of a reason to use a typed dataset.

But back to what you're asking about ADO.
You can do this with ADOX. Reference "Microsoft ADO Ext. 2.8 for..." and the following code should work:

Dim cat As New ADOX.Catalog
        cat.Create("Provider='Microsoft.Jet.OLEDB.4.0';Data Source='C:\new.mdb'")

More info on ADOX:
http://msdn.microsoft.com/library/de...ireference.asp

[Lasering]

Thks a lot for the answer but one thing how can i add a ADOX API Reference

For curiosity how can u change the conection string in runtime mode with ADO.NET? If i dont have to use table adapters what do i use then?

[sevenhalo]

The same way as you would change any property or value in runtime mode? I guess I'm not following where you think the issue lies in changing this property?

[Lasering]

I go to Data Menu - Add New Data Source, and then create a new conection that stays stored in settings of the project. When i try to change the conection he says this propertie is readonly

Only a Example

My.Settings.LevelsConnectionString = Application.StartupPath

[jmcilhinney]

You're using a lot of the built-in functionality of the IDE to create your data access code. Like all wizards and code generation mechanisms it will make various assumptions about what you want to do. There's a lot more to ADO.NET than that. You can create a typed DataSet without creating a Data Source. You can also create all your connections and data adapters yourself, which allows more control than letting the IDE do it for you. That's how ADO.NET worked in the last two versions of .NET and whle it's not what Microsoft is pushing it is still available in .NET 2.0. You can simply create an OleDbConnection and set its ConnectionString property whenever you like. You should do some reading on ADO.NET, particularly as it was before .NET 2.0, to get an idea of what you can do yourself. TechGnome's ADO.NET tutorials (see my signature for links) are a good place to start.

Edit: Plus, even if you can't change what's in the settings at run time you can still change the ConnectionString of the connection. If you want to change what's in the settings you can either do so by manually editing the config file in Notepad or do it in code with an XmlDocument object. Forget ADO. ADO.NET can do exactly what you want and much more besides.

[Lasering]

So how can do this:

Conect.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & Chr(34) & Application.StartupPath & ActualDB & Chr(34))
        Conect.Execute("INSERT INTO" & " " & TabelaActual & " " & "VALUES ('" & ItemName.Text & "', '" & Category.Text _
        & "', '" & ItemNumber.Text & "', '" & ItemLevel.Text & "', '255', '0', '0', '0', '" & Altura.Text & "', '" & Largura.Text & "')")
        RS.Open(TabelaActual, Conect, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockPessimistic)
        RS.Update()
        RS.MoveFirst()
        For a As Short = 0 To RS.Fields.Count
            Do Until RS.EOF = True
                L1.Items.Add(RS.Fields.Item("Category").Value & " " & RS.Fields.Item("Number").Value & " " & RS.Fields.Item("Level").Value _
                & " " & RS.Fields.Item("Durability").Value & " " & RS.Fields.Item("Skill").Value & " " & RS.Fields.Item("Luck").Value _
                & " " & RS.Fields.Item("Options").Value & " - " & RS.Fields.Item("Name").Value)
 
                TabAux.Items.Add(RS.Fields.Item("Name").Value)
                RS.MoveNext()
            Loop
        Next
        RS.Close()
        Conect.Close()

With ADO.NET instead of ADO because i can't simply don't understand

[jmcilhinney]

As always there are a number of different ways to achieve the same end result in ADO.NET so this is not definitive. Also, I've never used ADO so I don't know exactly how RecordSets behave. Having said that, this is a pretty good approximation of what you have there:

Dim ActualDB As String
        Dim TabelaActual As String
 
        Dim myConnectionStringBuilder As New OleDbConnectionStringBuilder
 
        myConnectionStringBuilder.Provider = "Microsoft.Jet.OLEDB.4.0"
        myConnectionStringBuilder.DataSource = IO.Path.Combine("|DataDirectory|", ActualDB)
 
        Dim myConnection As New OleDbConnection(myConnectionStringBuilder.ConnectionString)
        Dim myInsertCommand As New OleDbCommand("INSERT INTO @Table (<you really should put the column names here>) VALUES (@Name, @Category, @Number, @Level, 255, 0, 0, 0, @Altura, @Largura)", myConnection)
 
        myInsertCommand.Parameters.AddWithValue("@Table", TabelaActual)
        myInsertCommand.Parameters.AddWithValue("@Name", ItemName.Text)
        myInsertCommand.Parameters.AddWithValue("@Category", Category.Text)
        myInsertCommand.Parameters.AddWithValue("@Number", ItemNumber.Text)
        myInsertCommand.Parameters.AddWithValue("@Level", ItemLevel.Text)
        myInsertCommand.Parameters.AddWithValue("@Altura", Altura.Text)
        myInsertCommand.Parameters.AddWithValue("@Largura", Largura.Text)
 
        myConnection.Open()
        myInsertCommand.ExecuteNonQuery()
 
        Dim myQuery As New OleDbCommand("SELECT Category, Number, Level, Durability, Skill, Luck, Options, Name FROM @Table", myConnection)
 
        myQuery.Parameters.AddWithValue("@Table", TabelaActual)
 
        Dim myDataReader As OleDbDataReader = myQuery.ExecuteReader(CommandBehavior.CloseConnection)
 
        While myDataReader.Read()
            L1.Items.Add(String.Format("{0} {1} {2} {3} {4} {5} {6} - {7}", _
                                       myDataReader("Category"), _
                                       myDataReader("Number"), _
                                       myDataReader("Level"), _
                                       myDataReader("Durability"), _
                                       myDataReader("Skill"), _
                                       myDataReader("Luck"), _
                                       myDataReader("Options"), _
                                       myDataReader("Name")))
            TabAux.Items.Add(myDataReader("Name"))
        End While
 
        myDataReader.Close()

You may need to make a few adjustments and you should add some exception handling. Also, you really should specify the column names in your INSERT statement.

[Lasering]

Thks a lot Again!!! (You a pro X2)

I prefer ADO because it is more simple and you have to write less lines, but with ADO u dont have the possiblity to create new DB and with ADO.NET u can. So i will have to surrender to the fact that in my case ADO.NET is best for the job!

[jmcilhinney]

From what I understand of ADO, ADO.NET is much more flexible. Like I said, there are numerous ways to achieve the same result. You could get away with writing less code than I have here, plus you can do a lot of the work visually in the designer if you like. The IDE will also do a lot for you you if you want it to, especially in 2005.

[Lasering]

With the code that u give me before i could change and i was getting what i wanted till i try to change this ado code to ado.net:

Conect.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & Chr(34) & Application.StartupPath & ActualDB & Chr(34))
RS.Open(TabelaActual, Conect, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockPessimistic)
RS.Update()
RS.Move(ActualIndex)
RS.Fields.Item("Name").Value = NovoNome
TabAux.Items(ActualIndex).Text = NovoNome
RS.Save(TabelaActual)
RS.Close()

I hope u could help me again because i really need help!

[Lasering]

Thks but i've found a way to do that through UPDATE SQL Statement thks anyway for the help.