Search db

[pirate]

Hi guys !

How can I delete & edit & search access db ? I know how to add new rows & update !:D .So I think it won't take more than 4 to 8 lines of code.

any help would be greatly appreciated !

[Nerseus]

How are you adding/updating right now?

 

You can use a command on a DataSet to do "automatic" inserts/updates/deletes. Or, you can issue direction SQL statements such as "INSERT INTO Customers..." (or call a proc using either method).

 

For searches, you'll need to write a query unless you just want to bring back all records for a table (not normally a good idea, but maybe).

 

Can you post the code you have so far?

 

-nerseus

[pirate]

sure :D

[VBCODE]

  'open Connection to the Database with Password
   Dim MyPath As String = Application.StartupPath & "\__mydb__.mdb"
   Dim MyPassword As String = "passme"
   Dim StrSQL As String = "Select * From MyTable"
   Dim MyConnection As New 
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & 
MyPath & ";Jet OLEDB:Database Password=" & MyPassword)
       MyConnection.Open()

   'This Saves data to a DataSet and then uses update method
   'against Database Source File
   Dim MyCommand As New OleDbCommand(StrSQL, MyConnection)
   Dim MyDataset As DataSet = New DataSet()
   Dim MyAdapter As New OleDb.OleDbDataAdapter(StrSQL, MyConnection)


       MyAdapter.Fill(MyDataset, "MyTable")
   Dim MyDataRow As DataRow = MyDataset.Tables("MyTable").NewRow
   'Fill the data in Four Columns in the Database (Fields)
       MyDataRow("1_Column") = Textbox1.Text
       MyDataRow("2_Column") = Textbox2.Text
       MyDataRow("3_Column") = Textbox3.Text
       MyDataRow("4_Column") = Textbox4.Text
       MyDataset.Tables("MyTable").Rows.Add(MyDataRow)
       MyAdapter.Update(MyDataset, "MyTable")
       MessageBox.Show("Data Saved..", "Saved",

   'if you want to write the data as well to XML file then you
   'include(this)before update method
       MyDataset.WriteXml(Application.StartupPath & "\_MyXMLFile_.XML")
   End Sub
End Class

[/VBCODE]

I need to have many options to set many criteria ! so can sql fit that .

thanx for your time

[pirate]

:( Sorry Nerseus , any hope ?:(

[Nerseus]

Slow down, I just got in! I'll have to add something later :)

 

-nerseus

[pirate]

Deletion problem sorted

 

one of the three problems's sorted .what left now is searchin and editing db .

thanx

[pirate]

Editing problem sorted

 

second problem's sorted .Searching db is tough one.I don't know how to solve this.:(

[Nerseus]

For searching you're going to have to write you own SQL. Let's say you have a column called LastName and you provide a textbox to search. You can build a SQL string using something like this:

Dim SQL As String
SQL = "SELECT * FROM MyTable WHERE LastName LIKE '" & textBox1.Text.Replace("'", "''") & "%'"
' The resulting string looks like:
'SELECT * FROM MyTable WHERE LastName LIKE 'jones%'

Replace the "%" with "*" if you're using Access.

 

Use the SQL string in your DataAdapter to .Fill a DataSet.

 

The .Replace(...) method replaces any single quotes with two single quotes so that the query will run. Without it, searching for the name O'donnel will cause an exception.

 

-nerseus

[pirate]

First I want kiss ya . Thanx for your patience .Right now I can't try it .I must later.If anything goes wrong , I don't know I maybe feedback here.

(one more thing I want to mention is :how can I search in textchange event .For example when I just type "J" , I find all names start in "J".It's looping through something but I don't know how to do this .Do you know how ?

thanx again and again and again Nerseus

[Nerseus]

You're welcome :)

I wouldn't try searching after every keypress - I'd wait til they tabbed out (at the earliest) or better yet, wait til they press a search button.

 

I've seen some people put a timer on a form. Every time the user types something, they restart the timer. If the timer goes off (usually after 1 or 2 seconds), they perform the search. That way, you give the user a chance to type a few letters before searching rather than doing a search after every keypress. I've never tried this, but you could give it a try if you wanted a more immediate response.

 

-nerseus

[pirate]

Well , Pirate is back:D of course with different bug:( .

I'm using this to search the database .But nothing happens at all .I dunno what's wrong here ?

thanx for any help

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
       Dim MyConnection1 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyPath & ";Jet OLEDBatabase Password=" & MyPassword)
       Dim myDataReader As OleDbDataReader
       Dim txt As String = "SELECT * FROM MyTab WHERE C_URL LIKE '" & TextBox7.Text.Replace("'", "''") & "*'"
       Dim myOleDbCommand = New OleDbCommand(txt, MyConnection1)

       MyConnection1.Open()
       myDataReader = myOleDbCommand.ExecuteReader()
       Do While (myDataReader.Read)

           If (myDataReader.IsDBNull(0)) Then
               MsgBox(myDataReader.GetString(1))
               Console.Write("N/A" + Chr(10))
               MsgBox("no data")
           Else
               Console.Write(myDataReader.GetInt32(4).ToString() + Chr(10))
               MsgBox(myDataReader.GetInt32(4).ToString() + Chr(10))
           End If
       Loop
       myDataReader.Close()
       MyConnection1.Close()

   End Sub

If anyone has another code for searching db ,It would be very nice of him to share.

thanx again .