Problem returning SQL data

I'm having trouble with the display of data when I test my asp page. I basically retrieves or pulls data from different tables in a database. Based on an ID number (which I can change manually by typing in a value and appending it to the URL), it shows GuestName, GuestDescription and URL as text link. I can get it to do that much. But for some reason it's not showing the URL associated with the Guest Name. I dont think it's the display section of my code. I think it's the Select statement.

Here is what the output looks like in my Firefox and IE browsers when I test. Some data has been changed to protect it's integrity:

(Name in bold) Mark Crisp's author of The Bush Lexicon. His new book...

Related Links:
John Doe interviewed by Susan Smith, 123 News

What it should say in the link is this: Mark Crisp's blog (as text link)

When I test for different data I just change the end of the URL like this:
defaultprogram4.asp?ID=1234 (this number represents an ID column in a table)

Code Snippet

<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:\webservice\Company\Company.UDL"
set rs = Server.CreateObject("ADODB.Recordset")

id=request.querystring("id") 'If this line is commented out the page will be blank.
'However you can still append a record number to the end of the URL and display that one.

IF id <> "" then id=id else id="1234" end if 'This line shows the default record of 1234. If this line is commented out the page will ONLY show the default record but will NOT allow you
'to append a different number

strSQL = "SELECT *, T_Programs.ID AS Expr1, T_ProgramGuests.ProgramID AS Expr2, T_ProgramGuests.GuestName AS Expr3, T_ProgramGuests.GuestDescription AS Expr4, T_ProgramLinks.URL AS Expr5, T_ProgramLinks.Description AS Expr6 FROM T_ProgramGuests CROSS JOIN T_Programs CROSS JOIN T_ProgramLinks WHERE (T_ProgramGuests.ProgramID = '" & id & "')"

rs.Open strSQL,con 'open a connection to the database

%>

<br />
<strong><% Response.Write RS ("GuestName") %> </strong> <% Response.Write RS("GuestDescription") %><br /><br />
Related Links:<br />
<li class='basic'><A HREF="<%= RS("URL") %>"><%= RS("Description") %></A></li>

<!-- END OF THE GUESTS AND LINKS SECTION -->

<%
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>

Bottom line here is this: Why would I be seeing one name but a link not associated with that name? It's as though it's reading a name from the ProgramGuests table and a URL from the
ProgramLinks table (Except that: it shows a completely different unrelated URL to that name).

What am I missing?Is you asp code showing anything ? As you are referencing the column e.g. GuestName, it should not display anything for this attribute as it does not exists in the recordset. The attribute is renamed (in your query to Expr3, so you either will have to delete the AS Expr3 part or rename the reference of the recordset, e.g. rs("Expr3").

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem inserting data into SQL Server 2005 DB from WebForm controls

Hello all,

I am having problems running a stored proc from an aspx.cs file. Basically I want to extract data from webForm controls and create a new record in a DB table. I have watched the "Getting Started" videos on thi site, and the only difference between my code and the code of the demonstrator is the connection string.

My conn string:-dataSrc.ConnectionString =ConfigurationManager.ConnectionStrings["ProjectTblConnString"].ConnectionString;

Demonstrator conn string:-dataSrc.ConnectionString =ConfigurationManager.ConnectionStrings("ProjectTblConnString");

When I debug with the string as shown by the demonstrator the error list reports that ConnectionStrings is a property and I am trying to use it as a mothod,
 which is fair enough, but I am just confused as how it worked for the demonstrator and not myself. When I debug with the code I used above I get no error, but nothing is inserted in the DB.

Also, it looks as if the only class in my aspx.cs file that is actually being executed is PageLoad() - the remainder of the code seems to be ignored. Below is the entired aspx.cs file:-

 
1using System;2using System.Data;3using System.Configuration;4using System.Collections;5using System.Web;6using System.Web.Security;7using System.Web.UI;8using System.Web.UI.WebControls;9using System.Web.UI.WebControls.WebParts;10using System.Web.UI.HtmlControls;1112public partialclass CreateProject : System.Web.UI.Page13{14protected void Page_Load(object sender, EventArgs e)15 {1617if (User.Identity.IsAuthenticated ==false)18 {19 Server.Transfer("Default.aspx");20 }2122if (chkbox_startDate.Checked ==true)23 {24 txtbox_startDate.ReadOnly =true;25 txtbox_startDate.Text = (System.DateTime.Now.ToString());26 }2728 }2930protected void btn_create_Click(object sender, EventArgs e)31 {32//connection string for connecting to SQL Server DB33 SqlDataSource dataSrc =new SqlDataSource();34 dataSrc.ConnectionString = ConfigurationManager.ConnectionStrings["ProjectTblConnString"].ConnectionString;353637//insert data in table using the CreateNewProject stored proc in the DB38 dataSrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;39 dataSrc.InsertCommand ="CreateNewProject";40 dataSrc.InsertParameters.Add("Project_Title", txtbox_title.ToString() );41 dataSrc.InsertParameters.Add("Description", txtbox_desc.ToString() );42 dataSrc.InsertParameters.Add("Start_Date", txtbox_startDate.ToString() );43 dataSrc.InsertParameters.Add("Primary_Dev_Lang", list_devLang.ToString() );44 dataSrc.InsertParameters.Add("Dev_Environment", list_devEnv.ToString() );45 dataSrc.InsertParameters.Add("No_Junior_Devs", txtbox_juniorDevs.ToString() );46 dataSrc.InsertParameters.Add("No_Senior_Devs", txtbox_seniorDevs.ToString() );4748int rowsAffected = 0;49try50 {51 rowsAffected = dataSrc.Insert();52 }53catch (Exception ex)54 {55 Server.Transfer(Error.aspx);56 }57finally58 {59 dataSrc =null;60 }6162if (rowsAffected != 1)63 {64 label_confirm.Text ="Error, Contact system admin";65 }66else67 {68 label_confirm.Text ="Success";69 }7071 }//end btn_create7273 //if cancel is clicked set all values back to default74protected void btn_cancel_Click(object sender, EventArgs e)75 {76 txtbox_title.Text ="";77 txtbox_desc.Text ="";78 txtbox_startDate.Text ="";79 txtbox_seniorDevs.Text ="";80 txtbox_juniorDevs.Text ="";81 list_devEnv.SelectedIndex = 0;82 list_devLang.SelectedIndex = 0;83 chkbox_startDate.Checked =true;84 label_confirm.Text ="";85 }8687//if checked the current dateTime is inserted into txtbox88protected void chkbox_startDate_CheckedChanged(object sender, EventArgs e)89 {90if (chkbox_startDate.Checked ==true)91 {92 txtbox_startDate.ReadOnly =true;93 txtbox_startDate.Text = (System.DateTime.Now.ToString());94 }95else96 {97 txtbox_startDate.ReadOnly =false;98 txtbox_startDate.Text ="";99 }100 }//end chkox101102103}//end class
 

Any help anyome can give is greatly appreciated. This is part of my Final Year College Dissertation which is due in 3wks !!!!

Sláinte á chaire,

Seán

Hi,

ConnectionStrings is a property name that returns a collection of connection strings. to get the items in the collection, you will need to use [] instead of () in C#. In VB, () is OK. The is the syntax.

Please check the EnableViewState property for each of the textboxes on your page, to see if they have been set to True. If false, the data in it will get lost when postback.

Also, since you're using a SqlDataSource, if CreateNewProject is a stored procedure, the parameter names should contain a @. prefix. Please check in your database to see if the CreateNewProject stored procedure has been created properly. Also use, such as @.Project_Title as parameter name.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Thanks for the reply Kevin.

As you can probably tell I am a newbie.

I have corrected all the problems you have mentioned, but it looks as if my "Submit" button is not executing any of the code at runtime. Any ideas on this?