SELECT isnull(Count(*),0) as total FROM SplitDetail WHERE SiteCode = 14 AND ProjectID = 4367Yet ExecuteScalar() in vb.net return a -1.
Any ideas on what I might be doing wrong... ?Discovered my error...
The ExecuteScalar method deep in the plumbing of my DAL was actually calling an ExecuteNonQuery() function...
I noticed that this chunk of code is not producing a value...
Using ConnAs New MySqlConnection(Settings.MySqlConnectionString)Using CmdAs New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE'%?CurrentPage%') AND Enabled=1", Conn)With Cmd.Parameters.Add(New MySqlParameter("?CurrentPage",thisPage))End WithConn.Open()Exists = Cmd.ExecuteScalar()End UsingEnd Using
Exists is declared outside of that block so that other logic can access it. thisPage is a variable declared outside, as well, that contains a simple string, like 'index.aspx'. With the value set to 'index.aspx' a count of 1 should be returned, and is returned in SQLYog.
SELECTCOUNT(*)FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1
This produces a value of 1, but NO value at all is returned from Cmd.ExecuteScalar(). I use this method in MANY places and don't have this problem, but here it rises out of the mist and I can't figure it out. I have no Try/Catch blocks so any error should be evident in the yellow/red error screen, but no errors occur in the server logs on in the application itself.
Does anybody have any ideas?
Try
WHERE (PermissionFiles LIKE'%' + ?CurrentPage + '%')
Jos
|||That didn't give me the desired result, either. It started returning "every" row that met all criteria but theCurrentPage.
But, thanks to your suggestion, what I ended up with was...
Using ConnAs New MySqlConnection(Settings.MySqlConnectionString)Using CmdAs New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE'%" & thisPage & "%') AND Enabled=1 AND Everybody=0", Conn)Conn.Open()Exists = Cmd.ExecuteScalar()End UsingEnd Using Which works "as intended". Thanks!|||execute scalar returns firts column from firts row of returned data so use this:
SELECT (SELECTCOUNT(*)FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1)
you can also use:
ifexists(SELECT *FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1)
select 1
else
select 0
which can work faster if you have more than one record whcih meet your criteria
I hope that it will work
sql-- C# --
aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};
object oRtnObject = null;
StoredProcCommandWrapper =
myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);
oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);
-- T-SQL --
ALTER PROCEDURE [dbo].[procmyCalendarInsert]
@.pBegin datetime,
@.pEnd datetime,
@.pUserId int,
@.pOutput varchar(200) output
AS
BEGIN
SET NOCOUNT ON;
select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId
if @.@.rowcount <0
begin
print 'Path 1'
set @.pOutput = 'Duplicated reservation'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 2'
-- check if upperlimit (2) is reached
select rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
,count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))))
from myCalendar
group by rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
having count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))) =2
and (rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))) =
rtrim(cast(@.pBegin as varchar(20)))+ ', ' + rtrim(cast(@.pEnd as
varchar(20))))
-- If the @.@.rowcount is not equal to 0 then
-- at the time between @.pBegin and @.pEnd the maximum count of 2 is
reached
if @.@.rowcount <0
begin
print 'Path 3'
set @.pOutput = '2 reservations are already taken for the hours'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 4'
--safe to insert
insert dbo.myCalendar(beginTime, endTime,userId)
values (@.pBegin, @.pEnd, @.pUserId)
if @.@.error = 0
begin
print 'Path 4:1 @.@.error=' + cast(@.@.error as varchar(1))
print 'Path 4:1 @.@.rowcount=' + cast(@.@.rowcount as varchar(1))
set @.pOutput = 'Reservation succeeded'
select @.pOutput as 'Result'
return 0
end
else
begin
print 'Path 4:2 @.@.rowcount=' + cast(@.@.rowcount as varchar(1))
set @.pOutput = 'Failed to make reservation'
select @.pOutput as 'Result'
return -1
end
end
end
ENDjs wrote:
Quote:
Originally Posted by
I am using the following C# code
But read on:
Quote:
Originally Posted by
and T-SQL to get result object from a
SQL Server database. When my application runs, the ExecuteScalar
returns "10/24/2006 2:00:00 PM" if inserting a duplicated record. It
returns null for all other conditions. Does anyone know why? Does
anyone know how to get the output value? Thanks.
>
-- C# --
aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};
object oRtnObject = null;
StoredProcCommandWrapper =
myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);
oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);
>
-- T-SQL --
ALTER PROCEDURE [dbo].[procmyCalendarInsert]
@.pBegin datetime,
@.pEnd datetime,
@.pUserId int,
@.pOutput varchar(200) output
AS
BEGIN
SET NOCOUNT ON;
>
select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId
>
if @.@.rowcount <0
If you want to verify if records exist, use IF EXISTS, as in
IF EXISTS (select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId)
This is more efficient because it does not retrieve a resultset, it only
verifies that the records meeting therequirements exist. If you really want
a count of the records that meet the requirements (which does not seem to be
te case here) you should use:
declare @.cnt int
Set @.cnt= (select count(*) from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId)
Because the result is assigned to a variable, no resultset is created that
needs to be sent to the client.
Quote:
Originally Posted by
begin
print 'Path 1'
set @.pOutput = 'Duplicated reservation'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 2'
-- check if upperlimit (2) is reached
select rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
,count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))))
from myCalendar
group by rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
having count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))) =2
and (rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))) =
rtrim(cast(@.pBegin as varchar(20)))+ ', ' + rtrim(cast(@.pEnd as
varchar(20))))
Quote:
Originally Posted by
>
-- If the @.@.rowcount is not equal to 0 then
-- at the time between @.pBegin and @.pEnd the maximum count of 2 is
reached
Anyways, you already determined above that the records exist. Why bother
checking again?
Quote:
Originally Posted by
>
if @.@.rowcount <0
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:
RecordID = cmd.ExecuteScalar
I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.
No exception is thrown of any type or anything. Does anybody know what may be happening?
Can you give us a peek at the procedure?
|||Need to check the SP, you should have the Select statement which returns the newly inserted value..
|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have theSELECT @.@.IDENTITY
at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.
The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.
Thank you!
|||You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.
You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.
|||Its better to use the SCOPE_IDENTITY function..
Select Scope_Identity()
|||Sounds good to me; I actually saw that as well in the MSDN example.
Could you tell me why it is better (performance, etc.)?
Thanks,
|||@.@.IDENTITY hold the global value (across the scope)
Scope_Identity hold the current Scope value.
When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.
I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:
RecordID = cmd.ExecuteScalar
I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.
No exception is thrown of any type or anything. Does anybody know what may be happening?
Can you give us a peek at the procedure?
|||Need to check the SP, you should have the Select statement which returns the newly inserted value..
|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have theSELECT @.@.IDENTITY
at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.
The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.
Thank you!
|||You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.
You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.
|||Its better to use the SCOPE_IDENTITY function..
Select Scope_Identity()
|||Sounds good to me; I actually saw that as well in the MSDN example.
Could you tell me why it is better (performance, etc.)?
Thanks,
|||@.@.IDENTITY hold the global value (across the scope)
Scope_Identity hold the current Scope value.
When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.
Hi all
I am currently developing a Help Desk for our company. One of my problems is Data lookups in other tables within a SQL 2000 DB. i.e. Client Details and Information in one table (hd_clients) and Client History (hd_history) in another.
'hd_history' contains a column called 'c_id' which references the 'hd_clients' table 'c_id' column A typical One-to-Many relationship. When a user goes to the Help Desk's Service page. I want to display the client's name in one of my GridView's Databound Columns. See Below:
...
<
asp:TemplateFieldHeaderText="Client"SortExpression="c_id"> <ItemTemplate> <asp:LabelID="lblClient"runat="server"Text='<%#GetClient(Eval("c_id")) %>'/> </ItemTemplate></asp:TemplateField>...
This then calls: GetClientName - Which is as follows.
...
Public
Function GetClientName(ByVal ClientID)Dim ScalarValueAsString =""Dim myConnectionAsNew SqlConnection("Data Source=XXX; Initial Catalog=XXX; uid=XXX; pwd=XXX")Dim myCommandAsNew SqlCommand("SELECT [Name] FROM [hd_clients] WHERE [c_id] = @.ClientID", myConnection)myCommand.Parameters.Add(
"@.ClientID", SqlDbType.Int)myCommand.Parameters(
"@.ClientID").Value = ClientID TrymyConnection.Open()
ScalarValue = myCommand.ExecuteScalar
Catch exAs ExceptionConsole.Write(ex.Message)
EndTryIf ScalarValue >""Then Return ScalarValue.ToString Else Return"<span style='color: #CCCCCC'>- NULL -</span>" EndIf
EndFunction
...
This works perfectly on my Laptop (which runs the IIS and SQL Server Instances + VS2005). But, when placed on our production server brings back the '- null -' value instead of the Client's Name. I have set both machines up in exaclty the same way - and cannot get this to work. I have tried 'ExecuteReader' but from what I understand is 'ExecuteScalar' is better for single value lookups.
Any help in this matter would be great and really appreciated. Thanks.
David
Dave_Winchester:
ScalarValue = myCommand.ExecuteScalar
Since ExecuteScalar returns and object, try converting the result to a string before assigning it to the local variable.
|||Hi
Thanks for the help. Now fixed - Also changed the Exception handling to be a bit better.
Dave
I am new to asp.net and studying on book.. currently i am stuck with a problem which not understand what is it !! Can anyone help me ?? I trying a shopping cart "Check Out" method, and when i am done the process.. My order_lines Table can update the OrderID which just generated !! What wrong with the statement ??
Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick
' Insert the order and order lines into the database
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmd As SqlCommand
Try
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans
' set the order details
cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address, @.City,@.State, @.PostCode, @.Country, @.Total)"
cmd.Parameters.Add("@.MemberName", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.OrderDate", Data.SqlDbType.DateTime)
cmd.Parameters.Add("@.Name", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.Address", Data.SqlDbType.VarChar, 255)
cmd.Parameters.Add("@.City", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.State", SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.PostCode", Data.SqlDbType.VarChar, 15)
cmd.Parameters.Add("@.Country", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.Total", Data.SqlDbType.Money)
cmd.Parameters("@.MemberName").Value = User.Identity.Name
cmd.Parameters("@.OrderDate").Value = DateTime.Now()
cmd.Parameters("@.Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text
cmd.Parameters("@.Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text
cmd.Parameters("@.City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text
cmd.Parameters("@.State").Value = CType(Wizard1.FindControl("txtState"), TextBox).Text
cmd.Parameters("@.PostCode").Value = CType(Wizard1.FindControl("txtPostCode"), TextBox).Text
cmd.Parameters("@.Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text
cmd.Parameters("@.Total").Value = Profile.Basket.Total
Dim OrderID As Integer
OrderID = Convert.ToInt32(cmd.ExecuteScalar()) <-- Is it wrong or need to add wat ?
' change the query and parameters for the order lines
cmd.CommandText = "INSERT INTO OrderLines(OrderID, ProductID,Quantity, Price) VALUES (@.OrderID, @.ProductID, @.Quantity, @.Price)"
cmd.Parameters.Clear()
cmd.Parameters.Add("@.OrderID", Data.SqlDbType.Int)
cmd.Parameters.Add("@.ProductID", Data.SqlDbType.Int)
cmd.Parameters.Add("@.Quantity", Data.SqlDbType.Int)
cmd.Parameters.Add("@.Price", Data.SqlDbType.Money)
cmd.Parameters("@.OrderID").Value = OrderID
For Each item As CartItem In Profile.Basket.Items
cmd.Parameters("@.ProductID").Value = item.ProductID
cmd.Parameters("@.Quantity").Value = item.Quantity
cmd.Parameters("@.Price").Value = item.UnitPrice
cmd.ExecuteNonQuery()
Next
' commit the transaction
trans.Commit()
Catch SqlEx As SqlException
' some form of error - rollback the transaction
' and rethrow the exception
If trans IsNot Nothing Then
trans.Rollback()
End If
' Log the exception
Throw
Finally
If conn IsNot Nothing Then
conn.Close()
End If
End Try
' we will only reach here if the order has been created successfully
' so clear the cart
Profile.Basket.Items.Clear()
End Sub
' set the order details|||
cmd.CommandText = "INSERT INTOOrders(MemberName, OrderDate, Name, Address, City, State, PostCode,Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address,@.City,@.State, @.PostCode, @.Country, @.Total; SELECT SCOPE_IDENTITY())"
Thanks tmorton for your reply.. i try with ur suggested code can return an error
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near ';'.
Incorrect syntax near ')'.
Line 84: End If
Line 85: ' Log the exception
Line 86: Throw
Line 87:
Line 88: Finally
I am noob for this case, is it other way to retrieve the new data identity ?
|||cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address, @.City,@.State, @.PostCode, @.Country, @.Total); SELECT SCOPE_IDENTITY()"|||
cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address, @.City,@.State, @.PostCode, @.Country, @.Total); SELECT SCOPE_IDENTITY()"