SQL Server and asp

[bongmarley]

I am connecting to a database using asp however when i try to write the records to the screen some records get written some do not and there seems to be no real pattern to this.Anyone have suggestions.

 

[Programmingjunkie]

This sounds more like an ASP problem than SQL...

Are you returning data via recordsets?
What type of connection are you using?

 

[SemperFiDownUnda]

without seeing code I can't really help you.
I'm assuming you are using ADO. The recordset won't loose any data so if you have a SELECT statement that runs in Query Analyser fine and the recordset is populated by the same statement (or stored procedure) and it is not all coming out I'd say there is a logic error in your ASP code.

 

[JamesLean]

This question is already being dealt with in the ASP forum.

See thread333-521479 --James

 

[bongmarley]

This is an asp problem so I will deal with it in that forum.
Thanks

 

[bongmarley]

Here is some code

set passwords = Con.execute("select * from pass where email ='blah' &quot



response.write(&quot;ID = &quot;&passwords(&quot;ID&quot&&quot;<BR>&quot
response.write(&quot;Title = &quot;&passwords(&quot;Title&quot&&quot;<BR>&quot
response.write(&quot;PrintShop = &quot;&passwords(&quot;Printshop&quot&&quot;<BR>&quot
response.write(&quot;what<br>&quot

response.write(&quot;ID = &quot;&passwords(&quot;ID&quot&&quot;<BR>&quot
response.write(&quot;Title= &quot;&passwords(&quot;Title&quot&&quot;<BR>&quot
response.write(&quot;PrintShop = &quot;&passwords(&quot;Printshop&quot&&quot;<BR>&quot

con.close

This is what it prints to the screen
Here you go:
ID = 376
Title = Manager
PrintShop = False
what
ID = 376
Title=
PrintShop = False


It wont print out the title a second time and if I move the order around and put title before Id it wont show the title either time. Why does it do this.

 

[JamesLean]

This really is an ASP issue! Post this detail in your existing question there. --James

 

[bongmarley]

I will the only thing I see is that this error maybe casued based on the datatypoes in SQL Server database.I imported my databases from access into SQL Server. What datatype in SQL Server is best to represent memo in access.

 

[JamesLean]

The equivalent of the memo type is text in SQL Server. However, remember that a varchar column can hold up to 8000 chars (compared to 255 for an Access text column) - only use text in SQL if you really need to store more than 8000 chars.

You are right though, there are certain oddities when dealing with memo/text fields through a recordset. Make sure the memo field appears last in the select list.

Also you can't refer to it more than once directly from the recordset - you must assign it to a local variable to do this. --James

 

[bongmarley]

Thank you this is exactly what I needed to know. It wasn't the asp it was this.What happens if you have more then one memo field in a table, or does this only apply for each seperate memo field.

Are there any other differences between Access and SQL Server I should know about when dealing with datatypes. Specifically between bit and yes/no fields.
Currently we are switching from a Access databse to SQL Server. Do you know of any sites that would be good for dealing with the differences in datatypes.

 

[SQLSister]

Bit and yes/no fields work pretty much the same way except bit uses 0 and 1 and yes/no uses 0 and -1. If you link the tables to Access and use access queries and forms, it will translate this. ASP probably doesn't so you will need to look at your code and change all the -1 refernces to 1.

DAtes are handles differntly as well. Access queries use #01/01/2003# and SQL Server queries use '01/01/2003'. The date formatting is different too. REad the FAQ on date handling for tips on how to deal with dates in SQL Server.

 

[bongmarley]

Thanks SQLSister

 

[tlbroadbent]

I highly recommend the following link. There are some excellent resources for integrating SQL Server and Access as well as upsizing from Access to SQL Server.

http://msdn.microsoft.com/library/en-us/dnacc97/html/acsqlres.asp

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm