Does anyone know how
-- during an insert --
to automatically populate a field with the same data as the field in the
last inserted row?
Thanks.rmg66 wrote:
> Does anyone know how
> -- during an insert --
> to automatically populate a field with the same data as the field in
> the last inserted row?
>
First, you need a way to identify the last inserted row. is there an
InsertionDate column?
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:%23ozT1swBGHA.208@.tk2msftngp13.phx.gbl...
> Does anyone know how
> -- during an insert --
> to automatically populate a field with the same data as the field in the
> last inserted row?
> Thanks.
SQL server has no concept of Last Inserted Row.
If you have a column on the table that is populated with the date and time
the row was inserted, you could query that row. But it's not impossible that
2 or more rows can be inserted at exactly the same time.
Also, in your case, what happens if the value later changes in that Last
Inserted Row.
If you tell us what the logic is behind what you're trying to do, someone
here could perhaps suggest a better solution.|||> Does anyone know how
> -- during an insert --
> to automatically populate a field with the same data as the field in the
> last inserted row?
Since a table is an unordered set of rows, how do you define "last"?
And why not keep this "last" value in another table?|||Does anyone know why
-- during an insert --
you would want to do this.
I mean if you are doing a select into or and insert select then you can
choose the data you want, if you are doing simple insert stmts. then you
already know what the data is.
post some ddl and maybe we can help
"rmg66" wrote:
> Does anyone know how
> -- during an insert --
> to automatically populate a field with the same data as the field in the
> last inserted row?
> Thanks.
>
>|||You could use a insert trigger on the table.
If value is specified add it as an extended property to the table.
If the value is not specified lookup the extended property on the table.
John
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OOr62uwBGHA.412@.TK2MSFTNGP15.phx.gbl...
> rmg66 wrote:
> First, you need a way to identify the last inserted row. is there an
> InsertionDate column?
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||John Kendrick wrote:
> You could use a insert trigger on the table.
>
Why are you replying to me?
I'm not the OP ;-)
> If value is specified add it as an extended property to the table.
> If the value is not specified lookup the extended property on the
> table.
Which gets us back to:
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||> If value is specified add it as an extended property to the table.
> If the value is not specified lookup the extended property on the table.
What does an extended property have to do with data in the table?|||You could create an extended property for each columns that is use the last
inserted record.
When the insert trigger fires it will lookup and use that last column value.
The extended property is an alternative way to handle getting the last
record value without querying the table. Otherwise you would need some way
to find the latest record. Like max(datetime) as Bob mentioned.
John
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23S$7lDxBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> What does an extended property have to do with data in the table?
>|||>> You could create an extended property for each columns that is use the
Can you please elaborate on this approach a bit? Perhaps an example would be
helpful.
Anith
Showing posts with label value. Show all posts
Showing posts with label value. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Point label - formatting decimal places
I have calculated a number of fields with the following method:
=SUM(Fields!Asian.Value) / SUM(Fields!Asian.Value + Fields!Alaskan.Value +
Fields!Black.Value + Fields!Hispanic.Value + Fields!Caucasian.Value +
Fields!AmericanIndian.Value + Fields!NotReported.Value) * 100 & "%" & " Asian"
This gives me my Asian population percentage, but when it renders it carries
on so long that I lose my " Asian" off the chart. I just want to limit the
expression to two decimal places. I tried to put the expression to format
the number in the format code as ##.00 as I would a table to limit the
decimal places to two places. What do I need to do to make this happen?
--
Thanks,
ChrisNot sure if you ever got a response.
The formatcode only works if the datatype of your expression is not a
string. In your example however, the expression will generate a string. Try
to use the FormatNumber function inside the calculation and apply the format
code directly:
=FormatNumber( SUM(Fields!Asian.Value) / SUM(Fields!Asian.Value +
Fields!Alaskan.Value + Fields!Black.Value + Fields!Hispanic.Value +
Fields!Caucasian.Value + Fields!AmericanIndian.Value +
Fields!NotReported.Value) * 100, 2) & "%" & " Asian"
See also:
http://msdn.microsoft.com/library/en-us/script56/html/vsfctFormatNumber.asp
Alternatively you could use the Format function which accepts a format code
as argument. E.g.: =Format( ..., "N2")
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctformat.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"cmcdavid" <cmcdavid@.discussions.microsoft.com> wrote in message
news:8E76FFB4-15E6-4ED0-841E-B7C722BC193B@.microsoft.com...
> I have calculated a number of fields with the following method:
> =SUM(Fields!Asian.Value) / SUM(Fields!Asian.Value + Fields!Alaskan.Value +
> Fields!Black.Value + Fields!Hispanic.Value + Fields!Caucasian.Value +
> Fields!AmericanIndian.Value + Fields!NotReported.Value) * 100 & "%" & "
Asian"
> This gives me my Asian population percentage, but when it renders it
carries
> on so long that I lose my " Asian" off the chart. I just want to limit
the
> expression to two decimal places. I tried to put the expression to format
> the number in the format code as ##.00 as I would a table to limit the
> decimal places to two places. What do I need to do to make this happen?
> --
> Thanks,
> Chris
=SUM(Fields!Asian.Value) / SUM(Fields!Asian.Value + Fields!Alaskan.Value +
Fields!Black.Value + Fields!Hispanic.Value + Fields!Caucasian.Value +
Fields!AmericanIndian.Value + Fields!NotReported.Value) * 100 & "%" & " Asian"
This gives me my Asian population percentage, but when it renders it carries
on so long that I lose my " Asian" off the chart. I just want to limit the
expression to two decimal places. I tried to put the expression to format
the number in the format code as ##.00 as I would a table to limit the
decimal places to two places. What do I need to do to make this happen?
--
Thanks,
ChrisNot sure if you ever got a response.
The formatcode only works if the datatype of your expression is not a
string. In your example however, the expression will generate a string. Try
to use the FormatNumber function inside the calculation and apply the format
code directly:
=FormatNumber( SUM(Fields!Asian.Value) / SUM(Fields!Asian.Value +
Fields!Alaskan.Value + Fields!Black.Value + Fields!Hispanic.Value +
Fields!Caucasian.Value + Fields!AmericanIndian.Value +
Fields!NotReported.Value) * 100, 2) & "%" & " Asian"
See also:
http://msdn.microsoft.com/library/en-us/script56/html/vsfctFormatNumber.asp
Alternatively you could use the Format function which accepts a format code
as argument. E.g.: =Format( ..., "N2")
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctformat.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"cmcdavid" <cmcdavid@.discussions.microsoft.com> wrote in message
news:8E76FFB4-15E6-4ED0-841E-B7C722BC193B@.microsoft.com...
> I have calculated a number of fields with the following method:
> =SUM(Fields!Asian.Value) / SUM(Fields!Asian.Value + Fields!Alaskan.Value +
> Fields!Black.Value + Fields!Hispanic.Value + Fields!Caucasian.Value +
> Fields!AmericanIndian.Value + Fields!NotReported.Value) * 100 & "%" & "
Asian"
> This gives me my Asian population percentage, but when it renders it
carries
> on so long that I lose my " Asian" off the chart. I just want to limit
the
> expression to two decimal places. I tried to put the expression to format
> the number in the format code as ##.00 as I would a table to limit the
> decimal places to two places. What do I need to do to make this happen?
> --
> Thanks,
> Chris
Tuesday, March 20, 2012
PLS. HELP. SQL NEWBIE
hello all. please tell me why the following update staement doesn't
work.
work.
what i want to do is update tblmaster.mcol2 based on the value of
tblheader.hcol2
hcol2 values:
1 = add ( tbldetails.dcol3 * tbldetails.dcol4 ) to mcol2
2 = subtract ( tbldetails.dcol3 * tbldetails.dcol4 ) from mcol2
i tried it using query analyzer but it still adds even though the
value of hcol2 is 2.
create table tblmaster ( mcol1 nvarchar(3),
mcol2 float )
insert into tblmaster values ('001', 1)
insert into tblmaster values ('002', 1)
insert into tblmaster values ('003', 1)
insert into tblmaster values ('004', 1)
insert into tblmaster values ('005', 1)
create table tblheader ( hcol1 int,
hcol2 smallint )
create table tbldetails ( dcol1 int,
dcol2 nvarchar(3),
dcol3 float,
dcol4 float )
insert into tblheader values (1, 1)
insert into tblheader values (2, 1)
insert into tblheader values (3, 2)
insert into tbldetails values ( 1, '001', 1, 10 )
insert into tbldetails values ( 1, '002', 1, 10 )
insert into tbldetails values ( 2, '001', 1, 10 )
insert into tbldetails values ( 2, '003', 2, 10 )
insert into tbldetails values ( 3, '004', 1, 10 )
insert into tbldetails values ( 3, '005', 2, 10 )
declare @.lo as int
declare @.hi as int
set @.lo = 1
set @.hi = 3
UPDATE tblmaster
SETmcol2 =
CASE h.hcol2
WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
END
FROM tblmaster t, tblheader h, tbldetails d
WHERE t.mcol1 = d.dcol2 AND h.hcol1 BETWEEN @.lo and @.hi
select * from tblmaster
TIA,
diegoHi Rey
It is not clear how your tables are actually related but you may want to try
something like:
"Rey Guerrero" <rey_guerrero@.hotmail.com> wrote in message
news:d401c59b.0412220652.54dfa7cb@.posting.google.c om...
> hello all. please tell me why the following update staement doesn't
> work.
> what i want to do is update tblmaster.mcol2 based on the value of
> tblheader.hcol2
> hcol2 values:
> 1 = add ( tbldetails.dcol3 * tbldetails.dcol4 ) to mcol2
> 2 = subtract ( tbldetails.dcol3 * tbldetails.dcol4 ) from mcol2
> i tried it using query analyzer but it still adds even though the
> value of hcol2 is 2.
>
> create table tblmaster ( mcol1 nvarchar(3),
> mcol2 float )
> insert into tblmaster values ('001', 1)
> insert into tblmaster values ('002', 1)
> insert into tblmaster values ('003', 1)
> insert into tblmaster values ('004', 1)
> insert into tblmaster values ('005', 1)
> create table tblheader ( hcol1 int,
> hcol2 smallint )
> create table tbldetails ( dcol1 int,
> dcol2 nvarchar(3),
> dcol3 float,
> dcol4 float )
> insert into tblheader values (1, 1)
> insert into tblheader values (2, 1)
> insert into tblheader values (3, 2)
> insert into tbldetails values ( 1, '001', 1, 10 )
> insert into tbldetails values ( 1, '002', 1, 10 )
> insert into tbldetails values ( 2, '001', 1, 10 )
> insert into tbldetails values ( 2, '003', 2, 10 )
> insert into tbldetails values ( 3, '004', 1, 10 )
> insert into tbldetails values ( 3, '005', 2, 10 )
> declare @.lo as int
> declare @.hi as int
> set @.lo = 1
> set @.hi = 3
> UPDATE tblmaster
> SET mcol2 =
> CASE h.hcol2
> WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> END
> FROM tblmaster t, tblheader h, tbldetails d
> WHERE t.mcol1 = d.dcol2 AND h.hcol1 BETWEEN @.lo and @.hi
> select * from tblmaster
> TIA,
> diego|||Hi Rey
It is not clear how your tables are actually related but you may want to try
something like:
UPDATE t
SET mcol2 =
CASE h.hcol2
WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
END
FROM tblmaster t
JOIN tbldetails d ON t.mcol1 = d.dcol2
JOIN tblheader h ON h.hcol1 = d.dcol1
WHERE h.hcol1 BETWEEN @.lo and @.hi
As tblmaster has is a one to many relationship with tbldetails you will have
to either restrict which rows match or use an aggregate
UPDATE t
SET mcol2 = mcol2 + val
FROM tblmaster t
JOIN ( SELECT d.dcol2, SUM( CASE WHEN h.col2 = 1 THEN d.dcol3 * dcol4
ELSE -1 * dcol3 * dcol4 END ) as val
FROM tbldetails d
JOIN tblheader h ON h.hcol1 = d.dcol1
WHERE h.hcol1 BETWEEN @.lo and @.hi GROUP BY d.dcol2 ) d ON t.mcol1 =
d.dcol2
John
"Rey Guerrero" <rey_guerrero@.hotmail.com> wrote in message
news:d401c59b.0412220652.54dfa7cb@.posting.google.c om...
> hello all. please tell me why the following update staement doesn't
> work.
> what i want to do is update tblmaster.mcol2 based on the value of
> tblheader.hcol2
> hcol2 values:
> 1 = add ( tbldetails.dcol3 * tbldetails.dcol4 ) to mcol2
> 2 = subtract ( tbldetails.dcol3 * tbldetails.dcol4 ) from mcol2
> i tried it using query analyzer but it still adds even though the
> value of hcol2 is 2.
>
> create table tblmaster ( mcol1 nvarchar(3),
> mcol2 float )
> insert into tblmaster values ('001', 1)
> insert into tblmaster values ('002', 1)
> insert into tblmaster values ('003', 1)
> insert into tblmaster values ('004', 1)
> insert into tblmaster values ('005', 1)
> create table tblheader ( hcol1 int,
> hcol2 smallint )
> create table tbldetails ( dcol1 int,
> dcol2 nvarchar(3),
> dcol3 float,
> dcol4 float )
> insert into tblheader values (1, 1)
> insert into tblheader values (2, 1)
> insert into tblheader values (3, 2)
> insert into tbldetails values ( 1, '001', 1, 10 )
> insert into tbldetails values ( 1, '002', 1, 10 )
> insert into tbldetails values ( 2, '001', 1, 10 )
> insert into tbldetails values ( 2, '003', 2, 10 )
> insert into tbldetails values ( 3, '004', 1, 10 )
> insert into tbldetails values ( 3, '005', 2, 10 )
> declare @.lo as int
> declare @.hi as int
> set @.lo = 1
> set @.hi = 3
> UPDATE tblmaster
> SET mcol2 =
> CASE h.hcol2
> WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> END
> FROM tblmaster t, tblheader h, tbldetails d
> WHERE t.mcol1 = d.dcol2 AND h.hcol1 BETWEEN @.lo and @.hi
> select * from tblmaster
> TIA,
> diego
Monday, March 12, 2012
Pls Help... How can I retieve the value of fields in my sqldatabase using sqldatareader
Please help me with my thesis... I am using ASP.NET
How can I retieve the value of fields in my sqldatabase using sqldatareader
thnks
Try the training videos in the SQL section.....http://www.asp.net/learn/sql-videos/
These will help you get started learning how to retreive data from the database
Burl
|||Dim DBConnAsNew SqlConnection("Server=localhost;Password=YourPWD;Persist Security Info=False;User ID=YourID;Initial Catalog=Northwind;Data Source=YourDataSource")Dim DBCmdAsNew SqlCommandDim DRAs SqlDataReaderDBCmd =New SqlCommand("SELECT Id, Name FROM Customers WHERE CustomerID = @.CustomerID", DBConn)DBCmd.Parameters.Add("@.CustomerID", SqlDbType.NChar, 5).Value = txtQuery.TextDR = DBCmd.ExecuteReader()While(DR.Read())
{
id as string = DR["ID"].ToString()
}
Hey it is good to read the DataTable values from Data Reader. Try using the linkhttp://dotnetsavvyblog.blogspot.com/2007/11/to-retrieve-fields-using-sqlreader.html.
Do let me know if you still face problems.
PLS Help with query
Please help me with complicated query:
eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where
value in "a" field is dublicate, but leave one of each. in simple sentence I
need to delete invert distinct of field "a".
Please help me with this query
Rows are NOT duplicated, just value in one of fields are
There are primary key in this table
Following the similar explanation of this table
log_id int(11) UNSIGNED auto_increment //ID
rnd_id int(11) //SOMETHING
option_id int(11) //SOMETHING
timestamp DateTime //SOMETHING
ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
host varchar(70) //SOMETHING
agent varchar(80) //SOMETHING
thxHi
You may want to try something like:
DELETE FROM MyTable
FROM MyTable A
WHERE EXISTS ( SELECT * FROM MyTable B WHERE A.IP_addr = B.IP_addr and
A.log_id > B.log_id )
or
DELETE FROM A
FROM MyTable A JOIN MyTable B ON A.IP_addr = B.IP_addr and A.log_id >
B.log_id
John
"Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
news:O44xqCnMEHA.3712@.TK2MSFTNGP10.phx.gbl...
> Please help me with complicated query:
> eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where
> value in "a" field is dublicate, but leave one of each. in simple sentence
I
> need to delete invert distinct of field "a".
> Please help me with this query
> Rows are NOT duplicated, just value in one of fields are
> There are primary key in this table
> Following the similar explanation of this table
> log_id int(11) UNSIGNED auto_increment //ID
> rnd_id int(11) //SOMETHING
> option_id int(11) //SOMETHING
> timestamp DateTime //SOMETHING
> ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
> host varchar(70) //SOMETHING
> agent varchar(80) //SOMETHING
> thx
>
>|||Thank you for response, but
As far as I see it will delete everything.
I need to leave one of each kind
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eu3vAvnMEHA.556@.tk2msftngp13.phx.gbl...
> Hi
> You may want to try something like:
> DELETE FROM MyTable
> FROM MyTable A
> WHERE EXISTS ( SELECT * FROM MyTable B WHERE A.IP_addr = B.IP_addr and
> A.log_id > B.log_id )
> or
> DELETE FROM A
> FROM MyTable A JOIN MyTable B ON A.IP_addr = B.IP_addr and A.log_id >
> B.log_id
> John
> "Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
> news:O44xqCnMEHA.3712@.TK2MSFTNGP10.phx.gbl...
> > Please help me with complicated query:
> > eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where
> > value in "a" field is dublicate, but leave one of each. in simple
sentence
> I
> > need to delete invert distinct of field "a".
> > Please help me with this query
> > Rows are NOT duplicated, just value in one of fields are
> > There are primary key in this table
> > Following the similar explanation of this table
> > log_id int(11) UNSIGNED auto_increment //ID
> > rnd_id int(11) //SOMETHING
> > option_id int(11) //SOMETHING
> > timestamp DateTime //SOMETHING
> > ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
> > host varchar(70) //SOMETHING
> > agent varchar(80) //SOMETHING
> >
> > thx
> >
> >
> >
>|||Hi
It will only delete those that have a higher log_id and the same Ip address.
John
"Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
news:Owbc3aoMEHA.3940@.tk2msftngp13.phx.gbl...
> Thank you for response, but
> As far as I see it will delete everything.
> I need to leave one of each kind
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:eu3vAvnMEHA.556@.tk2msftngp13.phx.gbl...
> > Hi
> >
> > You may want to try something like:
> >
> > DELETE FROM MyTable
> > FROM MyTable A
> > WHERE EXISTS ( SELECT * FROM MyTable B WHERE A.IP_addr = B.IP_addr and
> > A.log_id > B.log_id )
> >
> > or
> >
> > DELETE FROM A
> > FROM MyTable A JOIN MyTable B ON A.IP_addr = B.IP_addr and A.log_id >
> > B.log_id
> >
> > John
> >
> > "Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
> > news:O44xqCnMEHA.3712@.TK2MSFTNGP10.phx.gbl...
> > > Please help me with complicated query:
> > > eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows
where
> > > value in "a" field is dublicate, but leave one of each. in simple
> sentence
> > I
> > > need to delete invert distinct of field "a".
> > > Please help me with this query
> > > Rows are NOT duplicated, just value in one of fields are
> > > There are primary key in this table
> > > Following the similar explanation of this table
> > > log_id int(11) UNSIGNED auto_increment //ID
> > > rnd_id int(11) //SOMETHING
> > > option_id int(11) //SOMETHING
> > > timestamp DateTime //SOMETHING
> > > ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
> > > host varchar(70) //SOMETHING
> > > agent varchar(80) //SOMETHING
> > >
> > > thx
> > >
> > >
> > >
> >
> >
>|||Hi Tamir,
I noticed that the issue was posted in
microsoft.public.sqlserver.programming with the same title. Based on my
test, John's T-SQL statements hit the right answer.
Anyway, if you have follow up questions, please post there and I will be
glad to work with you.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where
value in "a" field is dublicate, but leave one of each. in simple sentence I
need to delete invert distinct of field "a".
Please help me with this query
Rows are NOT duplicated, just value in one of fields are
There are primary key in this table
Following the similar explanation of this table
log_id int(11) UNSIGNED auto_increment //ID
rnd_id int(11) //SOMETHING
option_id int(11) //SOMETHING
timestamp DateTime //SOMETHING
ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
host varchar(70) //SOMETHING
agent varchar(80) //SOMETHING
thxHi
You may want to try something like:
DELETE FROM MyTable
FROM MyTable A
WHERE EXISTS ( SELECT * FROM MyTable B WHERE A.IP_addr = B.IP_addr and
A.log_id > B.log_id )
or
DELETE FROM A
FROM MyTable A JOIN MyTable B ON A.IP_addr = B.IP_addr and A.log_id >
B.log_id
John
"Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
news:O44xqCnMEHA.3712@.TK2MSFTNGP10.phx.gbl...
> Please help me with complicated query:
> eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where
> value in "a" field is dublicate, but leave one of each. in simple sentence
I
> need to delete invert distinct of field "a".
> Please help me with this query
> Rows are NOT duplicated, just value in one of fields are
> There are primary key in this table
> Following the similar explanation of this table
> log_id int(11) UNSIGNED auto_increment //ID
> rnd_id int(11) //SOMETHING
> option_id int(11) //SOMETHING
> timestamp DateTime //SOMETHING
> ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
> host varchar(70) //SOMETHING
> agent varchar(80) //SOMETHING
> thx
>
>|||Thank you for response, but
As far as I see it will delete everything.
I need to leave one of each kind
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eu3vAvnMEHA.556@.tk2msftngp13.phx.gbl...
> Hi
> You may want to try something like:
> DELETE FROM MyTable
> FROM MyTable A
> WHERE EXISTS ( SELECT * FROM MyTable B WHERE A.IP_addr = B.IP_addr and
> A.log_id > B.log_id )
> or
> DELETE FROM A
> FROM MyTable A JOIN MyTable B ON A.IP_addr = B.IP_addr and A.log_id >
> B.log_id
> John
> "Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
> news:O44xqCnMEHA.3712@.TK2MSFTNGP10.phx.gbl...
> > Please help me with complicated query:
> > eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where
> > value in "a" field is dublicate, but leave one of each. in simple
sentence
> I
> > need to delete invert distinct of field "a".
> > Please help me with this query
> > Rows are NOT duplicated, just value in one of fields are
> > There are primary key in this table
> > Following the similar explanation of this table
> > log_id int(11) UNSIGNED auto_increment //ID
> > rnd_id int(11) //SOMETHING
> > option_id int(11) //SOMETHING
> > timestamp DateTime //SOMETHING
> > ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
> > host varchar(70) //SOMETHING
> > agent varchar(80) //SOMETHING
> >
> > thx
> >
> >
> >
>|||Hi
It will only delete those that have a higher log_id and the same Ip address.
John
"Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
news:Owbc3aoMEHA.3940@.tk2msftngp13.phx.gbl...
> Thank you for response, but
> As far as I see it will delete everything.
> I need to leave one of each kind
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:eu3vAvnMEHA.556@.tk2msftngp13.phx.gbl...
> > Hi
> >
> > You may want to try something like:
> >
> > DELETE FROM MyTable
> > FROM MyTable A
> > WHERE EXISTS ( SELECT * FROM MyTable B WHERE A.IP_addr = B.IP_addr and
> > A.log_id > B.log_id )
> >
> > or
> >
> > DELETE FROM A
> > FROM MyTable A JOIN MyTable B ON A.IP_addr = B.IP_addr and A.log_id >
> > B.log_id
> >
> > John
> >
> > "Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
> > news:O44xqCnMEHA.3712@.TK2MSFTNGP10.phx.gbl...
> > > Please help me with complicated query:
> > > eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows
where
> > > value in "a" field is dublicate, but leave one of each. in simple
> sentence
> > I
> > > need to delete invert distinct of field "a".
> > > Please help me with this query
> > > Rows are NOT duplicated, just value in one of fields are
> > > There are primary key in this table
> > > Following the similar explanation of this table
> > > log_id int(11) UNSIGNED auto_increment //ID
> > > rnd_id int(11) //SOMETHING
> > > option_id int(11) //SOMETHING
> > > timestamp DateTime //SOMETHING
> > > ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
> > > host varchar(70) //SOMETHING
> > > agent varchar(80) //SOMETHING
> > >
> > > thx
> > >
> > >
> > >
> >
> >
>|||Hi Tamir,
I noticed that the issue was posted in
microsoft.public.sqlserver.programming with the same title. Based on my
test, John's T-SQL statements hit the right answer.
Anyway, if you have follow up questions, please post there and I will be
glad to work with you.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
Friday, March 9, 2012
Please, please help
CategoryA
Description Value Type
nbbnbnb jjhdjh hde
hjhjjhwjh jdjj j j jjnjnj
Description Value Amount
hbsjbj bj hbhjb $23.45
hsdhsbdh bnnbnb $57.89
CategoryB
Description Value Type
vvvvbvbvb mmkmhh gfjh
hhjjkjkjkh uiuiuyjh ytyuy
Description Value Amount
hbhghgbj huyuyjb $40.89
hsdrerrtdh bmnbnb $234.90
Hi All,
I have to create a report in the above fashion. For this I have created a stored procedure like this
Create proc proc1
declared the temporary table and varibles
insert into @.tempTable(category,Description,Value,Type)
select categoryA,Description,Value,Type from Table A
insert into @.tempTable(category,Description,Value,Amount)
select categoryA,Description,Value,Amount from Table A
--
select * from @.tempTable
end of procedure.
When Iam doing inserts in this way the report i get is like this
In the report I placed two tables from the toolbox onto the report . TableA has {Category,Description Value Type}
columns but Table B has {Category,Description Value Amount}columns
CategoryA
Description Value Type
nbbnbnb jjhdjh hde
hjhjjhwjh jdjj j j jjnjnj
Description Value
hbsjbj bj hbhjb
hsdhsbdh bnnbnb
CategoryB
Description Value Type
vvvvbvbvb mmkmhh gfjh
hhjjkjkjkh uiuiuyjh ytyuy
Description Value
hbhghgbj huyuyjb
hsdrerrtdh bmnbnb
CategoryA
Description Value
nbbnbnb jjhdjh
hjhjjhwjh jdjj j j
Description Value Amount
hbsjbj bj hbhjb $23.45
hsdhsbdh bnnbnb $57.89
CategoryB
Description Value
vvvvbvbvb mmkmhh
hhjjkjkjkh uiuiuyjh
Description Value Amount
hbhghgbj huyuyjb $40.89
hsdrerrtdh bmnbnb $234.90
I hope u guys could see my problem. Please help me to correct this.
Thank u so much
Hi,
you can try using 2 store procedures : 1 for Type and 1 for Amount.
Then, you can bind first store procedure for Table A and second store procedure for Table B.
|||
Hi,
i will try it out as u said. Could u tell me if i could use two stored procedures as scu said. Would be able to answer my question. Because, while i'm building the report there is space for only one stored procedure in the data section. Thank u so much.|||Hi nissan,
you can add a new DataSet (another store proc) in Data Tab selecting dropdown DataSet -> New DataSet.
So, you can refer to 2 DataSet in Layout Tab.
|||
Hi bwageh,
I have the same question again after a long time, How can I show or hide the amount or value based on the grouptype. Could you please please explain me in more detail.
|||Hi bwageh,
I have the same question again after a long time, How can I show or hide the amount or value based on the grouptype. Could you please please explain me in more detail.
Description Value Type
nbbnbnb jjhdjh hde
hjhjjhwjh jdjj j j jjnjnj
Description Value Amount
hbsjbj bj hbhjb $23.45
hsdhsbdh bnnbnb $57.89
CategoryB
Description Value Type
vvvvbvbvb mmkmhh gfjh
hhjjkjkjkh uiuiuyjh ytyuy
Description Value Amount
hbhghgbj huyuyjb $40.89
hsdrerrtdh bmnbnb $234.90
Hi All,
I have to create a report in the above fashion. For this I have created a stored procedure like this
Create proc proc1
declared the temporary table and varibles
insert into @.tempTable(category,Description,Value,Type)
select categoryA,Description,Value,Type from Table A
insert into @.tempTable(category,Description,Value,Amount)
select categoryA,Description,Value,Amount from Table A
--
select * from @.tempTable
end of procedure.
When Iam doing inserts in this way the report i get is like this
In the report I placed two tables from the toolbox onto the report . TableA has {Category,Description Value Type}
columns but Table B has {Category,Description Value Amount}columns
CategoryA
Description Value Type
nbbnbnb jjhdjh hde
hjhjjhwjh jdjj j j jjnjnj
Description Value
hbsjbj bj hbhjb
hsdhsbdh bnnbnb
CategoryB
Description Value Type
vvvvbvbvb mmkmhh gfjh
hhjjkjkjkh uiuiuyjh ytyuy
Description Value
hbhghgbj huyuyjb
hsdrerrtdh bmnbnb
CategoryA
Description Value
nbbnbnb jjhdjh
hjhjjhwjh jdjj j j
Description Value Amount
hbsjbj bj hbhjb $23.45
hsdhsbdh bnnbnb $57.89
CategoryB
Description Value
vvvvbvbvb mmkmhh
hhjjkjkjkh uiuiuyjh
Description Value Amount
hbhghgbj huyuyjb $40.89
hsdrerrtdh bmnbnb $234.90
I hope u guys could see my problem. Please help me to correct this.
Thank u so much
Hi,
you can try using 2 store procedures : 1 for Type and 1 for Amount.
Then, you can bind first store procedure for Table A and second store procedure for Table B.
|||
u can build your query in the following way
select Category, 1 as grouptyp, Description , Value , Type, 0 as amount from Table A
union
select Category, , 2 as grouptyp , Description , Value , '' as Type, amount from Table A
then u can group your reporty by Category and grouptyp
them show or hide the amount or value based on the grouptype
hope it works
Bolos
Hi,
how can i use two stored procedures. Because, while i'm building the report there is space for only one stored procedure right in the data section. Could explain me in more detail.
Thank u so much.
bwageh wrote:
u can build your query in the following way
select Category, 1 as grouptyp, Description , Value , Type, 0 as amount from Table A
union
select Category, , 2 as grouptyp , Description , Value , '' as Type, amount from Table A
then u can group your reporty by Category and grouptyp
them show or hide the amount or value based on the grouptype
hope it works
Bolos
Hi,
i will try it out as u said. Could u tell me if i could use two stored procedures as scu said. Would be able to answer my question. Because, while i'm building the report there is space for only one stored procedure in the data section. Thank u so much.|||Hi nissan,
you can add a new DataSet (another store proc) in Data Tab selecting dropdown DataSet -> New DataSet.
So, you can refer to 2 DataSet in Layout Tab.
|||
u can build your query in the following way
select Category, 1 as grouptyp, Description , Value , Type, 0 as amount from Table A
union
select Category, , 2 as grouptyp , Description , Value , '' as Type, amount from Table A
then u can group your reporty by Category and grouptyp
them show or hide the amount or value based on the grouptype
hope it works
Bolos
Hi bwageh,
I have the same question again after a long time, How can I show or hide the amount or value based on the grouptype. Could you please please explain me in more detail.
|||Hi bwageh,
I have the same question again after a long time, How can I show or hide the amount or value based on the grouptype. Could you please please explain me in more detail.
Wednesday, March 7, 2012
PLEASE PLEASE HELP - How can I get a return value from a SQL Stored Proc is ASP.NET?
Hi. I'm sorry to bother all of you, but I have spent two days looking
at code samples all over the internet, and I can not get a single one
of them to work for me. I am simply trying to get a value returned to
the ASP from a stored procedure. The error I am getting is: Item can
not be found in the collection corresponding to the requested name or
ordinal.
Just a few pointers here:
- creating a parameter will just create a parameter. To use it, you need
to add it to the command object using oSProc.Parameters.Append
- in a stored procedure you can only use the RETURN keyword to return an
integer, so @.sRetUPC is out of the question
- if you want to use the value of the output parameter, then you should
access it through the Parameters collection of the Command object. The
syntax you are currently using refers to the resultset, but the stored
procedure does not have one
oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
>
Then, I added the line oSproc.Execute()
After that is:
Dim res
res = oSproc.Parameters.Item("sRetUPC").Value.toString()
>
This is not working. Do you know how I can get access to the value of
the parameter that is returned?
Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?
oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
>
Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?
>
Since I don't even know how your code looks like right now, just two
notes:
>
1) Use parameter names with leading @.. The underlying provider may
prefer that.
>
2) Use adParamInputOutput for the output value. T-SQL does not have any
true output-only parameters. (Save the return value, but there is a
separate enum value for return values as I recall.)
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Why are you using stored proc when you can use a function(s)? I always
thought the output parameter was a bit of a hack and clumsy to use.
Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?
Since then you changed the code according to Gert-Jan's advice, and we
don't know what it looked after that.
>
Since then you changed the code according to Gert-Jan's advice, and we
don't know what it looked after that.
>
Basically, if you only say "not working" without specifying why, and
don't show us the code, don't expect that much help. But that's your call.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||jbonifacejr (jbonifacejr@.hotmail.com) writes:
Posting the same question independently to two forums is not a nice
thing to. This means that people can waste time on answering your post,
when it has already been answered elsewhere.
Again, without seeing your code it's hard to tell. There is a difference
between ADO and SqlClient though: with ADO, the parameter names are
just local to the application, so if you misspell a parameter name,
you may get away with it. Not so with SqlClient.
Since your procedure has an output parameter, but no result set, the most
conventient method to use is ExecuteNonQuery, in which case you only need
the Command object.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
at code samples all over the internet, and I can not get a single one
of them to work for me. I am simply trying to get a value returned to
the ASP from a stored procedure. The error I am getting is: Item can
not be found in the collection corresponding to the requested name or
ordinal.
Here is my Stored Procedure code.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Go
ALTER PROCEDURE [dbo].[sprocRetUPC]
@.sUPC varchar(50),
@.sRetUPC varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @.sRetUPC = (SELECT bcdDVD_Title FROM tblBarcodes WHERE bcdUPC =
@.sUPC)
RETURN @.sRetUPC
END
Here is my ASP.NET code.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim oConnSQL As ADODB.Connection
oConnSQL = New ADODB.Connection
oConnSQL.ConnectionString = "DSN=BarcodeSQL"
oConnSQL.Open()
Dim oSproc As ADODB.Command
oSproc = New ADODB.Command
oSproc.ActiveConnection = oConnSQL
oSproc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
oSproc.CommandText = "sprocRetUPC"
Dim oParam1
Dim oParam2
oParam1 = oSproc.CreateParameter("sRetUPC",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamOutput, 50)
oParam2 = oSproc.CreateParameter("sUPC", ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "043396005396")
Dim res
res = oSproc("sRetUPC")
Response.Write(res.ToString())
End Sub
If I put the line -
oSproc.Execute()
above the "Dim res" line, I end up with the following error:
Procedure or function 'sprocRetUPC' expects parameter '@.sUPC', which
was not supplied. I thought that oParam2 was the parameter. I was also
under the assumption that the return parameter has to be declared
first. What am I doing wrong here?jbonifacejr wrote:
Quote:
Originally Posted by
>
Hi. I'm sorry to bother all of you, but I have spent two days looking
at code samples all over the internet, and I can not get a single one
of them to work for me. I am simply trying to get a value returned to
the ASP from a stored procedure. The error I am getting is: Item can
not be found in the collection corresponding to the requested name or
ordinal.
>
Here is my Stored Procedure code.
>
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Go
ALTER PROCEDURE [dbo].[sprocRetUPC]
@.sUPC varchar(50),
@.sRetUPC varchar(50) OUTPUT
>
AS
>
BEGIN
SET NOCOUNT ON;
SET @.sRetUPC = (SELECT bcdDVD_Title FROM tblBarcodes WHERE bcdUPC =
@.sUPC)
RETURN @.sRetUPC
>
END
>
Here is my ASP.NET code.
>
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
>
Dim oConnSQL As ADODB.Connection
>
oConnSQL = New ADODB.Connection
oConnSQL.ConnectionString = "DSN=BarcodeSQL"
oConnSQL.Open()
>
Dim oSproc As ADODB.Command
oSproc = New ADODB.Command
oSproc.ActiveConnection = oConnSQL
oSproc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
oSproc.CommandText = "sprocRetUPC"
>
Dim oParam1
Dim oParam2
oParam1 = oSproc.CreateParameter("sRetUPC",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamOutput, 50)
oParam2 = oSproc.CreateParameter("sUPC", ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "043396005396")
>
Dim res
res = oSproc("sRetUPC")
>
Response.Write(res.ToString())
>
End Sub
>
If I put the line -
oSproc.Execute()
>
above the "Dim res" line, I end up with the following error:
Procedure or function 'sprocRetUPC' expects parameter '@.sUPC', which
was not supplied. I thought that oParam2 was the parameter. I was also
under the assumption that the return parameter has to be declared
first. What am I doing wrong here?
Just a few pointers here:
- creating a parameter will just create a parameter. To use it, you need
to add it to the command object using oSProc.Parameters.Append
- in a stored procedure you can only use the RETURN keyword to return an
integer, so @.sRetUPC is out of the question
- if you want to use the value of the output parameter, then you should
access it through the Parameters collection of the Command object. The
syntax you are currently using refers to the resultset, but the stored
procedure does not have one
HTH,
Gert-Jan|||Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...
I removed the Return @.sRetUPC line. I am guessing that I can rely on
the set @.sRetUPC line to set the value of the output parameter
Quote:
Originally Posted by
>From there, I appended the parameters in the ASP code...like this
oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
Then, I added the line oSproc.Execute()
After that is:
Dim res
res = oSproc.Parameters.Item("sRetUPC").Value.toString()
This is not working. Do you know how I can get access to the value of
the parameter that is returned?
Quote:
Originally Posted by
Just a few pointers here:
- creating a parameter will just create a parameter. To use it, you need
to add it to the command object using oSProc.Parameters.Append
- in a stored procedure you can only use the RETURN keyword to return an
integer, so @.sRetUPC is out of the question
- if you want to use the value of the output parameter, then you should
access it through the Parameters collection of the Command object. The
syntax you are currently using refers to the resultset, but the stored
procedure does not have one
>
HTH,
Gert-Jan
Quote:
Originally Posted by
Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...
>
I removed the Return @.sRetUPC line. I am guessing that I can rely on
the set @.sRetUPC line to set the value of the output parameter
>
Quote:
Originally Posted by
>>From there, I appended the parameters in the ASP code...like this
oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
>
Then, I added the line oSproc.Execute()
After that is:
Dim res
res = oSproc.Parameters.Item("sRetUPC").Value.toString()
>
This is not working. Do you know how I can get access to the value of
the parameter that is returned?
Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?
Since I don't even know how your code looks like right now, just two
notes:
1) Use parameter names with leading @.. The underlying provider may
prefer that.
2) Use adParamInputOutput for the output value. T-SQL does not have any
true output-only parameters. (Save the return value, but there is a
separate enum value for return values as I recall.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||If you look at the top post you will see where I put the code I am
using. I also tried to let people know what happened when I tried their
suggestions. But, thanks for the advice...and I'll look at those SQL
Books online.
Jan
Erland Sommarskog wrote:
Quote:
Originally Posted by
jbonifacejr (jbonifacejr@.hotmail.com) writes:
Quote:
Originally Posted by
Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...
I removed the Return @.sRetUPC line. I am guessing that I can rely on
the set @.sRetUPC line to set the value of the output parameter
Quote:
Originally Posted by
>From there, I appended the parameters in the ASP code...like this
oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
Then, I added the line oSproc.Execute()
After that is:
Dim res
res = oSproc.Parameters.Item("sRetUPC").Value.toString()
This is not working. Do you know how I can get access to the value of
the parameter that is returned?
>
Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?
>
Since I don't even know how your code looks like right now, just two
notes:
>
1) Use parameter names with leading @.. The underlying provider may
prefer that.
>
2) Use adParamInputOutput for the output value. T-SQL does not have any
true output-only parameters. (Save the return value, but there is a
separate enum value for return values as I recall.)
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Why are you using stored proc when you can use a function(s)? I always
thought the output parameter was a bit of a hack and clumsy to use.
jbonifacejr wrote:
Quote:
Originally Posted by
If you look at the top post you will see where I put the code I am
using. I also tried to let people know what happened when I tried their
suggestions. But, thanks for the advice...and I'll look at those SQL
Books online.
>
Jan
>
Erland Sommarskog wrote:
Quote:
Originally Posted by
jbonifacejr (jbonifacejr@.hotmail.com) writes:
Quote:
Originally Posted by
Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...
>
I removed the Return @.sRetUPC line. I am guessing that I can rely on
the set @.sRetUPC line to set the value of the output parameter
>
>>From there, I appended the parameters in the ASP code...like this
oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
>
Then, I added the line oSproc.Execute()
After that is:
Dim res
res = oSproc.Parameters.Item("sRetUPC").Value.toString()
>
This is not working. Do you know how I can get access to the value of
the parameter that is returned?
Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?
Since I don't even know how your code looks like right now, just two
notes:
1) Use parameter names with leading @.. The underlying provider may
prefer that.
2) Use adParamInputOutput for the output value. T-SQL does not have any
true output-only parameters. (Save the return value, but there is a
separate enum value for return values as I recall.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Quote:
Originally Posted by
If you look at the top post you will see where I put the code I am
using.
Since then you changed the code according to Gert-Jan's advice, and we
don't know what it looked after that.
Basically, if you only say "not working" without specifying why, and
don't show us the code, don't expect that much help. But that's your call.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland...I see where I screwed up. I thought I had explained
what was wrong, but I did that in a different thread in another forum.
Anyway, I got this working using classic ASP and ADODB. Now I am going
to try to get it working over ASP.NET and ADO.NET. Wish me luck.
So far, eveerything works except that I am constantly being told that
the stored procedure expects a parameter that was not supplied.
However, The same two parameters are created and added to the
Parameters of the command object.
I'll continue to work on it and see if I can get it to work. Looks like
I need a datareader or some other object. I found a great KB article
that basically shows me everythig I am doing (right and wrong)...
http://support.microsoft.com/kb/306574
Erland Sommarskog wrote:
Quote:
Originally Posted by
jbonifacejr (jbonifacejr@.hotmail.com) writes:
Quote:
Originally Posted by
If you look at the top post you will see where I put the code I am
using.
>
Since then you changed the code according to Gert-Jan's advice, and we
don't know what it looked after that.
>
Basically, if you only say "not working" without specifying why, and
don't show us the code, don't expect that much help. But that's your call.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||jbonifacejr (jbonifacejr@.hotmail.com) writes:
Quote:
Originally Posted by
Thanks Erland...I see where I screwed up. I thought I had explained
what was wrong, but I did that in a different thread in another forum.
Posting the same question independently to two forums is not a nice
thing to. This means that people can waste time on answering your post,
when it has already been answered elsewhere.
Quote:
Originally Posted by
Anyway, I got this working using classic ASP and ADODB. Now I am going
to try to get it working over ASP.NET and ADO.NET. Wish me luck.
>
So far, eveerything works except that I am constantly being told that
the stored procedure expects a parameter that was not supplied.
However, The same two parameters are created and added to the
Parameters of the command object.
Again, without seeing your code it's hard to tell. There is a difference
between ADO and SqlClient though: with ADO, the parameter names are
just local to the application, so if you misspell a parameter name,
you may get away with it. Not so with SqlClient.
Quote:
Originally Posted by
I'll continue to work on it and see if I can get it to work. Looks like
I need a datareader or some other object.
Since your procedure has an output parameter, but no result set, the most
conventient method to use is ExecuteNonQuery, in which case you only need
the Command object.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Saturday, February 25, 2012
Please Help: Error: Missing semicolon (;) at end of SQL statement.
Hey
I am trying to retieve a value from teh database and add one to it, then update the database with thenew value before redirecting to a page.
I am recieving this error and don't know why, i have the following coed below.
Dim objReaderQ as OleDBDataReaderDim strSQLRead As String
Dim objCmd As New OleDbCommandstrSQLRead ="Select Quantity from tblCart Where (Productid=" & intProdidHold & ") AND (Cartid='" & strCartid & "')"
objCmd = new OleDbCommand(strSQLRead, objConn)
objReaderQ = objCmd.ExecuteReader()if objReaderQ.Read()
'update quantity by 1Dim i as integer
i = objReaderQ("quantity")
i = i + 1objReaderQ.Close()
Dim strSQLQuantity as String = "INSERT INTO tblCart (Quantity) VALUES (@.quantity) WHERE (productid=" & intProdidHold & ") AND (Cartid='" & strCartid & "');"
Dim objCmdQuantity As New OleDbCommand(strSQLQuantity, objConn)
objCmdQuantity.Connection = objConn
objCmdQuantity.Parameters.Add("@.quantity", OleDbType.VarChar, 255)
objCmdQuantity.Parameters("@.quantity").Value = iobjCmdQuantity.ExecuteNonQuery() ' <-- Error Is Occuring On This Line
Response.Redirect("ViewBasket.aspx")
end if
I really can't see what is wrong as i have placed the semi colon it wanted at the end of the string.
Thanks you for your time
ChrisHey,
I have solved this problem, wrong sql statement, should be update! lol :(
But i do have the problem that once go to the viewbasket.aspx page it shows the product with the quantity 1, as it pulls the quantity from the db and its default value is 1 (which is correct).
But now if that same product is clicked 'Add To basket' for a second time, it executes the above code and goes to the viewbasket.aspx page, but the quantity stays as 1 ! (should be 2)
And now if that same product is clicked 'Add To basket' for a third time, it executes the above code again and goes to the viewbasket.aspx page, but this time the quantity is 2 ! (should be 3)
From this point on the code worked fine and increments the number properly, 4,5,6 etc..
Any idea why the first two clicks dont work ?
Thank you for your help
Chris
Please help. How to set a Parameter in SSRS in runtime? Parameters!myParam.Value property is rea
You can set the default value of a reporting services parameter by any expression.
But with code I'm NOT allowed to do:
Parameters!myParam.Value = CDate("01.01.2007")
This is cause Value property is read-only.
So the question would be:
Are there any way I set a parameter value in runtime ?
Hope anyone can help...
Use VB.Net or C# code (Report -> Report Properties -> Code tab) to store the parameter in some shared variable. Use this variable to set and retrieve value whenever and wherever you need in the report by using the syntax Code.MySharedVar
Shyam
|||Hi Shyam,
I have tried that also, but the problem is that the calendar control turn into a drop-down combo control when you apply either code or connect it to a dataset.
The parameter is a datetime type. As long as you add logic to the "default value" it showns the calendar control, if you add anything to "available values" the calendar disappears and a combo is shown. Any ida what I can do to keep it displaying the calendar control ?
|||I guess you are raising a new issue now. I dont think your issue can be solved at the reporting services level.
Subscribe to:
Posts (Atom)