Bernie
Thanks for the code.
I'm able to assign values to the parameters. The only problem I have now is
that I get a syntax error
"Syntax error converting datetime from character string."
How do I pass a datetime value to a datetime parameter. Following is the
code for the parameters.
Dim dStart, dEnd As SqlParameter
dStart = SprocCommand.Parameters.Add("@dStart", SqlDbType.DateTime, 8)
dEnd = SprocCommand.Parameters.Add("@dEnd", SqlDbType.DateTime, 8)
dStart.Direction = ParameterDirection.Input
dEnd.Direction = ParameterDirection.Input
dStart.Value = Me.dteStart.DateTime.ToShortDateString
dEnd.Value = Me.dteEnd.DateTime.ToShortDateString
Thanks
David Davis
"Bernie Yaeger" wrote:
> Hi David,
>
> This should give you the general idea of how to use a command object to
> build the parameters and then to execute the stored proc. ocmd2 is the
> command object you'll be interested in.
>
> HTH,
>
> Bernie Yaeger
> Dim oconn As New SqlConnection("data source=d5z0071;initial
> catalog=imc;integrated security=sspi;")
>
> Dim ocmd As New SqlCommand("select * from histd_", oconn)
>
> Try
>
> oconn.Open()
>
> Catch ex As Exception
>
> MessageBox.Show(ex.Message)
>
> End Try
>
> Dim ocmd2 As New SqlCommand
>
> ocmd2 = New SqlCommand("sp_testupdatehistd_", oconn)
>
> Dim oda As New SqlDataAdapter(ocmd2)
>
> Dim pimcacct, pbipad, ****uecode As SqlParameter
>
> ocmd2.CommandType = CommandType.StoredProcedure
>
> pimcacct = ocmd2.Parameters.Add("@mimcacct", SqlDbType.Char, 21)
>
> pbipad = ocmd2.Parameters.Add("@mbipad", SqlDbType.Char, 6)
>
> ****uecode = ocmd2.Parameters.Add("@missuecode", SqlDbType.Char, 15)
>
> pimcacct.Direction = ParameterDirection.Input
>
> pbipad.Direction = ParameterDirection.Input
>
> ****uecode.Direction = ParameterDirection.Input
>
> pimcacct.Value = "12778-000001"
>
> pbipad.Value = "92789"
>
> ****uecode.Value = "200406"
>
> Try
>
> ocmd2.ExecuteNonQuery()
>
> Catch ex As Exception
>
> MessageBox.Show(ex.Message)
>
> End Try
>
> oconn.Close()
>
> "David Davis" <(E-Mail Removed)> wrote in message
> news:64A2E85A-22E0-4AF9-9F0A-(E-Mail Removed)...
> >I have the following stored procedure in an sql database
> >
> > ------------------------------------
> > CREATE PROCEDURE zspQuoteSummary
> > @dStart DateTime,
> > @dEnd DateTime
> > AS
> >
> > SELECT qtmast.fstatus,
> > qtmast.festimator,
> > qtmast.fcompany,
> > qtmast.fquoteno AS Quote,
> > qtmast.frevno AS Rev,
> > qtmast.fquotedate,
> > qtitem.fenumber AS ItemNumber,
> > qtitem.fpartno AS Part,
> > qtitem.fpartrev AS PartRev,
> > qtpest.fmatlcost + qtpest.ftoolcost + qtpest.flabcost + qtpest.fovhdcost +
> > qtpest.fsubcost + qtpest.fothrcost + qtpest.fsetupcost AS TotalCost,
> > qtitem.funetprice AS UnitPrice,
> > qtitem.festqty AS Qty,
> > qtitem.funetprice*qtitem.festqty AS Extended,
> >
> > GM =
> > CASE
> > WHEN (qtitem.funetprice*qtitem.festqty) <> 0 THEN
> > ((qtitem.funetprice*qtitem.festqty) - (qtpest.fmatlcost + qtpest.ftoolcost
> > +
> > qtpest.flabcost + qtpest.fovhdcost + qtpest.fsubcost + qtpest.fothrcost +
> > qtpest.fsetupcost) )/(qtitem.funetprice*qtitem.festqty)
> > ELSE
> > 0
> > END,
> >
> > EAU =
> > CASE
> > WHEN SUBSTRING(qtitem.fdescmemo,1,4) = UPPER('EAU:') THEN
> > SUBSTRING(qtitem.fdescmemo,5,10)
> > ELSE
> > 0
> > END,
> >
> > EAUExtended =
> > CASE
> > WHEN SUBSTRING(qtitem.fdescmemo,1,4) = UPPER('EAU:') THEN
> > SUBSTRING(qtitem.fdescmemo,5,10)* qtitem.funetprice
> > ELSE
> > 0
> > END,
> >
> > EAUGM =
> > CASE
> > WHEN SUBSTRING(qtitem.fdescmemo,1,4) = UPPER('EAU:') THEN
> > CASE
> > WHEN SUBSTRING(qtitem.fdescmemo,5,10)* qtitem.funetprice <> 0 THEN
> > ((SUBSTRING(qtitem.fdescmemo,5,10)* qtitem.funetprice) - (qtpest.fmatlcost
> > +
> > qtpest.ftoolcost + qtpest.flabcost + qtpest.fovhdcost + qtpest.fsubcost +
> > qtpest.fothrcost + qtpest.fsetupcost)) /
> > (SUBSTRING(qtitem.fdescmemo,5,10)*
> > qtitem.funetprice)
> > ELSE
> > 0
> > END
> > ELSE
> > 0
> > END,
> >
> > 'For ' + @dStart + ' thru ' + @dEnd AS Parameters
> > INTO ztmpQuoteSummary
> > FROM qtmast INNER JOIN (qtitem INNER JOIN qtpest ON
> > (qtitem.fpartrev=qtpest.fcpartrev) AND (qtitem.fpartno=qtpest.fpartno) AND
> > (qtitem.finumber=qtpest.finumber) AND (qtitem.fenumber=qtpest.fenumber)
> > AND
> > (qtitem.fquoteno=qtpest.fquoteno)) ON qtmast.fquoteno=qtitem.fquoteno
> > WHERE (((qtmast.fquotedate)>= @dStart And (qtmast.fquotedate)<=@dEnd))
> > ORDER BY qtmast.fcompany, qtmast.fquoteno, qtmast.frevno;
> > GO
> > ------------------------------------
> >
> > how do I call this proc from within VB
> > --
> > Thanks
> >
> > David Davis
>
>
>
Similar Threads
