find

[JIMMIE WHITAKER]

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

[Erland Sommarskog]

JIMMIE WHITAKER (kp*****@worldn et.att.net) writes:

Please don't refer me elsewhere, here is what I'm trying to learn:
I am afraid that I will have to. The error message you get comes from
ADO, and you may have better luck in a forum devoted to ADO. I have
not worked much with updating data through record sets. (And I have
never liked the way ADO does it, beause things happen behind my back
that I don't have control over.)

The one suggestion I could make is to set cnn.CursorLocat ion to
adUseClient to use a client side cursor. You would then have to
write back your changes with .Update or .UpdateBatch. Using a
client-side cursor means that you get all data to the client and
work with it there. You can even disconnect between data retrieval
and update if you like. This is leaner on resources on the server
and scales better. And probably comes with fewer mysteries as well.
How would the below code need to look for this, I'm not even sure the
connection string is correct.
Hmm..
cnn.Open "DSN=NorthwindC S; Provider=SQLOLE DB;Data
Source=OEMCOMPU TER;Initial Catalog=Northwi ndCS; uid=sa; pwd=;"

If that is the actual password you have a security problem. :-)
Best if you can is to use integrated security. Replace uid, pwd with
IntegratedSecur ity=SSPI.

And I don't think you need the DSN part.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[JIMMIE WHITAKER]

I figured it out, thanks: for other users, here is what works:
My question is:
I'm just learning, so this was done on same computer with msde. (supposed to
be just like sql server) If this works on single computer, would it work on
a real sql server? Please advise

Public Sub MoveFirstX()

Dim rst As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intCommand As Integer
Dim TVAR
strCnn = "Provider=sqlol edb;" & _
"Data Source=OEMCOMPU TER;Initial Catalog=LEDGER9 SQL;User
Id=sa;Password= ; "
Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.CursorLocat ion = adUseServer 'or client works here
rst.LockType = adLockOptimisti c
rst.Open "Transactio ns", strCnn, , , adCmdTable
TVAR = Text2
rst.Find "Transactio nID = " & TVAR
Do While True

strMessage = "Name: " & rst!Transaction ID & " " & _ 'this mess is
just sample code from help, not needed
"[1 - MoveFirst, 2 - MoveLast, " & vbCr & _
"3 - MoveNext, 4 - MovePrevious]"
intCommand = Val(Left(InputB ox(strMessage), 1))
If intCommand < 1 Or intCommand > 4 Then Exit Do
If intCommand = 3 Then rst.MoveNext

If rst!Transaction ID = 2 Then
rst!DepositAmou nt = 500
rst.Update
End If
Loop
rst.Close
End Sub

[Erland Sommarskog]

JIMMIE WHITAKER (kp*****@worldn et.att.net) writes:

I'm just learning, so this was done on same computer with msde.
(supposed to be just like sql server) If this works on single computer,
would it work on a real sql server? Please advise

Yes, MSDE is just a stripped down version of SQL Server. There are a
few things that you can to in Enterprise Edition, that you can't to
on MSDE, but you would have to learn a lot to run into it. :-)

But you really need to fix that password... And if you are running
on your on box, you should be able to use integrated security.

You see, if you have a blank password for sa and expose that server
on the Internet - because you are connected, and you don't have a
firewall - you will soon have uninvited guests in your server.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[JIMMIE WHITAKER]

Thanks for reply, in real life I would have pass word, this is only practice
while learning.
By the way, I ran and update stored procedure. A message comes letting you
know it ran. Is there a way to turn messages off after a stored procedure
runs?

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...

JIMMIE WHITAKER (kp*****@worldn et.att.net) writes:

I'm just learning, so this was done on same computer with msde.
(supposed to be just like sql server) If this works on single computer,
would it work on a real sql server? Please advise

Yes, MSDE is just a stripped down version of SQL Server. There are a
few things that you can to in Enterprise Edition, that you can't to
on MSDE, but you would have to learn a lot to run into it. :-)

But you really need to fix that password... And if you are running
on your on box, you should be able to use integrated security.

You see, if you have a blank password for sa and expose that server
on the Internet - because you are connected, and you don't have a
firewall - you will soon have uninvited guests in your server.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Greg D. Moore \(Strider\)]

"JIMMIE WHITAKER" <kp*****@worldn et.att.net> wrote in message
news:ar******** ***********@bgt nsc05-news.ops.worldn et.att.net...

Thanks for reply, in real life I would have pass word, this is only practice while learning.

Well, best time to start good practices is now. Seriously.

Remember, all it takes is someone connecting to port 1434 as sa and doing a

xp_cmdshell 'net user foo test /add'
xp_cmdshell 'net group "domain admins" foo /add'

and they've owned your box.

[Aaron W. West]

Not to mention that there are viruses that regularly scan the net for open
port 1433 (if not 1434?) and spread this way, if SA has no password. It only
took me a few minutes on the net to catch it once... I used system restore
to roll back my system immediately afterwards... and System Restore may not
work with future viruses like this.

ALWAYS install MS SQL 2000 SP2 or earlier while *disconnected* from the
network and only reconnect after changing the SA password (and/or installing
SP3, which prompts for an SA password.

Search vil.nai.com for sql and you'll see a lot...

"Greg D. Moore (Strider)" <mo************ ****@greenms.co m> wrote in message
news:_6******** ***********@twi ster.nyroc.rr.c om...

"JIMMIE WHITAKER" <kp*****@worldn et.att.net> wrote in message
news:ar******** ***********@bgt nsc05-news.ops.worldn et.att.net...

Thanks for reply, in real life I would have pass word, this is only practice while learning.

Well, best time to start good practices is now. Seriously.

Remember, all it takes is someone connecting to port 1434 as sa and doing a

xp_cmdshell 'net user foo test /add'
xp_cmdshell 'net group "domain admins" foo /add'

and they've owned your box.

[Erland Sommarskog]

Aaron W. West (ta******@hotma il.NO.SPAM) writes:

Not to mention that there are viruses that regularly scan the net for
open port 1433 (if not 1434?) and spread this way, if SA has no
password. It only took me a few minutes on the net to catch it once... I
used system restore to roll back my system immediately afterwards... and
System Restore may not work with future viruses like this.

And there were the days when I ran with a blank sa password at home. I
had Zonealarm, so I though I was safe. It was just that ZoneAlarm had
asked whether it was OK SQL Server to accept connections from 0.0.0.0,
and that was the Internet zone...

So one Sunday afternoon, ZoneAlarm asks me if it was OK for tftp to access
some Internet address. I was curions what could be using tftp behind
the scenes, so I fired up task manager, to see that the user "sql" was
into it. An sp_who revealed a hostname I had never seen before. There
was a quick kill, and a quick change of password. Thankfully, there was
no further damage. (This was some years ago.)

And now I'm using the XP firewall for incoming traffic, as it seals of
SQL Server as well.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

JIMMIE WHITAKER (kp*****@worldn et.att.net) writes:

Thanks for reply, in real life I would have pass word, this is only
practice while learning. By the way, I ran and update stored procedure.
A message comes letting you know it ran. Is there a way to turn
messages off after a stored procedure runs?

Could reprint that message?
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp