I am trying to pass value to the parameter field which i have created in crystal report 8. the parameter field is of type date. but i get error. the code is as follows.
.Reset
.ReportFileName = App.Path & "\reports\vaccine_flow_ledger.rpt"
.Connect = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=APACCESS"
.DiscardSavedData = True
.ParameterFields(0) = "SDT;" & #1/7/2005#
.ParameterFields(1) = "EDT;" & #1/7/2005#
.Action = 1
End With
Please help me to resolve this problem.Try this
.ParameterFields(0) = "SDT;"# & 1/7/2005 & #"
.ParameterFields(1) = "EDT;"# & #1/7/2005& #"|||I tried that one,but i didn't work.
but
.parameterfields(0) = "sdt;" & "date(2005,1,1);"
works finesql
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Friday, March 30, 2012
passing null date value to database
how can i pass null value to database? date is not required field in my database. i can pass default date but i think default date is not good in my case as it is DOB of a customer.Hi there,
This is the function where I have passed the dates but i can't assign the System.Data.SqlTypes.Null to date variable.|||OK, I believe I have misled you. You should be using System.DBNull. Let us know if this works better for you.
Check out the DBNull class. :)|||DBNull.Value should do the trick ...
But don't forget when reading back from the table to check for the NULL value|||well, i tried that it says can't convert System.DBNull to datetime. i also used Convert.DBNull but it shows error at run time if the value isn't provided. well, it seems like i have to pass DBNull.Value directly but in my case i have to assign it to variable coz i am using stored procedure and using SqlHelper class.
what about integers though? is it the same thing?|||I think you should be able to use System.Data.SqlTypes.SqlDateTime.Null.
Terri|||hi terri,
it still says "value of type System.Data.SqlTypes.SqlDateTime.Null cannot be converted to 'Date'"|||Can you please post the relevant code?
Terri|||
Dim commDate As DateTimeIf tbCommDate.Text = "" Then
'commDate = New DateTime(1900, 1, 1)'can't use this statement
commDate = System.Data.SqlTypes.SqlDateTime.Null
Else
commDate = CType(tbCommDate.Text, DateTime)
End If
I pass this "commDate" to the stored procedure. i am using SQLHelper class.|||We need to also see the code where you are telling the SQLHelper class the data type of the commDate parameter.
Terri|||
Public Shared Function ProspectiveInsert(ByVal agentId As Integer, ByVal tripId As Integer, ByVal institution As String, ByVal nationality As String, ByVal title As String, ByVal dob As DateTime, ByVal sex As String, ByVal elicos As String, ByVal course1 As String, ByVal course2 As String, ByVal fname As String, ByVal mname As String, ByVal lname As String, ByVal osStud As Boolean, ByVal osAdd As String, ByVal osPhone As String, ByVal osMobile As String, ByVal osFax As String, ByVal osSuburb As String, ByVal osPostcode As String, ByVal osCity As String, ByVal osCountry As String, ByVal add As String, ByVal phone As String, ByVal mobile As String, ByVal fax As String, ByVal suburb As String, ByVal postcode As String, ByVal city As String, ByVal country As String, ByVal email As String, ByVal ugQual As String, ByVal ugIns As String, ByVal ugYear As String, ByVal seconQual As String, ByVal seconYear As Integer, ByVal seconIns As String, ByVal pgQual As String, ByVal pgIns As String, ByVal pgYear As Integer, ByVal appStatus As String, ByVal appNotes As String, ByVal appDate As String, ByVal ielts As Double, ByVal engRemarks As String, ByVal coeDate As DateTime, ByVal coeRemarks As String, ByVal offerDate As DateTime, ByVal offerRemarks As String, ByVal recAddDate As DateTime, ByVal prefCommDate As DateTime, ByVal notes As String, ByVal modBy As String) As IntegerTry
SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings("connectionString"), "MCS_ProspectiveStudentInsert", agentId, tripId, institution, nationality, title, dob, sex, elicos, course1, course2, fname, mname, lname, osStud, osAdd, osPhone, osMobile, osFax, osSuburb, osPostcode, osCity, osCountry, add, phone, mobile, fax, suburb, postcode, city, country, email, ugQual, ugIns, ugYear, seconQual, seconIns, seconYear, pgQual, pgIns, pgYear, appStatus, appNotes, appDate, ielts, engRemarks, coeDate, coeRemarks, offerDate, offerRemarks, recAddDate, prefCommDate, notes, modBy)
Catch ex As SqlException
Return ex.Number
End TryReturn 0
End Function
This is the function where I have passed the dates but i can't assign the System.Data.SqlTypes.Null to date variable.|||OK, I believe I have misled you. You should be using System.DBNull. Let us know if this works better for you.
Terri|||it says "DBNull is a type in system and can't be used as an expression."
at the moment i am using default value but i think that's not a good idea is it?|||thanx a lot for your time Terri.
i appreciate your willingness to help me.|||THe problem has NOTHING to do with SQL. See:
[code]
Dim commDate As DateTime
If tbCommDate.Text = "" Then
'commDate = New DateTime(1900, 1, 1)
'can't use this statement
commDate = System.Data.SqlTypes.SqlDateTime.Null
[/code]
NATURALLY blows. You can not assign SqlDateTime.Null to a DateTime.
You have to make the assignment "lower" - i.e. you pass the SqlDateTime directly into the relevant SQL parameter. You can not store it in another non-compatible time in the meantime.
For example, the EntityBroker - my O/R-Mapper - makes the switch statement directly when putting the value into the PARAMETER (or encoding the value for the SQL string). THEN it gets accepted (DBNull.Value, btw.). The way you do it earlier just results in an invalid cast.|||but i am using SqlHelper class. so i think i can't use it then. right?
Wednesday, March 28, 2012
Passing multiple rows of data to a code function
Is there a way to pass multiple rows to a function on the report? Here's
what I'm trying to do:
I have field in my detail section, "customer", that is shows each customer:
[Customer1]
[Customer2]
[Customer3]
[etc.]
I'd like to wrap these into a single field at the parent group so I get the
following in a single field:
[Customer1, Customer2, Customer3, etc.]
Does that make sense?
I was thinking I might be able to write a VB function to take in a group of
records, itereate through them, and return the reformated string.
Of course if there's another way to accomplish this, I'm completely open to
other ideas.hi,i think it's better 2 do it on the sql side,so u get it in the ds as one
field.
"Greg S" wrote:
> Is there a way to pass multiple rows to a function on the report? Here's
> what I'm trying to do:
> I have field in my detail section, "customer", that is shows each customer:
> [Customer1]
> [Customer2]
> [Customer3]
> [etc.]
> I'd like to wrap these into a single field at the parent group so I get the
> following in a single field:
> [Customer1, Customer2, Customer3, etc.]
> Does that make sense?
> I was thinking I might be able to write a VB function to take in a group of
> records, itereate through them, and return the reformated string.
> Of course if there's another way to accomplish this, I'm completely open to
> other ideas.
>
>|||Take a look at the matrix control and see if that will work for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg S" <gregslistacct@.hotmail.com> wrote in message
news:e2lvbHHIGHA.532@.TK2MSFTNGP15.phx.gbl...
> Is there a way to pass multiple rows to a function on the report? Here's
> what I'm trying to do:
> I have field in my detail section, "customer", that is shows each
> customer:
> [Customer1]
> [Customer2]
> [Customer3]
> [etc.]
> I'd like to wrap these into a single field at the parent group so I get
> the following in a single field:
> [Customer1, Customer2, Customer3, etc.]
> Does that make sense?
> I was thinking I might be able to write a VB function to take in a group
> of records, itereate through them, and return the reformated string.
> Of course if there's another way to accomplish this, I'm completely open
> to other ideas.
>|||Follow up solution to my own thread:
Well, I did find some way to do this
concatenation/aggregation/rows-to-a-column on the SQL side. Here's a good
example using CROSS APPLY and leveraging FOR XML in sql 2005
http://www.aspfaq.com/show.asp?id=2529
I found a number of other examples as well - some using UDF functions,
orthers using customer CRL assemblies. Most threads had someone commenting
to the effect of "... this is usually needed for some kind of reporting and
should be handled in the presentation layer... doing it via SQL is breaking
the idea of pure relational databases..." Just thought this was funny as
my presentation layer (reporting services) can't do it. :^)
"'" <@.discussions.microsoft.com> wrote in message
news:4D2617C3-804E-4A10-AE87-53C8CB077DD4@.microsoft.com...
> hi,i think it's better 2 do it on the sql side,so u get it in the ds as
> one
> field.
> "Greg S" wrote:
>> Is there a way to pass multiple rows to a function on the report? Here's
>> what I'm trying to do:
>> I have field in my detail section, "customer", that is shows each
>> customer:
>> [Customer1]
>> [Customer2]
>> [Customer3]
>> [etc.]
>> I'd like to wrap these into a single field at the parent group so I get
>> the
>> following in a single field:
>> [Customer1, Customer2, Customer3, etc.]
>> Does that make sense?
>> I was thinking I might be able to write a VB function to take in a group
>> of
>> records, itereate through them, and return the reformated string.
>> Of course if there's another way to accomplish this, I'm completely open
>> to
>> other ideas.
>>
what I'm trying to do:
I have field in my detail section, "customer", that is shows each customer:
[Customer1]
[Customer2]
[Customer3]
[etc.]
I'd like to wrap these into a single field at the parent group so I get the
following in a single field:
[Customer1, Customer2, Customer3, etc.]
Does that make sense?
I was thinking I might be able to write a VB function to take in a group of
records, itereate through them, and return the reformated string.
Of course if there's another way to accomplish this, I'm completely open to
other ideas.hi,i think it's better 2 do it on the sql side,so u get it in the ds as one
field.
"Greg S" wrote:
> Is there a way to pass multiple rows to a function on the report? Here's
> what I'm trying to do:
> I have field in my detail section, "customer", that is shows each customer:
> [Customer1]
> [Customer2]
> [Customer3]
> [etc.]
> I'd like to wrap these into a single field at the parent group so I get the
> following in a single field:
> [Customer1, Customer2, Customer3, etc.]
> Does that make sense?
> I was thinking I might be able to write a VB function to take in a group of
> records, itereate through them, and return the reformated string.
> Of course if there's another way to accomplish this, I'm completely open to
> other ideas.
>
>|||Take a look at the matrix control and see if that will work for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg S" <gregslistacct@.hotmail.com> wrote in message
news:e2lvbHHIGHA.532@.TK2MSFTNGP15.phx.gbl...
> Is there a way to pass multiple rows to a function on the report? Here's
> what I'm trying to do:
> I have field in my detail section, "customer", that is shows each
> customer:
> [Customer1]
> [Customer2]
> [Customer3]
> [etc.]
> I'd like to wrap these into a single field at the parent group so I get
> the following in a single field:
> [Customer1, Customer2, Customer3, etc.]
> Does that make sense?
> I was thinking I might be able to write a VB function to take in a group
> of records, itereate through them, and return the reformated string.
> Of course if there's another way to accomplish this, I'm completely open
> to other ideas.
>|||Follow up solution to my own thread:
Well, I did find some way to do this
concatenation/aggregation/rows-to-a-column on the SQL side. Here's a good
example using CROSS APPLY and leveraging FOR XML in sql 2005
http://www.aspfaq.com/show.asp?id=2529
I found a number of other examples as well - some using UDF functions,
orthers using customer CRL assemblies. Most threads had someone commenting
to the effect of "... this is usually needed for some kind of reporting and
should be handled in the presentation layer... doing it via SQL is breaking
the idea of pure relational databases..." Just thought this was funny as
my presentation layer (reporting services) can't do it. :^)
"'" <@.discussions.microsoft.com> wrote in message
news:4D2617C3-804E-4A10-AE87-53C8CB077DD4@.microsoft.com...
> hi,i think it's better 2 do it on the sql side,so u get it in the ds as
> one
> field.
> "Greg S" wrote:
>> Is there a way to pass multiple rows to a function on the report? Here's
>> what I'm trying to do:
>> I have field in my detail section, "customer", that is shows each
>> customer:
>> [Customer1]
>> [Customer2]
>> [Customer3]
>> [etc.]
>> I'd like to wrap these into a single field at the parent group so I get
>> the
>> following in a single field:
>> [Customer1, Customer2, Customer3, etc.]
>> Does that make sense?
>> I was thinking I might be able to write a VB function to take in a group
>> of
>> records, itereate through them, and return the reformated string.
>> Of course if there's another way to accomplish this, I'm completely open
>> to
>> other ideas.
>>
Monday, March 26, 2012
Passing IN() values as parameter
Hi...
I'm creating a procedure to fetch rows from table. One field will come come as IN(). Its the condition. That field is numeric field (note down), i would like to pass the In values as parameter.
eg: procedurename @.fieldvalue varchar(100)
as
begin
...
WHERE fieldname IN(@.fieldvalue)
while executing the procedure how to pass the value... or procedure itself has problem...?
Help me...
Tx in Advance...Once again dynamic SQL seems to be the deal here, unless a more complete description of the problem is available.
The thing here is that you cannot do a procedure that works in the way you describe without using dynamic SQL, because of the datatype clash.
Your IN clause wants to do comparisons with integers, and therefore your argument to the procedure will never be able to work unless you convert your values in the fieldvalue variable to a string which you add to the the last part of your existing query, and execute it with dynamic SQL. I cannot judge whether this is the right decision to do in your particular case without more information about the problem. Hope I got your thinking going atleast ...|||In your procedure, parse the parameter into a temp table. Join your temp table to the production table to limit the result set. Works every time, and often runs much faster than the dynamic SQL solution.
-PatP
I'm creating a procedure to fetch rows from table. One field will come come as IN(). Its the condition. That field is numeric field (note down), i would like to pass the In values as parameter.
eg: procedurename @.fieldvalue varchar(100)
as
begin
...
WHERE fieldname IN(@.fieldvalue)
while executing the procedure how to pass the value... or procedure itself has problem...?
Help me...
Tx in Advance...Once again dynamic SQL seems to be the deal here, unless a more complete description of the problem is available.
The thing here is that you cannot do a procedure that works in the way you describe without using dynamic SQL, because of the datatype clash.
Your IN clause wants to do comparisons with integers, and therefore your argument to the procedure will never be able to work unless you convert your values in the fieldvalue variable to a string which you add to the the last part of your existing query, and execute it with dynamic SQL. I cannot judge whether this is the right decision to do in your particular case without more information about the problem. Hope I got your thinking going atleast ...|||In your procedure, parse the parameter into a temp table. Join your temp table to the production table to limit the result set. Works every time, and often runs much faster than the dynamic SQL solution.
-PatP
Passing Field values in a URL
Hello,
I need to pass field values to a URL when we click on a particular datavalue
on the Report. Basically I am trying to put up a URL on a column of the
report. And I would like to pass that corresponding column value dynamically.
Please someone let me know how to do that. At present I was trying to do
something like this that doesnt work :
http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
where DATAKEY is one of my fields. But this doesnt help me pass the dynamic
values of that particular row. Please someone give me a idea.
Thanks,
BabithaAre you trying to pass parameters into the Rpt Services report to be used for
rendering, or are you trying to extract them out of a rendered report?
sebring1130
"Babitha" wrote:
> Hello,
> I need to pass field values to a URL when we click on a particular datavalue
> on the Report. Basically I am trying to put up a URL on a column of the
> report. And I would like to pass that corresponding column value dynamically.
> Please someone let me know how to do that. At present I was trying to do
> something like this that doesnt work :
> http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> where DATAKEY is one of my fields. But this doesnt help me pass the dynamic
> values of that particular row. Please someone give me a idea.
> Thanks,
> Babitha|||I have the same problem, I am tryinf to read a row from reportviewer control
(data is alrady shown by reportviewer on the screen and I need to select one
line and process it), is there ny example?
"sebring1130" wrote:
> Are you are trying to pass parameters into the Rpt Services report to be used for
> rendering, or are you trying to extract them out of a rendered report?
> sebring1130
>
> "Babitha" wrote:
> > Hello,
> >
> > I need to pass field values to a URL when we click on a particular datavalue
> > on the Report. Basically I am trying to put up a URL on a column of the
> > report. And I would like to pass that corresponding column value dynamically.
> >
> > Please someone let me know how to do that. At present I was trying to do
> > something like this that doesnt work :
> >
> > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> >
> > where DATAKEY is one of my fields. But this doesnt help me pass the dynamic
> > values of that particular row. Please someone give me a idea.
> >
> > Thanks,
> > Babitha|||I too am trying to achive the same outcome. I don't think this is going to
be doable.
"Babitha" <Babitha@.discussions.microsoft.com> wrote in message
news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> Hello,
> I need to pass field values to a URL when we click on a particular
datavalue
> on the Report. Basically I am trying to put up a URL on a column of the
> report. And I would like to pass that corresponding column value
dynamically.
> Please someone let me know how to do that. At present I was trying to do
> something like this that doesnt work :
> http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> where DATAKEY is one of my fields. But this doesnt help me pass the
dynamic
> values of that particular row. Please someone give me a idea.
> Thanks,
> Babitha|||Have you tried using the hyperlink feature of the cells on the report? If
you go into the texbox cell's properties and hit the "navigation" tab there
are several options to set up hyperlinks so that when you click on a cell on
the report you can automatically jump to a new URL. It looks like the URL
can be anything ... I'd be very surprized if you couldn't insert parameters
and field values in the the URL.
sebring1130
"Art Simcoe" wrote:
> I too am trying to achive the same outcome. I don't think this is going to
> be doable.
> "Babitha" <Babitha@.discussions.microsoft.com> wrote in message
> news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> > Hello,
> >
> > I need to pass field values to a URL when we click on a particular
> datavalue
> > on the Report. Basically I am trying to put up a URL on a column of the
> > report. And I would like to pass that corresponding column value
> dynamically.
> >
> > Please someone let me know how to do that. At present I was trying to do
> > something like this that doesnt work :
> >
> > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> >
> > where DATAKEY is one of my fields. But this doesnt help me pass the
> dynamic
> > values of that particular row. Please someone give me a idea.
> >
> > Thanks,
> > Babitha
>
>|||I am also trying to achieve this very ting. It appears you cannot insert
parameters and field values into the URL. You can build the expression but
the resulting URL simply contains the variable name you enter, not its value.
Anyone solved this?
"sebring1130" wrote:
> Have you tried using the hyperlink feature of the cells on the report? If
> you go into the texbox cell's properties and hit the "navigation" tab there
> are several options to set up hyperlinks so that when you click on a cell on
> the report you can automatically jump to a new URL. It looks like the URL
> can be anything ... I'd be very surprized if you couldn't insert parameters
> and field values in the the URL.
> sebring1130
>
> "Art Simcoe" wrote:
> > I too am trying to achive the same outcome. I don't think this is going to
> > be doable.
> >
> > "Babitha" <Babitha@.discussions.microsoft.com> wrote in message
> > news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> > > Hello,
> > >
> > > I need to pass field values to a URL when we click on a particular
> > datavalue
> > > on the Report. Basically I am trying to put up a URL on a column of the
> > > report. And I would like to pass that corresponding column value
> > dynamically.
> > >
> > > Please someone let me know how to do that. At present I was trying to do
> > > something like this that doesnt work :
> > >
> > > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> > >
> > > where DATAKEY is one of my fields. But this doesnt help me pass the
> > dynamic
> > > values of that particular row. Please someone give me a idea.
> > >
> > > Thanks,
> > > Babitha
> >
> >
> >|||What you need to to is to create the string in the expressions:
i.e.
="http://blah.mmm.com/etc etc" & fields!fieldname.value
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stuart" <Stuart@.discussions.microsoft.com> wrote in message
news:EB4306DC-595A-447F-A07D-B5A7BE18C1EF@.microsoft.com...
> I am also trying to achieve this very ting. It appears you cannot insert
> parameters and field values into the URL. You can build the expression
but
> the resulting URL simply contains the variable name you enter, not its
value.
> Anyone solved this?
> "sebring1130" wrote:
> > Have you tried using the hyperlink feature of the cells on the report?
If
> > you go into the texbox cell's properties and hit the "navigation" tab
there
> > are several options to set up hyperlinks so that when you click on a
cell on
> > the report you can automatically jump to a new URL. It looks like the
URL
> > can be anything ... I'd be very surprized if you couldn't insert
parameters
> > and field values in the the URL.
> >
> > sebring1130
> >
> >
> > "Art Simcoe" wrote:
> >
> > > I too am trying to achive the same outcome. I don't think this is
going to
> > > be doable.
> > >
> > > "Babitha" <Babitha@.discussions.microsoft.com> wrote in message
> > > news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> > > > Hello,
> > > >
> > > > I need to pass field values to a URL when we click on a particular
> > > datavalue
> > > > on the Report. Basically I am trying to put up a URL on a column of
the
> > > > report. And I would like to pass that corresponding column value
> > > dynamically.
> > > >
> > > > Please someone let me know how to do that. At present I was trying
to do
> > > > something like this that doesnt work :
> > > >
> > > > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> > > >
> > > > where DATAKEY is one of my fields. But this doesnt help me pass the
> > > dynamic
> > > > values of that particular row. Please someone give me a idea.
> > > >
> > > > Thanks,
> > > > Babitha
> > >
> > >
> > >
I need to pass field values to a URL when we click on a particular datavalue
on the Report. Basically I am trying to put up a URL on a column of the
report. And I would like to pass that corresponding column value dynamically.
Please someone let me know how to do that. At present I was trying to do
something like this that doesnt work :
http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
where DATAKEY is one of my fields. But this doesnt help me pass the dynamic
values of that particular row. Please someone give me a idea.
Thanks,
BabithaAre you trying to pass parameters into the Rpt Services report to be used for
rendering, or are you trying to extract them out of a rendered report?
sebring1130
"Babitha" wrote:
> Hello,
> I need to pass field values to a URL when we click on a particular datavalue
> on the Report. Basically I am trying to put up a URL on a column of the
> report. And I would like to pass that corresponding column value dynamically.
> Please someone let me know how to do that. At present I was trying to do
> something like this that doesnt work :
> http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> where DATAKEY is one of my fields. But this doesnt help me pass the dynamic
> values of that particular row. Please someone give me a idea.
> Thanks,
> Babitha|||I have the same problem, I am tryinf to read a row from reportviewer control
(data is alrady shown by reportviewer on the screen and I need to select one
line and process it), is there ny example?
"sebring1130" wrote:
> Are you are trying to pass parameters into the Rpt Services report to be used for
> rendering, or are you trying to extract them out of a rendered report?
> sebring1130
>
> "Babitha" wrote:
> > Hello,
> >
> > I need to pass field values to a URL when we click on a particular datavalue
> > on the Report. Basically I am trying to put up a URL on a column of the
> > report. And I would like to pass that corresponding column value dynamically.
> >
> > Please someone let me know how to do that. At present I was trying to do
> > something like this that doesnt work :
> >
> > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> >
> > where DATAKEY is one of my fields. But this doesnt help me pass the dynamic
> > values of that particular row. Please someone give me a idea.
> >
> > Thanks,
> > Babitha|||I too am trying to achive the same outcome. I don't think this is going to
be doable.
"Babitha" <Babitha@.discussions.microsoft.com> wrote in message
news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> Hello,
> I need to pass field values to a URL when we click on a particular
datavalue
> on the Report. Basically I am trying to put up a URL on a column of the
> report. And I would like to pass that corresponding column value
dynamically.
> Please someone let me know how to do that. At present I was trying to do
> something like this that doesnt work :
> http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> where DATAKEY is one of my fields. But this doesnt help me pass the
dynamic
> values of that particular row. Please someone give me a idea.
> Thanks,
> Babitha|||Have you tried using the hyperlink feature of the cells on the report? If
you go into the texbox cell's properties and hit the "navigation" tab there
are several options to set up hyperlinks so that when you click on a cell on
the report you can automatically jump to a new URL. It looks like the URL
can be anything ... I'd be very surprized if you couldn't insert parameters
and field values in the the URL.
sebring1130
"Art Simcoe" wrote:
> I too am trying to achive the same outcome. I don't think this is going to
> be doable.
> "Babitha" <Babitha@.discussions.microsoft.com> wrote in message
> news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> > Hello,
> >
> > I need to pass field values to a URL when we click on a particular
> datavalue
> > on the Report. Basically I am trying to put up a URL on a column of the
> > report. And I would like to pass that corresponding column value
> dynamically.
> >
> > Please someone let me know how to do that. At present I was trying to do
> > something like this that doesnt work :
> >
> > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> >
> > where DATAKEY is one of my fields. But this doesnt help me pass the
> dynamic
> > values of that particular row. Please someone give me a idea.
> >
> > Thanks,
> > Babitha
>
>|||I am also trying to achieve this very ting. It appears you cannot insert
parameters and field values into the URL. You can build the expression but
the resulting URL simply contains the variable name you enter, not its value.
Anyone solved this?
"sebring1130" wrote:
> Have you tried using the hyperlink feature of the cells on the report? If
> you go into the texbox cell's properties and hit the "navigation" tab there
> are several options to set up hyperlinks so that when you click on a cell on
> the report you can automatically jump to a new URL. It looks like the URL
> can be anything ... I'd be very surprized if you couldn't insert parameters
> and field values in the the URL.
> sebring1130
>
> "Art Simcoe" wrote:
> > I too am trying to achive the same outcome. I don't think this is going to
> > be doable.
> >
> > "Babitha" <Babitha@.discussions.microsoft.com> wrote in message
> > news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> > > Hello,
> > >
> > > I need to pass field values to a URL when we click on a particular
> > datavalue
> > > on the Report. Basically I am trying to put up a URL on a column of the
> > > report. And I would like to pass that corresponding column value
> > dynamically.
> > >
> > > Please someone let me know how to do that. At present I was trying to do
> > > something like this that doesnt work :
> > >
> > > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> > >
> > > where DATAKEY is one of my fields. But this doesnt help me pass the
> > dynamic
> > > values of that particular row. Please someone give me a idea.
> > >
> > > Thanks,
> > > Babitha
> >
> >
> >|||What you need to to is to create the string in the expressions:
i.e.
="http://blah.mmm.com/etc etc" & fields!fieldname.value
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stuart" <Stuart@.discussions.microsoft.com> wrote in message
news:EB4306DC-595A-447F-A07D-B5A7BE18C1EF@.microsoft.com...
> I am also trying to achieve this very ting. It appears you cannot insert
> parameters and field values into the URL. You can build the expression
but
> the resulting URL simply contains the variable name you enter, not its
value.
> Anyone solved this?
> "sebring1130" wrote:
> > Have you tried using the hyperlink feature of the cells on the report?
If
> > you go into the texbox cell's properties and hit the "navigation" tab
there
> > are several options to set up hyperlinks so that when you click on a
cell on
> > the report you can automatically jump to a new URL. It looks like the
URL
> > can be anything ... I'd be very surprized if you couldn't insert
parameters
> > and field values in the the URL.
> >
> > sebring1130
> >
> >
> > "Art Simcoe" wrote:
> >
> > > I too am trying to achive the same outcome. I don't think this is
going to
> > > be doable.
> > >
> > > "Babitha" <Babitha@.discussions.microsoft.com> wrote in message
> > > news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> > > > Hello,
> > > >
> > > > I need to pass field values to a URL when we click on a particular
> > > datavalue
> > > > on the Report. Basically I am trying to put up a URL on a column of
the
> > > > report. And I would like to pass that corresponding column value
> > > dynamically.
> > > >
> > > > Please someone let me know how to do that. At present I was trying
to do
> > > > something like this that doesnt work :
> > > >
> > > > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> > > >
> > > > where DATAKEY is one of my fields. But this doesnt help me pass the
> > > dynamic
> > > > values of that particular row. Please someone give me a idea.
> > > >
> > > > Thanks,
> > > > Babitha
> > >
> > >
> > >
passing empty string to stored procedure -SQL Express 2005
I am taking data from a form and passing it to a stored procedure to insert
into a table. If there is nothing entered in the field I receive the
following error message:
"Parameter object is improperly defined. Inconsistent or incomplete
information was provided"
The stored procedure is:
ALTER PROCEDURE [dbo].[AddNewContract]
@.strContractorName nVARCHAR(50),
@.strOrderNumber nVARCHAR(50) = null,
@.strWorkLocation ntext = null,
@.Report datetime = null,
@.NewContractID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.tblAcceptContract (ContractorName, OrderNumber,
WorkLocation, SubmitDate,)
SELECT @.strContractorName, @.strOrderNumber, @.strWorkLocation,
@.dtReport;
SELECT @.NewContractID = SCOPE_IDENTITY();
END
The VB code is:
With MyCmd
.ActiveConnection = conn
.CommandText = "dbo.AddNewContract"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.ContractorName", adVarChar,
adParamInput, Len(strContractorName), strContractorName)
.Parameters.Append .CreateParameter("@.OrderNumber", adVarChar,
adParamInput, Len(strOrderNumber), strOrderNumber)
.Parameters.Append .CreateParameter("@.WorkLocation", adLongVarChar,
adParamInput, Len(strWorkLocation), strWorkLocation)
.Parameters.Append .CreateParameter("@.dtReport", adDate, adParamInput,
Len(dtReport), dtReport)
.Parameters.Append .CreateParameter("@.NewContractID", adInteger,
adParamOutput)
End With
MyCmd.Execute
lContractID = MyCmd.Parameters("@.NewContractID").Value()
I don't know if nothing is passed to the stored procedure if there is no
data in the field or a null is passed.
I though I could just set the default to null in the stored procedure, but
the above error message is displayed. I'd appreciate it if you could let me
know how I pass the empty/null string?Just some ideas, not sure if they will solve the problem or not...
I think that setting your parameter defaults to null is redundant, as the
default only gets used if a null parameter is passed to begin with. To pass
a null rather than an empty string, set the parameter to VBNull.Value (at
least that is what you use in VB.Net). You can pass an empty string for
varchar parameters, but not for date or numberic parameters.
Also, you could run into issues with your NTEXT value if it is very long
(over 4000 characters?).
Before you make any changes, however, step through your VB code and verify
that your variables are populated with the data you expect. If you can't
step through the code, at least print out the values of your variables.
Once you have confirmed what the values are that you are passing, you will
have a better idea of what is going wrong.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:70464E51-9AB4-4DD8-873A-DC6427188B8E@.microsoft.com...
> I am taking data from a form and passing it to a stored procedure to
insert
> into a table. If there is nothing entered in the field I receive the
> following error message:
> "Parameter object is improperly defined. Inconsistent or incomplete
> information was provided"
> The stored procedure is:
> ALTER PROCEDURE [dbo].[AddNewContract]
> @.strContractorName nVARCHAR(50),
> @.strOrderNumber nVARCHAR(50) = null,
> @.strWorkLocation ntext = null,
> @.Report datetime = null,
> @.NewContractID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.tblAcceptContract (ContractorName, OrderNumber,
> WorkLocation, SubmitDate,)
> SELECT @.strContractorName, @.strOrderNumber, @.strWorkLocation,
> @.dtReport;
> SELECT @.NewContractID = SCOPE_IDENTITY();
> END
> The VB code is:
> With MyCmd
> .ActiveConnection = conn
> .CommandText = "dbo.AddNewContract"
> .CommandType = adCmdStoredProc
> .Parameters.Append .CreateParameter("@.ContractorName", adVarChar,
> adParamInput, Len(strContractorName), strContractorName)
> .Parameters.Append .CreateParameter("@.OrderNumber", adVarChar,
> adParamInput, Len(strOrderNumber), strOrderNumber)
> .Parameters.Append .CreateParameter("@.WorkLocation", adLongVarChar,
> adParamInput, Len(strWorkLocation), strWorkLocation)
> .Parameters.Append .CreateParameter("@.dtReport", adDate, adParamInput,
> Len(dtReport), dtReport)
> .Parameters.Append .CreateParameter("@.NewContractID", adInteger,
> adParamOutput)
> End With
> MyCmd.Execute
> lContractID = MyCmd.Parameters("@.NewContractID").Value()
> I don't know if nothing is passed to the stored procedure if there is no
> data in the field or a null is passed.
> I though I could just set the default to null in the stored procedure, but
> the above error message is displayed. I'd appreciate it if you could let
me
> know how I pass the empty/null string?|||> I think that setting your parameter defaults to null is redundant, as the
> default only gets used if a null parameter is passed to begin with.
Not quite true. Defaults get used when parameters are not set at all by the
caller, or if the caller specifies DEFAULT as the parameter value.
Lisa,
Validate user data before calling a procedure, and only append parameters
(to the command object) that have no defaults - i.e. make sure the user
enters all necessary values or create some defaults in the application.
The error message you see is not a SQL Server error.
ML
http://milambda.blogspot.com/|||Jim, ML
Thank you for your feedback, unfortunately I haven't been able to try it out
as I had the insane idea to upgrade my SQL Express to SP1. The upgrade faile
d
and I now cannot uninstall or reinstall, so I'm only left with the option of
setting up another PC. Thankfully I did backup my database before I started
the upgrade!!! (Thanks Microsoft for giving me such challenges.)
I'll let you know how I got on when I'm able to resume.
"ML" wrote:
> Not quite true. Defaults get used when parameters are not set at all by th
e
> caller, or if the caller specifies DEFAULT as the parameter value.
> Lisa,
> Validate user data before calling a procedure, and only append parameters
> (to the command object) that have no defaults - i.e. make sure the user
> enters all necessary values or create some defaults in the application.
> The error message you see is not a SQL Server error.
>
> ML
> --
> http://milambda.blogspot.com/|||"I think that setting your parameter defaults to null is redundant, as
the
default only gets used if a null parameter is passed to begin with. To
pass
a null rather than an empty string, set the parameter to VBNull.Value
(at
least that is what you use in VB.Net). You can pass an empty string
for
varchar parameters, but not for date or numberic parameters. "
I just wanted to quickly mention that the parameter is DBNull.Value,
not VBNull.Value.
into a table. If there is nothing entered in the field I receive the
following error message:
"Parameter object is improperly defined. Inconsistent or incomplete
information was provided"
The stored procedure is:
ALTER PROCEDURE [dbo].[AddNewContract]
@.strContractorName nVARCHAR(50),
@.strOrderNumber nVARCHAR(50) = null,
@.strWorkLocation ntext = null,
@.Report datetime = null,
@.NewContractID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.tblAcceptContract (ContractorName, OrderNumber,
WorkLocation, SubmitDate,)
SELECT @.strContractorName, @.strOrderNumber, @.strWorkLocation,
@.dtReport;
SELECT @.NewContractID = SCOPE_IDENTITY();
END
The VB code is:
With MyCmd
.ActiveConnection = conn
.CommandText = "dbo.AddNewContract"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.ContractorName", adVarChar,
adParamInput, Len(strContractorName), strContractorName)
.Parameters.Append .CreateParameter("@.OrderNumber", adVarChar,
adParamInput, Len(strOrderNumber), strOrderNumber)
.Parameters.Append .CreateParameter("@.WorkLocation", adLongVarChar,
adParamInput, Len(strWorkLocation), strWorkLocation)
.Parameters.Append .CreateParameter("@.dtReport", adDate, adParamInput,
Len(dtReport), dtReport)
.Parameters.Append .CreateParameter("@.NewContractID", adInteger,
adParamOutput)
End With
MyCmd.Execute
lContractID = MyCmd.Parameters("@.NewContractID").Value()
I don't know if nothing is passed to the stored procedure if there is no
data in the field or a null is passed.
I though I could just set the default to null in the stored procedure, but
the above error message is displayed. I'd appreciate it if you could let me
know how I pass the empty/null string?Just some ideas, not sure if they will solve the problem or not...
I think that setting your parameter defaults to null is redundant, as the
default only gets used if a null parameter is passed to begin with. To pass
a null rather than an empty string, set the parameter to VBNull.Value (at
least that is what you use in VB.Net). You can pass an empty string for
varchar parameters, but not for date or numberic parameters.
Also, you could run into issues with your NTEXT value if it is very long
(over 4000 characters?).
Before you make any changes, however, step through your VB code and verify
that your variables are populated with the data you expect. If you can't
step through the code, at least print out the values of your variables.
Once you have confirmed what the values are that you are passing, you will
have a better idea of what is going wrong.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:70464E51-9AB4-4DD8-873A-DC6427188B8E@.microsoft.com...
> I am taking data from a form and passing it to a stored procedure to
insert
> into a table. If there is nothing entered in the field I receive the
> following error message:
> "Parameter object is improperly defined. Inconsistent or incomplete
> information was provided"
> The stored procedure is:
> ALTER PROCEDURE [dbo].[AddNewContract]
> @.strContractorName nVARCHAR(50),
> @.strOrderNumber nVARCHAR(50) = null,
> @.strWorkLocation ntext = null,
> @.Report datetime = null,
> @.NewContractID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.tblAcceptContract (ContractorName, OrderNumber,
> WorkLocation, SubmitDate,)
> SELECT @.strContractorName, @.strOrderNumber, @.strWorkLocation,
> @.dtReport;
> SELECT @.NewContractID = SCOPE_IDENTITY();
> END
> The VB code is:
> With MyCmd
> .ActiveConnection = conn
> .CommandText = "dbo.AddNewContract"
> .CommandType = adCmdStoredProc
> .Parameters.Append .CreateParameter("@.ContractorName", adVarChar,
> adParamInput, Len(strContractorName), strContractorName)
> .Parameters.Append .CreateParameter("@.OrderNumber", adVarChar,
> adParamInput, Len(strOrderNumber), strOrderNumber)
> .Parameters.Append .CreateParameter("@.WorkLocation", adLongVarChar,
> adParamInput, Len(strWorkLocation), strWorkLocation)
> .Parameters.Append .CreateParameter("@.dtReport", adDate, adParamInput,
> Len(dtReport), dtReport)
> .Parameters.Append .CreateParameter("@.NewContractID", adInteger,
> adParamOutput)
> End With
> MyCmd.Execute
> lContractID = MyCmd.Parameters("@.NewContractID").Value()
> I don't know if nothing is passed to the stored procedure if there is no
> data in the field or a null is passed.
> I though I could just set the default to null in the stored procedure, but
> the above error message is displayed. I'd appreciate it if you could let
me
> know how I pass the empty/null string?|||> I think that setting your parameter defaults to null is redundant, as the
> default only gets used if a null parameter is passed to begin with.
Not quite true. Defaults get used when parameters are not set at all by the
caller, or if the caller specifies DEFAULT as the parameter value.
Lisa,
Validate user data before calling a procedure, and only append parameters
(to the command object) that have no defaults - i.e. make sure the user
enters all necessary values or create some defaults in the application.
The error message you see is not a SQL Server error.
ML
http://milambda.blogspot.com/|||Jim, ML
Thank you for your feedback, unfortunately I haven't been able to try it out
as I had the insane idea to upgrade my SQL Express to SP1. The upgrade faile
d
and I now cannot uninstall or reinstall, so I'm only left with the option of
setting up another PC. Thankfully I did backup my database before I started
the upgrade!!! (Thanks Microsoft for giving me such challenges.)
I'll let you know how I got on when I'm able to resume.
"ML" wrote:
> Not quite true. Defaults get used when parameters are not set at all by th
e
> caller, or if the caller specifies DEFAULT as the parameter value.
> Lisa,
> Validate user data before calling a procedure, and only append parameters
> (to the command object) that have no defaults - i.e. make sure the user
> enters all necessary values or create some defaults in the application.
> The error message you see is not a SQL Server error.
>
> ML
> --
> http://milambda.blogspot.com/|||"I think that setting your parameter defaults to null is redundant, as
the
default only gets used if a null parameter is passed to begin with. To
pass
a null rather than an empty string, set the parameter to VBNull.Value
(at
least that is what you use in VB.Net). You can pass an empty string
for
varchar parameters, but not for date or numberic parameters. "
I just wanted to quickly mention that the parameter is DBNull.Value,
not VBNull.Value.
Friday, March 23, 2012
Passing DDL value to bit field in SQL
I have a DDL with two values: 0 and 1. When I use
sqlcommand.Parameters.Add(New SqlParameter("@.fld", SqlDbType.Bit, 1))
sqlcommandl.Parameters("@.fld").Value = System.Boolean.Parse(DropDownList1.SelectedValue)
or
sqlcommand.Parameters.Add(New SqlParameter("@.fld", SqlDbType.Bit, 1))
sqlcommandl.Parameters("@.fld").Value = System.Convert.ToBoolean(DropDownList1.SelectedValue)
to insert the selected value in a sql2k database BIT field I am given this error:
System.FormatException: String was not recognized as a valid Boolean.
How can I solve this problem?
// C# but you should get the idea
bool trueFalse = false;
if( DropDownList1.SelectedValue.Value.Equals("1") ){
trueFalse = true;
}
sqlcommandl.Parameters("@.fld").Value = trueFalse;
Passing Data From VB to SSRS using LocalReport
With Crystal reports, it was easy to pass a single piece of data from VB to a report in a formula field. How do you implement this functionality in SSRS 2005 (I am using VS 2005)?
Seems like this should be commonplace, but I can't seem to find any info on it.
I am using a LocalReport, so a URL is not an option.
Thanks.
For anyone else having this problem:
I have figured it out - you have to create a parameter using the Report - Report Properties menu. Then, you link the parameter to a TextBox on the report. Finally, you can pass data from a client using the LocalReport.SetParameters method.
Never did find any documentation on it. Very obscure.
Passing Data From VB to SSRS using LocalReport
With Crystal reports, it was easy to pass a single piece of data from VB to a report in a formula field. How do you implement this functionality in SSRS 2005 (I am using VS 2005)?
Seems like this should be commonplace, but I can't seem to find any info on it.
I am using a LocalReport, so a URL is not an option.
Thanks.
For anyone else having this problem:
I have figured it out - you have to create a parameter using the Report - Report Properties menu. Then, you link the parameter to a TextBox on the report. Finally, you can pass data from a client using the LocalReport.SetParameters method.
Never did find any documentation on it. Very obscure.
Tuesday, March 20, 2012
Passing an Array and/or Variable Field Name to an SProc
I have 2 questions.
I am trying to write a stored procedure to update a table. I am trying
to pass a variable that represents the name of the column/field and
another for the value that I am changing.
For example:
@.FieldName VARCHAR(100)
@.FieldValue VARCHAR(100)
AS
UPDATE tblTHETABLE
SET @.FieldName = @.FieldValue
First is it possible to use a variable as the column/field name? If
so, how do I go about it?
Also, it would be nice if I could have the @.FieldName and @.FieldValue
variables as arrays. Is that possible?
Thank-you for any assistance
Bill"~TheIcemanCometh~" <bhazelwood@.delta-elevator.com> wrote in message
news:8d372e43.0402171320.5d263673@.posting.google.c om...
> I have 2 questions.
> I am trying to write a stored procedure to update a table. I am trying
> to pass a variable that represents the name of the column/field and
> another for the value that I am changing.
> For example:
> @.FieldName VARCHAR(100)
> @.FieldValue VARCHAR(100)
> AS
> UPDATE tblTHETABLE
> SET @.FieldName = @.FieldValue
> First is it possible to use a variable as the column/field name? If
> so, how do I go about it?
> Also, it would be nice if I could have the @.FieldName and @.FieldValue
> variables as arrays. Is that possible?
> Thank-you for any assistance
> Bill
The short answer is that it's possible, but probably not advisable. The
first link should help explain why; the second covers arrays:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/arrays-in-sql.html
Simon|||[posted and mailed, please reply in news]
~TheIcemanCometh~ (bhazelwood@.delta-elevator.com) writes:
> I am trying to write a stored procedure to update a table. I am trying
> to pass a variable that represents the name of the column/field and
> another for the value that I am changing.
> For example:
> @.FieldName VARCHAR(100)
> @.FieldValue VARCHAR(100)
> AS
> UPDATE tblTHETABLE
> SET @.FieldName = @.FieldValue
> First is it possible to use a variable as the column/field name? If
> so, how do I go about it?
> Also, it would be nice if I could have the @.FieldName and @.FieldValue
> variables as arrays. Is that possible?
Anything is possible, but what's the point? Why not construct the
SQL statements in client code instead?
If you really want to know how to do it, I have an article on my web
site. There you also learn why you should not do it.
http://www.sommarskog.se/dynamic_sql.html.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Passing a VB string to a Crystal parameter field
Hello,
I'm having trouble passing a VB string to a Crystal Report parameter field. Following is an excerpt of the code:
----------------
strDocID = InputBox("Please enter the DocID")
If strDocID <> "" Then
With CrystalReport1
.ReportFileName = "C:\Program Files\CSC Imaging\CSCReports\Reports\DocumentHistory.rpt" .Connect = cn.ConnectionString
.WindowState = crptMaximized
strSelection = "{CSC_TRANS.Document_ID} = " & "'" + strDocID + "'"
.ReplaceSelectionFormula strSelection
.Destination = crptToWindow
.Action = 1
----------------
The user enters the DocID, but when the Crystal Report launches, the user has to enter it again.
Following is the entry in the Record Selection Formula:
{CSC_TRANS.Document_ID} = {?DocumentID_PROMPT}
Also, if they select Cancel in the Crystal parameter window, it still tries to run the report. How can I stop the report from running?
Thank you for your assistance!
Andyeither is wrong the formula,and is crystal report,doesn't know the field. or...
work with an report object,and put
Public reportApp As CRAXDRT.Application
Public Report As CRAXDRT.Report
reportApp=createobject("CRAXDRT.Application")
report=reportApp.loadReport("...rpt")
...for not showing parameter prompting
Report.EnableParameterPrompting=false
report is an object of craxdrt.dll i thing at least i have worked with this one.this is for not showing the parameters inputs again.
also,it should be existing in the report(in design) a parameter(Insert/Parameter) and the value for that should have your docID value
something like this.
Dim cpars As CRAXDRT.ParameterFieldDefinitions
Dim cpar As CRAXDRT.ParameterFieldDefinition
Set cpar = cpars(1)
cpar.AddCurrentValue (docID)
docID here is your VB variable
Maybe this will work,or try to verify carefully the selection formula,AND in report it should be existing a parameter that will take in the code that value!
Good luck|||Thank you very much for the reply, jasie24!
I'm having trouble passing a VB string to a Crystal Report parameter field. Following is an excerpt of the code:
----------------
strDocID = InputBox("Please enter the DocID")
If strDocID <> "" Then
With CrystalReport1
.ReportFileName = "C:\Program Files\CSC Imaging\CSCReports\Reports\DocumentHistory.rpt" .Connect = cn.ConnectionString
.WindowState = crptMaximized
strSelection = "{CSC_TRANS.Document_ID} = " & "'" + strDocID + "'"
.ReplaceSelectionFormula strSelection
.Destination = crptToWindow
.Action = 1
----------------
The user enters the DocID, but when the Crystal Report launches, the user has to enter it again.
Following is the entry in the Record Selection Formula:
{CSC_TRANS.Document_ID} = {?DocumentID_PROMPT}
Also, if they select Cancel in the Crystal parameter window, it still tries to run the report. How can I stop the report from running?
Thank you for your assistance!
Andyeither is wrong the formula,and is crystal report,doesn't know the field. or...
work with an report object,and put
Public reportApp As CRAXDRT.Application
Public Report As CRAXDRT.Report
reportApp=createobject("CRAXDRT.Application")
report=reportApp.loadReport("...rpt")
...for not showing parameter prompting
Report.EnableParameterPrompting=false
report is an object of craxdrt.dll i thing at least i have worked with this one.this is for not showing the parameters inputs again.
also,it should be existing in the report(in design) a parameter(Insert/Parameter) and the value for that should have your docID value
something like this.
Dim cpars As CRAXDRT.ParameterFieldDefinitions
Dim cpar As CRAXDRT.ParameterFieldDefinition
Set cpar = cpars(1)
cpar.AddCurrentValue (docID)
docID here is your VB variable
Maybe this will work,or try to verify carefully the selection formula,AND in report it should be existing a parameter that will take in the code that value!
Good luck|||Thank you very much for the reply, jasie24!
Monday, March 12, 2012
Passing a fields.name.value in the jump to url
I have a web page (asp) that I want to pass the field. value to the url.
ie. http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum=4285
this works but i want them to click on a field
="http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum="+Fields!cianum.Value
no error but no hyper link on the field cellOk i got it figured out the cianum is a (int)field when I "cast(cianum as
varchar(15))as cianum" in my query it worked fine. Is this a bug with
reporting services?
"ljhopkins_LOST and Cant find My Way" wrote:
> I have a web page (asp) that I want to pass the field. value to the url.
> ie. http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum=4285
> this works but i want them to click on a field
> ="http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum="+Fields!cianum.Value
> no error but no hyper link on the field cell
ie. http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum=4285
this works but i want them to click on a field
="http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum="+Fields!cianum.Value
no error but no hyper link on the field cellOk i got it figured out the cianum is a (int)field when I "cast(cianum as
varchar(15))as cianum" in my query it worked fine. Is this a bug with
reporting services?
"ljhopkins_LOST and Cant find My Way" wrote:
> I have a web page (asp) that I want to pass the field. value to the url.
> ie. http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum=4285
> this works but i want them to click on a field
> ="http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum="+Fields!cianum.Value
> no error but no hyper link on the field cell
passing a field name into a parameter
say I have a table with field1,field2,field3,...
and have a SP with a parameter @.myfield
is there a way I can do
Select @.myfield from Table1
thanks for your helpcreate procedure sp_fld (@.inFld varchar(50))
as
begin
declare @.sql varchar(200)
declare @.nsql nvarchar(200)
set @.sql = 'SELECT ' + @.inFld + ' FROM table'
set @.nsql = cast(@.sql as nvarchar(200))
exec sp_executesql @.nsql
end
--
David Rowland
http://dbmonitor.tripod.com|||Don (ir_don@.yahoo.com) writes:
> say I have a table with field1,field2,field3,...
> and have a SP with a parameter @.myfield
> is there a way I can do
> Select @.myfield from Table1
SELECT CASE @.myfield
WHEN 'field1' THEN field1
WHEN 'field2' THEN field2
...
END
FROM tbl
Another post suggested using dynamic SQL, but that's a poor solution.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Use CASE expressions. But if you have the correct design you probably
won't want to do that very often. It isn't a good idea to use column
names to encode data.
--
David Portas
SQL Server MVP
--|||Yes, there is. But before you do this, please get any book on **basic
software engineering** and look up the concept of cohesion in a code
module.|||Erland Sommarskog wrote:
> Don (ir_don@.yahoo.com) writes:
> > say I have a table with field1,field2,field3,...
> > and have a SP with a parameter @.myfield
> > is there a way I can do
> > Select @.myfield from Table1
> SELECT CASE @.myfield
> WHEN 'field1' THEN field1
> WHEN 'field2' THEN field2
> ...
> END
> FROM tbl
> Another post suggested using dynamic SQL, but that's a poor solution.
Except for the fact that the dynamic SQL still works if your columns
are different datatimes.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||dbmonitor (dbmonitor_support@.hotmail.com) writes:
> Erland Sommarskog wrote:
>> Don (ir_don@.yahoo.com) writes:
>> > say I have a table with field1,field2,field3,...
>>> > and have a SP with a parameter @.myfield
>>> > is there a way I can do
>>> > Select @.myfield from Table1
>>
>> SELECT CASE @.myfield
>> WHEN 'field1' THEN field1
>> WHEN 'field2' THEN field2
>> ...
>> END
>> FROM tbl
>>
>> Another post suggested using dynamic SQL, but that's a poor solution.
> Except for the fact that the dynamic SQL still works if your columns
> are different datatimes.
In such case I would question the sanity of the procedure at all. It
would be easier to just sent the SQL statement from the client.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> dbmonitor (dbmonitor_support@.hotmail.com) writes:
> > Erland Sommarskog wrote:
> >> Don (ir_don@.yahoo.com) writes:
> >> > say I have a table with field1,field2,field3,...
> >> >> > and have a SP with a parameter @.myfield
> >> >> > is there a way I can do
> >> >> > Select @.myfield from Table1
> >>
> >> SELECT CASE @.myfield
> >> WHEN 'field1' THEN field1
> >> WHEN 'field2' THEN field2
> >> ...
> >> END
> >> FROM tbl
> >>
> >> Another post suggested using dynamic SQL, but that's a poor
solution.
> > Except for the fact that the dynamic SQL still works if your
columns
> > are different datatimes.
> In such case I would question the sanity of the procedure at all. It
> would be easier to just sent the SQL statement from the client.
You have no arguement from me over that statement.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
Passing A Field Name as a Parameter (Dynamic SQL?)
I'm writing a Stored-Procedure and Id like to pass a field name as a
parameter. It seems simple enough but I can't seem to find the
solution.
Here is a shortened example of the procedure
Select Profiles.Name, Profiles.State, Profiles.City, @.ProfileColumn,
Profiles.County FROM Profiles WHERE State = 'CT'
The @.ProfileColumn is simple a field name in the DB table. The actual
select statement is 5 times as large and consists of many InnerJoins
but I shortened it here for display reasons.
Is there a simply way to do this?You will have to do this with dynamic SQL. It is generally considered a bad
idea to do this in a stored procedure, but it certainly may be fine in
yours.
I am a bit concerned as to how many columns, and if you are spending time in
the joins going to get data that might not be used (like are all of the
columns in the same table or spread around?)
If they are all in one table, and depending on the number of rows, then you
might consider using a temp table to hold the results of the non-dynamic
parts, and then join in the dynamic parts.
If they are spread around, then dynamic SQL might be the best way to go,
since you could possibly eliminate some of the joins if columns aren't
needed.
Just thoughts...
<funphxnaz@.aol.com> wrote in message
news:1137462325.499202.325310@.g44g2000cwa.googlegroups.com...
> I'm writing a Stored-Procedure and Id like to pass a field name as a
> parameter. It seems simple enough but I can't seem to find the
> solution.
> Here is a shortened example of the procedure
>
> Select Profiles.Name, Profiles.State, Profiles.City, @.ProfileColumn,
> Profiles.County FROM Profiles WHERE State = 'CT'
> The @.ProfileColumn is simple a field name in the DB table. The actual
> select statement is 5 times as large and consists of many InnerJoins
> but I shortened it here for display reasons.
> Is there a simply way to do this?
>|||<<Build a VIEW with all the columns you want to see, then filter out
what
you do not need in the front end, where display is supposed to be done.
I actually started this way but the VIEW would have thousands of rows.
<<<< I am a bit concerned as to how many columns, and if you are
spending time in
the joins going to get data that might not be used (like are all of the
columns in the same table or spread around?)>>>>
The columns are spread around many tables. A total of 30 columns + 2 I
am trying to set dynamically.|||Found this:
http://www.sommarskog.se/dyn-search.html
It was helpful.|||(funphxnaz@.aol.com) writes:
> I'm writing a Stored-Procedure and Id like to pass a field name as a
> parameter. It seems simple enough but I can't seem to find the
> solution.
> Here is a shortened example of the procedure
>
> Select Profiles.Name, Profiles.State, Profiles.City, @.ProfileColumn,
> Profiles.County FROM Profiles WHERE State = 'CT'
> The @.ProfileColumn is simple a field name in the DB table. The actual
> select statement is 5 times as large and consists of many InnerJoins
> but I shortened it here for display reasons.
> Is there a simply way to do this?
ProfileColumn = CASE @.ProfileColumn
WHEN 'thiscolumn' THEN 'thiscolumn'
WHEN 'thatcolumn' THEN 'thatcolumn'
..
END
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
parameter. It seems simple enough but I can't seem to find the
solution.
Here is a shortened example of the procedure
Select Profiles.Name, Profiles.State, Profiles.City, @.ProfileColumn,
Profiles.County FROM Profiles WHERE State = 'CT'
The @.ProfileColumn is simple a field name in the DB table. The actual
select statement is 5 times as large and consists of many InnerJoins
but I shortened it here for display reasons.
Is there a simply way to do this?You will have to do this with dynamic SQL. It is generally considered a bad
idea to do this in a stored procedure, but it certainly may be fine in
yours.
I am a bit concerned as to how many columns, and if you are spending time in
the joins going to get data that might not be used (like are all of the
columns in the same table or spread around?)
If they are all in one table, and depending on the number of rows, then you
might consider using a temp table to hold the results of the non-dynamic
parts, and then join in the dynamic parts.
If they are spread around, then dynamic SQL might be the best way to go,
since you could possibly eliminate some of the joins if columns aren't
needed.
Just thoughts...
<funphxnaz@.aol.com> wrote in message
news:1137462325.499202.325310@.g44g2000cwa.googlegroups.com...
> I'm writing a Stored-Procedure and Id like to pass a field name as a
> parameter. It seems simple enough but I can't seem to find the
> solution.
> Here is a shortened example of the procedure
>
> Select Profiles.Name, Profiles.State, Profiles.City, @.ProfileColumn,
> Profiles.County FROM Profiles WHERE State = 'CT'
> The @.ProfileColumn is simple a field name in the DB table. The actual
> select statement is 5 times as large and consists of many InnerJoins
> but I shortened it here for display reasons.
> Is there a simply way to do this?
>|||<<Build a VIEW with all the columns you want to see, then filter out
what
you do not need in the front end, where display is supposed to be done.
I actually started this way but the VIEW would have thousands of rows.
<<<< I am a bit concerned as to how many columns, and if you are
spending time in
the joins going to get data that might not be used (like are all of the
columns in the same table or spread around?)>>>>
The columns are spread around many tables. A total of 30 columns + 2 I
am trying to set dynamically.|||Found this:
http://www.sommarskog.se/dyn-search.html
It was helpful.|||(funphxnaz@.aol.com) writes:
> I'm writing a Stored-Procedure and Id like to pass a field name as a
> parameter. It seems simple enough but I can't seem to find the
> solution.
> Here is a shortened example of the procedure
>
> Select Profiles.Name, Profiles.State, Profiles.City, @.ProfileColumn,
> Profiles.County FROM Profiles WHERE State = 'CT'
> The @.ProfileColumn is simple a field name in the DB table. The actual
> select statement is 5 times as large and consists of many InnerJoins
> but I shortened it here for display reasons.
> Is there a simply way to do this?
ProfileColumn = CASE @.ProfileColumn
WHEN 'thiscolumn' THEN 'thiscolumn'
WHEN 'thatcolumn' THEN 'thatcolumn'
..
END
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
Wednesday, March 7, 2012
Pass user selected parameter
I have a report where the user will clik on a field value and anotherreport will run and appear. I want to pass the field value the userclicks on as a parameter to the report so when the second report appersit will have been filtered by the user selected value.
Pass the parameter in the URL to the report you are opening, and on the PageLoad method of the aspx page that contains the target report, just create a new ReportParameter using the value of the query string, add it to an array, and call report.SetParameters() with that array.
pass in null/blank value in the date field or declare the field as string and co
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@.option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@.mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please helpThe stored procedure will accept null if you define the parameter that way:
create procedure TESTPROCEDURE (@.TestDate datetime = NULL)
as
select @.TestDate
go
exec TESTPROCEDURE '1/1/2003'
go
exec TESTPROCEDURE
go
blindman|||I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.
Try the following:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)
I think the stored proc idea is better though, it's safer and better for your data integrity.
Dan|||Originally posted by danielacroft
I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.
Try the following:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)
I think the stored proc idea is better though, it's safer and better for your data integrity.
Dan
Hello Dan,
What I need is an empty string in the date field to pass in in the stored procedure. What is the vb code for that?
Thanks!|||The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)
I modified your existing code. I'm not 100% sure that this will work but it should.
Dan|||Originally posted by danielacroft
The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)
I modified your existing code. I'm not 100% sure that this will work but it should.
Dan
Thanks for replying so quickly.
I edited my code as you have it above.
I'm stilll having trouble getting the date displaying correctly. I need the year to display in four digits. It displays something '12/31/03'
This is code that I have
Function get_date(mfg_start_date as string,..)
mfg_start_date = Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)
mfg_start_date = CStr(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Year(mfg_start_date))
mfg_start_date is the textbox I need the date field to display but it will only eight digits of the year and place two empty strings after. I can't understand why. In the db design the mfg_start_date field is a char with length 10 as in the stored procedure.
Also there must be a better way to write the code that I have above.
Thank you again.|||The date format is normally determined by the locale settings ont he server when you're using VB. Can I ask why you're not using a date field in your database?
Dan
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@.option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@.mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please helpThe stored procedure will accept null if you define the parameter that way:
create procedure TESTPROCEDURE (@.TestDate datetime = NULL)
as
select @.TestDate
go
exec TESTPROCEDURE '1/1/2003'
go
exec TESTPROCEDURE
go
blindman|||I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.
Try the following:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)
I think the stored proc idea is better though, it's safer and better for your data integrity.
Dan|||Originally posted by danielacroft
I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.
Try the following:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)
I think the stored proc idea is better though, it's safer and better for your data integrity.
Dan
Hello Dan,
What I need is an empty string in the date field to pass in in the stored procedure. What is the vb code for that?
Thanks!|||The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)
I modified your existing code. I'm not 100% sure that this will work but it should.
Dan|||Originally posted by danielacroft
The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)
I modified your existing code. I'm not 100% sure that this will work but it should.
Dan
Thanks for replying so quickly.
I edited my code as you have it above.
I'm stilll having trouble getting the date displaying correctly. I need the year to display in four digits. It displays something '12/31/03'
This is code that I have
Function get_date(mfg_start_date as string,..)
mfg_start_date = Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)
mfg_start_date = CStr(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Year(mfg_start_date))
mfg_start_date is the textbox I need the date field to display but it will only eight digits of the year and place two empty strings after. I can't understand why. In the db design the mfg_start_date field is a char with length 10 as in the stored procedure.
Also there must be a better way to write the code that I have above.
Thank you again.|||The date format is normally determined by the locale settings ont he server when you're using VB. Can I ask why you're not using a date field in your database?
Dan
pass hidden parameter from one dataset to another
I have 2 datasets in my report and I want to pass a field from the 1st dataset as a parameter to the 2nd dataset.
How can I do this?
I tried to set the report parameter from a query using the 1st dataset but the parameter is always equal to the first record in the recordset-it never changes per row.
I used a subreport and passed it a parameter based on each row to get this to work.Saturday, February 25, 2012
Pass a parameter to an url in the default webbrowser
hi
i would like to launch an crystal report file from my vb.net program. This report has 1 parameter field (ID). I would like to give this parameter directly in the URL.
at the moment i use something like this but the parameterwindow still appears.
System.Diagnostics.Process.Start("http://Server/Reports/Report.rpt?ID="&Id)
anyone has a solution?
tnx in advance!
GeertI think there is method to suppress parameterwindow
If there is method like enablepopupmenu then set it to false|||tnx but will supressing the window pass the value? i believe not...
or is there a workaround this problem? like loading the report en then export it?
i would like to launch an crystal report file from my vb.net program. This report has 1 parameter field (ID). I would like to give this parameter directly in the URL.
at the moment i use something like this but the parameterwindow still appears.
System.Diagnostics.Process.Start("http://Server/Reports/Report.rpt?ID="&Id)
anyone has a solution?
tnx in advance!
GeertI think there is method to suppress parameterwindow
If there is method like enablepopupmenu then set it to false|||tnx but will supressing the window pass the value? i believe not...
or is there a workaround this problem? like loading the report en then export it?
Subscribe to:
Posts (Atom)