Database Related [solved]

Posts 1–15 of 17 · Page 1 of 2

Code:

'stuff I do not understand but use:
 Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String

        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = C:\Users\Manel\Downloads\AddressBook\AddressBook.mdb"

        con.ConnectionString = dbProvider & dbSource
'end of that stuff
        con.Open()

        sql = "SELECT * FROM tblContacts"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "AddressBook")
        MsgBox("Database is now open")
        ds.Tables("AddressBook").Columns.Contains(TextBox1.Text)
       If (ds.Tables("AddressBook")****ws(1).Item(1).Equals(TextBox1.Text)) Then
            Dim itid As String
            itid = ds.Tables("AddressBook")****ws(1).Item(0)
            MsgBox("It does exist, it's ID is " + itid + "!")

        Else
            MsgBox("It does not")

        End If
        con.Close()

        MsgBox("Database is now Closed")

So, it works but what I need is something like this:
---------------------

If (ds.Tables("AddressBook")****ws(ALL ROWS IN HERE).Item(ALL ITEMS IN HERE).Equals(TextBox1.Text)) then
Dim itid As String
itid = ds.Tables("AddressBook")****ws(ROWS WHERE ITEM EXISTS).Item(0)
MsgBox("It does exist, it's ID is " + itid + "!")

Else
MsgBox("It does not")

End If
--------------------

As an alternative if you know any database searching source-code in vb.net I'd be thankfull.
the **** is ". rows" without the space.
Thank you in advance,
omanel

@omanel

[highlight="VB.Net"] Dim conn As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLED B.4.0;" _
& "Data Source = C:\Users\Manel\Downloads\AddressBook\AddressBook.m db")
Dim reader As OleDbDataReader

Private Function GetItemId(ByVal AddressBook As String) As String
Dim sql As String = String.Format("SELECT * FROM tblContants WHERE AddressBook='{0}'", AddressBook)
Dim Ret As String = ""

conn.Open()

Dim command As New OleDbCommand(sql, conn)

reader = command.ExecuteReader

If reader.HasRows Then
While reader.Read
Ret = reader("ItemId")
End While
End If

conn.Close()

Return Ret
End Function[/highlight]

[highlight="VB.Net"]Msgbox(GetItemId(Textbox1.Text))[/highlight]

Didn't test. You have to customize it a little bit.

I Writed this before you edited your post, going to read it now.

Originally Posted by Blubb1337

Declare a oledbdatareader.

Change the query to something like...

[highlight="VB.Net"]sql = "SELECT * FROM tblContacts WHERE AddressBook='Condition' LIMIT 1"[/highlight]

Execute the sql query within the reader.
[highlight="VB.Net"]
While reader.read
msgbox(reader("ColumnNameYouWant2Read"))
end while
[/highlight]
As you see, those are just instructions, not full code, try2code the instructions w/o code yourself.

What would the reader be?

Btw, thanks for the advice on the sql, I changed it to
[highlight="VB.Net"]
sql = "SELECT * FROM tblContacts WHERE FirstName=" + TextBox1.Text + " LIMIT 1"
[/highlight]

How can I display the result of that in lets say a label or something?
That's what I do not understand

label1.text = GetItemId(Textbox1.Text)

So the code now looks like this:
[highlight="vb.net"]
Dim conn As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLED B.4.0;" _
& "Data Source = C:\Users\Manel\Downloads\AddressBook\AddressBook.m db")
Dim reader As OleDb.OleDbDataReader

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sql As String = String.Format("SELECT * FROM tblContacts WHERE FirstName=" + TextBox1.Text + "")
Dim Ret As String = ""

conn.Open()
MsgBox("open")

Dim command As New OleDb.OleDbCommand(sql, conn)

reader = command.ExecuteReader

If reader.HasRows Then
While reader.Read
Ret = reader("FirstName")
MsgBox(Ret)
End While

End If
conn.Close()
MsgBox("close")

[/highlight]

It gives an error at
[highlight="vb.net"]
reader = command.ExecuteReader
[/highlight]
It says one or more paramters have not been provided with a value or something like that.

Dim sql As String = String.Format("SELECT * FROM tblContacts WHERE FirstName=" + TextBox1.Text + "")

Change it back to my code, this is invalid sql syntax.

I got it to work!

This is the sql syntax I used:
[highlight="vb.net"]Dim sql As String = String.Format("SELECT * FROM tblContacts WHERE FirstName='" + TextBox1.Text + "'")
[/highlight]

Guess those few hours I spent on php and databases came handy in vb.net xD

Thank you anyway, most of the code is yours,I just changed some apostrophes and quotes. lol

Originally Posted by omanel

I got it to work!

The string.format is nonsense when you use it like that.

String.Format is used in the following way:

[highlight="vb.net"]Dim sql As String = String.Format("SELECT * FROM tblContacts WHERE FirstName='{0}',Textbox1.Text)[/highlight]

Glad you got it 2 work.

Also, do you know how I can check if textbox1 has part of a item?
something like

[highlight="vb.net"]Dim sql As String = String.Format("SELECT * FROM tblContacts WHERE FirstName='" + TextBox1.PARTIALText + "'") [/highlight]

so if a item was John and textbox1.text was Joh I could get the John result from using the Joh input/query?

Bumping in order to get an awnser if possible.

Btw,if there were like 3 results, is there a way I could set first result "John" to variable var1, second result Patrick to variable var2 and 3rd result carlos to variable var3?

@omanel

Originally Posted by omanel

When you are looping through the reader results, it will return all results it finds.

[highlight="VB.Net"]
'shows a messagebox for each row found
While reader.read
Msgbox(reader("FirstName"))
End While
[/highlight]

You can also save all found entrys in a list or so.

[highlight="VB.Net"]Dim lstNames as new list(of string)

while reader.read
lstNames.add(reader("FirstName"))
end while[/highlight]

Afterwards you can loop through your list.

[highlight="VB.Net"]if lstNames.Count <> 0
for i = 0 to lstNames.Count - 1
msgbox(lstNames(i))
Next
End If[/highlight]

Declare lstNames outside of the sub in order to access it in different subs.

################################################## #######

Originally Posted by omanel

Edit the query.
[highlight="VB.Net"]
Dim sql As String = String.Format("SELECT * FROM tblContacts WHERE FirstName LIKE '%{0}%', Textbox1.Text)
[/highlight]

% is a wildcard. %oh% will find John, Josh, Johan etc. It'll search for results with "oh" inbetween. Change it to {0}% if you just want the part after the search term to be wildcarded.

Originally Posted by Blubb1337

@omanel

When you are looping through the reader results, it will return all results it finds.

[highlight="VB.Net"]
'shows a messagebox for each row found
While reader.read
Msgbox(reader("FirstName"))
End While
[/highlight]

You can also save all found entrys in a list or so.

[highlight="VB.Net"]Dim lstNames as new list(of string)

while reader.read
lstNames.add(reader("FirstName"))
end while[/highlight]

Afterwards you can loop through your list.

[highlight="VB.Net"]if lstNames.Count <> 0
for i = 0 to lstNames.Count - 1
msgbox(lstNames(i))
Next
End If[/highlight]

Declare lstNames outside of the sub in order to access it in different subs.

################################################## #######

Edit the query.
[highlight="VB.Net"]
Dim sql As String = String.Format("SELECT * FROM tblContacts WHERE FirstName='%{0}%', Textbox1.Text)
[/highlight]

% is a wildcard. %oh% will find John, Josh, Johan etc. It'll search for results with "oh" inbetween. Change it to {0}% if you just want the part after the search term to be wildcarded.

Wildcards aren't applied with a literal "=" operator Kevin, use the LIKE operator.

WHERE FirstName LIKE "%someshit%"

Originally Posted by Jason

Wildcards aren't applied with a literal "=" operator Kevin, use the LIKE operator.

WHERE FirstName LIKE "%someshit%"

Wasn't sure about it when writing "=" :P.

http://dev.mysql.com/doc/refman/5.0/...functions.html

Look at the chapter about wildcard omanel.

Thank you both, in the % part :P

But I do not entirely understand how to use the list of strings

If I wanted to display something like:
[highlight="vb.net"]
Dim a As String = "0"

If reader.HasRows Then
While reader.Read
rf.Add(reader("ID"))
a = a + 1
End While
If rf.Count <> 0 Then
For i = 0 To rf.Count - 1
MsgBox("All of them:" + rf(0) + "")
If a > 1 Then
MsgBox("" + a + " results found for "+ Label1.Text + " )
MsgBox("Those results are:" '+rf(0) + to rf(a - 1)+ "" )
else ' only if it is 1, there is no chance it will be 0 due to reader.HasRows
MsgBox("" + a + " result found for "+ Label1.Text + " )
MsgBox("The result is"+ rf(0)+"" )

End If
Next
End If
[/highlight]

As you guys can see I need help in doing something like:
[highlight="vb.net"]MsgBox(""+rf(0)+", "+rf(1)+", "+rf(......)+", " +rf(a - 1)"!")[/highlight]

[highlight="VB.Net"]for i = 0 to rf.count - 1
Msgbox(rf(i)) 'not 0,1 or anything, i!
Next[/highlight]

[highlight="VB.Net"]dim Strb as new system.text.stringbuilder
for i = 0 to rf.count - 1
Strb.AppendLine(rf(i))
next

msgbox(strb.tostring)[/highlight]

There is no need to declare a STRING to count the results.

1. Use integers for numbers(if the numbers isn't too big)

2. Juse use rf.count if you want to know the amount of results.