Search button.

[tag141]

I have been building an address book prog for my company. I followed this tutorial

http://www.homeandlearn.co.uk/NET/nets12p1.html

which I think is excellent. If I can follow it and get it to work, anyone can. It's answered several questions I had as well. I wanted to expand on the basic address book and put on a search button but to no avail. My code for the button is this

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\AddressBook.mdb"
        con.Open()
        sql = "SELECT * FROM tblContacts WHERE Surname = '" & SurnameTextBox.Text & "' "
        da = New OleDb.OleDbDataAdapter(sql, con)
        con.Close()
        da.Fill(ds, "AddressBook")
        MaxRows = ds.Tables("AddressBook").Rows.Count
        inc = -1
        
    End Sub

The address books perfectly so the code I have is OK apart from the search button. I assume it's just a case of filling the textboxes with fields but where am I going wrong?

TIA

 

[Borvik]

First, it's helpful if you also tell us the error you ARE getting, so we know where to start.

Second, I'm not 100% sure (99% sounds good ) but try moving the line

con.Close()

below the line

da.Fill(ds, "AddressBook")

instead of having it above it. It most likely is closing the connection before you are actually getting the data.

 

[tag141]

Thanks for your help. Unfortunately there are no 'errors' or error messages. It simply doesn't populate any of the textboxes on the form. I know the code for the rest of the app works as I can navigate etc but I can't populate from that search button.

 

[earthandfire]

From memory, a dataadapter opens and closes the supplied connection as and when necessary, so you don't need con.open or con.close.


Hope this helps.

 

[chrissie1]

From memory, a dataadapter opens and closes the supplied connection as and when necessary, so you don't need con.open or con.close.

From my memory, only when you tell it to.

Christiaan Baes
Belgium

"My new site" - Me

 

[ecannizzo]

I don't actually see, in your posted code, any code where you specified where the results of the dataset should go. Do you have any code that looks something like this:

Label1.Text = ds.Tables("AddressBook").Rows(0)("yourcolumnname").ToString()

 

[tag141]

That could be the problem couldn't it. I haven't told the prog where to insert the data. So now I have this...

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        sql = "SELECT * FROM tblContacts WHERE Surname = '" & SurnameTextBox.Text & "' "
        con.Open()
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "AddressBook")
        FirstNameTextBox.Text = ds.Tables("AddressBook").Rows(0)("FirstName").ToString()

        MaxRows = ds.Tables("AddressBook").Rows.Count
        inc = -1
        con.Close()
    End Sub

However this grabs the first name in row 0. How can I get it to grab the row that corresponds to my surname input?

 

[chrissie1]

Apart from the fact you aren't using parameters (which is a deadly sin).

The first row should return the first name you want since that is what the query says it should do. It returns all those records with the same surname. So perhaps you have more then one record with the same first name.

But since you are using wizards (how do I know that?). Are you by any chance using databinding and is firstnametextbox bound to something?

Christiaan Baes
Belgium

"My new site" - Me

 

[tag141]

Apart from the fact you aren't using parameters (which is a deadly sin).

Can you expand on this further?

I'm not using a wizard. I copied the code directly from the tutorial I mentioned in my original post, so maybe they did? I definately will have a problem when people have the same first name. I found a tutorial on treeview and quite like the way that works so have been going with that instead. However, not being one to give up too quickly, I would still like to get my search button to work (then I'll have to separate the first names so even more posts here!!!)

 

[chrissie1]

So what does Maxrows return?

Parameters.

    dim cmdselect as new oledbcommand
dim dt as datatable
 cmdselect.Parameters.Add(New OleDbParameter("@surname",
OleDbType.varchar, 50))
cmdselect.parameters(0).value = surnametextbox.text 
cmdselect.commantext = "SELECT * FROM tblContacts WHERE Surname = @surname"
cmdselect.connection = con
        con.Open()
        da.selectcommand = cmdselect
        da.Fill(dt, "AddressBook")
        FirstNameTextBox.Text = dt.Rows(0)("FirstName").ToString()

        MaxRows = dt.Rows.Count
        inc = -1
        con.Close()

untested but it should work.

not sure why you use the dataset. but it is overkill in this instance.

Christiaan Baes
Belgium

"My new site" - Me

 

[earthandfire]

By setting the Connection property of a DataAdapter's Select, Update, Insert or Delete command, you remove the need to explicitly Open and Close the Connection.

MyDA.Fill(etc., etc.) DOES automatically open and close the connection.


Hope this helps.

 

[chrissie1]

Sorry for that

so that will make it

  dim cmdselect as new oledbcommand
dim dt as datatable
 cmdselect.Parameters.Add(New OleDbParameter("@surname",
OleDbType.varchar, 50))
cmdselect.parameters(0).value = surnametextbox.text 
cmdselect.commantext = "SELECT * FROM tblContacts WHERE Surname = @surname"
cmdselect.connection = con
        da.selectcommand = cmdselect
        da.Fill(dt, "AddressBook")
        FirstNameTextBox.Text = dt.Rows(0)("FirstName").ToString()

        MaxRows = dt.Rows.Count
        inc = -1

Christiaan Baes
Belgium

"My new site" - Me

 

[earthandfire]

No need to apologise chrissie, with the vast amount you know (being the genius you are), you are bound to occasionally forget the odd thing or two.