Can someone help on this:
I am just learning, and I'm connecting to the the northwindcs.mdf tables /
open file is northwindcs.adp .
This is the sample installed using msde, which is supposed to be mini sql
server to learn.
Please don't refer me elsewhere, here is what I'm trying to learn:
If I want to hit a command button and do the following:
1. Find a customerid
2. if found, edit the record, if not found, add a new record.
How would the below code need to look for this, I'm not even sure the
connection string is correct.
I'm getting following error:
run-time error 3219
operation not allowed in this context.
I get the y messagebox, but rst!ContactTitl e = "The Owner" doesn't work.
When I hit the debug, rst.close is highlighted.
Also, how do you handle a no find situation here, I noticed a nomatch
doesn't work.
I am real good at programming, but new to the server thing.
And finally, is there a way to hit this command button, and do all from a
stored procedure instead of code? But in background, no user intevention
once button is hit. Which is better, this code approach or a possible
stored procedure.
Please help, if I get this down, I think I'll have the rest wipped. The
connect string is one big thing confusing me along with handling record once
found / not found. I'm used of DAO. If some one is willing to help, I can
email detailed real code from a database I'm really working on. I need to
learn this first to convert code.
HERE IS SAMPLE CODE
Private Sub Command16_Click ()
Dim cnn As New ADODB.Connectio n
Dim rst As New ADODB.Recordset
Dim mark As Variant
Dim count As Integer
count = 0
cnn.Open "DSN=NorthwindC S; Provider=SQLOLE DB;Data Source=OEMCOMPU TER;Initial
Catalog=Northwi ndCS; uid=sa; pwd=;"
rst.Open "SELECT * FROM Customers", cnn, _
adOpenDynamic, adLockOptimisti c, adCmdText
'rst.Open "SELECT CustomerID FROM Customers", cnn, _
' adOpenDynamic, adLockReadOnly, adCmdText
' The default parameters are sufficient to search forward
' through a Recordset.
rst.Find "CustomerID = 'CHOPS'"
If rst!CustomerID = "CHOPS" Then
MsgBox "y"
rst!ContactTitl e = "The Owner"
Else
MsgBox "n"
End If
' Skip the current record to avoid finding the same row repeatedly.
' The bookmark is redundant because Find searches from the current
' position.
'Do While rst.EOF <> True 'Continue if last find succeeded.
' Debug.Print "Title ID: "; rst!CustomerID
count = count + 1 'Count the last title found.
'mark = rst.Bookmark 'Note current position.
'rst.Find "CustomerID = 'CHOPS'", 1, adSearchForward , mark
'Exit Do
'Loop
rst.Close
cnn.Close
Debug.Print "The number of business titles is " & count
End Sub
I am just learning, and I'm connecting to the the northwindcs.mdf tables /
open file is northwindcs.adp .
This is the sample installed using msde, which is supposed to be mini sql
server to learn.
Please don't refer me elsewhere, here is what I'm trying to learn:
If I want to hit a command button and do the following:
1. Find a customerid
2. if found, edit the record, if not found, add a new record.
How would the below code need to look for this, I'm not even sure the
connection string is correct.
I'm getting following error:
run-time error 3219
operation not allowed in this context.
I get the y messagebox, but rst!ContactTitl e = "The Owner" doesn't work.
When I hit the debug, rst.close is highlighted.
Also, how do you handle a no find situation here, I noticed a nomatch
doesn't work.
I am real good at programming, but new to the server thing.
And finally, is there a way to hit this command button, and do all from a
stored procedure instead of code? But in background, no user intevention
once button is hit. Which is better, this code approach or a possible
stored procedure.
Please help, if I get this down, I think I'll have the rest wipped. The
connect string is one big thing confusing me along with handling record once
found / not found. I'm used of DAO. If some one is willing to help, I can
email detailed real code from a database I'm really working on. I need to
learn this first to convert code.
HERE IS SAMPLE CODE
Private Sub Command16_Click ()
Dim cnn As New ADODB.Connectio n
Dim rst As New ADODB.Recordset
Dim mark As Variant
Dim count As Integer
count = 0
cnn.Open "DSN=NorthwindC S; Provider=SQLOLE DB;Data Source=OEMCOMPU TER;Initial
Catalog=Northwi ndCS; uid=sa; pwd=;"
rst.Open "SELECT * FROM Customers", cnn, _
adOpenDynamic, adLockOptimisti c, adCmdText
'rst.Open "SELECT CustomerID FROM Customers", cnn, _
' adOpenDynamic, adLockReadOnly, adCmdText
' The default parameters are sufficient to search forward
' through a Recordset.
rst.Find "CustomerID = 'CHOPS'"
If rst!CustomerID = "CHOPS" Then
MsgBox "y"
rst!ContactTitl e = "The Owner"
Else
MsgBox "n"
End If
' Skip the current record to avoid finding the same row repeatedly.
' The bookmark is redundant because Find searches from the current
' position.
'Do While rst.EOF <> True 'Continue if last find succeeded.
' Debug.Print "Title ID: "; rst!CustomerID
count = count + 1 'Count the last title found.
'mark = rst.Bookmark 'Note current position.
'rst.Find "CustomerID = 'CHOPS'", 1, adSearchForward , mark
'Exit Do
'Loop
rst.Close
cnn.Close
Debug.Print "The number of business titles is " & count
End Sub
Comment