473,388 Members | 993 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

find

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!ContactTitle = "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.Connection
Dim rst As New ADODB.Recordset
Dim mark As Variant
Dim count As Integer

count = 0
cnn.Open "DSN=NorthwindCS; Provider=SQLOLEDB;Data Source=OEMCOMPUTER;Initial
Catalog=NorthwindCS; uid=sa; pwd=;"
rst.Open "SELECT * FROM Customers", cnn, _
adOpenDynamic, adLockOptimistic, 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!ContactTitle = "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

Jul 20 '05 #1
8 2546
JIMMIE WHITAKER (kp*****@worldnet.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.CursorLocation 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=NorthwindCS; Provider=SQLOLEDB;Data
Source=OEMCOMPUTER;Initial Catalog=NorthwindCS; 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
IntegratedSecurity=SSPI.

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

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
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=sqloledb;" & _
"Data Source=OEMCOMPUTER;Initial Catalog=LEDGER9SQL;User
Id=sa;Password=; "
Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.CursorLocation = adUseServer 'or client works here
rst.LockType = adLockOptimistic
rst.Open "Transactions", strCnn, , , adCmdTable
TVAR = Text2
rst.Find "TransactionID = " & TVAR
Do While True

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

If rst!TransactionID = 2 Then
rst!DepositAmount = 500
rst.Update
End If
Loop
rst.Close
End Sub

Jul 20 '05 #3
JIMMIE WHITAKER (kp*****@worldnet.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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
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****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
JIMMIE WHITAKER (kp*****@worldnet.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****@sommarskog.se

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

Jul 20 '05 #5

"JIMMIE WHITAKER" <kp*****@worldnet.att.net> wrote in message
news:ar*******************@bgtnsc05-news.ops.worldnet.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.
Jul 20 '05 #6
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.com> wrote in message
news:_6*******************@twister.nyroc.rr.com...

"JIMMIE WHITAKER" <kp*****@worldnet.att.net> wrote in message
news:ar*******************@bgtnsc05-news.ops.worldnet.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.

Jul 20 '05 #7
Aaron W. West (ta******@hotmail.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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #8
JIMMIE WHITAKER (kp*****@worldnet.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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Dan Jones | last post by:
I'm writing a script to process a directory tree of images.  In each directory, I need to process each image and generate an HTML file listing all of the images and links to the subdirectories....
7
by: zhou | last post by:
Hi there, We have a compiler specific issue which requires us to force template instantiation. This works fine. The problem comes when I try using std:find() on vector. Since vector has no member...
0
by: amit | last post by:
I want to find out that if there is a mechanism to find a text inside a C# file and replace it with another string. I am using DTE to do it, the find proerty does it, the results are getting...
0
by: AMIT PUROHIT | last post by:
hi, this is a qry which I m stuck up with I want to find out that if there is a mechanism to find a text inside a C# file and replace it with another string. I am using DTE(EnvDTE) to do it,...
0
by: amit | last post by:
hi I have created a tool which does a find and replace thru DTE, now after it is done, it opens up a window, "FIND REACHED THE STARTING POINT OF SEARCH" I want to disbale this window...
5
by: Mike Labosh | last post by:
In VB 6, the Form_QueryUnload event had an UnloadMode parameter that let me find out *why* a form is unloading, and then conditionally cancel the event. In VB.NET, the Closing event passes a...
3
by: DJTN | last post by:
I'm getting the following error when I try to compile my setup project in VS 2002. I have re-installed the .net framework 1.1 and it didnt solve the problem. WARNING: Unable to find dependency...
3
by: David T. Ashley | last post by:
Hi, Red Hat Enterprise Linux 4.X. I'm writing command-line PHP scripts for the first time. I get the messages below. What do they mean? Are these operating system library modules, or...
0
by: Derek | last post by:
I am creating an intranet using Visual Web Developer Express Edition. Everything has been working OK until yesterday when I started getting 62 messages all beginning "Could not find schema...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.