|
Hi!
I have three Columns. Category, Type and Item. How would I write a stored Procedure to take values in those three columns and combine them into one column?
So instead of having Category = Test, Type = Test and Item = Test, you will have one column with all the values like: Test / Test / Test.
Thanks!
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
SQL Server usage
SELECT category + '/' + type + '/' + item AS colname FROM tblA
will return a "/" seperated string.
|
|
|
|
|
Hallo frends,
I want to take result from stored procedure into my Crystal report but cnt see strored procs
into my database expert of Crystal Report.
I m using vb.net & mysql 5.0 & using ODBC for connection on report which includes DSN that i made already on my machine.
But though i m check on "Show Stored procedure" under database exper->options->DataExplorer.
But not showing any stored proc yet only tables.
How should i call my stored proc in crystal RPt.
Please Tell me soon.
Thanks.
-Regards Priya.
"The Difficult i can do it now...
The Impossible will take a little longer."
|
|
|
|
|
Hi Priya,
Please tell me which version of .Net (2003 or 2005) and Crystal Report version (9 or 10) are you using??
I am currently working .Net 2005 with SQL 2000 as backend. I am not sure the same should work on MySQL.
NOTE 1 point: When adding Stored Procedure in Crystal Report, you should have only one SQL Query.
For example.
CREATE PROCEDURE Sql1
AS
BEGIN
Select * from Table1
-- Select * from Table2 (Note -- this should be commented)
END
Hope you are getting the above example. If not, please reply. I will explain in detail.
- Harini
|
|
|
|
|
Hi,
I am using the sql server for creating the database. I want to create the database which is having userid and password. This I require since I wanted to restrict acces to database and also only authorise user to database can use the userid and password created at time of creation of database.
So how can I create the database which is having userid and password?
Thanks in advance..
MikeRT
|
|
|
|
|
I think you may work with "security"
select your database and go to "security" you can there set logins.
|
|
|
|
|
CREATE TABLE [dbo].[TblLogin] (
[Name] [varchar] (50) ,
[username] [varchar] (20),
[passwd] [varchar] (20) ,
[Email] [varchar] (100)
)
GO
with Regards,
Imthiyas Ahamed.A
|
|
|
|
|
what is understand is, you are trying to create a database from your application and you want to have a valid users for it.
If so, create database, then create user and assign access permission for accessing the database.
however sa user will be able to access the new database. also any other user who have System Administrators role on other database same instance of SQL Server will be able to access the database. to my knowledge this cannot be restricted
|
|
|
|
|
Hi,
Problem is that the sql server on which i want to create the database is having some password.However as the login id to access the Master is 'sa' and which is universally fix . I can create database using master. But when I want to give userid and password to database I have created I should know the userid and password of sql server. Theirafter I can access the database and change the user of it. That's why I am tinking that if at time of creation If we assign some id and password to database it can be easy.
I think it's lot explanatory.
Mike
|
|
|
|
|
not getting exactly what you are looking for
|
|
|
|
|
I want to create database on sql server which is having it's own userid and password. The application which is going to create database on sql server do not have the user id and password of sql server. However the userid for Master database is 'sa' and password ''(blank) application can create database using master database.
Then since apllication is not aware of userid and password of sql server I am thinking that If we can give userid and password to the database at time of creation then It can be acessible to my application and also provide security as userid and password is with application only. Can it be possible?
Mike
|
|
|
|
|
what i understood & replied (my 1st reply), is for the same i.e.
1. connect to SQL Server using sa user
2. create new database
3. create new user
4. grand permissions for new user to access the new database
5. after this let application use the new user for accessing the data.
|
|
|
|
|
How to write the sql statement using COUNT ...
give me example...
|
|
|
|
|
i got the answer thanks...
|
|
|
|
|
|
Hi!
I have a problem with my ODBC Adapters
The Adapter dont create a Update or Delete Statement. I get the error
update/delete "Could not determine which columns uniquely identify the rows for ...."
The MS Access Database behind has a primary key with autonumber in each Table, so there is a column with an unique identifier.
I couldn't get rid of this problem so far!...
When i use an OLEDB Adapter it works perfectly...but i have to use ODBC..
did anyone had the same problems so far?
please help!
|
|
|
|
|
Are you using the SQL command builder class to create your adapter?
|
|
|
|
|
Good morning freinds,
i m trying to create following procedure in mysql5.0 but getting error abt check syntax in sql statement on 2nd column i.e. at sum(Tuesday).
Monday & Tuesday are two columns of which i wnt to display sum of all records of Monday & tuesday so i wrote following procedure but Error??
As I tried already, i selected sum of other 2 columns at a time in one select stmt, but it is also giving error at 2nd column.
In simple query i can select sum of more than one columns but why not in procedure?
Anybody please help me following is the my procedure.---->
DELIMITER $$
DROP PROCEDURE IF EXISTS `timesheet`.`sp_select` $$
CREATE PROCEDURE `sp_select`()
BEGIN
DECLARE mon,tue double;
SELECT sum(Monday) into mon , sum(Tuesday) as tue FROM timesheet_nonbillables;
SELECT mon,tue;
END $$
DELIMITER ;
What should i change in it to run properly.
Please Reply Soon.
Thanx.
-Regards Priya.
"The Difficult i can do it now...
The Impossible will take a little longer."
|
|
|
|
|
SELECT sum(Monday) into mon , sum(Tuesday) as tue FROM timesheet_nonbillables;
I haven't run this query and tested
May be this could be the reason:
sum(Monday) into mon - variable mon has been assigned
sum(Tuesday) as tue - It is pseudo column won't that be sum(Tuesday) into tue
|
|
|
|
|
hai priya,
you have not added Group by keyword in that, is that may be problem?
using aggregate functions without groupby may also create problems.
change that and try...
Cheers
asithangae
|
|
|
|
|
hai,
check this it works for you...
declare @mon decimal<br />
declare @tue decimal<br />
select @mon = sum(ContactID), @tue = sum(ManagerID) from Employee<br />
select @mon,@tue
cheers
asithangae
|
|
|
|
|
Hello
VS 2005
I have a parent and child table.
I am trying to delete the parent which will also delete all the corresponding children in the child table.
I am using the typed dataset and have created the schema in the .xsd. I have not created the relationship between the table in the .xsd as I find sometimes I have problem updating as there is always some constraint problem. So I normally create the relationship in code when I need to. I think i will have to cascade down.
this is my code for deleting.
<br />
Try<br />
'Get the AssemblyID number of the assembly that will be deleted<br />
drv = DirectCast(Me.cboAssemblies.SelectedItem, DataRowView)<br />
AssemblyID = drv("AssemblyID")<br />
<br />
'Create the parent/child relationship<br />
Me.DsAssembly.Relations.Add("relAssembly", Me.DsAssembly._Assembly.AssemblyIDColumn, Me.DsAssembly.AssembliedEquipment.AssemblyIDColumn)<br />
<br />
'Get the row of data for the assembly that will be deleted<br />
datarow = Me.DsAssembly._Assembly.FindByAssemblyID(AssemblyID)<br />
<br />
'Delete this row<br />
rowsAffected = Me.TA_Assembly_DsAssembly1.Delete(datarow.AssemblyID, datarow.AssemblyName)<br />
<br />
If (rowsAffected = 1) Then<br />
MessageBox.Show("Assemblied part has been deleted", "Delete Assembly", MessageBoxButtons.OK, MessageBoxIcon.Information)<br />
Else<br />
MessageBox.Show("Assemblied part cannot be deleted", "Delete Assembly", MessageBoxButtons.OK, MessageBoxIcon.Error)<br />
End If<br />
<br />
Catch ex As Exception<br />
MessageBox.Show(ex.Message)<br />
End Try<br />
It deletes the parent but does cascade down to delete all the children that belong to this parent.
Is there something I can do to improve this.
Many thanks,
Steve
|
|
|
|
|
I am using foreign key constraints but i can delete the parent but does not delete the children.
My code below:
<br />
Try<br />
'Get the AssemblyID number of the assembly that will be deleted<br />
drv = DirectCast(Me.cboAssemblies.SelectedItem, DataRowView)<br />
AssemblyID = drv("AssemblyID")<br />
<br />
If (Me.DsAssembly.AssembliedEquipment.Constraints.Count > 0) Then<br />
Me.DsAssembly.AssembliedEquipment.Constraints.Clear()<br />
End If<br />
<br />
FK_Assembly = New ForeignKeyConstraint("AssemblyContriant", Me.DsAssembly._Assembly.AssemblyIDColumn, Me.DsAssembly.AssembliedEquipment.AssemblyIDColumn)<br />
FK_Assembly.DeleteRule = Rule.SetNull<br />
FK_Assembly.UpdateRule = Rule.Cascade<br />
FK_Assembly.AcceptRejectRule = AcceptRejectRule.Cascade<br />
<br />
Me.DsAssembly.AssembliedEquipment.Constraints.Add(FK_Assembly)<br />
Me.DsAssembly.EnforceConstraints = True<br />
<br />
'Get the row of data for the assembly that will be deleted<br />
datarow = Me.DsAssembly._Assembly.FindByAssemblyID(AssemblyID)<br />
<br />
'Delete this row<br />
rowsAffected = Me.TA_Assembly_DsAssembly1.Delete(datarow.AssemblyID, datarow.AssemblyName)<br />
<br />
If (rowsAffected = 1) Then<br />
MessageBox.Show("Assemblied part has been deleted", "Delete Assembly", MessageBoxButtons.OK, MessageBoxIcon.Information)<br />
Else<br />
MessageBox.Show("Assemblied part cannot be deleted", "Delete Assembly", MessageBoxButtons.OK, MessageBoxIcon.Error)<br />
End If<br />
<br />
Catch ex As Exception<br />
MessageBox.Show(ex.Message)<br />
Finally<br />
If (Me.DsAssembly.AssembliedEquipment.Constraints.Count > 0) Then<br />
Me.DsAssembly.AssembliedEquipment.Constraints.Clear()<br />
End If<br />
End Try<br />
Many thanks if you can help me with this problem.
Steve
|
|
|
|
|
What's the proper syntax for doing a contains on multiple columns?
Select * From T1
WHERE CONTAINS (f1, ' "value1" ') AND CONTAINS (f2, ' "value2" ') I was hoping the above was it but realize it's not that simple, however that should give you the idea for what I want...
|
|
|
|
|
i'm just pasting below examples from SQL Server help
hope this may help you
Examples
A. Use CONTAINS with <simple_term>
This example finds all products with a price of $15.00 that contain the word "bottles".
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE UnitPrice = 15.00
AND CONTAINS(QuantityPerUnit, 'bottles')
GO
B. Use CONTAINS and phrase in <simple_term>
This example returns all products that contain either the phrase "sasquatch ale" or "steeleye stout".
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
GO
C. Use CONTAINS with <prefix_term>
This example returns all product names with at least one word starting with the prefix choc in the ProductName column.
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "choc*" ')
GO
D. Use CONTAINS and OR with <prefix_term>
This example returns all category descriptions containing the strings "sea" or "bread".
USE Northwind
SELECT CategoryName
FROM Categories
WHERE CONTAINS(Description, '"sea*" OR "bread*"')
GO
E. Use CONTAINS with <proximity_term>
This example returns all product names that have the word "Boysenberry" near the word "spread".
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, 'spread NEAR Boysenberry')
GO
F. Use CONTAINS with <generation_term>
This example searches for all products with words of the form dry: dried, drying, and so on.
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' FORMSOF (INFLECTIONAL, dry) ')
GO
G. Use CONTAINS with <weighted_term>
This example searches for all product names containing the words spread, sauces, or relishes, and different weightings are given to each word.
USE Northwind
GO
SELECT CategoryName, Description
FROM Categories
WHERE CONTAINS(Description, 'ISABOUT (spread weight (.8),
sauces weight (.4), relishes weight (.2) )' )
GO
H. Use CONTAINS with variables
This example uses a variable instead of a specific search term.
USE pubs
GO
DECLARE @SearchWord varchar(30)
SET @SearchWord ='Moon'
SELECT pr_info FROM pub_info WHERE CONTAINS(pr_info, @SearchWord)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
