|
Is not easy to get the result in one simple query! . At least you must write a cursor to doing this..
D!shan
|
|
|
|
|
Try this also.. its little bit matching to ur prob.. OR is it ok to doing this through cursors?
USE pubs
GO
DECLARE @title_ids varchar(150), @delimiter char
SET @delimiter = ','
SELECT @title_ids = COALESCE(@title_ids + @delimiter, '') + title_id FROM titles
SELECT @title_ids AS [List of Title IDs]
D!shan
|
|
|
|
|
THANKS,I will try it
Arvind
Arvind
|
|
|
|
|
Im trying to do the following query on a view in access db:
"SELECT List Totals.Expr1,List Totals.Order ID FROM List Totals"
this is the view definition that i got from the db:
"SELECT Sum([Orders Detail].[Unit Price]*[Orders Detail].[Quantity]) AS Expr1, Orders.[Order ID]\r\nFROM Orders INNER JOIN [Orders Detail] ON Orders.[Order ID] = [Orders Detail].[Order ID]\r\nGROUP BY Orders.[Order ID];\r\n"
and i get syntax error missing operator in query expression List Totals.Expr1
how do i do this right?
|
|
|
|
|
|
Thanks ,this is a step forward , what does the square
brackets mean?
|
|
|
|
|
blankg wrote:
Thanks ,this is a step forward , what does the square
brackets mean?
It's a quote char, so you can use spaces as part of field and table names. If you don't use spaces on field and table names, you can safely ommit them. Actually, most programmers use the "_" char instead of space exactly for this reason.
Yes, even I am blogging now!
|
|
|
|
|
ive been creating a query using date as one of its parameters. the date is of CTime data type.
my code is like this:
CString dbWorkName;
CString dbPICName;
CString dbElementName;
CTime dbDate;
CString readSQL;
double ReviewActualHour = 0;
CString strReviewActualHour;
CString ProjName;
CString strReviewDay;
SYSTEMTIME date;
dbDate.GetAsSystemTime(date);
strReviewDay.Format("%04d-%02d-%02d",date.wYear,date.wMonth,date.wDay);
_bstr_t bstrQuery;
_variant_t getFiled;
_variant_t vRecsAffected;
vRecsAffected = 0L;
readSQL = "SELECT L_EveryDay.username, T_ProjectName.project, T_ProjectDetailName.detail, T_OccasionName.occasion, L_EveryDay.ymd, Sum(L_EveryDay.difference)/60 AS TOTALHOUR ";
readSQL += "FROM T_OccasionName INNER JOIN (T_ProjectDetailName INNER JOIN (T_ProjectName INNER JOIN L_EveryDay ON T_ProjectName.seq = L_EveryDay.project) ON T_ProjectDetailName.seq = L_EveryDay.projectdetail) ON T_OccasionName.seq = L_EveryDay.occasion ";
readSQL += "GROUP BY L_EveryDay.username, T_ProjectName.project, T_ProjectDetailName.detail, T_OccasionName.occasion, L_EveryDay.ymd ";
readSQL += "HAVING (((L_EveryDay.username)=";
readSQL += '"';
readSQL += dbPICName;
readSQL += '"';
readSQL += ") ";
readSQL += "AND ((T_ProjectName.project) LIKE ";
readSQL += '"';
readSQL += '%';
readSQL += ProjName;
readSQL += '"';
readSQL += ") ";
readSQL += "AND ((T_ProjectDetailName.detail)=";
readSQL += '"';
readSQL += dbWorkName;
readSQL += '"';
readSQL += ") ";
readSQL += "AND ((T_OccasionName.occasion)=";
readSQL += '"';
readSQL += dbElementName;
readSQL += '"';
readSQL += "AND ((L_EveryDay.ymd)=";
readSQL += "#";
readSQL += strReviewDay;
readSQL += "#";
readSQL += "));";
bstrQuery = readSQL;
MessageBox(NULL, readSQL, "Test", MB_OK);
try
{
recordSet_ = connection_->Execute(bstrQuery,&vRecsAffected,adOptionUnspecified);
}
catch( _com_error &errMessage )
{
MessageBox(NULL, "failed", "Test", MB_OK);
TRACE(errMessage.Description());
TRACE(errMessage.ErrorMessage());
TRACE(bstrQuery);
}
When the program executes the query, runtime error appears. im having difficulty on how to format the date during the query. In MS Access, the date is enclosed with #. So, i tried the same way too. I tried so many times on formatting the date but no one returns the good result.. so for those, who have encountered the same problem as I am, please help me with this...
what should I do with my query?
thanks in advance
|
|
|
|
|
Use parameters. You'll need a Command object. Parameters send the values directly to the database engine with no translation to and from ambiguous string formats.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Like Mike said, using parameters is best way to do it.
But if you want to build your own SQL statement, try to format the date into yyyy-mm-dd or yyyy/mm/dd format in the SQL statement, e.g. for Access it is #1999/05/24#
By using this you'll:
1. Remove ambiguity between date and month.
2. Remove error when passing date into SQL statement.
Cheers,
Edbert P.
|
|
|
|
|
what is the syntax to perform SELECT query on a stored View in an Access Data Base?
|
|
|
|
|
I am not too familiar with MS Access, but I wouldn't have thought it would be any different from a table:
SELECT * FROM view_name WHERE blah=blah
SELECT * FROM table_name WHERE blah=blah
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
I tried this way ,it didnt work (it works fine with sql server) .
thanks anyway , does anyone have a diffrent way?
|
|
|
|
|
I think it is the same syntax as the above submitted comment. It might be the same between a view in sql server and view in access. Because view in access is using access probject and view is store in the sql server. But some issue you should consern about such as:
1. Does your system connect to sql server database every time? and make sure that it is not interrup.
2. When create database object (table, query, view...) try to use naming convention. (Ex: if you want to create table name "Customers", name it "tblCustomers" instead of "Customers). Naming convention help you to identify the right database object in your SELECT sql statement.
3. Use bracket if your database object name has a space in the middle. (Ex: you have table named "Customer name", in SELECT statement use the bracket to [Customer name], SELECT * FROM [Customer name])
Check it again and told us if it still have a probme. Post an error message when you challenges with it again.
Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Problem solved, thanks a lot to both u guys.
|
|
|
|
|
How could you solve it? Do you found any other solution to solve it or get the solution from our idea? I just want to know, if our idea is right then I will help the other people who have the same problem like you. If it is wrong I will try to research on it. Any way, I still try all of my best to help you and other people. 
Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Using the brackets solved the exception in my query.
|
|
|
|
|
Hello,
In my SQL Server, Table named Hardware, Attributes - HardwareID(VarChar), Price(money). When I want to get the edit text boxes, I declare as
Dim hprice As String = Ctype(e.Item.Cells(4).Controls(0), TextBox).Text
What can I do to stop the error below?
----------------------------------------------------------------------
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.ArgumentException: System.FormatException: Input string was not in a correct format. at System.Number.ParseDecimal(String s, NumberStyles style, NumberFormatInfo info) at System.Decimal.Parse(String s, NumberStyles style, IFormatProvider provider) at System.Convert.ToDecimal(String value, IFormatProvider provider) at System.String.System.IConvertible.ToDecimal(IFormatProvider provider) at System.Convert.ToDecimal(Object value) at System.Data.Common.DecimalStorage.Set(Int32 record, Object value) at System.Data.DataColumn.set_Item(Int32 record, Object value)Couldn't store <> in Price Column. Expected type is Decimal.
Concerned
|
|
|
|
|
The error is not occuring on the line you have specified.
From the call stack, it looks like you are attempting to insert an empty string (I am assuming this is coming from the text box). Is this so? If that is the case then it is cannot be converted to a valid decimal format. You need to check for the user inputting invalid information before attempting to insert it into the database.
If you need to insert a NULL valid into the field use System.DBNull.Value
Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
From the email that you sent me the appropriate line appears to be:
UpdateCommand.Parameters.Add("@Price", SqlDbType.Decimal, 9).Value = hprice
But only if the textbox used to populate hprice (which you mentioned in your previous post) contains a value that cannot be interpreted as a decimal.
As I've already mentioned, you need to perform validation on the user input to ensure that invalid and nonsense values are rejected properly. As this is a price, I would guess that the only valid characters are the numeric characters and the decimal point (or comma - depending on your locality)
One easy way around this to perform the conversion yourself, then pass the converted value as the parameter. Wrap the conversion in a try catch block so that if the input from the user is incorrect you can catch it and report to the user their input is incorrect.
Decimal convertedHPrice = Convert.ToDecimal(hprice);
UpdateCommand.Parameters.Add("@Price", SqlDbType.Decimal, 9).Value = convertedHPrice
Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
Colin...... the code is here...
Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)
' update the database with the new values
' get the edit text boxes
Dim id As String = (CType(e.Item.Cells(2).Controls(0), TextBox)).Text
Dim lname As String = (CType(e.Item.Cells(3).Controls(0), TextBox)).Text
'********************** Problem is here below here...
Dim hprice As String = (Ctype(e.Item.Cells(4).Controls(0), TextBox)).Text
Dim hdesc As String = (CType(e.Item.Cells(5).Controls(0), TextBox)).Text
Dim myConnection As New SqlConnection(ConnectionString)
Dim UpdateCommand As SqlCommand = new SqlCommand()
UpdateCommand.Connection = myConnection
If AddingNew = True Then
UpdateCommand.CommandText = "INSERT INTO Hardware(HardwareID, Name, Price, Description) VALUES (@HardwareID, @Name, @Price, @Description)"
Else
UpdateCommand.CommandText = "UPDATE Hardware SET HardwareID = @HardwareID, Name = @Name, Price = @Price, Description = @Description WHERE HardwareID = @HardwareID"
End If
UpdateCommand.Parameters.Add("@HardwareID", SqlDbType.Varchar, 5).Value = id
UpdateCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = lname
'************************************** Problem is here below here...
UpdateCommand.Parameters.Add("@Price", SqlDbType.Money, 8).Value = hprice
UPdateCommand.Parameters.Add("@Description", SqlDbType.VarChar, 100).Value = hdesc
' execute the command
Try
myConnection.Open()
UpdateCommand.ExecuteNonQuery()
Catch ex as Exception
Message.Text = ex.ToString()
Finally
myConnection.Close()
End Try
' Resort the grid for new records
If AddingNew = True Then
DataGrid1.CurrentPageIndex = 0
AddingNew = false
End If
' rebind the grid
DataGrid1.EditItemIndex = -1
BindGrid()
End Sub
|
|
|
|
|
i thought SQLsrv2k had a interface for 3rd part software to gain operation infomation. For example, while a record is inserted into a table, the 3rd part software might get this information.
also it might like the windows message mechanism, any operation may produce a message to windows or others.
is this possible ?
|
|
|
|
|
DO you mean TRIGGERS? Can set one up On insert / update / delete which can then execute code inside SQL Server, or do you mean the ability for a third party app to recieve change notification?
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Triggers might be a solution for this. But it need to modify the database. So i prefer a third party application to do this without modify the database.
i thought it can receive change notification like windows hook doing.
where can i start ? And is there aother way ?
|
|
|
|
|
Think that you would struggle to do it - the 3rd party app would need to constantly poll the DB looking for changes, which at best would affect performance!
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
