ASP Message Board - Database (ADO/SQL/Access)

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

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 :)

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

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?