Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-05 : 08:03:08
|
| Chris Kukowski writes "I have this stored procedure that looks up a region based on a 3 char zip code prefix. I'm using record count to test for if records exist but even when there isn't a zip in the table it still returns -1 (true) and a record set with 2 blank cells so I get an error. My stored procedure lets me add data. Is that supposed to happen? I's just a select statement with a parameter for the ZIP? Does it need to be made read-only somehow.ChrisPrivate Sub cmdLookup_Click() Dim dcnDatabase As New ADODB.Connection Dim cmdSP As New ADODB.Command Dim parZIP As New ADODB.Parameter Dim ZIPList As New ADODB.Recordset parZIP.Value = 0 'Open connection to DB. dcnDatabase.Open ("DSN=institute") Set cmdSP.ActiveConnection = dcnDatabase cmdSP.CommandText = "dbo.sp_RegionalOfficeLookup" cmdSP.CommandType = adCmdStoredProc Set parZIP = cmdSP.CreateParameter("Zip") parZIP.Type = adChar parZIP.Direction = adParamInput parZIP.Size = 10 txtArea.SetFocus txtArea.Text = "" txtRegionalOffice.SetFocus txtRegionalOffice.Text = "" txtZipCode.SetFocus parZIP.Value = txtZipCode.Text cmdSP.Parameters.Append parZIP 'Call Stored Procedure and give results to the MailList RecordSet. Set ZIPList = cmdSP.Execute 'Check to make sure there aren't 0 records. If ZIPList.RecordCount = 0 Then MsgBox "Not In List.", vbQuestion, "You need to Move." Exit Sub End If txtArea.SetFocus txtArea.Text = ZIPList("Area") txtRegionalOffice.SetFocus txtRegionalOffice.Text = ZIPList("RegionalOffice") txtZipCode.SetFocus ZIPList.Close Set ZIPList = Nothing 'parZIP.Close 'Set parZIP = Nothing dcnDatabase.Close Set dcnDatabase = NothingEnd Sub" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-04-05 : 08:30:05
|
| no....it's to do with the type of recordset you are opening....recordcount = -1 will appear if cursor-type is of a certain type regardless of whether or not records are read.....I think...adOpenStatic....adOpenForwardOnly (is faster and) will return a recordcount, but it does have certain operating differences....that may be key to you....read BOL.checking for eof might be better for you in this situation.... |
 |
|
|
|
|
|
Why isn't my RecordCount 0?