Search db

[pirate]

Hi guys !
How can I delete & edit & search access db ? I know how to add new rows & update ! .So I think it wont 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, youll 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
[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! Ill have to add something later

-nerseus

 

[pirate]

Deletion problem sorted

one of the three problemss sorted .what left now is searchin and editing db .
thanx

 

[pirate]

Editing problem sorted

second problems sorted .Searching db is tough one.I dont know how to solve this.

 

[Nerseus]

For searching youre going to have to write you own SQL. Lets 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 youre 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 Odonnel will cause an exception.

-nerseus

 

[pirate]

First I want kiss ya . Thanx for your patience .Right now I cant try it .I must later.If anything goes wrong , I dont 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".Its looping through something but I dont know how to do this .Do you know how ?
thanx again and again and again Nerseus

 

[Nerseus]

Youre welcome
I wouldnt try searching after every keypress - Id wait til they tabbed out (at the earliest) or better yet, wait til they press a search button.

Ive 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. Ive never tried this, but you could give it a try if you wanted a more immediate response.

-nerseus

 

[pirate]

Well , Pirate is back of course with different bug .
Im using this to search the database .But nothing happens at all .I dunno whats 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 .