ASP Message Board

Fancy recordset layout?

rjoseph — Tue, 18 Jun 2013 20:09:04 GMT

Hi Guys

I have a table (sqlserver) with a field called "towns" and in this field I have approx 400 town records listed.

I want to display my full list of town on my asp page but instead of listing them vertically or in standard table/cell/row format, I would love to lay the results out like this website:

http://www.carbuyingadvisor.co.uk/re...s-by-town.aspx

As you can see somehow this guy has listed his towns alphabetically and also under an associated letter of the alphabet.

Is this ease to achieve? My first thought was that he has 26 different queries running on the page? Can this be so? If not, how on earth is this done via an on-page sql statement?

Any help would be fully appreciated

Best regards

Rod from the UK

I am having all kinds of problems with this

IheChi — Mon, 17 Jun 2013 03:12:45 GMT

Hi again Experts,

This issue is really, really driving me nuts.

Please take a look at code below:

Code:

s = "INSERT INTO tblTrainings (CourseId, tblLocations.LocationId, dateId,AvailableSeats,username) VALUES (@CosID, @LocID, @dat, @Available_Seating, @Username)"

                Dim cmd = New SqlCommand(s, conn)

                cmd.Parameters.AddWithValue("@cosID", Request.QueryString("cosId"))

                cmd.Parameters.AddWithValue("@locID", Request.QueryString("locid"))

                cmd.Parameters.AddWithValue("@dat", Request.QueryString("iddate"))

                cmd.Parameters.AddWithValue("@Available_Seating", 45 - 1)

                cmd.Parameters.AddWithValue("@UserName", username)

                conn.Open()

                cmd.ExecuteNonQuery()

                conn.Close()

We have 2 fieldnames, one field called Capacity is on a table called Location.

This Capacity is of integer data type and has a value of 45.

The other fieldname is called Available and is also of integer data type.

This fieldname is on a table called trainings.

The idea is to show Capacity Seating (which is 45) and to show Available Seats.

Initially, the Available fieldname has no value.

This trainings table is used to store information about people who register for a class.

The first time a user signs up for a class, Available will show 44 seats remaining. You can see this from the code above:

cmd.Parameters.AddWithValue("@Available_Seating", 45 - 1)

The problem is that after this initial decrease from 45 to 44, it doesn't decrease anymore.

The idea is that each time someone signs up, the number decreases by 1 and will continue to decrease until the number is now 0.

Of course, when it is 0, any attempt to sign up again by a user, a message will go out that class is full and user will be on a waiting list. This means that the user's registration info will be saved on the waiting list table.

Not really worried about this waitinglist part.

I just want to be able to have Available decrease by 1 each time a user signs up until 0.

How do I make this work?

Thanks as always for your continued assistance
.

To Bill Wilkinson

eramgarden — Sun, 16 Jun 2013 00:56:56 GMT

HeyBill, it's good to see you still on this forum, helping other developers. I haven't been to this forum in a long time. You helped me out a lot yrs ago.

Good to see you still going strong.

Seeking better performance

sbhmf — Fri, 14 Jun 2013 22:31:43 GMT

Code:

CREATE PROCEDURE D_Tigers 

@RestId binary(20)

AS 

DECLARE @Table table ( id binary (20) ) 

DECLARE @Id binary(20)

DECLARE @Result bit 

SET @Result = 0 



IF (SELECT restId FROM tblRest WHERE restId=@RestId AND restParentId<>@RestId) = @RestId

BEGIN

        DELETE FROM tblRest WHERE restId=@RestId 

        DELETE FROM tblBrawl WHERE restId=@RestId 

        DELETE FROM tblBook WHERE restId=@RestId 

        DELETE FROM tblIndie WHERE restId=@RestId 

        DELETE FROM tblScore WHERE restId=@RestId 

        DELETE FROM tblBlock WHERE restId=@RestId 

        DELETE FROM tblItem WHERE restId=@RestId 

        DELETE FROM tblStone WHERE restId=@RestId 

        DELETE FROM tblBlob WHERE restId=@RestId 

        DELETE FROM tblSecret WHERE restId=@RestId 

END

ELSE

BEGIN

        INSERT INTO @Table SELECT restId FROM tblRest WHERE restParentId=@RestId

        WHILE ( SELECT COUNT(id) FROM @Table ) > 0

        BEGIN

                SET @Id = (SELECT TOP 1 id FROM @Table ORDER BY id ASC)

                        DELETE FROM tblRest WHERE restId=@Id 

                        DELETE FROM tblBrawl WHERE restId=@Id 

                        DELETE FROM tblBook WHERE restId=@Id 

                        DELETE FROM tblIndie WHERE restId=@Id 

                        DELETE FROM tblScore WHERE restId=@Id 

                        DELETE FROM tblBlock WHERE restId=@Id 

                        DELETE FROM tblItem WHERE restId=@Id 

                        DELETE FROM tblStone WHERE restId=@Id 

                        DELETE FROM tblBlob WHERE restId=@Id 

                        DELETE FROM tblSecret WHERE restId=@Id 

                DELETE FROM @Table WHERE id=@Id

        END

END

SET @Result = 1 

SELECT @Result

GO

Consume a SSL web service from Classic ASP

TsetsE — Thu, 13 Jun 2013 09:52:49 GMT

I've researched the web extensively before posting this questions here, couldn't find anything useful for me, so here it goes. Sorry in advance for the wall of text.

I have a classic ASP website that needs to call a web service method (not wcf, but an asmx page) on a asp.net website (4.0)

The way I consume the web service is as follows:

I have a webserviceclass.asp that helps me consume web services in classic asp.

code of webserviceclass.asp -> https://docs.google.com/file/d/0B1Wr...it?usp=sharing

Then calling the class to consume the method.

Code:

Dim strWebServiceResult

Dim ws

Set ws = new webservice

ws.url = "http://www.myurl.com/mywebservice.asmx"

ws.method = "HelloWorld"



ws.parameters.Add "Parameter1", "Test1"

ws.parameters.Add "Parameter2", "Test2"



ws.Execute



strWebServiceResult = ws.response

This works perfectly if www.myurl.com is not encrypted, strWebServiceResult holds the XML returned by mywebservice.asmx, however I really need the data traveling between the users and www.myurl.com to be encrypted (mywebservice.asmx has parameters for login and password to authenticate on the webservice) so I bought an SSL cert and assigned it to www.myurl.com website becoming https://www.myurl.com

So when I set the URL in the class to:

Code:

ws.url = "https://www.myurl.com/mywebservice.asmx"

and that is the only thing that changes, the method doesn't get called anymore. I have log on pageinit and pageload of the mywebservice.aspx and it doesn't even get called.

Sorry about the long text, and I hope someone can help me. I have no experience with jquery or json and I'd rather not go that way to solve this problem, however if that's the only way, I can try if there's enough info out there to help me out but I'd rather try to fix my current code if that possible. I just don't understand why it works with http but not with https

Many thanks in advance

All contact forms broken?

SimonSmart — Wed, 12 Jun 2013 16:54:38 GMT

I want to contact 4guysfromrolla regarding advertising and authoring articles. However, the feedback form does not appear to exist any more and submitting the advertising form results in an error. The captcha on the advertising form is also broken.

Is there any way to get in touch with the site's administrators at all?

paging with custom class objects

markbpriv — Sat, 08 Jun 2013 09:34:43 GMT

Hi,

I have created some custom class objects.

public class astroObjects
{

public int Id { get; set; }

public String Name { get; set; }

public String Desc { get; set; }

public int CatId { get; set; }

public String ImageThumb { get; set; }

}

My data is stored in an SQL database.

When I want to display the data I query the database and make a list as so.

public static List GetAstroObjects(int catId)
{
List astroobjects = new List();

// Do all sql work including setting up connection, command, ect.

SqlConnection conn;
SqlCommand comm;
SqlDataReader reader;
String mySQL;

int thecatid = catId;

mySQL = "SELECT objectName, object_id, Desc, objectThumbnail WHERE CatId = @catId ORDER BY objectName";

string connectionString = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
conn = new SqlConnection(connectionString);
comm = new SqlCommand(mySQL, conn);

comm.Parameters.Add("@catid", System.Data.SqlDbType.Int);
comm.Parameters["@catid"].Value = thecatid;

conn.Open();

reader = comm.ExecuteReader();

while (reader.Read())
{

astroObjects c = new astroObjects();
c.Name = reader["ObjectName"].ToString();

c.Desc = reader["Desc"].ToString();

c.Id = Convert.ToInt16( reader["object_id"]);
c.ImageThumb=reader["ObjectThumbnail"].ToString();

astroobjects.Add(c);
}

return astroobjects;
}

I next want to bind this list to a datalist.

Most examples of paging to datalist on the web I have seen are using dataset but I read this old article online http://www.dotnetcurry.com/ShowArticle.aspx?ID=345 that says you can just bind to a pageddatasource so have done the following:

protected void getTheData(int catid)

{

PagedDataSource page = new PagedDataSource();

page.AllowCustomPaging = true;
page.AllowPaging = true;

page.DataSource = myproject.astroObjects.GetAstroObjects(catid);
page.PageSize = 10;

page.CurrentPageIndex = CurPage;

Decimal intNumRecs;
intNumRecs = 50;//need to replace with actual number of records returned in the query

Decimal numPages;
numPages = Math.Ceiling(intNumRecs / page.PageSize);

int strCurPage;
strCurPage = Convert.ToInt16(CurPage) + 1;

LabelCurrentPage.Text = "Pg: " + strCurPage + " of " + numPages + " (" + intNumRecs + " objects)";

ButtonBack.Visible = (!page.IsFirstPage);
ButtonNext.Visible = (!page.IsLastPage);

DataList1.DataSource = page;

DataList1.DataBind();

}

and here's the code for my next and previous buttons

public void Next_Click(Object obj, EventArgs e)
{

//identify current datafilter

CurPage += 1;

getTheData(Convert.ToInt16(ViewState["DataFilterId"]));

}

public void Prev_Click(Object obj, EventArgs e)
{

CurPage -= 1;

getTheData(Convert.ToInt16(ViewState["DataFilterId"]));
}

and here's my page load code

protected void Page_Load(object sender, EventArgs e)
{

checkloggedin();

if (!IsPostBack) //if page loaded for the first time
{

CurPage = 0;
getTheData(0);

}

if (IsPostBack) //if page has already loaded
{

CurPage = Convert.ToInt32(ViewState["CurPage"]);
getTheData(Convert.ToInt16(ViewState["DataFilterId"]));

}

}

Here's how I set CurPage variable (Current page)

public int CurPage
{
get
{
if (this.ViewState["CurPage"] == null)
return 0;
else

return Convert.ToInt16(this.ViewState["CurPage"].ToString());

}
set
{
this.ViewState["CurPage"] = value;
}

}

and here's how I handle DataFilterId variable (category in the query)

public int DataFilterId
{

get
{
if (this.ViewState["DataFilterId"] == null)
return 0;
else

return Convert.ToInt16(this.ViewState["DataFilterId"].ToString());

}
set
{
this.ViewState["DataFilterId"] = value;
}
}

When I visit the page 10 records are displayed as expected. However when I click the next button the next 10 records aren't shown but the right page number is.

I can't figure why this isn't working.

The article uses Linq and I want to just do an SQL query as in the my list code above. I need to just query database so just the amount of records I need are retrieved from the database and added to the list.

Hope some one can help me.

Cheers

Mark :)

Passig
value to end of url?

rjoseph — Mon, 03 Jun 2013 18:24:25 GMT

Hi Guys

I hope this one is possible.

Basically, I have the following form script on my page (http://www.mysite.com/mycar/audi/a4?bodysyle=saloon):

-----------------------

Name:

Add year

-----------------------

Which is fine, however, I need to post (get) the "year" value on the end of the url so that when I submit the form the url reads:

http://www.mysite.com/mycar/audi/a4?...loon&year=2012

If I try adding the "http://www.mysite.com/mycar/audi/a4?bodysyle=saloon" url to the "action" value it will create:

http://www.mysite.com/mycar/audi/a4?...loon?year=2012

which is no good. Is there a workaround?

Any help would be fully appreciated

Best regards

Rod from the UK

Broken Links for demo pages

mike4ta — Mon, 03 Jun 2013 17:28:15 GMT

I was looking at the DataGrid Web Control series and was getting errors with demo links. I get this error:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)

On this page http://aspnet.4guysfromrolla.com/demos/dgExample1.aspx

I know this is a very old article but it has helped me in the past.

Thanks Mike

404 redirect

mike11 — Mon, 03 Jun 2013 13:29:59 GMT

Not sure where to put this question, here or IIS as it kind of ties in both. I am running some classic asp in iis 7.5 and have the usual 404 response code: Response.Status = "404 Not Found". When this is triggered my site will redirect to a custom 404 page, which is fine. However, there are some search pages where results may come up null, this is not exactly a 404 as the user is told there are no results and to change search parameters. The problem is sites like Google treat this as a soft 404, which is not very good in their books for "user experience". So what I can't seem to figure out is how to return a 404 response with asp for the sake of robots on specific pages, but not do a redirect to a 404 page. This did work fine back with iis 6, but with 7.5 every time the 404 code is returned the page is redirected no matter what.

Problems using the SQL duplicates code from SQL Guru on this site

jeffsimon — Fri, 24 May 2013 03:49:56 GMT

I used the following code (adapted from a posting from SQL Guru). However, I am getting the error at the bottom:

DELETE FROM tbl_events_temp WHERE tbl_events_temp.event_id IN (
SELECT F.event_id
FROM tbl_events_temp AS F
WHERE EXISTS (

SELECT airport_id, event_from, Count( event_id )
FROM tbl_events_temp
WHERE tbl_events_temp.airport_id = F.airport_id
AND tbl_events_temp.event_from = F.event_from
AND tbl_events_temp.airport_id != ''
GROUP BY tbl_events_temp.airport_id, tbl_events_temp.event_from
HAVING Count( tbl_events_temp.event_id ) >1
)
)
AND tbl_events_temp.event_id NOT
IN (

SELECT Min( event_id )
FROM tbl_events_temp AS F
WHERE EXISTS (

SELECT airport_id, event_from, Count( event_id )
FROM tbl_events_temp
WHERE tbl_events_temp.airport_id = F.airport_id
AND tbl_events_temp.event_from = F.event_from
GROUP BY tbl_events_temp.airport_id, tbl_events_temp.event_from
HAVING Count( tbl_events_temp.event_id ) >1
)
GROUP BY airport_id, event_from
)

MySQL said: Documentation
#1093 - You can't specify target table 'tbl_events_temp' for update in FROM clause

Adding new validation to radio button validation code

iiswansongiiAtWork — Thu, 23 May 2013 15:32:28 GMT

I have code in place that does its job that an entry 'must' be made on each set of radio buttons. Either Yes or No.

However, people are 'required' to enter yes on at least one of them and this validation doesn't check for that.
It 'will' allow people to check 'no' for all three sets'. I need it to know that they are checking at least one of the 'yes' selections. If anyone has advice on what needs to be added i'd appreciate the point in the right direction!

Here is the javascript...

Code:

function RadioValidator()

{

    var ShowAlert = '';

    var AllFormElements = window.document.getElementById("form1").elements;

    for (i = 0; i < AllFormElements.length; i++) 

    {

        if (AllFormElements[i].type == 'radio') 

        {

            var ThisRadio = AllFormElements[i].name;

            var ThisChecked = 'No';

            var AllRadioOptions = document.getElementsByName(ThisRadio);

            for (x = 0; x < AllRadioOptions.length; x++)

            {

                 if (AllRadioOptions[x].checked && ThisChecked == 'No')

                 {

                     ThisChecked = 'Yes';

                     break;

                 } 

            }   

            var AlreadySearched = ShowAlert.indexOf (ThisRadio);

            if (ThisChecked == 'No' && AlreadySearched == -1)

            {

            ShowAlert = ShowAlert + ThisRadio + 'at least one must be answered\n';

            }     

        }

    }

    if (ShowAlert != '')

    {

    alert(ShowAlert);

    return false;

    }

    else

    {

    return true;

    }

}

Here is the code in the yes/no radio buttons...

HTML Code:

"radio">

    "label">I am a this?

    "reason1">"radio" name="This?" id="reason1" value="Yes" onclick="document.getElementById('divUrl1').style.display='block'">Yes

    "radio" name="This?" id="reason1" value="No" onclick="document.getElementById('divUrl1').style.display='none'"> No





"radio">

    "label">That?

    "reason2">"radio" name="That?" id="reason2" value="Yes" onclick="document.getElementById('divUrl2').style.display='block'"> Yes"radio" name="That?" id="reason2" value="No" onclick="document.getElementById('divUrl2').style.display='none'"> No





"radio">

    "label">I am the other?

    "reason3">"radio" name="theother?" id="reason3" value="Yes" onclick="document.getElementById('divUrl3').style.display='block'"> Yes"radio" name="theother?" id="reason3" value="No" onclick="document.getElementById('divUrl3').style.display='none'"> No

thank you in advance for any advice/input you can provide!

iiSwanSongii

Weighting sql results

enigma65 — Tue, 21 May 2013 23:05:35 GMT

I'm using MSSQL2008. I have a table that I need to search one field for matches from a user input search string. I have a script that pieces together the SQL statement depending on how may words are in the search string.

So far so good. Now I want to be able to sort the table so that the records that have the most matches of words in the search string will be first. To this end I have come up with this:

Original Search String: "The Tonic Tavern & Kitchen"

Some script code converts that into the individual words "Tonic" and "Tavern" and "Kitchen".

SELECT v_name ,
(CASE WHEN v_name LIKE '%Tonic%' THEN 1 ELSE 0 END) AS weight0,
(CASE WHEN v_name LIKE '%Tavern%' THEN 1 ELSE 0 END) AS weight1,
(CASE WHEN v_name LIKE '%Kitchen%' THEN 1 ELSE 0 END) AS weight2,
SUM(weight0+weight1+weight2+ 0) AS masterweight
FROM ss_venue
WHERE v_state = 'AZ'
AND (v_name like '%Tonic%' OR v_name like '%Tavern%' OR v_name like '%Kitchen%' )
ORDER BY masterweight DESC

Assuming the 3-word input, this is meant to give me column called "masterweight" with a value of 3 for records that match all terms, 2 for records that match 2 terms etc...

I get an error that says " Invalid column name 'weight0'. " yet if I remove the "SUM()" and say ORDER BY weight0 at the end it's fine. So why is it a column one way and not the other??? What am I missing?

How do I send my user to a custom login page if/when they time out?

PhilHoop@mm.com — Tue, 21 May 2013 10:46:14 GMT

I have implemented a timeout with slidingexpiration in my web.config which by default sends the user to the default.aspx page which has the login control. I woulf like to change this behavior such that the user would be directed to a page that will have a login control plus information telling them that they have arrived at this page due to a timeout. I'm trying to avoid the following scenario. A user leaves their computer while logged in to my application intending to return shortly and thus does not log out. However they get distracted and do not return within the allowed timeout period and find that when they make an entry they are immediately looking at the login page without any indication as to why they have been sent here. I have found some blogs with ideas for presenting an alert that a timeout is about to occur, but with the slidingexpiration this would not occur unless they were not activiely using the application and thus would not be present to see the alert. Thank you for any suggestions you choose to offer..............................Phil Hoop

ASP Message Board

Fancy recordset layout?

I am having all kinds of problems with this

To Bill Wilkinson

Seeking better performance

Consume a SSL web service from Classic ASP

All contact forms broken?

paging with custom class objects

Passig value to end of url?

Broken Links for demo pages

404 redirect

Problems using the SQL duplicates code from SQL Guru on this site

Adding new validation to radio button validation code

Weighting sql results

How do I send my user to a custom login page if/when they time out?

Passig
value to end of url?