Re: Parameterized query problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

OK finally getting somewhere ! Thanks for your help !

Starting from one server where I could connect to the database but not see
any error info to another where I could see the error info but not connect
to the db it was pretty difficult to try any asp out !

Yes there was a mismatch between the name and mdf name. using the name from
the left most colum I can now connect to it

Now that I can see the error info I've found the problem.

I cannot pass my array to the execute statement I need to use the array
function to create the array with the appropriate number of elements

ie if I have say DIM aParamValue(3) I actaully end up with 4 elements which
will always be one too many elements

OK so I can just dim or re dim with one less element

I am aware there is an array() function that returns an array made of the
strings passed to it

although these can be variable or literals the actual bit between the
brackets is 'hard coded' so to speak

so to write a generic function that I can pass a query string to with ? and
the values in an array i've got to copy them into a new aray that is excatly
the correct size

or I guess instead of buildin the array like

aSQLParamValue(0) ="D%"
aSQLParamValue(1) ="R%"
etc

Just pass Array("D%","R%") into my function and never actually declare and
array to hold my params because it's almost always going to be the wrong
size most of the time if you sort of see what I mean

Just getting used to the VBScript quirks but think i'm finally starting to
get there

On the server 500 errors on the sever I do not have control over is there
anything else that should be set other than in IIS send errors to client and
in the browser untick show friendly errors ?

Thanks for all your help


"Bob Barrows" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:eTbTSpccKHA.1028@xxxxxxxxxxxxxxxxxxxxxxx
So that means there is an issue with the database itself.
What can you see when you run this:
select * from master.dbo.sysdatabases

Make sure mydb is in the results. If not, look for the row containing
the mdf file and look at the name column.

MiniEggs wrote:
use mydb;
exec sp_helpuser;

this gives me the ....

Could not locate entry in sysdatabases for database
'mydb'. No entry found with that name. Make sure that the name is
entered correctly.

However if I chosse mydb from the drop down then exec sp_helpuser
runs and shows aspuser in the results

i've tried the sp_changed_users_login and alter user both of which
worked. the grant execute did not though

tried sp_helpuser again and got the same result ie ok if i choose the
db from the drop down otherwise errors on use statement

Thanks


"Bob Barrows" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:e3wsuwbcKHA.1028@xxxxxxxxxxxxxxxxxxxxxxx
MiniEggs wrote:
OK Thanks Bob

Lets go at it from this angle to start with

I used the windows local logon to load SSMS and then ran this

EXECUTE AS login='aspuser'
select * from mydb.dbo.tablename
REVERT

which gives me this error .... Invalid object name
'mydb.dbo.tablename'.
If I change it to this

execute as login='aspuser'
use mydb
select * from dbo.tablename
revert

I Get error .... Could not locate entry in sysdatabases for
database 'mydb'. No entry found with that name. Make sure that the
name is entered correctly.

Hopefully this now gives us a clue ?


It certainly appears to be a msspelling somewhere, probably in the
name of the user that was created in mydb.

Can you run this statement in SSMS and verify that "aspuser" appears
in the results?

use mydb;
exec sp_helpuser;

If not, run this statement:

use mydb;
exec sp_adduser 'aspuser';

If the user does appear in the results, it might be orphaned (as the
result of a database restore or detach/reattach operation). You can
fix it using:
EXEC sp_change_users_login 'Update_One', 'aspuser', 'aspuser';



And make sure 'dbo' is the default schema for this user by running:

use mydb;
ALTER USER aspuser WITH DEFAULT_SCHEMA = dbo;

Rather than making this account db_owner, I prefer to grant it
rights to all objects in the dbo schema:

use mydb;
GRANT EXECUTE,SELECT,UPDATE,DELETE, INSERT ON dbo TO aspuser;

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
HTH,
Bob Barrows




.

Relevant Pages

  • Re: T-SQL CLOSE Connection to DB
    ... The 'MyDb' is still under the status RUNNABLE for the command 'SELECT ... INTO' under the ProgamName 'Microsoft SQL Server Management Studio - ... Management Studio uses the ALTER DATABASE command I ... paste the contents of the clipboard in a query window or in a ...
    (comp.databases.ms-sqlserver)
  • RE: SqlExpress & C# problem in an ASP.NET web site
    ... ALTER DATABASE [MyDB] SET ANSI_NULLS OFF ... ALTER DATABASE SET ANSI_PADDING OFF ... > public static SqlConnection GetConnection(string filename) ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Newbie question - public function
    ... I have a VB5 front end for an Access 97 database. ... Public Function openmydb() ... Dim mydb As Database ...
    (microsoft.public.vb.database)
  • RE: [Info-ingres] copying a database
    ... Start a telnet or dos command window ... Createdb mydb ... I have inherited a old Ingres database that just sits and happily chugs ...
    (comp.databases.ingres)