ExecuteScalar

Mar 23, 2007

private void buttonLogin_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirecto ry|\PEService.mdf;Integrated Security=True;User Instance=True";
conn.Open();
string strSQL = "Select Count(*) as ctr From Cust Where Email=" + textBoxEmail + "and Passwd=" + textBoxPW;

SqlCommand cmd = new SqlCommand(strSQL,conn);
int ctr=(int)cmd.ExecuteScalar();
if (ctr == 1)
MessageBox.Show("Correct");
else
MessageBox.Show("Wrong");
conn.Close();
}

i have this code for my login form. when i remove conn.Open(); in the code
it says... ExecuteScalar requires an open and available Connection. The connection's current state is closed.

and when i put conn.Open();
it says... An attempt to attach an auto-named database for file C:... failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

what is the problem?

View 6 Replies


ADVERTISEMENT

ExecuteScalar() Not Returning Value?

Dec 12, 2007

Okay so here's a wierd one.  I use SQLYog to peek into/administrate my databases.I noticed that this chunk of code is not producing a value... Using Conn As New MySqlConnection(Settings.MySqlConnectionString)
Using Cmd As New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE '%?CurrentPage%') AND Enabled=1", Conn)
With Cmd.Parameters
.Add(New MySqlParameter("?CurrentPage",thisPage))
End With
Conn.Open()
Exists = Cmd.ExecuteScalar()
End Using
End 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. SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE '%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?

View 3 Replies View Related

ExecuteScalar() Returns -1

Oct 15, 2004

The following query returns 0 when executing in Query Analyzer: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... ?

View 1 Replies View Related

Get Count With Executescalar()

Apr 18, 2005

Howdie y'all!
I'm trying to do an executescalar() on the next stored procudure...
SELECT COUNT(*) FROM tblUsers WHERE UserEmail = @UserEmail;
Strangely enough I get SqlServer exception that tells me there's a syntax error.
Is there something I'm overseeing?
Cheers,
Wes

View 2 Replies View Related

ExecuteScalar Problems. Need Help

Apr 26, 2006

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:TemplateField HeaderText="Client" SortExpression="c_id">
   <ItemTemplate>
      <asp:Label ID="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 ScalarValue As String = ""
Dim myConnection As New SqlConnection("Data Source=XXX; Initial Catalog=XXX; uid=XXX; pwd=XXX")
Dim myCommand As New SqlCommand("SELECT [Name] FROM [hd_clients] WHERE [c_id] = @ClientID", myConnection)
myCommand.Parameters.Add("@ClientID", SqlDbType.Int)
myCommand.Parameters("@ClientID").Value = ClientID
   Try
      myConnection.Open()
      ScalarValue = myCommand.ExecuteScalar
   Catch ex As Exception
      Console.Write(ex.Message)
   End Try
 
   If ScalarValue > "" Then
      Return ScalarValue.ToString
   Else
      Return "<span style='color: #CCCCCC'>- NULL -</span>"
   End If

 
End Function
...
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
 

View 2 Replies View Related

SqlCommand.ExecuteScalar()

May 4, 2006

Is there documentation on what ExecuteScalar() will return if the SQL statement is returning an image?

View 1 Replies View Related

ExecuteScalar - Count(*)

Mar 25, 2007

i have Cust table with 5 columns (Name, Add, Contact, UserID, Passwd)

my sql statement is not working correctly..
"SELECT COUNT(*) FROM Cust WHERE UserID='" + textBoxEmail + "'AND Passwd='" + textBoxPW + "'"

what maybe the problem? i have 1 record and when im running it, whether the input is right or wrong, the count is always zero(0). i think the problem is in my sql statement(maybe in the where clause) because i tried counting the records by "select count(*) from cust" and it correctly says 1 record. pls help!

View 3 Replies View Related

How To Store ExecuteScalar Value Into Variable ?

Oct 16, 2006

I having a strange problem, my code is as below: cmd.connection = cnncmd.commandtext = "select count(*) from member" dim i as integeri = cInt(cmd.executescalar) However, what the result tat i get is "&H0" ! When I use the same query in sqlquery, it did show out the result as "11".I have no idea abt wat is goin on, can anyone gv me some guide ?  Thanks.

View 2 Replies View Related

ExecuteScalar And 2 Rows In My DB On Every Write?

May 30, 2008

Hey folks, I was just learning how to work with ScopeIdentity and I found out I needed somthing called executescalar. Things seem to be working really well, and I think I did it write, except I'm having one strange little oddity. Everytime my page writes to my databases, it writes rows. I'm really curious to know what I did wrong. Any ideas? Thank you as always :-)
 Public Sub newoptic(ByVal sender As Object, ByVal e As System.EventArgs) Handles newpostBTN.Click
If newpostTXTBX.Text = "" Then
Exit Sub
End If
Dim mySQL As String = "insert into msg_topics (topic_title, topic_forum_id) values (@topicTITLE, @forumID); Select Scope_Identity()"Dim myConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("DBconnect"))
Dim cmd As New SqlCommand(mySQL, myConn)cmd.Parameters.AddWithValue("@topicTITLE", newposttitleTXTBX.Text)
cmd.Parameters.AddWithValue("@forumID", HDN_topic_forum_ID_LBL.Text)
myConn.Open()Dim topic_ID_holder As Integer = cmd.ExecuteScalar()
HDN_topic_id_holder_LBL.Text = (topic_ID_holder)
cmd.ExecuteNonQuery()
myConn.Close()
End Sub
 
Oh, one more quick question if whoever responds knows the answer. Why do I need a semi-colon here "@forumID); Select" ? The websites I learned about this from didn't explain that, and I've never used a semicolon in my select statements before, so I figured there must be something special about it.
 
 Thank you

View 2 Replies View Related

Getting GUID Value From StoredProcedure.ExecuteScalar()

Jun 12, 2008

I need to execute stored procedure which is suppose to return GUID to my IF statement and if it is Nothing I execute other Stored procedures else some other procedures. My problem is that even though by looking at the data I know that after the execution of the procedure it should return some guid value it doesn't anybody who had the same issue??? That is the code block where I am trying to return guid from my stored procedure:   getGroupID.Parameters("@GroupName").Value = dr.Item("Group ID").ToString()            If getGroupID.ExecuteScalar() = Nothing Then                'Find Group by IP address if input Data Table doesn't have group                getGroupIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString()                If getGroupIDByIP.ExecuteScalar() = Nothing Then                    insertGroup.Parameters("@GroupID").Value = Guid.NewGuid                    insertGroup.Parameters("@Group").Value = dr.Item("Group ID")                    insertGroup.Parameters("@ACCID").Value = getAccID.ExecuteScalar()                    insertGroup.ExecuteNonQuery()                    command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar()                Else                    command.Parameters("@Group_ID").Value = getGroupIDByIP.ExecuteScalar()                End If            Else                command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar()            End If Thank you 

View 2 Replies View Related

ExecuteScalar Returns Null

Oct 25, 2006

I am using the following C# code and T-SQL to get result object from aSQL Server database. When my application runs, the ExecuteScalarreturns "10/24/2006 2:00:00 PM" if inserting a duplicated record. Itreturns null for all other conditions. Does anyone know why? Doesanyone 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) outputASBEGINSET NOCOUNT ON;select * from myCalendarwhere beginTime >= @pBegin and endTime <= @pEnd and userId = @pUserIdif @@rowcount <0beginprint 'Path 1'set @pOutput = 'Duplicated reservation'select @pOutput as 'Result'return -1endelsebeginprint 'Path 2'-- check if upperlimit (2) is reachedselect 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 myCalendargroup 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)))) =2and (rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30))) =rtrim(cast(@pBegin as varchar(20)))+ ', ' + rtrim(cast(@pEnd asvarchar(20))))-- If the @@rowcount is not equal to 0 then-- at the time between @pBegin and @pEnd the maximum count of 2 isreachedif @@rowcount <0beginprint 'Path 3'set @pOutput = '2 reservations are already taken for the hours'select @pOutput as 'Result'return -1endelsebeginprint 'Path 4'--safe to insertinsert dbo.myCalendar(beginTime, endTime,userId)values (@pBegin, @pEnd, @pUserId)if @@error = 0beginprint '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 0endelsebeginprint 'Path 4:2 @@rowcount=' + cast(@@rowcount as varchar(1))set @pOutput = 'Failed to make reservation'select @pOutput as 'Result'return -1endendendEND

View 1 Replies View Related

Getting NullReferenceException When Executing ExecuteScalar()

Nov 20, 2007

I'm trying to add user information to the database however I'm getting a NullReferenceException.

Here's the code:


SqlCommand UserAddCommand = new SqlCommand();

int AssignedUserID = 0;

UserAddCommand.CommandType = CommandType.StoredProcedure;

UserAddCommand.Connection = m_DBConnection;

UserAddCommand.CommandText = "SP_UserAdd";

UserAddCommand.Parameters.AddWithValue("@User_Name", DbType.String);

UserAddCommand.Parameters.AddWithValue("@Street_Address", DbType.String);

UserAddCommand.Parameters.AddWithValue("@City", DbType.String);

UserAddCommand.Parameters.AddWithValue("@State", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@Zip_Code", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@Email_Address", DbType.String);

UserAddCommand.Parameters.AddWithValue("@Phone_Number", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@User_Login_Name", DbType.String);

UserAddCommand.Parameters.AddWithValue("@User_Password", DbType.String);

UserAddCommand.Parameters.AddWithValue("@Referrer_Name", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@User_Type", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters["@User_Name"].Value = UserInfo.Name;

UserAddCommand.Parameters["@Street_Address"].Value = UserInfo.StreetAddress;

UserAddCommand.Parameters["@City"].Value = UserInfo.City;

UserAddCommand.Parameters["@State"].Value = UserInfo.State;

UserAddCommand.Parameters["@Zip_Code"].Value = UserInfo.ZipCode;

UserAddCommand.Parameters["@Email_Address"].Value = UserInfo.EmailAddress;

UserAddCommand.Parameters["@Phone_Number"].Value = UserInfo.PhoneNumber;

UserAddCommand.Parameters["@User_Login_Name"].Value = UserInfo.UserName;

UserAddCommand.Parameters["@User_Password"].Value = UserInfo.UserPassword;

UserAddCommand.Parameters["@Referrer_Name"].Value = UserInfo.ReferrerName;

if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.FAMILY)

{

UserAddCommand.Parameters["@User_Type"].Value = "Family";

}

else if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.FRIEND)

{

UserAddCommand.Parameters["@User_Type"].Value = "Friend";

}

else if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.MANAGER)

{

UserAddCommand.Parameters["@User_Type"].Value = "Manager";

}

else if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.OWNER)

{

UserAddCommand.Parameters["@User_Type"].Value = "Owner";

}

try

{

AssignedUserID = (int)UserAddCommand.ExecuteScalar(); this line of code produces the NullReferenceException

UserInfo.UserID = AssignedUserID;

m_UserInfo = UserInfo;

}

catch (Exception Ex)

{

Console.WriteLine(Ex.ToString());

}



Please help me figure out what I am doing wrong.

View 1 Replies View Related

Calling Method To Do Executescalar With Parameters

Feb 15, 2008

I have a method like follows:
string EmpCount = null;
DateTime _dtstart = Convert.ToDateTime(txtStart.Text.Trim());
DateTime _dtend = Convert.ToDateTime(txtEnd.Text.Trim());SQLProvider sqlp = new SQLProvider(System.Configuration.ConnectionStrings["ConString"].ConnectionString);string SqlText = @"
select count(*) from employee
where activeemp=1 and startdate BETWEEN  @dtstart and @dtend;
using (sqlp.Connection) {
sqlpm [] param = {
 new sqlpm("dtStart", _dtstart),
 new SqlP("dtEnd", _dtend)
 };
 }EmpCount = sqlp.ExecuteScalar(SqlText, param).ToString();
return Convert.ToInt32(mbrCount);
 
Then the method I am calling is:public Object ExecuteScalar(String sqlText, Sqlp[] param)
{try
{
//Some Code here
}
}
So in the calling method (ExecuteScalar), the second parameter is defined as an array, is it ok to have an array in the called method too?

View 1 Replies View Related

ExecuteScalar Returns 0 (null) But INSERT Is Successful.

Sep 25, 2007



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?

View 7 Replies View Related

ExecuteScalar --&> How To Get The OrderID(Identity) From A Table To Another Table ??

Mar 23, 2006

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

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved