STORED PROCEDURE is not working HELP

  • Thread starter Thread starter Joriz
  • Start date Start date
J

Joriz

I have this code in my btnclick event.

Dim cnn As New SqlConnection(Constants.SQLConnectionString)
Dim cmd As New SqlCommand


Dim cmd As New SqlCommand("spAverage", cnn)
cnn.Open()



cmd.CommandType = CommandType.StoredProcedure
Dim prm1 As SqlParameter = cmd.Parameters.Add("@sdate",
txtsdate.Text)
Dim prm2 As SqlParameter = cmd.Parameters.Add("@edate",
txtedate.Text)
cmd.ExecuteNonQuery(CommandBehavior.CloseConnection)

The spAverage does not return any resultset, but it does create a new
table with the all the rows based on the date range parameter that i
passed.

but right after i execute the sp and i check the table that was
created. its empty..altho i know there should be rows in there.

anybody have this problem as well.
Please HELP


here's my sp.

CREATE PROCEDURE spAverage @sdate NVARCHAR(10),@edate NVARCHAR(10)
AS

SET NOCOUNT ON

SET @sdate = CONVERT(NVARCHAR(8),CAST(@sdate AS DATETIME),112)
SET @edate = CONVERT(NVARCHAR(8),CAST(@edate AS DATETIME),112)

IF not object_id('tempdb..#t1') is null
DROP TABLE #t1

SELECT r.qid,svalue1 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO #T1
FROM groupq g
INNER JOIN questionaires q ON q.groupid = g.groupid
INNER JOIN result r ON r.qid = q.qid
INNER JOIN participant p ON p.participantid=r.participantid
INNER JOIN lkcourse l ON l.courseid= p.levelid
WHERE q.ratetype IN (1,2) and surveyvalue is not null and
surveyvalue <> 0 AND l.grp IN ('G')
AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
@edate)
GROUP BY r.qid
order by r.qid

if not object_id('tempdb..#t2') is null
drop table #t2

select qid2=r.qid,svalue2 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO
#T2
FROM groupq g
INNER JOIN questionaires q ON q.groupid = g.groupid
INNER JOIN result r ON r.qid = q.qid
INNER JOIN participant p ON p.participantid=r.participantid
INNER JOIN lkcourse l ON l.courseid= p.levelid
WHERE q.ratetype IN (1,2) and surveyvalue is not null and
surveyvalue <> 0 AND l.grp IN ('M')
AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
@edate)
GROUP BY r.qid
order by r.qid
if not object_id('tempdb..#t3') is null
drop table #t3

select qid3=r.qid,svalue3 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO
#T3
FROM groupq g
INNER JOIN questionaires q ON q.groupid = g.groupid
INNER JOIN result r ON r.qid = q.qid
INNER JOIN participant p ON p.participantid=r.participantid
INNER JOIN lkcourse l ON l.courseid= p.levelid
WHERE q.ratetype IN (1,2) and surveyvalue is not null and
surveyvalue <> 0 AND l.grp IN ('P')
AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
@edate)
GROUP BY r.qid
order by r.qid

if not object_id('tempdb..#t4') is null
drop table #t4

SELECT * INTO #T4 FROM #T1 t1
FULL JOIN #T2 t2 ON t2.qid2 = t1.qid
FULL JOIN #T3 t3 ON t3.qid3 = t1.qid

UPDATE #T4 SET qid=ISNULL(qid2,qid3) WHERE ISNULL(qid,'')=''

if EXISTS(select [name] from sysobjects where [name] = 'tpAverage')
DROP TABLE tpAverage

select tp.topicid,tp.topicdesc,g.qnum,g.groupdesc,q.qdesc,
t4.qid,t4.svalue1,t4.svalue2,t4.svalue3
INTO tpAverage
FROM #t4 t4
INNER JOIN questionaires q ON q.qid=t4.qid
INNER JOIN groupq g ON g.groupid = q.groupid
INNER JOIN topics tp ON tp.topicid = g.topicid
ORDER BY tp.topicid, qnum, t4.qid

-- SELECT * FROM tpAverage
SET NOCOUNT OFF
GO
 
You're using executenonquery.

take out cmd.ExecuteNonQuery(CommandBehavior.CloseConnection)
Replace with

Dim da as new sqldataadapter(cmd)
Dim ds as new dataset

da.fill(ds, "TableName")

Dim dr as datarow

for each dr in ds.tables("TableName").Rows
.....
Next


Joriz said:
I have this code in my btnclick event.

Dim cnn As New SqlConnection(Constants.SQLConnectionString)
Dim cmd As New SqlCommand


Dim cmd As New SqlCommand("spAverage", cnn)
cnn.Open()



cmd.CommandType = CommandType.StoredProcedure
Dim prm1 As SqlParameter = cmd.Parameters.Add("@sdate",
txtsdate.Text)
Dim prm2 As SqlParameter = cmd.Parameters.Add("@edate",
txtedate.Text)
cmd.ExecuteNonQuery(CommandBehavior.CloseConnection)

The spAverage does not return any resultset, but it does create a new
table with the all the rows based on the date range parameter that i
passed.

but right after i execute the sp and i check the table that was
created. its empty..altho i know there should be rows in there.

anybody have this problem as well.
Please HELP


here's my sp.

CREATE PROCEDURE spAverage @sdate NVARCHAR(10),@edate NVARCHAR(10)
AS

SET NOCOUNT ON

SET @sdate = CONVERT(NVARCHAR(8),CAST(@sdate AS DATETIME),112)
SET @edate = CONVERT(NVARCHAR(8),CAST(@edate AS DATETIME),112)

IF not object_id('tempdb..#t1') is null
DROP TABLE #t1

SELECT r.qid,svalue1 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO #T1
FROM groupq g
INNER JOIN questionaires q ON q.groupid = g.groupid
INNER JOIN result r ON r.qid = q.qid
INNER JOIN participant p ON p.participantid=r.participantid
INNER JOIN lkcourse l ON l.courseid= p.levelid
WHERE q.ratetype IN (1,2) and surveyvalue is not null and
surveyvalue <> 0 AND l.grp IN ('G')
AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
@edate)
GROUP BY r.qid
order by r.qid

if not object_id('tempdb..#t2') is null
drop table #t2

select qid2=r.qid,svalue2 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO
#T2
FROM groupq g
INNER JOIN questionaires q ON q.groupid = g.groupid
INNER JOIN result r ON r.qid = q.qid
INNER JOIN participant p ON p.participantid=r.participantid
INNER JOIN lkcourse l ON l.courseid= p.levelid
WHERE q.ratetype IN (1,2) and surveyvalue is not null and
surveyvalue <> 0 AND l.grp IN ('M')
AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
@edate)
GROUP BY r.qid
order by r.qid
if not object_id('tempdb..#t3') is null
drop table #t3

select qid3=r.qid,svalue3 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO
#T3
FROM groupq g
INNER JOIN questionaires q ON q.groupid = g.groupid
INNER JOIN result r ON r.qid = q.qid
INNER JOIN participant p ON p.participantid=r.participantid
INNER JOIN lkcourse l ON l.courseid= p.levelid
WHERE q.ratetype IN (1,2) and surveyvalue is not null and
surveyvalue <> 0 AND l.grp IN ('P')
AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
@edate)
GROUP BY r.qid
order by r.qid

if not object_id('tempdb..#t4') is null
drop table #t4

SELECT * INTO #T4 FROM #T1 t1
FULL JOIN #T2 t2 ON t2.qid2 = t1.qid
FULL JOIN #T3 t3 ON t3.qid3 = t1.qid

UPDATE #T4 SET qid=ISNULL(qid2,qid3) WHERE ISNULL(qid,'')=''

if EXISTS(select [name] from sysobjects where [name] = 'tpAverage')
DROP TABLE tpAverage

select tp.topicid,tp.topicdesc,g.qnum,g.groupdesc,q.qdesc,
t4.qid,t4.svalue1,t4.svalue2,t4.svalue3
INTO tpAverage
FROM #t4 t4
INNER JOIN questionaires q ON q.qid=t4.qid
INNER JOIN groupq g ON g.groupid = q.groupid
INNER JOIN topics tp ON tp.topicid = g.topicid
ORDER BY tp.topicid, qnum, t4.qid

-- SELECT * FROM tpAverage
SET NOCOUNT OFF
GO
Click to expand...
 
Joriz:

Have you verified the procs execution outside of ADO.NET (ie query
analyzer)?

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
Joriz said:
I have this code in my btnclick event.

Dim cnn As New SqlConnection(Constants.SQLConnectionString)
Dim cmd As New SqlCommand


Dim cmd As New SqlCommand("spAverage", cnn)
cnn.Open()



cmd.CommandType = CommandType.StoredProcedure
Dim prm1 As SqlParameter = cmd.Parameters.Add("@sdate",
txtsdate.Text)
Dim prm2 As SqlParameter = cmd.Parameters.Add("@edate",
txtedate.Text)
cmd.ExecuteNonQuery(CommandBehavior.CloseConnection)

The spAverage does not return any resultset, but it does create a new
table with the all the rows based on the date range parameter that i
passed.

but right after i execute the sp and i check the table that was
created. its empty..altho i know there should be rows in there.

anybody have this problem as well.
Please HELP


here's my sp.

CREATE PROCEDURE spAverage @sdate NVARCHAR(10),@edate NVARCHAR(10)
AS

SET NOCOUNT ON

SET @sdate = CONVERT(NVARCHAR(8),CAST(@sdate AS DATETIME),112)
SET @edate = CONVERT(NVARCHAR(8),CAST(@edate AS DATETIME),112)

IF not object_id('tempdb..#t1') is null
DROP TABLE #t1

SELECT r.qid,svalue1 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO #T1
FROM groupq g
INNER JOIN questionaires q ON q.groupid = g.groupid
INNER JOIN result r ON r.qid = q.qid
INNER JOIN participant p ON p.participantid=r.participantid
INNER JOIN lkcourse l ON l.courseid= p.levelid
WHERE q.ratetype IN (1,2) and surveyvalue is not null and
surveyvalue <> 0 AND l.grp IN ('G')
AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
@edate)
GROUP BY r.qid
order by r.qid

if not object_id('tempdb..#t2') is null
drop table #t2

select qid2=r.qid,svalue2 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO
#T2
FROM groupq g
INNER JOIN questionaires q ON q.groupid = g.groupid
INNER JOIN result r ON r.qid = q.qid
INNER JOIN participant p ON p.participantid=r.participantid
INNER JOIN lkcourse l ON l.courseid= p.levelid
WHERE q.ratetype IN (1,2) and surveyvalue is not null and
surveyvalue <> 0 AND l.grp IN ('M')
AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
@edate)
GROUP BY r.qid
order by r.qid
if not object_id('tempdb..#t3') is null
drop table #t3

select qid3=r.qid,svalue3 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO
#T3
FROM groupq g
INNER JOIN questionaires q ON q.groupid = g.groupid
INNER JOIN result r ON r.qid = q.qid
INNER JOIN participant p ON p.participantid=r.participantid
INNER JOIN lkcourse l ON l.courseid= p.levelid
WHERE q.ratetype IN (1,2) and surveyvalue is not null and
surveyvalue <> 0 AND l.grp IN ('P')
AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
@edate)
GROUP BY r.qid
order by r.qid

if not object_id('tempdb..#t4') is null
drop table #t4

SELECT * INTO #T4 FROM #T1 t1
FULL JOIN #T2 t2 ON t2.qid2 = t1.qid
FULL JOIN #T3 t3 ON t3.qid3 = t1.qid

UPDATE #T4 SET qid=ISNULL(qid2,qid3) WHERE ISNULL(qid,'')=''

if EXISTS(select [name] from sysobjects where [name] = 'tpAverage')
DROP TABLE tpAverage

select tp.topicid,tp.topicdesc,g.qnum,g.groupdesc,q.qdesc,
t4.qid,t4.svalue1,t4.svalue2,t4.svalue3
INTO tpAverage
FROM #t4 t4
INNER JOIN questionaires q ON q.qid=t4.qid
INNER JOIN groupq g ON g.groupid = q.groupid
INNER JOIN topics tp ON tp.topicid = g.topicid
ORDER BY tp.topicid, qnum, t4.qid

-- SELECT * FROM tpAverage
SET NOCOUNT OFF
GO
Click to expand...
 
the sp works fine in QA. and like i said i used ExecuteNonQuery coz
the sp does not return any resultset.

But what i found is when i remove the date parameters in my SP. it
works fine. so it gotta be the date parameters. I played around with
it already but nothing seems to work. Im really not sure whats
causing the problem.

Please help..thank you very much
 
i tried this as well

Dim cnn As New SqlConnection(Constants.SQLConnectionString)
Dim cmd As New SqlCommand("spAverage")


cmd.CommandType = CommandType.StoredProcedure

Dim prm1 As SqlParameter
prm1 = New SqlParameter("@sdate", SqlDbType.NVarChar, 20)
Dim prm2 As SqlParameter
prm2 = New SqlParameter("@edate", SqlDbType.NVarChar, 20)

cmd.Parameters.Add(prm1)
cmd.Parameters.Add(prm2)

prm1.Value = "01/01/2001" 'txtsdate.Text
prm1.Direction = ParameterDirection.Input
prm2.Value = "01/01/2004" 'txtedate.Text"
prm2.Direction = ParameterDirection.Input


cnn.Open()
cmd.Connection = cnn
cmd.ExecuteNonQuery()

PLEASE HELP!!!
thanks
 
1) Turn on the SQL Profiler to see what's getting executed.
2) I would setup the parameter as a DateTime or SmallDateTime (depending on
how it's defined in the SP).
3) It would help to see the SP code. What tables does it create? If they are
#temp tables, consider that these are dropped after the SP that creates them
ends.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Joriz said:
I have this code in my btnclick event.

Dim cnn As New SqlConnection(Constants.SQLConnectionString)
Dim cmd As New SqlCommand


Dim cmd As New SqlCommand("spAverage", cnn)
cnn.Open()



cmd.CommandType = CommandType.StoredProcedure
Dim prm1 As SqlParameter = cmd.Parameters.Add("@sdate",
txtsdate.Text)
Dim prm2 As SqlParameter = cmd.Parameters.Add("@edate",
txtedate.Text)
cmd.ExecuteNonQuery(CommandBehavior.CloseConnection)

The spAverage does not return any resultset, but it does create a new
table with the all the rows based on the date range parameter that i
passed.

but right after i execute the sp and i check the table that was
created. its empty..altho i know there should be rows in there.

anybody have this problem as well.
Please HELP


here's my sp.

CREATE PROCEDURE spAverage @sdate NVARCHAR(10),@edate NVARCHAR(10)
AS

SET NOCOUNT ON

SET @sdate = CONVERT(NVARCHAR(8),CAST(@sdate AS DATETIME),112)
SET @edate = CONVERT(NVARCHAR(8),CAST(@edate AS DATETIME),112)

IF not object_id('tempdb..#t1') is null
DROP TABLE #t1

SELECT r.qid,svalue1 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO #T1
FROM groupq g
INNER JOIN questionaires q ON q.groupid = g.groupid
INNER JOIN result r ON r.qid = q.qid
INNER JOIN participant p ON p.participantid=r.participantid
INNER JOIN lkcourse l ON l.courseid= p.levelid
WHERE q.ratetype IN (1,2) and surveyvalue is not null and
surveyvalue <> 0 AND l.grp IN ('G')
AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
@edate)
GROUP BY r.qid
order by r.qid

if not object_id('tempdb..#t2') is null
drop table #t2

select qid2=r.qid,svalue2 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO
#T2
FROM groupq g
INNER JOIN questionaires q ON q.groupid = g.groupid
INNER JOIN result r ON r.qid = q.qid
INNER JOIN participant p ON p.participantid=r.participantid
INNER JOIN lkcourse l ON l.courseid= p.levelid
WHERE q.ratetype IN (1,2) and surveyvalue is not null and
surveyvalue <> 0 AND l.grp IN ('M')
AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
@edate)
GROUP BY r.qid
order by r.qid
if not object_id('tempdb..#t3') is null
drop table #t3

select qid3=r.qid,svalue3 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO
#T3
FROM groupq g
INNER JOIN questionaires q ON q.groupid = g.groupid
INNER JOIN result r ON r.qid = q.qid
INNER JOIN participant p ON p.participantid=r.participantid
INNER JOIN lkcourse l ON l.courseid= p.levelid
WHERE q.ratetype IN (1,2) and surveyvalue is not null and
surveyvalue <> 0 AND l.grp IN ('P')
AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
@edate)
GROUP BY r.qid
order by r.qid

if not object_id('tempdb..#t4') is null
drop table #t4

SELECT * INTO #T4 FROM #T1 t1
FULL JOIN #T2 t2 ON t2.qid2 = t1.qid
FULL JOIN #T3 t3 ON t3.qid3 = t1.qid

UPDATE #T4 SET qid=ISNULL(qid2,qid3) WHERE ISNULL(qid,'')=''

if EXISTS(select [name] from sysobjects where [name] = 'tpAverage')
DROP TABLE tpAverage

select tp.topicid,tp.topicdesc,g.qnum,g.groupdesc,q.qdesc,
t4.qid,t4.svalue1,t4.svalue2,t4.svalue3
INTO tpAverage
FROM #t4 t4
INNER JOIN questionaires q ON q.qid=t4.qid
INNER JOIN groupq g ON g.groupid = q.groupid
INNER JOIN topics tp ON tp.topicid = g.topicid
ORDER BY tp.topicid, qnum, t4.qid

-- SELECT * FROM tpAverage
SET NOCOUNT OFF
GO
Click to expand...
 
Back
Top