adodb syntax

[buddyel]

Below is some code I am working on to check an Access database for the presence of a part number, then prompting the user if they want to overwrite the record..

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ExistingRecord As Boolean
Dim prmptuser As Long

' Open an ADO Connection
Dim adoCn As New ADODB.Connection()
adoCn.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\Development\Databases\STNDCARDS.mdb&quot
' Open an ADO Recordset
Dim adoRs As New ADODB.Recordset()
adoRs.Open("SELECT * FROM tblTemp WHERE CATALOG_NO = & ' " S4-7445-AJ" ', adoCn)
With adoRs
If .RecordCount > 0 Then
ExistingRecord = True
Else
ExistingRecord = False
End If
If ExistingRecord = True Then
prmptuser = MsgBox("Record already exists&quot
Else
MsgBox("Not on File&quot
End If
End With
adoRs.Close()
adoCn.Close()
End Sub

As I have it now, i have the select statement finding a match for a specific number and the program tells me it is not on file. I copied the part number directly from the database, so i am baffled at that. I would like the WHERE criteria to be = textbox1.text. What do I have wrong and what is the proper syntax for what i need? Thanks in advance!!

 

[Zarcom]

You have your & in the wrong place your's is like so:
adoRs.Open("SELECT * FROM tblTemp WHERE CATALOG_NO = & ' " S4-7445-AJ" ', adoCn)

It should look like this
adoRs.Open("SELECT * FROM tblTemp WHERE CATALOG_NO = 'S4-7445-AJ'", adoCn)

in case of the textbox then it would be more like this.

adoRs.Open("SELECT * FROM tblTemp WHERE CATALOG_NO = '" & texbox1.text & "'", adoCn)

May I ask why you are using the old ADO objects. The new ones are tweaked for speed and reliability. Just wondering since the recordset object doesn't exist anymore.

That'l do donkey, that'l do
Mark

 

[buddyel]

I am new to VB.NET and programming in general. If you see my other post (OleDbConnection syntax) you will see I am having trouble with both methods. I got the program to work this way but now I am having another problem...

If the user click YES to update the record, i have the following code... for example..
.Fields("QTY&quot = "6"
But VB tells me > Propery 'Item' is 'Read Only'

What does this mean? There is no security on the database.