Calling a SQL stored procedure

[David Davis]

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

[Bernie Yaeger]

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, pissuecode As SqlParameter

ocmd2.CommandType = CommandType.StoredProcedure

pimcacct = ocmd2.Parameters.Add("@mimcacct", SqlDbType.Char, 21)

pbipad = ocmd2.Parameters.Add("@mbipad", SqlDbType.Char, 6)

pissuecode = ocmd2.Parameters.Add("@missuecode", SqlDbType.Char, 15)

pimcacct.Direction = ParameterDirection.Input

pbipad.Direction = ParameterDirection.Input

pissuecode.Direction = ParameterDirection.Input

pimcacct.Value = "12778-000001"

pbipad.Value = "92789"

pissuecode.Value = "200406"

Try

ocmd2.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

oconn.Close()

"David Davis" <Da********@discussions.microsoft.com> wrote in message
news:64**********************************@microsof t.com...

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

[David Davis]

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, pissuecode As SqlParameter

ocmd2.CommandType = CommandType.StoredProcedure

pimcacct = ocmd2.Parameters.Add("@mimcacct", SqlDbType.Char, 21)

pbipad = ocmd2.Parameters.Add("@mbipad", SqlDbType.Char, 6)

pissuecode = ocmd2.Parameters.Add("@missuecode", SqlDbType.Char, 15)

pimcacct.Direction = ParameterDirection.Input

pbipad.Direction = ParameterDirection.Input

pissuecode.Direction = ParameterDirection.Input

pimcacct.Value = "12778-000001"

pbipad.Value = "92789"

pissuecode.Value = "200406"

Try

ocmd2.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

oconn.Close()

"David Davis" <Da********@discussions.microsoft.com> wrote in message
news:64**********************************@microsof t.com...

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

[C-Services Holland b.v.]

David Davis wrote:

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:

about this: dStart.Value = Me.dteStart.DateTime.ToShortDateString
Why do you convert it to shortdatestring?
--
Rinze van Huizen
C-Services Holland b.v.

[David Davis]

I'm using the shortdatestring because even though the field in sql is
datetime they are only storing the first 8 positions (the date).

I found out that the problem was not with the assigning of the parameter to
pass but a header line:

'For ' + @dStart + ' thru ' + @dEnd AS hdrLine

I had to change the header line to look like this:

'For ' + Convert(VarChar, @dStart, 101) + ' thru ' + Convert(VarChar,@dEnd,
101) AS hdrLine

"C-Services Holland b.v." wrote:

David Davis wrote:

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:

about this: dStart.Value = Me.dteStart.DateTime.ToShortDateString
Why do you convert it to shortdatestring?
--
Rinze van Huizen
C-Services Holland b.v.

[Bernie Yaeger]

Hi David,

I think it's the value of the date variables that is the problem.

The following works fine for me:
Dim ocmd3 As New SqlCommand

ocmd3 = New SqlCommand("testdatevar", oconn)

Dim oda3 As New SqlDataAdapter(ocmd3)

Dim pdate As SqlParameter

ocmd3.CommandType = CommandType.StoredProcedure

pdate = ocmd3.Parameters.Add("@datevar", SqlDbType.DateTime, 8)

pdate.Direction = ParameterDirection.Input

pdate.Value = #1/1/2004#

Try

ocmd3.ExecuteNonQuery()

MessageBox.Show("fine")

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

HTH,

Bernie Yaeger

"David Davis" <Da********@discussions.microsoft.com> wrote in message
news:BE**********************************@microsof t.com...

I'm using the shortdatestring because even though the field in sql is
datetime they are only storing the first 8 positions (the date).

I found out that the problem was not with the assigning of the parameter
to
pass but a header line:

'For ' + @dStart + ' thru ' + @dEnd AS hdrLine

I had to change the header line to look like this:

'For ' + Convert(VarChar, @dStart, 101) + ' thru ' +
Convert(VarChar,@dEnd,
101) AS hdrLine

"C-Services Holland b.v." wrote:

David Davis wrote:

> 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:
>

about this: dStart.Value = Me.dteStart.DateTime.ToShortDateString
Why do you convert it to shortdatestring?
--
Rinze van Huizen
C-Services Holland b.v.