trouble executing SP, SqlDbType.Bit...

ASP.NET 2.0
This stored procedure fails because the variable type contains a number.
I've debugged the SP in VS2005 and it works if I change the 0/1 value of
type to true/false... How should I fix this' I cannot just replace type
with the phrase "false" or "true"... maybe using SqlDbType.Bit is wrong
This is header of this SP:
ALTER PROCEDURE dbo.DeleteMessage @dotnet.itags.org.id uniqueidentifier, @dotnet.itags.org.user int, @dotnet.itags.org.type
bit
Here is the ASP.NET code
public override void DeleteMessage(System.Guid id, int user, Boolean type)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("SendMessage", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@dotnet.itags.org.id", SqlDbType.UniqueIdentifier).Value = id;
cmd.Parameters.Add("@dotnet.itags.org.user", SqlDbType.Int).Value = user;
cmd.Parameters.Add("@dotnet.itags.org.type", SqlDbType.Bit).Value = type;
}
}
any suggetions?
JeffIt says boolean, so you need to pass true (with no quotation marks) or false
-- for the value.
Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net
"Jeff" wrote:

> ASP.NET 2.0
> This stored procedure fails because the variable type contains a number.
> I've debugged the SP in VS2005 and it works if I change the 0/1 value of
> type to true/false... How should I fix this' I cannot just replace type
> with the phrase "false" or "true"... maybe using SqlDbType.Bit is wrong
> This is header of this SP:
> ALTER PROCEDURE dbo.DeleteMessage @.id uniqueidentifier, @.user int, @.type
> bit
> Here is the ASP.NET code
> public override void DeleteMessage(System.Guid id, int user, Boolean type)
> {
> using (SqlConnection cn = new SqlConnection(this.ConnectionString))
> {
> SqlCommand cmd = new SqlCommand("SendMessage", cn);
> cmd.CommandType = CommandType.StoredProcedure;
> cmd.Parameters.Add("@.id", SqlDbType.UniqueIdentifier).Value = id;
> cmd.Parameters.Add("@.user", SqlDbType.Int).Value = user;
> cmd.Parameters.Add("@.type", SqlDbType.Bit).Value = type;
> }
> }
> any suggetions?
> Jeff
>
>

trouble executing SP, SqlDbType.Bit...

ASP.NET 2.0

This stored procedure fails because the variable type contains a number.
I've debugged the SP in VS2005 and it works if I change the 0/1 value of
type to true/false... How should I fix this?? I cannot just replace type
with the phrase "false" or "true"... maybe using SqlDbType.Bit is wrong

This is header of this SP:
ALTER PROCEDURE dbo.DeleteMessage @dotnet.itags.org.id uniqueidentifier, @dotnet.itags.org.user int, @dotnet.itags.org.type
bit

Here is the ASP.NET code
public override void DeleteMessage(System.Guid id, int user, Boolean type)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("SendMessage", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@dotnet.itags.org.id", SqlDbType.UniqueIdentifier).Value = id;
cmd.Parameters.Add("@dotnet.itags.org.user", SqlDbType.Int).Value = user;
cmd.Parameters.Add("@dotnet.itags.org.type", SqlDbType.Bit).Value = type;
}
}

any suggetions?

JeffIt says boolean, so you need to pass true (with no quotation marks) or false
-- for the value.
Peter

--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net
"Jeff" wrote:

Quote:

Originally Posted by

ASP.NET 2.0
>
This stored procedure fails because the variable type contains a number.
I've debugged the SP in VS2005 and it works if I change the 0/1 value of
type to true/false... How should I fix this?? I cannot just replace type
with the phrase "false" or "true"... maybe using SqlDbType.Bit is wrong
>
This is header of this SP:
ALTER PROCEDURE dbo.DeleteMessage @.id uniqueidentifier, @.user int, @.type
bit
>
Here is the ASP.NET code
public override void DeleteMessage(System.Guid id, int user, Boolean type)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("SendMessage", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.id", SqlDbType.UniqueIdentifier).Value = id;
cmd.Parameters.Add("@.user", SqlDbType.Int).Value = user;
cmd.Parameters.Add("@.type", SqlDbType.Bit).Value = type;
}
}
>
any suggetions?
>
Jeff
>
>
>

trouble retrieving data from ASPState Database

I'm trying to put together a web form that lists all current session informa
tion. The session info is stored in SQL server database (ASPState) and I'm t
rying to retreive and display using a SQLDataReader. I seem to have no troub
le querying the "ASPStateTe
mpSessions" table but when I try to write it to the page, I throw an excepti
on that basically says that there is no data to display. Is there some speci
al method you must use when performing this sort of task?
The code is as follows:
----
dim cn as new SqlConnection(ConfigurationSettings.AppSetting ("ASPState").to
String())
cn.Open()
dim cmd as new SqlCommand("select * from ASPStateTempSessions where TimeOut
= 40", cn)
dim dr as SqlDataReader
dr = cmd.ExecuteReader()
Response.Write(dr("Locked"))
----
The exception content is as follows:
System.InvalidOperationException: Invalid attempt to read when no data is pr
esent. at System.Data.SqlClient.SqlDataReader.PrepareRecord(Int32 i) at Syst
em.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at System.Data.SqlClient.S
qlDataReader.get_Item(Strin
g name) at ASP.AgreementListing_aspx.Page_Load(Object Sender, EventArgs E) i
n C:\Inetpub\wwwroot\onehour\AgreementList
ing.aspx:line 82when using a datareader, you have to first call the "read" method.
dr = cmd.ExecuteReader()
dr.read()
Response.Write(dr("Locked"))
OR to get all rows:
do while dr.read()
Response.Write(dr("Locked"))
loop (i think)
"Glenn Venzke" <GlennVenzke@.discussions.microsoft.com> wrote in message
news:BBA23A84-4649-4967-8AA3-6D98648C0555@.microsoft.com...
> I'm trying to put together a web form that lists all current session
information. The session info is stored in SQL server database (ASPState)
and I'm trying to retreive and display using a SQLDataReader. I seem to have
no trouble querying the "ASPStateTempSessions" table but when I try to write
it to the page, I throw an exception that basically says that there is no
data to display. Is there some special method you must use when performing
this sort of task?
> The code is as follows:
> ----
> dim cn as new SqlConnection(ConfigurationSettings.AppSetting
("ASPState").toString())
> cn.Open()
> dim cmd as new SqlCommand("select * from ASPStateTempSessions where
TimeOut = 40", cn)
> dim dr as SqlDataReader
> dr = cmd.ExecuteReader()
> Response.Write(dr("Locked"))
>
> ----
> The exception content is as follows:
> System.InvalidOperationException: Invalid attempt to read when no data is
present. at System.Data.SqlClient.SqlDataReader.PrepareRecord(Int32 i) at
System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at
System.Data.SqlClient.SqlDataReader.get_Item(String name) at
ASP.AgreementListing_aspx.Page_Load(Object Sender, EventArgs E) in
C:\Inetpub\wwwroot\onehour\AgreementList
ing.aspx:line 82
Boy, am I a dope! I thought datareader.read worked along the lines of record
set.EOF. I thought it was a simple boolean property that told you if any rec
ords were returned or not. But it is actually a method that returns a boolea
n. I guess you learn someth
ing new every day. Thanks much!!
"mark" wrote:

> when using a datareader, you have to first call the "read" method.
> dr = cmd.ExecuteReader()
> dr.read()
> Response.Write(dr("Locked"))
> OR to get all rows:
> do while dr.read()
> Response.Write(dr("Locked"))
> loop (i think)
>
>
> "Glenn Venzke" <GlennVenzke@.discussions.microsoft.com> wrote in message
> news:BBA23A84-4649-4967-8AA3-6D98648C0555@.microsoft.com...
> information. The session info is stored in SQL server database (ASPState)
> and I'm trying to retreive and display using a SQLDataReader. I seem to ha
ve
> no trouble querying the "ASPStateTempSessions" table but when I try to wri
te
> it to the page, I throw an exception that basically says that there is no
> data to display. Is there some special method you must use when performing
> this sort of task?
> ("ASPState").toString())
> TimeOut = 40", cn)
> present. at System.Data.SqlClient.SqlDataReader.PrepareRecord(Int32 i) at
> System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at
> System.Data.SqlClient.SqlDataReader.get_Item(String name) at
> ASP.AgreementListing_aspx.Page_Load(Object Sender, EventArgs E) in
> C:\Inetpub\wwwroot\onehour\AgreementList
ing.aspx:line 82
>
>

trouble retrieving data from ASPState Database

I'm trying to put together a web form that lists all current session information. The session info is stored in SQL server database (ASPState) and I'm trying to retreive and display using a SQLDataReader. I seem to have no trouble querying the "ASPStateTempSessions" table but when I try to write it to the page, I throw an exception that basically says that there is no data to display. Is there some special method you must use when performing this sort of task?
The code is as follows:
------------------
dim cn as new SqlConnection(ConfigurationSettings.AppSetting ("ASPState").toString())
cn.Open()
dim cmd as new SqlCommand("select * from ASPStateTempSessions where TimeOut = 40", cn)
dim dr as SqlDataReader
dr = cmd.ExecuteReader()
Response.Write(dr("Locked"))

------------------
The exception content is as follows:

System.InvalidOperationException: Invalid attempt to read when no data is present. at System.Data.SqlClient.SqlDataReader.PrepareRecord( Int32 i) at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at System.Data.SqlClient.SqlDataReader.get_Item(Strin g name) at ASP.AgreementListing_aspx.Page_Load(Object Sender, EventArgs E) in C:\Inetpub\wwwroot\onehour\AgreementListing.aspx:l ine 82when using a datareader, you have to first call the "read" method.

dr = cmd.ExecuteReader()
dr.read()
Response.Write(dr("Locked"))

OR to get all rows:

do while dr.read()
Response.Write(dr("Locked"))
loop (i think)

"Glenn Venzke" <GlennVenzke@.discussions.microsoft.com> wrote in message
news:BBA23A84-4649-4967-8AA3-6D98648C0555@.microsoft.com...
> I'm trying to put together a web form that lists all current session
information. The session info is stored in SQL server database (ASPState)
and I'm trying to retreive and display using a SQLDataReader. I seem to have
no trouble querying the "ASPStateTempSessions" table but when I try to write
it to the page, I throw an exception that basically says that there is no
data to display. Is there some special method you must use when performing
this sort of task?
> The code is as follows:
> ------------------
> dim cn as new SqlConnection(ConfigurationSettings.AppSetting
("ASPState").toString())
> cn.Open()
> dim cmd as new SqlCommand("select * from ASPStateTempSessions where
TimeOut = 40", cn)
> dim dr as SqlDataReader
> dr = cmd.ExecuteReader()
> Response.Write(dr("Locked"))
>
> ------------------
> The exception content is as follows:
> System.InvalidOperationException: Invalid attempt to read when no data is
present. at System.Data.SqlClient.SqlDataReader.PrepareRecord( Int32 i) at
System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at
System.Data.SqlClient.SqlDataReader.get_Item(Strin g name) at
ASP.AgreementListing_aspx.Page_Load(Object Sender, EventArgs E) in
C:\Inetpub\wwwroot\onehour\AgreementListing.aspx:l ine 82
Boy, am I a dope! I thought datareader.read worked along the lines of recordset.EOF. I thought it was a simple boolean property that told you if any records were returned or not. But it is actually a method that returns a boolean. I guess you learn something new every day. Thanks much!!

"mark" wrote:

> when using a datareader, you have to first call the "read" method.
> dr = cmd.ExecuteReader()
> dr.read()
> Response.Write(dr("Locked"))
> OR to get all rows:
> do while dr.read()
> Response.Write(dr("Locked"))
> loop (i think)
>
>
> "Glenn Venzke" <GlennVenzke@.discussions.microsoft.com> wrote in message
> news:BBA23A84-4649-4967-8AA3-6D98648C0555@.microsoft.com...
> > I'm trying to put together a web form that lists all current session
> information. The session info is stored in SQL server database (ASPState)
> and I'm trying to retreive and display using a SQLDataReader. I seem to have
> no trouble querying the "ASPStateTempSessions" table but when I try to write
> it to the page, I throw an exception that basically says that there is no
> data to display. Is there some special method you must use when performing
> this sort of task?
> > The code is as follows:
> > ------------------
> > dim cn as new SqlConnection(ConfigurationSettings.AppSetting
> ("ASPState").toString())
> > cn.Open()
> > dim cmd as new SqlCommand("select * from ASPStateTempSessions where
> TimeOut = 40", cn)
> > dim dr as SqlDataReader
> > dr = cmd.ExecuteReader()
> > Response.Write(dr("Locked"))
> > ------------------
> > The exception content is as follows:
> > System.InvalidOperationException: Invalid attempt to read when no data is
> present. at System.Data.SqlClient.SqlDataReader.PrepareRecord( Int32 i) at
> System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at
> System.Data.SqlClient.SqlDataReader.get_Item(Strin g name) at
> ASP.AgreementListing_aspx.Page_Load(Object Sender, EventArgs E) in
> C:\Inetpub\wwwroot\onehour\AgreementListing.aspx:l ine 82
>

trouble with passing date to stored procedure

Trying to create a app that will read through our log files. I am reading through a directory and for each file I loop line by line and split the log into an array by SPACE and then send the IP, name of the log file, referer URL, and datetime to a stored procedure (below). The following code just returns 0 as a result and is not true. I believe my issue is in the asp.net datetime vs the datetime format in my database.

The datetime sent to the SP is: 10/17/2004 12:00:19 AM
The datetime in the field DT_TM I am comparing to is: 2004-06-23 10:17:23.000

Just hoping someone has a good grasp on this datetime format issue since I am clueless at this point on what to do. Cause I was sending in without converting to a date and kept getting errors.

This section of code is a portion taken from my .aspx file

'----
Dim dtLogDateTime as DateTime
adoCon = New SqlConnection("Server=xx.xx.xx.xx;Database=dbname;Uid=user;Pwd=pwd")
cmdSQL = New SqlCommand("sc_add_referer", adoCon)
cmdSQL.CommandType = CommandType.StoredProcedure
'Input Parameters
cmdSQL.Parameters.Add("@dotnet.itags.org.SENT_IP", arrArray2(5))
cmdSQL.Parameters.Add("@dotnet.itags.org.SENT_LOG_FILE", arFile(arFile.Length -1))
cmdSQL.Parameters.Add("@dotnet.itags.org.SENT_REFERER", arrArray2(8))
dtLogDateTime = arrArray2(0) & " " & arrArray2(1)
cmdSQL.Parameters.Add("@dotnet.itags.org.SENT_DT_TM", dtLogDateTime)
'Output parameter
parmSQL = cmdSQL.Parameters.Add("ReturnValue", SqlDbType.Int)
parmSQL.Direction = ParameterDirection.ReturnValue
adoCon.Open()
cmdSQL.ExecuteNonQuery()
iReturnVal = cmdSQL.Parameters("ReturnValue").Value
If iReturnVal = -2 Then
Response.Write("<br>" & arrArray2(3) & " " & arrArray2(5))
End if
adoCon.Close()
'----

The following is the SQL Server stored procedure I am calling

CREATE PROCEDURE sc_add_referer
(
 @dotnet.itags.org.SENT_IP varchar(15),
 @dotnet.itags.org.SENT_LOG_FILE varchar(15),
 @dotnet.itags.org.SENT_REFERER text,
 @dotnet.itags.org.SENT_DT_TM datetime
)
 AS
DECLARE @dotnet.itags.org.ROW_CNT INT
DECLARE @dotnet.itags.org.ROW_ORDERID INT
DECLARE @dotnet.itags.org.ROW_DT_TM INT
DECLARE @dotnet.itags.org.SUB_CNT INT
--FIRST NEED TO FIND OUT IF IN MAIN TABLE
SELECT @dotnet.itags.org.ROW_CNT = count(*), @dotnet.itags.org.ROW_ORDERID = ORDER_ID
FROM sc_ip_tracking
WHERE IP = @dotnet.itags.org.SENT_IP
GROUP BY ORDER_ID
SELECT @dotnet.itags.org.SUB_CNT = count(*)
FROM sc_ip_tracking_referers
WHERE ORDER_ID = @dotnet.itags.org.ROW_ORDERID
AND LOG_FILE = @dotnet.itags.org.SENT_LOG_FILE
IF @dotnet.itags.org.ROW_CNT > 0
 -- IP FOUND SO SEE IF FOUND IN REFERER LOG
 IF @dotnet.itags.org.SUB_CNT > 0
 --ROW ROUND
 RETURN 0
 ELSE
 -- NO ROW FOUND SO NEED TO ADD INFO
 BEGIN
 SELECT @dotnet.itags.org.ROW_DT_TM = datediff(hour, @dotnet.itags.org.SENT_DT_TM, DT_TM) FROM sc_ip_tracking WHERE IP = @dotnet.itags.org.SENT_IP AND ORDER_ID = @dotnet.itags.org.ROW_ORDERID
 IF @dotnet.itags.org.ROW_DT_TM >= 0 AND @dotnet.itags.org.ROW_DT_TM <= 8
 BEGIN
 INSERT INTO sc_ip_tracking_referers (ORDER_ID, LOG_FILE, REFERER, LAST_CHANGE_DT)
 values (@dotnet.itags.org.ROW_ORDERID, @dotnet.itags.org.SENT_LOG_FILE, @dotnet.itags.org.SENT_REFERER, getdate())
 END
 RETURN -2
 END
ELSE
 RETURN 0
GO

Replace the "/" in the date with "-" and drop the " AM" at the end of the string. You should be good to go.