losing some results

i have created a report that fits the layout to achieve the fields that i
require, i then created an aspx page where my users can select any number of
fields and values to use in the where clause of the sql statement. My aspx
page then builds an sql statement based on these selections and passes this
sql statement to the report as a parameter. The report calls a stored
procedure that executes the sql statement passed in. This works great for
all but one situation that i have found. When a user enters '%bel%' to use
in the where clause for some reason when it gets to reporting services
report the sql statement is modified to 'l%'. Dropping the '%be'. Is
'%be' a reserved command.
example:
if my table had the following entries in a column name city Boston,
Belville,New York,Detroit,Los Angeles, Lakeville
my user wants to find all cities that have 'bel' in the name
the resulting sql would be select city from table where city like '%bel%'
i setup up my report to show the parameters when the aspx page redirects to
the report using the url of the report
the sql that shows up in the parameter field is select city from table where
city like 'l%'
Any help would be appreciated.
Thank youSolved my own problem. what i had to do was replace all my '%' to '%25' to
encode my url before i issued a response.redirect.
"Mike" <mike.no.spam.please@.no.spam.com> wrote in message
news:u2LsSw6tEHA.1596@.TK2MSFTNGP10.phx.gbl...
>i have created a report that fits the layout to achieve the fields that i
>require, i then created an aspx page where my users can select any number
>of fields and values to use in the where clause of the sql statement. My
>aspx page then builds an sql statement based on these selections and passes
>this sql statement to the report as a parameter. The report calls a stored
>procedure that executes the sql statement passed in. This works great for
>all but one situation that i have found. When a user enters '%bel%' to use
>in the where clause for some reason when it gets to reporting services
>report the sql statement is modified to 'l%'. Dropping the '%be'. Is
>'%be' a reserved command.
> example:
> if my table had the following entries in a column name city Boston,
> Belville,New York,Detroit,Los Angeles, Lakeville
> my user wants to find all cities that have 'bel' in the name
> the resulting sql would be select city from table where city like '%bel%'
>
> i setup up my report to show the parameters when the aspx page redirects
> to the report using the url of the report
> the sql that shows up in the parameter field is select city from table
> where city like 'l%'
> Any help would be appreciated.
> Thank you
>

lose data

In my applications (web version and client version)
sometime my users in the day after loss data!
I think are a transaction problem but
I don't know why...
Have you any suggests for my problem...
thank to all
Pippo
2 things you might try...
Run Profiler to capture inserts, updates, and deletes to see what is going
on. or
use Lumigents tool ( Log Exploerer) to search the transaction log and do the
same thing.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"PIPPO" <anonymous@.discussions.microsoft.com> wrote in message
news:118a01c4ab8f$00744770$a601280a@.phx.gbl...
> In my applications (web version and client version)
> sometime my users in the day after loss data!
> I think are a transaction problem but
> I don't know why...
> Have you any suggests for my problem...
> thank to all
> Pippo
|||- check for rollback of transactions
- check also if some process is updating your initial rows.

>--Original Message--
>2 things you might try...
>Run Profiler to capture inserts, updates, and deletes to
see what is going
>on. or
>use Lumigents tool ( Log Exploerer) to search the
transaction log and do the
>same thing.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"PIPPO" <anonymous@.discussions.microsoft.com> wrote in
message
>news:118a01c4ab8f$00744770$a601280a@.phx.gbl...
>
>.
>

lose data

In my applications (web version and client version)
sometime my users in the day after loss data!
I think are a transaction problem but
I don't know why...
Have you any suggests for my problem...
thank to all
Pippo2 things you might try...
Run Profiler to capture inserts, updates, and deletes to see what is going
on. or
use Lumigents tool ( Log Exploerer) to search the transaction log and do the
same thing.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"PIPPO" <anonymous@.discussions.microsoft.com> wrote in message
news:118a01c4ab8f$00744770$a601280a@.phx.gbl...
> In my applications (web version and client version)
> sometime my users in the day after loss data!
> I think are a transaction problem but
> I don't know why...
> Have you any suggests for my problem...
> thank to all
> Pippo|||- check for rollback of transactions
- check also if some process is updating your initial rows.
>--Original Message--
>2 things you might try...
>Run Profiler to capture inserts, updates, and deletes to
see what is going
>on. or
>use Lumigents tool ( Log Exploerer) to search the
transaction log and do the
>same thing.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"PIPPO" <anonymous@.discussions.microsoft.com> wrote in
message
>news:118a01c4ab8f$00744770$a601280a@.phx.gbl...
>> In my applications (web version and client version)
>> sometime my users in the day after loss data!
>> I think are a transaction problem but
>> I don't know why...
>> Have you any suggests for my problem...
>> thank to all
>> Pippo
>
>.
>sql

looping thru a table and updating the contents

Hello, Its hard trying to explain this.

I have 3 tables

Table 1 is where the users are stored, each user has a username and a userrank

Table 2 is where the points that decides the userrank are stored

Table 3 contains the available userranks like this

Table 1 (user_list) looks briefly like this:
username nvarchar(20),
userrank int, -- Reference to Table3 id
... alot more fields

Table 2 (settings_profile) looks like this:
username nvarchar(20),
total_active_points int,
... some more fields

Table 3 (data_ranks) looks like this:
id int primary key auto inc,
rankname nvarchar(20),
min_pts int,
max_pts int

Points get added to table 2 whenever they do something that generates points on the site. Points also get withdrawn every 7 days, so a user can only collect points for 7 days, on the 8th day, all points he earned on the 1st day is reduced from the current points with this code:

WHILE(SELECT @.username= username, @.id= id, @.temp1= ap_sentmails, @.temp2= ap_createdthreads, @.temp3= ap_createdanswers, @.temp4= ap_signguestbook, @.temp5= ap_blogcomment, @.temp6= ap_createblogentry, @.temp7= ap_profilefirsttime, @.temp8= ap_profilephoto, @.temp9= ap_activateguestbook, @.temp10= ap_addnewfriend, @.temp11= ap_superguruvote, @.temp12= ap_forumtopicvote, @.temp13= ap_labervote, @.temp14= ap_funstuffitemvote, @.temp15= ap_movievote, @.temp16= ap_actorvote, @.temp17= ap_money_newWHERE(created<Dateadd(dd,-7, @.todaysdate))
BEGIN
SET @.sum= 0
SET @.sum= @.temp1+ @.temp2+ @.temp3+ @.temp4+ @.temp5+ @.temp6+ @.temp7+ @.temp8+ @.temp9+ @.temp10+ @.temp11+ @.temp12+ @.temp13+ @.temp14+ @.temp15+ @.temp16+ @.temp17
UPDATE settings_profileSET total_active_points= total_active_points- @.sumWHERE(username= @.username)
DELETEFROM konto_daylistWHERE(id= @.id)
END

Now my question is this, i want to loop thru the table A, collect all usernames inside of it, then run it against table b and table c to determine the current rank of the user.
Something like this...

DECLARE @.username nvarchar(20)
DECLARE @.pts int, @.rank int

...something that starts a loop thru table A (user_list) and get the username into @.username...

SELECT @.pts =total_active_points FROM settings_profile WHERE (username = @.username)
-- Determine the rank here, by compairing the points the user have against the pointstabel in table data_ranks
SELECT @.rank = id FROM data_ranks WHERE (pts_min => @.pts AND pts_max < @.pts)
UPDATE user_list SET rank = @.rank WHERE (username = @.username)

...next persion in the loop...

This SP runs once a day and will first reduce the points from 8days ago, then it will run thru all the users and determine their new rank...

But how do i loop thru all the users? with a cursor?

I don't have time to write this for you, but here's the line of attack to use:

Create and test a query that computes the the correct score for each user.

then, issue an update in this format:

update settings_profile

set total_active_points = ( the query from above where settings_profile.username = other_table.username)

|||

Hi, i dont truly understand what you meant. I can compute the score for each user If i have their username, thats why i need to create a loop that loops thru every post in table A, so that i can update every single post with the rank they earned from the points. I need to find someway to loop thru all posts.

|||

No, you do not need to write a loop thru the users.

This is because the SQL Update statement provides an implied loop for you.

Example:

createtable xxx(idint, textvaluevarchar(20))

insertinto xxxvalues(1,'Nikki')

insertinto xxxvalues(2,'John')

update xxxset textvalue='Ginger'

select*from xxx

You will note that every xxx record now has Ginger in the textvalue column.

The update statement IS a loop!

And, while it is processing any given xxx record, it knows the value in each of that record's columns.

I do not have to hard-code the value that will be used to set textvalue with.

I can issue a query instead.

So, let's create a new table called nametable.

createtable nametable(idint,namevarchar(20))

insertinto nametablevalues(1,'Amber')

insertinto nametablevalues(2,'Tracy')

update xxx set textvalue = (select name from nametable where nametable.id = xxx.id)

select * from xxx

Note that the name for record 1 is now Amber and the name for record 2 is now Tracy

Hope that helps clarify things.

|||

Hi,

Its working like a clock now, thanks for the explanation.

I build this line with the example code you sent, and its brilliant.

UPDATE profile_publicinfoSET userrank=(SELECT data_userrank.idFROM settings_profileINNERJOIN data_userrankON(data_userrank.pointsmin<= settings_profile.total_active_pointsAND data_userrank.pointsmax> settings_profile.total_active_points)WHERE(settings_profile.username= profile_publicinfo.username))

|||

Great!

Now all you have to do is mark the thread as answered, so other folks don't spend their time trying to help you only to find out you don't need help anymore! :)

The key to being ultra-productive with SQL is to think in terms of "sets of data" instead of looping thru records of data one at a time.

|||

Then i just have to ask, is it possible to update several records with a IF statement?

Like this,

UPDATE clubsSET isactive=(IF(clubs_account.money-clubs_account.cost< 0SELECT 1) ELSE (SELECT 0)FROM clubs_account)

Thats money - expenses, if its less then 0, return a 1 otherwise return a 0

|||

The SQL equivalent of an IF statement is a CASE statement.

But don't try to write it the way your if statement was coded.

Try this instead:

update clubs
set isactive = (select case clubs_account.money - clubs_account.cost < 0
when true then 1
else 0
end case
from clubs_account
where clubs_account.? = clubs.?
)

|||

Looks very good, but i just keeps getting an error when i tried it (i also tried to put () around the math calculations, but i still get the same result),

The clubs.inactive is a bit field if that is of any importance.

UPDATE clubsSET isactive=(SELECTCASE club_konto.money- club_konto.cost< 0WHEN trueTHEN 1ELSE 0ENDCASEFROM club_kontoWHERE club_konto.clubid= clubs.id)

Incorrect syntax near '<'.

|||

bit fields are 1s and 0s, not true and false.

Also, I work in too many different languages. :( It's END, not END CASE.

createtable clubs(idint, isactivebit)

createtable club_konto( clubidint,moneyint, costint)

insertinto clubsvalues(1,1)

insertinto clubsvalues(2,1)

insertinto clubsvalues(3,0)

insertinto clubsvalues(4,0)

insertinto club_kontovalues(1,50,10)

insertinto club_kontovalues(2,10,50)

insertinto club_kontovalues(3,50,10)

insertinto club_kontovalues(4,10,50)

select*from clubs

select*from club_konto

UPDATE clubs

SET isactive=(SELECTCASEWHEN club_konto.money- club_konto.cost< 0THEN 1ELSE 0END

FROM club_kontoWHERE club_konto.clubid= clubs.id)

select*from clubs

I have to caution you about this - the select statement used to populate the isactive column must NOT return more than one row of data.

If it can return more than one row, you need to change it so it only returns one row. So, if there can be two or more club_konto records per club, the query above will not work. Instead you would need it to be something like this:

UPDATE clubs

SET isactive=(SELECTCASEWHEN sum(club_konto.money)- sum(club_konto.cost)< 0THEN 1ELSE 0END

FROM club_kontoWHERE club_konto.clubid= clubs.id)

|||

I see that i need to learn alot more about how data is handeled inside MS SQL 2005, its working perfect with your example,

Thanks alot

(I have afew more SPs with While loops that i have to go thru now and see if i can upgrade them according to the new update way.)

looking up client side connection info

I am trying to create an audit trail using triggers on a particular table
for inserts, updates, and deletes.
This would be a simple task if the users were logging in under different
names but there is a single login that gets used by all connections.
Luckily the application has its own user table and the table I am creating
the audit for has a column that contains the application user that modified
the row. This is fine for inserts and updates but a problem for deletes.
So before I go looking for alternatives - a stored procedure for all access,
etc. - I wanted to know whether there is a way to find the client side
connection info by way of @.@.SPID somehow. A client machine name or IP at
the very least. Is it possible to retrieve a OLE DB connection string that
the client used to connect to the server in the first place? I know that
the connection string wouldn't really be of any interest to SQL server but I
was hoping that it would forward the info anyway.
Thanks in advance.
Jiho Han wrote:
> the very least. Is it possible to retrieve a OLE DB connection string that
> the client used to connect to the server in the first place? I know that
> the connection string wouldn't really be of any interest to SQL server but I
> was hoping that it would forward the info anyway.
I have searched high and low for such information and could not find any
reliable source for it.
What you can use is SET CONTEXT_INFO on the client side to have it
deposit up to 128 bytes of any info you like. Triggers can then read
this packet of info as follows:
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @.@.spid
Steve
|||Thanks for the info.
I don't really control the client, well, not all of them anyway. But that
may come in handy some day...
Jiho
"Steve Troxell" <steve_troxell@.hotmail-nospamforme.com> wrote in message
news:emJfEFrGFHA.3876@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Jiho Han wrote:
that[vbcol=seagreen]
that[vbcol=seagreen]
but I
> I have searched high and low for such information and could not find any
> reliable source for it.
> What you can use is SET CONTEXT_INFO on the client side to have it
> deposit up to 128 bytes of any info you like. Triggers can then read
> this packet of info as follows:
> SELECT context_info
> FROM master.dbo.sysprocesses
> WHERE spid = @.@.spid
>
> Steve

looking up client side connection info

I am trying to create an audit trail using triggers on a particular table
for inserts, updates, and deletes.
This would be a simple task if the users were logging in under different
names but there is a single login that gets used by all connections.
Luckily the application has its own user table and the table I am creating
the audit for has a column that contains the application user that modified
the row. This is fine for inserts and updates but a problem for deletes.
So before I go looking for alternatives - a stored procedure for all access,
etc. - I wanted to know whether there is a way to find the client side
connection info by way of @.@.SPID somehow. A client machine name or IP at
the very least. Is it possible to retrieve a OLE DB connection string that
the client used to connect to the server in the first place? I know that
the connection string wouldn't really be of any interest to SQL server but I
was hoping that it would forward the info anyway.
Thanks in advance.Jiho Han wrote:
> the very least. Is it possible to retrieve a OLE DB connection string that
> the client used to connect to the server in the first place? I know that
> the connection string wouldn't really be of any interest to SQL server but I
> was hoping that it would forward the info anyway.
I have searched high and low for such information and could not find any
reliable source for it.
What you can use is SET CONTEXT_INFO on the client side to have it
deposit up to 128 bytes of any info you like. Triggers can then read
this packet of info as follows:
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @.@.spid
Steve|||Thanks for the info.
I don't really control the client, well, not all of them anyway. But that
may come in handy some day...
Jiho
"Steve Troxell" <steve_troxell@.hotmail-nospamforme.com> wrote in message
news:emJfEFrGFHA.3876@.TK2MSFTNGP14.phx.gbl...
> Jiho Han wrote:
> > the very least. Is it possible to retrieve a OLE DB connection string
that
> > the client used to connect to the server in the first place? I know
that
> > the connection string wouldn't really be of any interest to SQL server
but I
> > was hoping that it would forward the info anyway.
> I have searched high and low for such information and could not find any
> reliable source for it.
> What you can use is SET CONTEXT_INFO on the client side to have it
> deposit up to 128 bytes of any info you like. Triggers can then read
> this packet of info as follows:
> SELECT context_info
> FROM master.dbo.sysprocesses
> WHERE spid = @.@.spid
>
> Steve

looking up client side connection info

I am trying to create an audit trail using triggers on a particular table
for inserts, updates, and deletes.
This would be a simple task if the users were logging in under different
names but there is a single login that gets used by all connections.
Luckily the application has its own user table and the table I am creating
the audit for has a column that contains the application user that modified
the row. This is fine for inserts and updates but a problem for deletes.
So before I go looking for alternatives - a stored procedure for all access,
etc. - I wanted to know whether there is a way to find the client side
connection info by way of @.@.SPID somehow. A client machine name or IP at
the very least. Is it possible to retrieve a OLE DB connection string that
the client used to connect to the server in the first place? I know that
the connection string wouldn't really be of any interest to SQL server but I
was hoping that it would forward the info anyway.
Thanks in advance.Jiho Han wrote:
> the very least. Is it possible to retrieve a OLE DB connection string tha
t
> the client used to connect to the server in the first place? I know that
> the connection string wouldn't really be of any interest to SQL server but
I
> was hoping that it would forward the info anyway.
I have searched high and low for such information and could not find any
reliable source for it.
What you can use is SET CONTEXT_INFO on the client side to have it
deposit up to 128 bytes of any info you like. Triggers can then read
this packet of info as follows:
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @.@.spid
Steve|||Thanks for the info.
I don't really control the client, well, not all of them anyway. But that
may come in handy some day...
Jiho
"Steve Troxell" <steve_troxell@.hotmail-nospamforme.com> wrote in message
news:emJfEFrGFHA.3876@.TK2MSFTNGP14.phx.gbl...
> Jiho Han wrote:
that[vbcol=seagreen]
that[vbcol=seagreen]
but I[vbcol=seagreen]
> I have searched high and low for such information and could not find any
> reliable source for it.
> What you can use is SET CONTEXT_INFO on the client side to have it
> deposit up to 128 bytes of any info you like. Triggers can then read
> this packet of info as follows:
> SELECT context_info
> FROM master.dbo.sysprocesses
> WHERE spid = @.@.spid
>
> Steve

Looking for direction on how to add 4000+ users into ASPNETDB manually

Hello and thanks for reading,

I would like to know if it's possible to add around 4000 users into the ASPNETDB without adding them one at a time. I have to avoid using a "create user wizard" because this login is for a private online election only. The list of users is in an access database and looks like this:

Username Password

DHunziker Hu8645

SEnderton En0854

BAckler Ac9576

...

Unfortunately, I don't have valid emails for all 4000 people, nor do I have the time and resources to collect security questions and answers so I don't know if this is even possible. Can you help me?

Thanks!

Peter Kellner (http://peterkellner.net/zdm_1_2/index.php) provides a nice membership management tool for this database and you can download from his site.

You can use his objectdatasource to do the batch insert. Here is a sample from a button click:

Create a SpreadSheet with these columns: UserName,password, and email.

Import this excel file to this database as table: yourUsers$

Here is the code for the button click:

protected void batchInsertButton_Click(object sender, EventArgs e) {string strConn = ConfigurationManager.ConnectionStrings["1ConnectionString"].ConnectionString;string strSQL ="SELECT * FROM yourUsers$"; SqlConnection myConnection =new SqlConnection(strConn); myConnection.Open(); SqlCommand myCommand =new SqlCommand(strSQL,myConnection); SqlDataReader myReader; myReader = myCommand.ExecuteReader();while (myReader.Read()) { ObjectDataSourceMembershipUser.InsertParameters["UserName"].DefaultValue = myReader["UserName"].ToString();//TextBoxUserName.Text; ; ObjectDataSourceMembershipUser.InsertParameters["password"].DefaultValue = myReader["password"].ToString();//TextBoxPassword.Text; ObjectDataSourceMembershipUser.InsertParameters["passwordQuestion"].DefaultValue ="your qestion";//TextBoxPasswordQuestion.Text; ObjectDataSourceMembershipUser.InsertParameters["passwordAnswer"].DefaultValue ="your answer";//TextBoxPasswordAnswer.Text; ObjectDataSourceMembershipUser.InsertParameters["email"].DefaultValue = myReader["email"].ToString();//TextBoxEmail.Text; ObjectDataSourceMembershipUser.InsertParameters["isApproved"].DefaultValue ="true";//CheckboxApproval.Checked == true ? "true" : "false"; ObjectDataSourceMembershipUser.Insert();//hard code this user role Roles.AddUserToRole(myReader["UserName"].ToString(),"NormalUser"); } myConnection.Close(); GridViewMemberUser.DataBind(); GridViewRole.DataBind(); }

Hope this helps.

|||

Wow! Thanks!

I have already got peter's managment tool running and the information that you provided is very helpful. Thanks a million!