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 - 2006-09-14 : 09:54:28
|
| Ken writes "Difficult to explain as I'm not sure what the cause of problem is, it may be nulls or text fields or just simply too many fields being selected. Basically, I have a select statement in ASP that reads data from a view but the fields are only populated up to a certain point, all fields after that point are blank. Fields after that point, I have applied an isnull function to give them a value if they are blank and they still come through as blank.I believe the SQL is populating the fields OK as I can affect the cut off point by moving the fields in the select statement around, it would appear to be going wrong in reading the recordset. Some of the fields are defined as text and they seems very sensitive in their placement, although I have got values from them sometimes. Equally, if text is not returning a value, moving that to the front of the SQL field list wipes out all the fields after even though these were being populated before.Originally I used select * and got nothing back anywhere but when I listed all the columns it seemed to work until I check another row in the database.Below is the code I used, work_desc and work_notes are the text fields: DSN_Name = Application("bkh_online_ConnectionString") Set Connection = Server.CreateObject("ADODB.Connection") Connection.Open DSN_Name SQL = "select lead_type_code, " & _ " isnull(work_desc,' ') as work_desc, " & _ " lead_type_short_name, " & _ " lead_type_desc, " & _ " order_type_code, " & _ " priority_code, " & _ " priority_title, " & _ " priority_short_name, " & _ " isnull(new_user_name,' ') as new_user_name, " & _ " isnull(new_user_handle,' ') as new_user_handle, " & _ " isnull(new_email_address,' ') as new_email_address, " & _ " isnull(addr_line1,' ') as addr_line1, " & _ " isnull(addr_line2,' ') as addr_line2, " & _ " isnull(addr_town,' ') as addr_town, " & _ " isnull(county_name,' ') as county_name, " & _ " isnull(addr_postcode,' ') as addr_postcode, " & _ " isnull(tel_mobile,' ') as tel_mobile, " & _ " isnull(tel_home,' ') as tel_home, " & _ " isnull(tel_time,' ') as tel_time, " & _ " isnull(tel_work,' ') as tel_work, " & _ " isnull(tel_fax,' ') as tel_fax, " & _ " isnull(work_notes, ' ') as work_notes, " & _ " status_code, " & _ " county_name, " & _ " user_id, " & _ " contact_id, " & _ " title_code, " & _ " email_address, " & _ " display_name, " & _ " user_name, " & _ " user_handle, " & _ " new_user_id, " & _ " caddr_id " & _ " from v_payt_lead " & _ " where lead_id = " & Vlead_id Set RS = Connection.Execute (SQL) if not RS.EOF then Vlead_type_code = trim(RS("lead_type_code")) Vlead_type_short_name = trim(RS("lead_type_short_name")) Vlead_type_desc = trim(RS("lead_type_desc")) Vorder_type_code = trim(RS("order_type_code")) Vtitle_code = trim(RS("title_code")) Vemail_address = trim(RS("email_address")) Vdisplay_name = trim(RS("display_name")) Vuser_name = trim(RS("user_name")) Vuser_handle = trim(RS("user_handle")) Vnew_user_name = trim(RS("new_user_name")) Vnew_user_handle = trim(RS("new_user_handle")) Vnew_email_address = trim(RS("new_email_address")) Vaddr_line1 = trim(RS("addr_line1")) Vaddr_line2 = trim(RS("addr_line2")) Vaddr_town = trim(RS("addr_town")) Vcounty_name = trim(RS( |
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2006-09-14 : 16:09:18
|
| Ken,Did you try running your query in Query Analyzer? Did it work there correctly (did you get the column values you expected)?If not, post the DML to create your table, some sample data to populate it with, and the expected output. |
 |
|
|
|
|
|
|
|
Field data being lost