Programming Forums - searching an access database?

Programming Forums (http://www.programmingforums.org/forumindex.php)

- Visual Basic .NET (http://www.programmingforums.org/forum19.html)

- - searching an access database? (http://www.programmingforums.org/showthread.php?t=7059)

searching an access database?

I making a program that is used to signup students willing to join a university computer club... i'm using an access database and i've already connected the database to the program and i'm adding, deleting and updating students .. but my first problem is that i haven't figured out away to search the database... so if there is anybody that can help me out here ...
Also my other problem is that i'm using this project to help me learn programming and vb.net better and i have a couple of books to help out so i connected the database using the wizard and i used some simple coding as well for the inserting and deleting....etc as one of my books showed . my other book showed another way that i liked better it used sql for the modification of the database but it didn't workout ...the problem is that i don't know where the error is .. but i feel that it's somewhere in the connecting or making tha adapter or dataset?? so what i need is that if some could help me out with the steps of doing so...

an example of the code i'm using for inserting to the DB:

Private Sub add()

Try

Dim newRow As DataRow = Dsqucc1.mainTable.NewRow

newRow("firstName") = txtFirstName.Text

newRow("lastName") = txtLastName.Text

newRow("studentNo") = txtStudentNo.Text

newRow("email") = txtEmail.Text

newRow("pass") = txtPassword.Text

newRow("experiences") = rtxtExperiencies.Text

newRow("cellPhone") = txtCellPhone.Text

newRow("pcHours") = cboPcHours.Text

newRow("netHours") = cboNetHours.Text

newRow("comments") = rtxtComments.Text

newRow("ranking") = cboRanking.Text

newRow("memberLevel") = cboMemberLevel.Text

Dsqucc1.mainTable.Rows.Add(newRow)



Catch

MessageBox.Show("error", "error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RightAlign)

End Try

End Sub

and then to update the database i use :

dbQuccAdapter1.Update(Dsqucc1, "mainTable")

So whats the SQL way?

thanks alot.

There probably is the equivalent of a search function in the VBA COM (filter on the record iterator I believe) but you're right, the way to do this is via SQL, especially for more complex searching tasks. Aside from the fact that Access SQL is slightly nonstandard in the quotes it uses, an ordinary ADO connection should do it: could you post your SQL based version, and then I predict roughly 20 people here will tell you to switch to MySQL. :)

thanks roy, this is an example of the SQL version i tried to use :

Try



         ' ensure first and last name input

         If (txtLastName.Text <> "" AndAlso txtFirstName.Text <> "") Then



            ' create the SQL query to insert a row

            dbQuccAdapter1.InsertCommand.CommandText = _

               "INSERT INTO addresses(firstName, " & _

               "lastName) " & _

               "VALUES('" & txtFirstName.Text & "' , " & _

               "'" & txtLastName.Text & "' )"



            ' send query

            dbQuccAdapter1.InsertCommand. _

               ExecuteNonQuery()



         Catch exception As System.Data.OleDb.OleDbException

              Console.WriteLine(exception.StackTrace)

              txtStatus.Text &= exception.ToString



       End Try

i get an error... usually on executing :
ExecuteNonQuery()

don't know what the problem is ..but the book i used to do this had a different way connecting to the database and making the adapter and dataset i tried it's way but it didn't work but on the example from the books cd there was an oledbcommand tool for the insert, select, delete, and update queries????

About the MySQl isn't that a database that works on the internet for web projects? because mine is a windows based project..

thanks again

to do a search, you could use a DataReader.

 



'declare stuff 

Dim DataReader As OleDbDataReader

Dim Command As OleDbCommand

Dim Connection As OleDbConnection 

Dim CustomerIdVar as string = "1234"



'connection string

Connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Test.mdb") 



'set up command object to do a select statement taking your var

Command = New OleDbCommand("Select * from Table1 Where CustomerID=" & CustomerIdVar)

Command.Connection = Connection

Command.Connection.Open() 



'Execute the query

DataReader = Command.ExecuteReader() 



If DataReader.HasRows()

   'get the first row returned from the reader and display column in message box, column name in DB is ID

   DataReader.Read()

   msgbox(DataReader.Item("ID").ToString())

else

   'display message

   msgbox("That customer ID was not found")

endif

ok, there are probably errors in my code, I typed it up right here in the code block, but this is just to give you an idea on one way you could set up a search.

thanks alot melbolt i'm going to give it a try!

How did you come to choose your user name? Just curious.

i own a programming, website design, and hosting group so my username is our website (of cource we just began so there isn't anything on it now were still working on it)...

Are we your "experts"?? Again, just curious.

what do u mean exactly?? didn't understand???

Quote:

Originally Posted by DaWei

Are we your "experts"?? Again, just curious.

LMAO.

Try not to pick on the resident pond life. :D

I believe an OleDbCommand will let you run an INSERT it will just return no rows (null result).