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... ?
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
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?
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?
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?
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
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
Is there documentation on what ExecuteScalar() will return if the SQL statement is returning an image?
View 1 Replies View Relatedi 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!
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 RelatedHey 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
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 RelatedI'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.
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?
Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.
View 6 Replies View RelatedI 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
CREATE PROCEDURE UpdateAccount
(
@AccountID int,
@EmailAddress varchar(255),
@Password varchar(16)
)
AS
DECLARE @actualPassword varchar(16)
SELECT @actualPassword = Password
FROM Account WHERE AccountID = @AccountID
If Exists( SELECT EmailAddress FROM Account
WHERE EmailAddress = @EmailAddress )
RETURN -1
ELSE IF @Password <> @actualPassword
RETURN - 2
ELSE
UPDATE Account
SET
EmailAddress = @EmailAddress,
Password = @Password
WHERE AccountID = @AccountID
GO
Whats wrong with the following code,its compiling but returning only first memberid. i want all memberids to be returned
DECLARE @memberList VARCHAR(4000),@hDoc INT
SET @memberList = '<MemberList><MemberID>7136</MemberID><MemberID>7137</MemberID><MemberID>7138</MemberID></MemberList>'exec sp_xml_preparedocument @hDoc OUTPUT, @memberListSELECT MemberID FROM OPENXML (@hdoc, 'MemberList', 2) WITH (MemberID BIGINT)
EXEC sp_xml_removedocument @hDoc
@Names is a query string passed in, I need to count the number of records as a result of the below query/
Dim test As String
Dim sqlConnection3 As New SqlConnection("data Source=EQ-520-WEBSQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True")
Dim cmd As New SqlCommand
Dim returnValue As Object
cmd.CommandText = "SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @Names)"
cmd.CommandType = Data.CommandType.Text
cmd.Connection = sqlConnection3
cmd.Parameters.Add("@Names", Data.SqlDbType.NVarChar)
sqlConnection3.Open()
cmd.Parameters("@Names").Value = test
If test = "" Then
Response.Write("An error occured")
Exit Sub
Else
returnValue = cmd.ExecuteScalar()
sqlConnection3.Close()
Label6.Text = "Number " & returnValue
I have a stored procedure with an output bit variable. When I set this variable to 0, I get an output of 0. When I set this variable to 1, I get an output of -1. What is going on?
View 9 Replies View RelatedI have an sp (attached below) that returns records as desired when run in the query analyzer, but when run from within my adp I get only a "the sp ran successfully but did not return any records" message box. I would like to use it as a record source for a report, and when I try to do that I get a message box saying that the "Provider command for child rowset does not produce a rowset".
To repeat, the sp runs fine in QA, just not in access.
Any thoughts, anybody? Many thanks. David
Alter Procedure spDailyMicroLabel
@Date Datetime = null
AS
IF @Date IS NULL
BEGIN
SET @Date=getdate()
SELECT FERMID, fldfermtank AS "Tank", CONVERT(varchar(12), fldfilltime, 101) AS "Filled",
fldfermprod AS "Brand", CONVERT(varchar(15),fldfilltime, 108) AS "FillTime", CONVERT(varchar(12), @Date, 101) as "ReportDate",
fldfermidold AS "FermenterCode",
CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) AS "FillDay",
"Sample Test" = CASE
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int)=1
THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 3 THEN 'XTF'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 4 THEN 'HLP, TF'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 6 THEN 'TA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 7 THEN 'HLP/UBA, BU/Color'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 10 THEN 'HLP'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 13 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 16 THEN 'HLP'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 19 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 90 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 91 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 92 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 93 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 94 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 95 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 96 THEN 'HLP/UBA'
ELSE 'No Test Micro'
END
INTO #tblTempA
FROM vwUnfilteredFermenters
SELECT #tblTempA.FermenterCode AS "Fermenter ID", #tblTempA.Filled AS "Fill Date", #tblTempA.FillDay,
tblMicroTestDays.Test, tblMicroTestDays.HLPResults AS "HLP Results", tblMicroTestDays.UBAResults AS "UBA Results"
FROM tblMicroTestDays RIGHT JOIN #tblTempA ON tblMicroTestDays.TestDay = #tblTempA.FillDay
END
hello,
could any one tell how to go about keeping carriage returns and line breaks when inserting a chunk of text so that when that text is recieved it will still contain those carriage returns and line breaks?
also, what datatype would be most appropriate?
thakyou,
zaffi.
When I was using a simple query using select statement with where clauses, I can get the results. When
I use AND to specify more conditions. It returns no row even though I get the result when query seperately.
What should be the possible cause of this. I am using SQL Server 6.5. Thank you
Hi folks,
I have a problem where my subquery is returning two values and I need it to return only one. I believe it is doing this because of the group by DocType clause as there are two DocTypes.
Is there anyway I can sum these values in the subquery, to return only one value. This way the query should run ok.
Thanks for any help you might be able to give,
robinsql
SELECT OCRD.Cardcode,
OCRD.cardname,
@YearStart AS YearStart,
@YearEnd AS YearEnd,
@PeriodStart AS PeriodStart,
@PeriodEnd AS PeriodEnd,
OCRD.County,
(SELECT CASE WHEN OINV.DocType = 'I' AND SUM(INV1.Quantity) <>0 THEN COALESCE(SUM(INV1.Price * INV1.Quantity), 0) ELSE COALESCE(SUM(INV1.Price), 0) END FROM INV1, OINV WHERE INV1.DocEntry = OINV.DocEntry AND OINV.CardCode = OCRD.CardCode AND OINV.DocDate >= @YearStart AND OINV.DocDate <= @YearEnd GROUP BY OINV.DocType) AS YTDInvoice,
FROM
OCRD, OINV
WHERE
CardType='C'
I've got some text files that I need to read and make some calculations, before inserting data into a table.
When I use:
Code:
select BulkColumn as txt from openrowset(BULK 'c:Tempdata35.txt', SINGLE_NCLOB) as text
It only retrieves the first row. How can I select all the lines in the file?
I'm having trust issue with my HAVING clause.
I'm trying to find customers who have spent X (in this case 500 or more) dollars in a month
--photogs spending $500 or more on products in April, 2008
SELECT PhotogCode, SUM(ProductRevenue)
FROM TBL_Reporting_DailySalesRevenueAndProductUseBy_Photog
WHERE DATEPART(MM, [Date]) = 4
AND DATEPART(YY, [Date]) = 2008
GROUP BY PhotogCode
HAVING SUM(ProductRevenue) >= 500
However, I'm finding that if I use a variation of this (see below)
--all photogs spending money on products in April, 2008 ordered by total
SELECT PhotogCode, SUM(ProductRevenue)
FROM TBL_Reporting_DailySalesRevenueAndProductUseBy_Photog
WHERE DATEPART(MM, [Date]) = 4
AND DATEPART(YY, [Date]) = 2008
GROUP BY PhotogCode
ORDER BY SUM(ProductRevenue) DESC
and I look at the returned rows, I'm discovering that there are an additional 12 customers who have spent $500 or more...is there something I'm missing with the query using HAVING?
Thanks, Steph
I havethe following query which returns one row of data, however, the MedicalcodeID is NULL.
SELECT db1.dbo.Referral.ReferralGuidDigest, dbo.patient.PatientID, dbo.Consultation.ConsultationID, dbo.Staff.StaffID,
db1.dbo.Referral.EffectiveDateTime AS EventDate, db1.dbo.Referral.Status AS ReferralStatus, db1.dbo.Referral.Mode AS ReferralMode,
db1.dbo.Referral.ServiceType, db1.dbo.Referral.Urgency, db1.dbo.Referral.Direction, db1.dbo.Referral.Transport,
db1.dbo.Referral.EndedDate, db1.dbo.Referral.ReceivedDate, dbo.lkupMedical.MedicalCodeID, db1.dbo.Referral.Term,
[code]...
It is clear from teh above - that the expected MedicalCodeID = 33959 and NOT NULL. I dont understand why SQL added the COLLATE SQL_Latin1_General_CP1_CS_AS to the query - am working on a database developed by another person. Could it be the ACode and ReadCode in dbo.lkupMedical is not set up with SQL_Latin1_General_CP1_CS_AS. How to implement to LkupMedical table....
I changed HIGHLIGHTED JOIN to Inner/Right but it never yielded any results, no record found..
When I'm checking the syntax in Query Analyzer for the following code, I get the error:
"Must declare the variable '@TempVariableTable' "
spGetTableTesting returns a table
CREATE PROCEDURE spGetTable
(@VALUE1 nvarchar(50),
@VALUE2 nvarchar(50))
AS
SET NOCOUNT ON
DECLARE @TempVariableTable table
(Field1 int)
EXEC @TempVariableTable = spGetTableTesting @VALUE1,@VALUE2
GO
How do I set the @TempVariableTable to the results from spGetTableTesting?
Thanks
It is common for me to need to create ratios from data in my database such as
SELECT
( list_value / sale_price ) as ratio
FROM
values
The value returned is always an integer whether decimal is cast or not. IE if sale_price is > list_value then 1 or 0 is returned instead of the percentage (ratio) as expected. Only whole numbers are returned.
BTW. Same is true in postgres db I have as well. What is it that I am doing wrong?
doco
Hi all
According to BOL, Stored Procedure SP_DEPENDS will return two result sets , showing Dependency of an object for example a Table
USE Northwind
EXEC sp_depends 'Customers'
But when i execute the above command nothings happens !!! and sql server returns nothing ? why? where am i wrong?
Thanks in advance.
Regards.
Goodmorning,Could I have a SELECT statement that normally returns two rows,but that instead returns one row appending to the first row the secondone of the result ?For exampleQuery: "SELECT username from tab1 where year in (2001,2002)"Result:1° - "'John'"2° - "'Adam'"Instead I need:Result:"'John','Adam'"?I have Win2000 Pro , SqlServer2000.Thank You--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
View 3 Replies View RelatedMy first foray into the SQL CLR world is a simple function to return the size of a specified file.
I created the function in VS2005, where it works as expected.
Running the function in SSMS, however, returns a value of zero, regardless of the file it is pointed at.
Here's the class member code:
Public Shared Function GetFileSize(ByVal strTargetFolder As String, ByVal strTargetFile As String) As Long
' Returns the size of the specified file.
' Parameters: strTargetFolder = path to target file, strTargetFile = target file name.
Dim lngFileSize As Long
Dim objFileInfo As FileInfo
' Confirm file exists.
If Not File.Exists(strTargetFolder & "" & strTargetFile) Then
Return -1
End If
Try
objFileInfo = My.Computer.FileSystem.GetFileInfo(strTargetFolder & "" & strTargetFile)
lngFileSize = objFileInfo.Length
Catch
' TODO: add error handling; system folders cause error during processed.
End Try
Return lngFileSize
End Function
In SSMS (sp2), here's my assembly steps (this is my local dev machine; I have admin rights):
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
-- For file system access, set the database TRUSTWORTHY property.
ALTER DATABASE dba_use
SET TRUSTWORTHY ON
CREATE ASSEMBLY GetFolderInfo
FROM 'C:ProjectsGetFolderInfoGetFolderInfoinDebugGetFolderInfo.dll'
WITH PERMISSION_SET = UNSAFE
Here's the udf declaration:
CREATE FUNCTION dbo.udfGetFileSize( @strTargetFolder nvarchar(200), @strTargetFile nvarchar(50) )
RETURNS bigint
AS EXTERNAL NAME
GetFolderInfo.[GetFolderInfo.clsFolderInfo].GetFileSize
And the function call - note the target file is on a remote server. Actual file name differs slightly:
SELECT dbo.udfGetFileSize('\SomeServerName$MSSQL2000MSSQLData', 'SomeDBName_Data.MDF')
This always returns zero with no error displayed. Running Profiler was little help and there's not much in the Event Log.
The function returns correct values in VS2005.
The assembly is created with UNSAFE because using EXTERNAL_ACCESS resulted in a security error that prevented the assembly from being created, let alone running. Security is, I suspect, at the root of this issue as well, but I'm not sure what or where to look to verify this.
Any assistance is greatly appreciated.
Hi all,
My Problem is Hoe the Stored Procedure returns data to the Calling Env.If it is fieds values then we can use the OUT Parameters.In Case of My SP returns more number of rows .Then how we capture the data and returs to the Calling Env.i know that we can use the Cursors in Oracle but how is it in SQLSERVER???????pls
pls Help me throw some samll exps.Any help Really appriciate..ThaksRams