Hi everyone , I am doing a log in& registration form. And i am using a adodb connection. can someone pls tell me what is wrong with my code on the rst.open part, and what should i replace. Im trying to call the password from ms access and suddenly there is a COMExceptionwasUnhandled appearing and it says .. " [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Username ='regina where Password =asdfghjkl". "
How can i resolve this problem? thank you very much!

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset


    Private Sub LinkLabel2_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles SignLink.LinkClicked
        cnn.Open("DBQ=" & Application.StartupPath & "\InventoryDatabase.accdb;Driver={Microsoft Access Driver (*.mdb, *.accdb)};uid=;pwd=;")

        rst.Open("select * from Register where Username ='" & TextBox1.Text & " where Password ='" & TextBox2.Text & "'", cnn, 1, 3)

        If rst.EOF Then
            MsgBox("Invalid Username or Password!")

            TextBox1.Clear()
            TextBox2.Clear()
            TextBox1.Focus()
        Else
            main.Show()
            Me.Hide()
        End If

        rst.Close()
    End Sub

and also can someo ne please tell me the basic codes for Search, Delete and update in database adodb recordset without using sql. i dont have any knowledge in sql. I only know adodb. pls. anyone out there . i just really need it to build my project. ive been searching tutorials but it includes sql which i dont know :(..

My Code for Add:

rst.Open("select * from Customer", cnn, 1, 3)
        rst.AddNew()
        rst.Fields("CustomerID").Value = IDbox.Text
        rst.Fields("FirstName").Value = Cfname.Text
        rst.Fields("LastName").Value = Clname.Text
        rst.Fields("Address").Value = Cadd.Text
        rst.Fields("ContactNo").Value = Ccont.Text
        rst.Fields("Email").Value = Cemail.Text
        rst.Fields("Status").Value = statcb.Text

        rst.Update()
        rst.Close()

        rst.Open("select * from Customer", cnn, 1, 3)
        Me.ListView1.Items.Clear()
        While Not rst.EOF
            Me.ListView1.BeginUpdate()
            Dim DL As ListViewItem
            DL = Me.ListView1.Items.Add(rst.Fields("CustomerID").Value)
            DL.SubItems.Add(rst.Fields("FirstName").Value)
            DL.SubItems.Add(rst.Fields("LastName").Value)
            DL.SubItems.Add(rst.Fields("Address").Value)
            DL.SubItems.Add(rst.Fields("ContactNo").Value)
            DL.SubItems.Add(rst.Fields("Email").Value)
            DL.SubItems.Add(rst.Fields("Status").Value)
            Me.ListView1.EndUpdate()
            Me.ListView1.Refresh()
            rst.MoveNext()
        End While
        rst.Close()

BUT I DONT KNOW HOW WILL I CODE the SEARCH,UPDATE AND DELETE Button. SOMEBODY HELP ME PLEASE. THE SUBMISSION OF OUR PROJECT IS TOMORROW :(
im sorry if i ask too much , i just dont know someone to ask, just a newbie freshman college. Big Thanks!

For the first question, the format of the query should be

SELECT * FROM Register
 WHERE Username = 'somevalue'
   AND Password = 'somevalue'

and because in some databases, password is a reserved word, you may have to code it as

SELECT * FROM Register
 WHERE Username = 'somevalue'
   AND [Password] = 'somevalue'

Instead of doing

rst.AddNew()

use an insert query which looks like

INSERT INTO tablename (fldname1, fldname2, ...)
VALUES (value1, value2, ...)

Then you do the insert by

cnn.Execute(qry)

Keep in mind that for non-numeric fields you have to put single quotes around the values. To retrieve and display data in a listview you can do

rst.Open("select * from Customer", cnn, 1, 3)
ListView1.Items.Clear

While Not rst.EOF
    ListView1.Items.Add(New ListViewItem({rst("CustomerID").Value,
        rst("FirstName").Value,rst("LastName").Value,rst("Address").Value,
        rst("ContactNo").Value,rst("Email").Value,rst("Status").Value})
    rst.MoveNext
End While

Edited 2 Years Ago by Reverend Jim

This article has been dead for over six months. Start a new discussion instead.