Parse SQL Transaction log in .NET

I want to parse transaction log file.

Actually, i need to trace out changes in my database.[either by insertion/updation/deletion of data or adding/modifiying object.] within my application.

I study SQL Profile. But it is limited for its run and also resource hunger or extra burden on server in case of large size database and busy server.

So please guide me. Also suggest be its appropriate solution.

Thanks.

You could create internal trace in SQL Profiler format for SQL Server. You could do it using sp:

sp_trace_create/sp_trace_setvent

|||

May you guide little bit more or refer me any link for further guide... either parsing to transaction log or using any other mean.

Thansk

Jhan Zaib

|||Hi,

http://www.databasejournal.com/features/mssql/article.php/10894_2239461_2

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

ParameterValueClass for setting Parameters Property of ReportViewer

I am trying to set the ReportViewer (the one that runs as a .net control)
Parameters property, which is looking for an array of ParameterValue
objects. Where is the ParameterValue object defined? I cannot find it in
the ReportViewer, or the Web Services. Any ideas how to pass a set of
parameneters to the Parameters property of the ReportViewer?
Thanx, BobAre you referring to the ReportViewer sample control that ships with
Reporting Services?
The Parameters property of the control refers specifically to the parameters
area of the toolbar which can display input fields for report parameters.
This is not a property that can be used to pass report parameters. The
ReportViewer sample utilizes the report server built in parameters support.
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"Bob Feller" <bob@.nospam.morningdew.net> wrote in message
news:eoz$ml2aEHA.1644@.tk2msftngp13.phx.gbl...
> I am trying to set the ReportViewer (the one that runs as a .net control)
> Parameters property, which is looking for an array of ParameterValue
> objects. Where is the ParameterValue object defined? I cannot find it in
> the ReportViewer, or the Web Services. Any ideas how to pass a set of
> parameneters to the Parameters property of the ReportViewer?
> Thanx, Bob
>

Parameters to insert data from form into SQL database

Hi,
I'm having problem inserting and storing data from asp.net web form to
SQL database. I use the following parameters for the SqlCommand object
to do the insert:
cmdTest.Parameters.Add(New SqlParameter("@.FirstN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.LastN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.Org",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Addr1",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.City",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Email",
SqlDbType.NVarChar, 50))
cmdTest.Parameters("@.FirstN").Value = "FirstN"
cmdTest.Parameters("@.LastN").Value = "LastN"
cmdTest.Parameters("@.Org").Value = "Org"
cmdTest.Parameters("@.Addr1").Value = "Addr1"
cmdTest.Parameters("@.City").Value = "City"
cmdTest.Parameters("@.Email").Value = "Email"
It seems like the value of the fields didn't get inserted when I submit
the page. All I'm getting is just the names of the fields (e.g. FirstN,
LastN), not the values themselves (e.g. John, Doe).
Can anyone help me?
Thanks,
hfk0
That's what you inserted. :-)
Try:
cmdTest.Parameters("@.FirstN").Value = "John"
cmdTest.Parameters("@.LastN").Value = "Doe"
cmdTest.Parameters("@.Org").Value = "FLy by Night Airlines"
cmdTest.Parameters("@.Addr1").Value = "123 Main St"
cmdTest.Parameters("@.City").Value = "Anytown"
cmdTest.Parameters("@.Email").Value = nospam@.nospam.com
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<hery@.infoventures.com> wrote in message
news:1141152263.896973.136620@.p10g2000cwp.googlegr oups.com...
Hi,
I'm having problem inserting and storing data from asp.net web form to
SQL database. I use the following parameters for the SqlCommand object
to do the insert:
cmdTest.Parameters.Add(New SqlParameter("@.FirstN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.LastN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.Org",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Addr1",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.City",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Email",
SqlDbType.NVarChar, 50))
cmdTest.Parameters("@.FirstN").Value = "FirstN"
cmdTest.Parameters("@.LastN").Value = "LastN"
cmdTest.Parameters("@.Org").Value = "Org"
cmdTest.Parameters("@.Addr1").Value = "Addr1"
cmdTest.Parameters("@.City").Value = "City"
cmdTest.Parameters("@.Email").Value = "Email"
It seems like the value of the fields didn't get inserted when I submit
the page. All I'm getting is just the names of the fields (e.g. FirstN,
LastN), not the values themselves (e.g. John, Doe).
Can anyone help me?
Thanks,
hfk0
|||Hi Tom,
Oh ok I get it now...I guess whatever I put within the "" is what is
inserted.
I'm also adding the following parameters:
cmdTest.Parameters.Add(New SqlParameter("@.State", SqlDbType.NChar, 2))
cmdTest.Parameters.Add(New SqlParameter("@.Phone", SqlDbType.NChar, 10))
cmdTest.Parameters("@.State").Value = State.SelectedItem.Value
cmdTest.Parameters("@.Phone").Value = Phone.Text
When viewing the page, somehow these two values didn't get inserted to
the database.
Did I use the wrong data type?
Thanks again,
hfk0
|||I'd check the database directly with Query Analyzer (QA) if you're using SQL
2000 or SQL Server Management Studio (SSMS) if you're using SQL 2005. Also,
consider using SQL Profiler to see what is being sent to SQL Server. There
could be a problem in your VB .NET code somewhere.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<hery@.infoventures.com> wrote in message
news:1141156805.957210.276400@.j33g2000cwa.googlegr oups.com...
Hi Tom,
Oh ok I get it now...I guess whatever I put within the "" is what is
inserted.
I'm also adding the following parameters:
cmdTest.Parameters.Add(New SqlParameter("@.State", SqlDbType.NChar, 2))
cmdTest.Parameters.Add(New SqlParameter("@.Phone", SqlDbType.NChar, 10))
cmdTest.Parameters("@.State").Value = State.SelectedItem.Value
cmdTest.Parameters("@.Phone").Value = Phone.Text
When viewing the page, somehow these two values didn't get inserted to
the database.
Did I use the wrong data type?
Thanks again,
hfk0

Parameters to insert data from form into SQL database

Hi,
I'm having problem inserting and storing data from asp.net web form to
SQL database. I use the following parameters for the SqlCommand object
to do the insert:
cmdTest.Parameters.Add(New SqlParameter("@.FirstN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.LastN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.Org",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Addr1",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.City",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Email",
SqlDbType.NVarChar, 50))
cmdTest.Parameters("@.FirstN").Value = "FirstN"
cmdTest.Parameters("@.LastN").Value = "LastN"
cmdTest.Parameters("@.Org").Value = "Org"
cmdTest.Parameters("@.Addr1").Value = "Addr1"
cmdTest.Parameters("@.City").Value = "City"
cmdTest.Parameters("@.Email").Value = "Email"
It seems like the value of the fields didn't get inserted when I submit
the page. All I'm getting is just the names of the fields (e.g. FirstN,
LastN), not the values themselves (e.g. John, Doe).
Can anyone help me?
Thanks,
hfk0That's what you inserted. :-)
Try:
cmdTest.Parameters("@.FirstN").Value = "John"
cmdTest.Parameters("@.LastN").Value = "Doe"
cmdTest.Parameters("@.Org").Value = "FLy by Night Airlines"
cmdTest.Parameters("@.Addr1").Value = "123 Main St"
cmdTest.Parameters("@.City").Value = "Anytown"
cmdTest.Parameters("@.Email").Value = nospam@.nospam.com
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<hery@.infoventures.com> wrote in message
news:1141152263.896973.136620@.p10g2000cwp.googlegroups.com...
Hi,
I'm having problem inserting and storing data from asp.net web form to
SQL database. I use the following parameters for the SqlCommand object
to do the insert:
cmdTest.Parameters.Add(New SqlParameter("@.FirstN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.LastN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.Org",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Addr1",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.City",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Email",
SqlDbType.NVarChar, 50))
cmdTest.Parameters("@.FirstN").Value = "FirstN"
cmdTest.Parameters("@.LastN").Value = "LastN"
cmdTest.Parameters("@.Org").Value = "Org"
cmdTest.Parameters("@.Addr1").Value = "Addr1"
cmdTest.Parameters("@.City").Value = "City"
cmdTest.Parameters("@.Email").Value = "Email"
It seems like the value of the fields didn't get inserted when I submit
the page. All I'm getting is just the names of the fields (e.g. FirstN,
LastN), not the values themselves (e.g. John, Doe).
Can anyone help me?
Thanks,
hfk0|||Hi Tom,
Oh ok I get it now...I guess whatever I put within the "" is what is
inserted.
I'm also adding the following parameters:
cmdTest.Parameters.Add(New SqlParameter("@.State", SqlDbType.NChar, 2))
cmdTest.Parameters.Add(New SqlParameter("@.Phone", SqlDbType.NChar, 10))
cmdTest.Parameters("@.State").Value = State.SelectedItem.Value
cmdTest.Parameters("@.Phone").Value = Phone.Text
When viewing the page, somehow these two values didn't get inserted to
the database.
Did I use the wrong data type?
Thanks again,
hfk0|||I'd check the database directly with Query Analyzer (QA) if you're using SQL
2000 or SQL Server Management Studio (SSMS) if you're using SQL 2005. Also,
consider using SQL Profiler to see what is being sent to SQL Server. There
could be a problem in your VB .NET code somewhere.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<hery@.infoventures.com> wrote in message
news:1141156805.957210.276400@.j33g2000cwa.googlegroups.com...
Hi Tom,
Oh ok I get it now...I guess whatever I put within the "" is what is
inserted.
I'm also adding the following parameters:
cmdTest.Parameters.Add(New SqlParameter("@.State", SqlDbType.NChar, 2))
cmdTest.Parameters.Add(New SqlParameter("@.Phone", SqlDbType.NChar, 10))
cmdTest.Parameters("@.State").Value = State.SelectedItem.Value
cmdTest.Parameters("@.Phone").Value = Phone.Text
When viewing the page, somehow these two values didn't get inserted to
the database.
Did I use the wrong data type?
Thanks again,
hfk0

Parameters to insert data from form into SQL database

Hi,
I'm having problem inserting and storing data from asp.net web form to
SQL database. I use the following parameters for the SqlCommand object
to do the insert:
cmdTest.Parameters.Add(New SqlParameter("@.FirstN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.LastN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.Org",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Addr1",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.City",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Email",
SqlDbType.NVarChar, 50))
cmdTest.Parameters("@.FirstN").Value = "FirstN"
cmdTest.Parameters("@.LastN").Value = "LastN"
cmdTest.Parameters("@.Org").Value = "Org"
cmdTest.Parameters("@.Addr1").Value = "Addr1"
cmdTest.Parameters("@.City").Value = "City"
cmdTest.Parameters("@.Email").Value = "Email"
It seems like the value of the fields didn't get inserted when I submit
the page. All I'm getting is just the names of the fields (e.g. FirstN,
LastN), not the values themselves (e.g. John, Doe).
Can anyone help me?
Thanks,
hfk0That's what you inserted. :-)
Try:
cmdTest.Parameters("@.FirstN").Value = "John"
cmdTest.Parameters("@.LastN").Value = "Doe"
cmdTest.Parameters("@.Org").Value = "FLy by Night Airlines"
cmdTest.Parameters("@.Addr1").Value = "123 Main St"
cmdTest.Parameters("@.City").Value = "Anytown"
cmdTest.Parameters("@.Email").Value = nospam@.nospam.com
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<hery@.infoventures.com> wrote in message
news:1141152263.896973.136620@.p10g2000cwp.googlegroups.com...
Hi,
I'm having problem inserting and storing data from asp.net web form to
SQL database. I use the following parameters for the SqlCommand object
to do the insert:
cmdTest.Parameters.Add(New SqlParameter("@.FirstN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.LastN",
SqlDbType.NVarChar, 25))
cmdTest.Parameters.Add(New SqlParameter("@.Org",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Addr1",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.City",
SqlDbType.NVarChar, 50))
cmdTest.Parameters.Add(New SqlParameter("@.Email",
SqlDbType.NVarChar, 50))
cmdTest.Parameters("@.FirstN").Value = "FirstN"
cmdTest.Parameters("@.LastN").Value = "LastN"
cmdTest.Parameters("@.Org").Value = "Org"
cmdTest.Parameters("@.Addr1").Value = "Addr1"
cmdTest.Parameters("@.City").Value = "City"
cmdTest.Parameters("@.Email").Value = "Email"
It seems like the value of the fields didn't get inserted when I submit
the page. All I'm getting is just the names of the fields (e.g. FirstN,
LastN), not the values themselves (e.g. John, Doe).
Can anyone help me?
Thanks,
hfk0|||Hi Tom,
Oh ok I get it now...I guess whatever I put within the "" is what is
inserted.
I'm also adding the following parameters:
cmdTest.Parameters.Add(New SqlParameter("@.State", SqlDbType.NChar, 2))
cmdTest.Parameters.Add(New SqlParameter("@.Phone", SqlDbType.NChar, 10))
cmdTest.Parameters("@.State").Value = State.SelectedItem.Value
cmdTest.Parameters("@.Phone").Value = Phone.Text
When viewing the page, somehow these two values didn't get inserted to
the database.
Did I use the wrong data type?
Thanks again,
hfk0|||I'd check the database directly with Query Analyzer (QA) if you're using SQL
2000 or SQL Server Management Studio (SSMS) if you're using SQL 2005. Also,
consider using SQL Profiler to see what is being sent to SQL Server. There
could be a problem in your VB .NET code somewhere.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<hery@.infoventures.com> wrote in message
news:1141156805.957210.276400@.j33g2000cwa.googlegroups.com...
Hi Tom,
Oh ok I get it now...I guess whatever I put within the "" is what is
inserted.
I'm also adding the following parameters:
cmdTest.Parameters.Add(New SqlParameter("@.State", SqlDbType.NChar, 2))
cmdTest.Parameters.Add(New SqlParameter("@.Phone", SqlDbType.NChar, 10))
cmdTest.Parameters("@.State").Value = State.SelectedItem.Value
cmdTest.Parameters("@.Phone").Value = Phone.Text
When viewing the page, somehow these two values didn't get inserted to
the database.
Did I use the wrong data type?
Thanks again,
hfk0

parameters show previous value on second execution of report

I'm running a crystal report from c#.net application, taking parameters from textboxes on the screen, using the parameters to do the data selection, and then printing the parameters in the heading of the report.

The first time I run the report, it displays properly, with the correct info in the heading. If I change the parameters and execute it again, the data on the report changes, but the heading still shows the original parameters that I entered.

Has anyone else encountered a problem like this? Or can you suggest a solution?

Thanks,
ChrisPost the code you are using for displaying the report.|||I solved this already by using a trick that fixes another crystal problem with the navigation end page button: I commented the code in the navigate method, and called my PopulateReport method from the page load event.

I don't know why this works, but it solved the problem.

Parameters ignored, Render report from VB .Net application

I seem to be unable to pass parameters from a VB .Net application to Reporting Services.

Before I added the parameters to the query, I got all the rows back. So I know the application is basically working. Now that I have added the parameters, I get nothing.

I thought the SetExecutionParameters function would help, but my syntax is wrong and it fails.

I would appreciate any hint as to what step I am missing.

Report setup:

Parameters

Name Value
@.Report =Parameters!Report.Value
@.Corp =Parameters!Corp.Value
@.Dept =Parameters!Dept.Value

Query conditions

WHERE Report = 'BudgetVarianceSummary'
AND PeriodEnd = CONVERT(DateTime,CONVERT(char,GETDATE()- DATEPART(day,GETDATE()),112))
AND Report = @.Report
AND Corp = @.Corp
AND Dept = @.Dept

VB Code snippet:

Dim reportPath As String = "/FinancialReports/BudgetVarianceSummary"
Dim format As String = "PDF"

' Prepare report parameter.
Dim parameters(3) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "Report"
parameters(0).Value = "BudgetVarianceSummary"
parameters(1) = New ParameterValue()
parameters(1).Name = "Corp"
parameters(1).Value = "10"
parameters(2) = New ParameterValue()
parameters(2).Name = "Dept"
parameters(2).Value = "7255"

Dim execInfo As New ExecutionInfo
Dim execHeader As New ExecutionHeader()
Dim SessionId As String
Dim extension As String = ""

rs.ExecutionHeaderValue = execHeader
execInfo = rs.LoadReport(reportPath, historyID)

'This line of code fails
'rs.SetExecutionParameters(parameters, "en-us")

result = rs.Render(format, devInfo, extension, encoding, mimeType, warnings, streamIDs)

Dim DateiName As String = Benutzer.Benutzer & "_" & Date.Now.ToString("yyMMdd_HHMMss") & ".pdf"

Dim rs As New ReportingServices.ReportExecutionService

Dim Info As New ReportingServices.ExecutionInfo

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim parameters(1) As ReportingServices.ParameterValue

parameters(0) = New ReportingServices.ParameterValue()

parameters(0).Name = "KB"

parameters(0).Value = _KB

parameters(1) = New ReportingServices.ParameterValue()

parameters(1).Name = "KBG"

parameters(1).Value = CStr(_IDKBG)

Info = rs.LoadReport("/SOR/KBGDruck10", Nothing)

rs.SetExecutionParameters(parameters, "de-ch")

Dim results() As Byte

results = rs.Render("PDF", "", "", "", "", Nothing, Nothing)

Dim Stream As System.IO.FileStream = System.IO.File.OpenWrite(DateiName)

Stream.Write(results, 0, results.Length)

Stream.Close()

Windows.Forms.Cursor.Current = Cursors.Default

Try

Diagnostics.Process.Start(DateiName)

Catch ex As Exception

End Try

XInfo("", "")

Parameters ignored, Render report from VB .Net application

I seem to be unable to pass parameters from a VB .Net application to Reporting Services.

Before I added the parameters to the query, I got all the rows back. So I know the application is basically working. Now that I have added the parameters, I get nothing.

I thought the SetExecutionParameters function would help, but my syntax is wrong and it fails.

I would appreciate any hint as to what step I am missing.

Report setup:

Parameters

Name Value
@.Report =Parameters!Report.Value
@.Corp =Parameters!Corp.Value
@.Dept =Parameters!Dept.Value

Query conditions

WHERE Report = 'BudgetVarianceSummary'
AND PeriodEnd = CONVERT(DateTime,CONVERT(char,GETDATE()- DATEPART(day,GETDATE()),112))
AND Report = @.Report
AND Corp = @.Corp
AND Dept = @.Dept

VB Code snippet:

Dim reportPath As String = "/FinancialReports/BudgetVarianceSummary"
Dim format As String = "PDF"

' Prepare report parameter.
Dim parameters(3) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "Report"
parameters(0).Value = "BudgetVarianceSummary"
parameters(1) = New ParameterValue()
parameters(1).Name = "Corp"
parameters(1).Value = "10"
parameters(2) = New ParameterValue()
parameters(2).Name = "Dept"
parameters(2).Value = "7255"

Dim execInfo As New ExecutionInfo
Dim execHeader As New ExecutionHeader()
Dim SessionId As String
Dim extension As String = ""

rs.ExecutionHeaderValue = execHeader
execInfo = rs.LoadReport(reportPath, historyID)

'This line of code fails
'rs.SetExecutionParameters(parameters, "en-us")

result = rs.Render(format, devInfo, extension, encoding, mimeType, warnings, streamIDs)

Dim DateiName As String = Benutzer.Benutzer & "_" & Date.Now.ToString("yyMMdd_HHMMss") & ".pdf"

Dim rs As New ReportingServices.ReportExecutionService

Dim Info As New ReportingServices.ExecutionInfo

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim parameters(1) As ReportingServices.ParameterValue

parameters(0) = New ReportingServices.ParameterValue()

parameters(0).Name = "KB"

parameters(0).Value = _KB

parameters(1) = New ReportingServices.ParameterValue()

parameters(1).Name = "KBG"

parameters(1).Value = CStr(_IDKBG)

Info = rs.LoadReport("/SOR/KBGDruck10", Nothing)

rs.SetExecutionParameters(parameters, "de-ch")

Dim results() As Byte

results = rs.Render("PDF", "", "", "", "", Nothing, Nothing)

Dim Stream As System.IO.FileStream = System.IO.File.OpenWrite(DateiName)

Stream.Write(results, 0, results.Length)

Stream.Close()

Windows.Forms.Cursor.Current = Cursors.Default

Try

Diagnostics.Process.Start(DateiName)

Catch ex As Exception

End Try

XInfo("", "")

parameters from asp.net

Hi
I am new to Reporting Services so I have a basic question. I would like to
start a report from an asp.net application, and I also want to send a
parameter to the report from the asp.net page. How do I do this. Is there a
nice example somehwere that I can look at.
Thanks
JuliaIf you don't mind using the querystring method it's pretty easy to pass in a
paramater.
http://msdn2.microsoft.com/en-us/library/aa256621(SQL.80).aspx
That should have everything you need.
From the URL Access Syntax:
http://server/virtualroot?[/pathinfo]&prefix:param=value[&prefix:param=value]...n]
Just drop the prefix: to pass it a report parameter.
"Julia" wrote:
> Hi
> I am new to Reporting Services so I have a basic question. I would like to
> start a report from an asp.net application, and I also want to send a
> parameter to the report from the asp.net page. How do I do this. Is there a
> nice example somehwere that I can look at.
> Thanks
> Julia

Parameters for sp that depend on the current row fields

How can I specify parameters to a store procedure that depend on a row
without using ado.net in code behind?
The problem is that I use 5 parameters for my store procedure. 2 are report
parameters, and I want other 3 to be specified by the fields of the current
row, in this way the returned value by the store procedure depends on certain
values of each row.
I currently use code behind with Ado.Net to specify the parameters but
opening and closing the connection takes some time (depending on the # of
rows), if I somewhat could call the sp from the given field in the row in
reporting services it would be way faster. Does someone knows of a way to do
this?
Thanks.You can embed a subreport in a field of the current row and pass those
fields and parameters to the subreport (I do this). Give it a try, should be
a lot easier and cleaner.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gchinl" <gchinl@.discussions.microsoft.com> wrote in message
news:64EF7852-D7B0-4479-8B64-971E93E88B9A@.microsoft.com...
> How can I specify parameters to a store procedure that depend on a row
> without using ado.net in code behind?
> The problem is that I use 5 parameters for my store procedure. 2 are
> report
> parameters, and I want other 3 to be specified by the fields of the
> current
> row, in this way the returned value by the store procedure depends on
> certain
> values of each row.
> I currently use code behind with Ado.Net to specify the parameters but
> opening and closing the connection takes some time (depending on the # of
> rows), if I somewhat could call the sp from the given field in the row in
> reporting services it would be way faster. Does someone knows of a way to
> do
> this?
> Thanks.

parameters for query

I'm not sure if any1 can answer is on this forum, but any help would be VERY appriceted...

I am creating a report using Visual Studio.Net..I want to write a query using parameters BUT in this case I dont no what the parameter will be...

ie the user can enter a customer account OR a customer group
and sort can be on product OR total sales...

Any ideas?!

Thanks!!! :)The intuitively obvious answer would be to use a stored procedure instead of a query, then build the "smarts" into the procedure. There may be better answers, but that one jumps out at me.

-PatP|||The intuitively obvious answer would be to use a stored procedure instead of a query, then build the "smarts" into the procedure. There may be better answers, but that one jumps out at me.

-PatP

coool... neva dun that wiv stored proc... how would i do that? or do u know any useful links?!

THANKS :)|||Sounds like dynamic sql to me|||Sounds like dynamic sql to meThat is the fools way out... Infinite flexibilty, infinite risk.

I was hoping to kind of leash things a bit, finding a good compromise between flexibility and safety.

-PatP|||So would the best idea be to write a stored proc somefing like this?!:

CREATE PROCEDURE test (@.parameter varchar, @.variable varchar) AS
SELECT * from SALES
where @.parameter = @.variable
GO|||CREATE PROCEDURE test (@.parameter1 varchar, @.parameter2 varchar) AS
SELECT * from SALES
where (Column1 = @.Parameter1 or @.Parameter1 is null)
and (Column2 = @.Parameter2 or @.Parameter2 is null)
...but dump the "select *", and let your interface handle the sorting.|||CREATE PROCEDURE test (@.parameter1 varchar, @.parameter2 varchar) AS
SELECT * from SALES
where (Column1 = @.Parameter1 or @.Parameter1 is null)
and (Column2 = @.Parameter2 or @.Parameter2 is null)
...but dump the "select *", and let your interface handle the sorting.

That cant be write... if they enter a value for parameter 1 (eg 'X' only then the query will become:

where (Column1 = 'X')
and (Column2 is null)

right?! That would not be write...it would return nothing....

:eek: I dun no how 2 right it so the user can enter a account no OR customer name...|||would this work:

if @.Account Is Not Null then
write query using @.Acount in where part
break
else if @.Group Is Not Null then
write query using @.Group in where part

break
end

??|||No, but you should set defaults for the parameters, like so:
CREATE PROCEDURE test (@.parameter1 varchar = null, @.parameter2 varchar = null) AS
SELECT * from SALES
where (Column1 = @.Parameter1 or @.Parameter1 is null)
and (Column2 = @.Parameter2 or @.Parameter2 is null)
If a parameter is submitted, the result set will be filtered on that value. If the parameter is ommitted, no filtering will occur on the column.

Parameterized query returns one row with null values.

I am hoping someone could help me understand why this is happening and perhaps a solution.

I am using ASP.NET 2.0 with a SQL 2005 database.

In code behind, I am performing a query using a parameter as below:

sql = "SELECT field_name FROM myTable WHERE (field_name = @.P1)"

objCommand.Parameters.Add(New SqlParameter("@.P1", TextBox1.Text))

The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query.

The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way:

sql =String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text)

I also get NO records retuned using the same criteria.

What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning?

Thanks!

I am not sure but see if adding the datatype helps:

objCommand.Parameters.Add(New SqlParameter("@.P1", SqlDbType.Varchar,30)).value = TextBox1.Text

|||

Thanks for the suggestion. I tried adding the data type as you suggested. It did not change the results.

I have found that if I change to a data reader, the null value is not being returned. So, now it looks to be related to the ExecuteScalar method.

|||

I also just realized that it is not a null value being returned but instead an empty value, ie "".

I can get around this easily enough in multiple ways, I am just wanting to understand why this is happening.

So far I have this narrowed down to the following:

A parameterized query, with a user account having select, insert, update permission, and using the ExecuteScalar method. This combination returns a record with an empty result when the criteria is not found in the table instead of returning no records at all.

|||

Eh?

ExecuteScalar is used to return the first column of the first row of the query. If there is no rows, the value comes back as null.

I think perhaps you are misunderstanding what ExecuteScalar is supposed to do. It doesn't return records, or recordsets, it returns a singular scalar value (One column of one row - the first of each).

For further help, please post the whole code block in question. How you initialize your connection, command objects, how you are actually executing the query, where you are storing the result of the query (And how it is defined), and what you expected the result to be, and what you actually got.

If the results are varying depending on what user is executing the query, please make sure that either you explicitly define the schema you want to use, or that there doesn't exist multiple tables with the same name under different schemas (Refer to the table as dbo.Table not just Table).

|||

Ok, my bad, stupid mistake(s) with both user permissions and also with the string.format method.

I at least have it consistenly returning the empty record.

One last question, why return null/empty instead of just nothing like a data reader?

Thank you very much for the response.

|||

Hi,

ExecuteScalar is designed to return a single value from a database command and the proper representation of a single non-existant value is returning null. The ExecuteScalar is a non-void method and should return something!

Enjoy C#,

Mehrdad

|||

Thank you to everyone for the help and clairification on ExecuteScalar.