passing parameter from vb 6 to crystal report 8

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

passing multiple values to a stored procedure

hi
my query in the stored proc is like ::
select * from tableA where name like @.name
where @. name is varchar type
but the input i get from a list box(where multiple values can be selected)
i think i have to write the query like
selct * from tableA where name like @.name1 or name like @.name2 or name like
@.name3...
but the problem is that i have no idea before hand to tell how many values
will be sent as params...
can some one help me
'
thanks
redDo you really mean LIKE @.name? This is the same as = @.name. Maybe you
meant LIKE '%'+@.name+'%'
Anyway, if you are using = it will be much more efficient than LIKE if you
have an index on name. And you can pass in a (limited) comma-separated list
and translate it to a table, and join on that table. See
http://www.aspfaq.com/2248 for an example.
A
On 3/3/05 8:25 PM, in article
18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
<red60man@.discussions.microsoft.com> wrote:
> hi
> my query in the stored proc is like ::
> select * from tableA where name like @.name
> where @. name is varchar type
> but the input i get from a list box(where multiple values can be selected)
> i think i have to write the query like
> selct * from tableA where name like @.name1 or name like @.name2 or name like
> @.name3...
> but the problem is that i have no idea before hand to tell how many values
> will be sent as params...
> can some one help me
> '
> thanks
> red|||You could try passing the selected text fields back as comma seporated
values, and then you can do an IN statement
something like
selct * from tableA where name IN (@.name1, @.name2, @.name3)
That assumes that you really didn't want to use the like statement as in
your example you didn't provide any type of wildcard character
--
Simon Worth
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
> meant LIKE '%'+@.name+'%'
> Anyway, if you are using = it will be much more efficient than LIKE if you
> have an index on name. And you can pass in a (limited) comma-separated
list
> and translate it to a table, and join on that table. See
> http://www.aspfaq.com/2248 for an example.
> A
>
> On 3/3/05 8:25 PM, in article
> 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> <red60man@.discussions.microsoft.com> wrote:
> > hi
> >
> > my query in the stored proc is like ::
> >
> > select * from tableA where name like @.name
> > where @. name is varchar type
> >
> > but the input i get from a list box(where multiple values can be
selected)
> >
> > i think i have to write the query like
> > selct * from tableA where name like @.name1 or name like @.name2 or name
like
> > @.name3...
> >
> > but the problem is that i have no idea before hand to tell how many
values
> > will be sent as params...
> >
> > can some one help me
> > '
> >
> > thanks
> > red
>|||HI,
BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS THERE
SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> selct * from tableA where name IN (@.name1, @.name2, @.name3,........@.nameN)
thanks
red
"Simon Worth" wrote:
> You could try passing the selected text fields back as comma seporated
> values, and then you can do an IN statement
> something like
> selct * from tableA where name IN (@.name1, @.name2, @.name3)
> That assumes that you really didn't want to use the like statement as in
> your example you didn't provide any type of wildcard character
> --
> Simon Worth
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> > Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
> > meant LIKE '%'+@.name+'%'
> >
> > Anyway, if you are using = it will be much more efficient than LIKE if you
> > have an index on name. And you can pass in a (limited) comma-separated
> list
> > and translate it to a table, and join on that table. See
> > http://www.aspfaq.com/2248 for an example.
> >
> > A
> >
> >
> > On 3/3/05 8:25 PM, in article
> > 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> > <red60man@.discussions.microsoft.com> wrote:
> >
> > > hi
> > >
> > > my query in the stored proc is like ::
> > >
> > > select * from tableA where name like @.name
> > > where @. name is varchar type
> > >
> > > but the input i get from a list box(where multiple values can be
> selected)
> > >
> > > i think i have to write the query like
> > > selct * from tableA where name like @.name1 or name like @.name2 or name
> like
> > > @.name3...
> > >
> > > but the problem is that i have no idea before hand to tell how many
> values
> > > will be sent as params...
> > >
> > > can some one help me
> > > '
> > >
> > > thanks
> > > red
> >
>
>|||Did you see my post? WHY ARE YOU YELLING?
--
http://www.aspfaq.com/
(Reverse address to reply.)
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED|||In your application format the selected text fields to be returned to the
stored procedure as comma seporated values.
So if the user selects John Doe, Jane Smith, and Fred Flintstone
format the selections to come back as 1 paramater
Var = Selection1 & ", " & Selection2 & ", " & Selection3
Value of Var = "John Doe, Jane Smith, Fred Flintstone"
In the stored procedure, the select statement would look somewhat like this,
but enhanced
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = '''John Doe'''
set @.var1 = @.var1 + ', ''Snow White'''
set @.var1 = @.var1 + ', ''Fred Flintstone'''
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> HI,
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> > selct * from tableA where name IN (@.name1, @.name2,
@.name3,........@.nameN)
> thanks
> red
> "Simon Worth" wrote:
> > You could try passing the selected text fields back as comma seporated
> > values, and then you can do an IN statement
> > something like
> > selct * from tableA where name IN (@.name1, @.name2, @.name3)
> >
> > That assumes that you really didn't want to use the like statement as in
> > your example you didn't provide any type of wildcard character
> >
> > --
> > Simon Worth
> >
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> > > Do you really mean LIKE @.name? This is the same as = @.name. Maybe
you
> > > meant LIKE '%'+@.name+'%'
> > >
> > > Anyway, if you are using = it will be much more efficient than LIKE if
you
> > > have an index on name. And you can pass in a (limited)
comma-separated
> > list
> > > and translate it to a table, and join on that table. See
> > > http://www.aspfaq.com/2248 for an example.
> > >
> > > A
> > >
> > >
> > > On 3/3/05 8:25 PM, in article
> > > 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> > > <red60man@.discussions.microsoft.com> wrote:
> > >
> > > > hi
> > > >
> > > > my query in the stored proc is like ::
> > > >
> > > > select * from tableA where name like @.name
> > > > where @. name is varchar type
> > > >
> > > > but the input i get from a list box(where multiple values can be
> > selected)
> > > >
> > > > i think i have to write the query like
> > > > selct * from tableA where name like @.name1 or name like @.name2 or
name
> > like
> > > > @.name3...
> > > >
> > > > but the problem is that i have no idea before hand to tell how many
> > values
> > > > will be sent as params...
> > > >
> > > > can some one help me
> > > > '
> > > >
> > > > thanks
> > > > red
> > >
> >
> >
> >|||Oh sorry, I messed that example up.. My appologies
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = @.ParameterPassed
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:u3SV14OIFHA.3072@.tk2msftngp13.phx.gbl...
> In your application format the selected text fields to be returned to the
> stored procedure as comma seporated values.
> So if the user selects John Doe, Jane Smith, and Fred Flintstone
> format the selections to come back as 1 paramater
> Var = Selection1 & ", " & Selection2 & ", " & Selection3
> Value of Var = "John Doe, Jane Smith, Fred Flintstone"
>
> In the stored procedure, the select statement would look somewhat like
this,
> but enhanced
> declare @.SQL nvarchar(200)
> declare @.var1 as varchar(100)
> set @.var1 = '''John Doe'''
> set @.var1 = @.var1 + ', ''Snow White'''
> set @.var1 = @.var1 + ', ''Fred Flintstone'''
> set @.SQL = 'select * from TableName where CustName in ('
> set @.SQL = @.SQL + @.Var1 + ')'
> EXECUTE sp_executesql @.SQL
>
> --
> Simon Worth
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> > HI,
> >
> > BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
> THERE
> > SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> > > selct * from tableA where name IN (@.name1, @.name2,
> @.name3,........@.nameN)
> >
> > thanks
> > red
> >
> > "Simon Worth" wrote:
> >
> > > You could try passing the selected text fields back as comma seporated
> > > values, and then you can do an IN statement
> > > something like
> > > selct * from tableA where name IN (@.name1, @.name2, @.name3)
> > >
> > > That assumes that you really didn't want to use the like statement as
in
> > > your example you didn't provide any type of wildcard character
> > >
> > > --
> > > Simon Worth
> > >
> > >
> > > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > > news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> > > > Do you really mean LIKE @.name? This is the same as = @.name. Maybe
> you
> > > > meant LIKE '%'+@.name+'%'
> > > >
> > > > Anyway, if you are using = it will be much more efficient than LIKE
if
> you
> > > > have an index on name. And you can pass in a (limited)
> comma-separated
> > > list
> > > > and translate it to a table, and join on that table. See
> > > > http://www.aspfaq.com/2248 for an example.
> > > >
> > > > A
> > > >
> > > >
> > > > On 3/3/05 8:25 PM, in article
> > > > 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> > > > <red60man@.discussions.microsoft.com> wrote:
> > > >
> > > > > hi
> > > > >
> > > > > my query in the stored proc is like ::
> > > > >
> > > > > select * from tableA where name like @.name
> > > > > where @. name is varchar type
> > > > >
> > > > > but the input i get from a list box(where multiple values can be
> > > selected)
> > > > >
> > > > > i think i have to write the query like
> > > > > selct * from tableA where name like @.name1 or name like @.name2 or
> name
> > > like
> > > > > @.name3...
> > > > >
> > > > > but the problem is that i have no idea before hand to tell how
many
> > > values
> > > > > will be sent as params...
> > > > >
> > > > > can some one help me
> > > > > '
> > > > >
> > > > > thanks
> > > > > red
> > > >
> > >
> > >
> > >
>|||i am sorry, i was not yelling .....my caps was on and i didnt notice it
"Aaron [SQL Server MVP]" wrote:
> Did you see my post? WHY ARE YOU YELLING?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
> > BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
> THERE
> > SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
>
>

passing column name as parameter to a stored procedure

Hi!
I want to pass a column name, sometimes a table name, to a stored
procedure, but it didn't work. I tried to define the data type as
char, vachar, nchar, text, but the result were same. Any one know how
to get it work?
Thanks a lot!!
Saiyou[posted and mailed, please reply in news]

Saiyou Anh (wangc@.alexian.net) writes:
> I want to pass a column name, sometimes a table name, to a stored
> procedure, but it didn't work. I tried to define the data type as
> char, vachar, nchar, text, but the result were same. Any one know how
> to get it work?

So why do you need to do this?

While this is possible to do this, you might essentially be throwing
out the baby with the bathtub and loose most of the advantages of
stored procedures. Often this is a token of bad design.

Anyway, I have a longer article on my web site that shows you how to do
it - and why you probably shouldn't.

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 large parameter to s stored procedure

We have a developer who is trying to pass a large
parameter (type = text) into a stored procedure. The
parameter he is trying to pass is the contents of a 56mb
XML file.
The procedure works great with the next largest XML file
(about 30mb), but we are getting a network error when
trying to pass this large parameter.
Has anyone run into this... Any ideasHi
The batch size is limited to (65'536 * Network Packet Size) or (65'536 * 512
= 33Mb)
The developer neeeds to chunk the data in.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconservingresourceswhenwritingblobvaluestosqlserver.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthappendchunkx.asp
Regards
Mike
"WEX" wrote:
> We have a developer who is trying to pass a large
> parameter (type = text) into a stored procedure. The
> parameter he is trying to pass is the contents of a 56mb
> XML file.
> The procedure works great with the next largest XML file
> (about 30mb), but we are getting a network error when
> trying to pass this large parameter.
> Has anyone run into this... Any ideas
>

Passing a large parameter to s stored procedure

We have a developer who is trying to pass a large
parameter (type = text) into a stored procedure. The
parameter he is trying to pass is the contents of a 56mb
XML file.
The procedure works great with the next largest XML file
(about 30mb), but we are getting a network error when
trying to pass this large parameter.
Has anyone run into this... Any ideas
Hi
The batch size is limited to (65'536 * Network Packet Size) or (65'536 * 512
= 33Mb)
The developer neeeds to chunk the data in.
http://msdn.microsoft.com/library/de...ql server.asp
http://msdn.microsoft.com/library/de...pendchunkx.asp
Regards
Mike
"WEX" wrote:

> We have a developer who is trying to pass a large
> parameter (type = text) into a stored procedure. The
> parameter he is trying to pass is the contents of a 56mb
> XML file.
> The procedure works great with the next largest XML file
> (about 30mb), but we are getting a network error when
> trying to pass this large parameter.
> Has anyone run into this... Any ideas
>

Pass text (or, best, ntext) type value to an OLE Automation function call

Hello!
Is that possible to create a user-defined function, that would take value of
type text, and pass it to the OLE Automation function call?
The SP code I use to do OLE Aut. call, is as follows:
DECLARE @.object int
DECLARE @.hr int
DECLARE @.property nvarchar(255)
DECLARE @.src nvarchar(255), @.desc nvarchar(255)
DECLARE @.return nvarchar(255)
EXEC @.hr = sp_OACreate 'Blah.Something', @.object OUT
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
EXEC @.hr = sp_OAMethod @.object, 'myMeth', @.return OUT, 'Test string'
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
PRINT @.return
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
****
I have currently working version, that uses char type, but I'd prefer to use
text or even ntext type. It seems like, that if it is not possible to pass
the text-type value to the OLE function call, I will be furced to pass a
record ID instead, and read the text value within the external code from the
same database, what actually sounds like risky way.
Thanks,
PavilsThere is no way to declare a local n/text variable. So, the answer is 'no'.
-oj
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:ufTfn5KTFHA.548@.tk2msftngp13.phx.gbl...
> Hello!
> Is that possible to create a user-defined function, that would take value
> of type text, and pass it to the OLE Automation function call?
> The SP code I use to do OLE Aut. call, is as follows:
> DECLARE @.object int
> DECLARE @.hr int
> DECLARE @.property nvarchar(255)
> DECLARE @.src nvarchar(255), @.desc nvarchar(255)
> DECLARE @.return nvarchar(255)
> EXEC @.hr = sp_OACreate 'Blah.Something', @.object OUT
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.object, 'myMeth', @.return OUT, 'Test string'
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> PRINT @.return
> EXEC @.hr = sp_OADestroy @.object
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> ****
> I have currently working version, that uses char type, but I'd prefer to
> use text or even ntext type. It seems like, that if it is not possible to
> pass the text-type value to the OLE function call, I will be furced to
> pass a record ID instead, and read the text value within the external code
> from the same database, what actually sounds like risky way.
> Thanks,
> Pavils
>

pass in null value for boolean data type in VB

What is the syntax to pass in null value for boolean data type in VB in the stored procedure?
This is what I tried and it doesn't work.
.Parameters.Append .CreateParameter("@.disposition", adBoolean, adParamInput, 1, vbNull)
Thank you.I'm no vb programmer (at least that's what I tell people)

How about:

.Parameters.Append .CreateParameter("@.disposition", adBoolean, adParamInput, 1, "Null")

??