Access table

[JenniferCheng]

I use SQL server 2000 and build a table TB1. Using remote connect to access this table. When I print this table in ASP page, I can not get the record in TB1. But I can get other record which perhaps has been deleted. Who knows what happens? Thanks.
This is the old application, now I am updating it.

 

[SQLBill]

Use Query Analyzer and run the query from there and see what results you get. You might have to provide us your script and what you expect to get.

-SQLBill

Posting advice: FAQ481-4875

 

[JenniferCheng]

I have two databases, EES and GGO. Both database has table TB1. Every day, some records in table TB1 in EES are transfered to table TB1 in GGO. It can performed this step successfully. But when access table TB1 in GGO, I only can get data of several month ago, not updated record.

Code in ASP file:
Set conDDS = Server.CreateObject("ADODB.Connection")
conDDS.Open Application("conGGO_ConnectionString")
Set cmdDDS = Server.CreateObject("ADODB.Command")
Set cmdDDS.ActiveConnection = conDDS
cmdDDS.CommandType = adCMDStoredProc
cmdDDS.CommandText = "sp_GetAssmntInventory"

Code for procedure "sp_GetAssmntInventory":
SELECT *** FROM TB1

Code in global.asa:
Application("conGGO_ConnectionString") = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=GGODB;Password=123456;Initial Catalog=GGO;Data Source=******;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=*******;"
Application("conGGO_ConnectionTimeout") = 15
Application("conGGO_CommandTimeout") = 120
Application("conGGO_CursorLocation") = 3
Application("conGGO_RuntimeUserName") = "GGODB"
Application("conGGO_RuntimePassword") = "123456"

Thanks.

 

[SQLBill]

What are the three *'s for? It should be:

SELECT * FROM tb1

But that should have errored out and not returned anything.

Run the above code in Query Analyzer and see what it returns. The data may not be getting into the table.

-SQLBill

Posting advice: FAQ481-4875

 

[JenniferCheng]

I used SELECT usrID FROM tb1, etc.
How to use Query Analyzer? Do I need download some software?
I am total new to this part.
Thanks for your kind advice.
Jen

 

[SQLBill]

I guess I should start at the beginning...

You are using a Microsoft SQL Server database aren't you? (I had assumed this since that's what this forum is for).

-SQLBill

Posting advice: FAQ481-4875

 

[JenniferCheng]

Yes, I use SQL server 2000.

 

[SQLBill]

Okay, with SQL Server the Client Tools are loaded by default. Go to Start>Programs>Microsoft SQL Server>Query Analyzer.

Open that up and make your connection. In the menu bar, find the text box to the right of the green arrow. That will a drop-down box listing your databases, select the proper database. Then type in this:

SELECT *
FROM tb1
GO

Then click on the green arrow to run it.

Do you get the results you expect?

-SQLBill

Posting advice: FAQ481-4875

 

[JenniferCheng]

I get right record using this method. That means table is correct.

 

[SQLBill]

That means you are probably pulling from the wrong tb1. Add this to your query:

USE <dbname>

That would make it look like:

USE <dbname>
SELECT * FROM tb1

(Replace the <dbname> with the name of the database you want to pull from).

-SQLBill

Posting advice: FAQ481-4875

 

[JenniferCheng]

Got right record again.

 

[JenniferCheng]

I found out the problem. The application is connected to wrong server. After changing server name, everything is fine. Thanks very much for SQLBill's help.