|
HELLO SIR
THANK YOU VERY MUCH IT'S WORKS NICE NOW
I WAS LOOPING IN MY SP AND VB BOTH SO NOW I HAVE MUCH BETTER IDEA ABOUT THIS SO
AND I HAVE CONVERTED TO VB. I CAN UNDERSTAND C# BUT I AM NOT VERY GOOD WITH IT BUT ANY WAY YOUR HELP AND UR SUPPORT WAS VERY GOOD
THANKS AGAIN
|
|
|
|
|
from the forum guidelines:
6. Do not remove or empty a message if others have replied. Keep the thread intact and available for others to search and read. If your problem was answered then edit your message and add "[Solved]" to the subject line of the original post, and cast an approval vote to the one or several answers that really helped you.
8. Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job.
|
|
|
|
|
Hi,
I have an application in C# that use SQL Server 2008 R2 Express.
When i install the package i need to choose option and to install:
1. Microsoft .Net Framework 3.5 SP1.
2. Windows Installer 4.5.
3. Windows PowerShell 1.0
I have an SQL Script that will build my DB at SQL 2008 R2 Express and will add the user configuration that i want.
I want to make install package that will include the SQL 2008 R2 Express (including "Microsoft .Net Framework 3.5 SP1.",
,"Windows Installer 4.5" and "Windows PowerShell 1.0 ").
Will run the script and install my application.
I don't want to let the user to choose configuration for the DB in the installation process.
How can i do that?
Thanks,
Shai.
|
|
|
|
|
Please do not post in more than one forum; you already posted this question under .NET and C#. Pick one forum and stick to it.
It's time for a new signature.
|
|
|
|
|
|
I have four tables:
Class- a "class"
PK IdClass
Instance- an instance
PK IdInstance
FK IdClass
PropertyDefinitions- definitions of properties of a concept
PK IdPropertyDef
FK IdClass
PropertyValues- values of properites of an instance
FK IdInstance
FK IdPropertyDef
A Class can have properties. Instance can be of a class. I want to save values of properties of each Instance, but I want to prohibit setting properties of an Instance, which are not defined in it's Class.
So, a cyclic dependence occurs. How to create a proper constraint in MS Sql Server to prevent a situation, when an instance has values of properties which are not defined in a class? Or maybe I could design a better DB schema?
Thanks.
EDIT: Here[^] you can find a applicaable part of a DB diagram.
Greetings - Jacek
|
|
|
|
|
i have a parameter named @State
my where clause is depend on this parameter
for example
if @State=1
<br />
select <br />
.<br />
.<br />
.<br />
From ...<br />
where Status=true AND Response=false<br />
if @State=2
<br />
select <br />
.<br />
.<br />
.<br />
From ...<br />
where Status=false AND UserId>@Number;<br />
i want some thing like switch case in my where clause
what should i do?
|
|
|
|
|
Perhaps this would help
(Status=true AND Response=false and @State=1) or (Status=false AND UserId>@Number and @State=2)
|
|
|
|
|
thanks it does work fine!
|
|
|
|
|
hi all
i need to have a query that will retrieve two rows from one table
for example :
one table has two foreign key from one table
ROWs of Tbl1
------
1) ID
2) UserId1
3) UserId2
what should i do?
select
[User].[FirstName],
[User].[LastName],
[User].[FirstName],
[User].[LastName]
From
Tbl1 Join User on [UserId1]=[User].[Id] or [UserId2]=[User].[Id];
please help me immediately
|
|
|
|
|
Join your User table 2 times 1 for UserID1 other for UserId2
Join User as b on [UserId1]=[b].[Id]
Join User as c on [UserId1]=[c].[Id]
|
|
|
|
|
Run profiler and see where the costs are!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Well you've not given us much to go on...
You might as well just google <your database=""> optimisation tips...
|
|
|
|
|
hello all
i am working on one of my assignment about sql win app. with stored procedure.and there is two different tables with ralation to PlotID so it's working fine. now only problem is this application must have functionality to add multiple customer names with related land plot.
At the moment i can add one name and one land plot
i mean if i add some details about land plot and name of customer then it works fine but now i need to create functionality to add multiple customer name with same land plot bueause one land plot may have multiple owners.
this is my insert sp
so one of my friend said you can use multiple parameters to add multiple names but what if i need to add 3 or 4 names
so this is what i have done according to my friend and this is also working fine.
ALTER PROCEDURE dbo.InsertNew
(
@FirstName nvarchar(25),
@LastName nvarchar(25),
@FirstName2 nvarchar(25),
@LastName2 nvarchar(25),
@Village nvarchar(25),
@District nvarchar(25)
)
AS
SET NOCOUNT ON;
DECLARE @PlotID int
INSERT INTO PlotDetails (Village,District)
VALUES (@Village, @District)
SELECT @PlotID=SCOPE_IDENTITY()
INSERT INTO CustomerDetails ([FirstName], [LastName],[PlotID])
(SELECT @FirstName, @LastName, @PlotID)
UNION ALL
(SELECT @FirstName2, @LastName2, @PlotID)
RETURN
and this is vb for insert record
Private Sub InsertNew()
Try
Comm = New SqlClient.SqlCommand("InsertNew", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure
Comm.CommandText = "InsertNew"
Comm.Parameters.Add("@Village", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem.ToString
Comm.Parameters.Add("@District", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem.ToString
Comm.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = TextBox1.Text
Comm.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = TextBox2.Text
Comm.Parameters.Add("@FirstName2", SqlDbType.NVarChar).Value = FirstName2.Text
Comm.Parameters.Add("@LastName2", SqlDbType.NVarChar).Value = LastName2.Text
Comm.ExecuteNonQuery()
Conn.Close()
MsgBox("Item Saved")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
so is there any other way to do this i mean loop in sql?
waiting for your kind help
thanks
|
|
|
|
|
Try this
Break your InsertNew into two SPs. One for PlotDetails with @Village and @District parameter and save into PlotDetails table. Create a second SP to save CustomerDetails as
ALTER PROCEDURE dbo.InsertNewCustomerDetails
(
@FirstName nvarchar(25),
@LastName nvarchar(25),
@PlotID int
)
AS
SET NOCOUNT ON;
IF @PlotID IS NULL BEGIN
SELECT @PlotID=SCOPE_IDENTITY()
END
INSERT INTO CustomerDetails ([FirstName], [LastName],[PlotID])
(SELECT @FirstName, @LastName, @PlotID)
SELECT @PlotID AS PLOTID
Now in your vb code send Null as plotid for the first time and use the same plotid generated in first time next.
|
|
|
|
|
hello
thanks for your rep.
ok but is this going to loop in sql quary?
for adding multiple names this is what i have done
Private Sub GetNewName()
AddName.Add(TextBox1.Text)
AddName.ToArray()
End Sub
so with this i can add one name or 100 names and i have debug this code and it's looks fine but only thing is how can i insert all this name from array to database
so in that case i have to loop while i am inserting
so this is i have done for insert
Comm = New SqlClient.SqlCommand("InsertNew", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure
Comm.CommandText = "InsertNew"
Comm.Parameters.Add("@Village", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem.ToString
Comm.Parameters.Add("@District", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem.ToString
For I As Integer = 0 To AddName.Count
Comm.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = AddName(I)
Next
Comm.ExecuteNonQuery()
Conn.Close()
MsgBox("Item Saved")
and it looping through all the name i have entered but at last it shows error
error "Index was out of range. must be non-negative and less then the size of the collection parameter name index"
waiting for your kind help
|
|
|
|
|
Not this way.
First Execute
Comm = New SqlClient.SqlCommand("InsertNewPlot", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure
Comm.Parameters.Add("@Village", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem.ToString
Comm.Parameters.Add("@District", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem.ToString
to Insert Plot where InsertNewPlot is the first SP to insert plot
then something like this
Dim iPlotID as integer
For I As Integer = 0 To AddName.Count
Comm = New SqlClient.SqlCommand("InsertNewCustomerDetails", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure
Comm.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = AddName(I)
Comm.Parameters.Add("@PlotId", SqlDbType.NVarChar).Value = iPlotID
Dim da As SqlDataAdapter = New SqlDataAdapter(Comm)
da.Fill(ds)
iPlotID = ds.Tables(0).Rows(0)("PLOTID")
Next
|
|
|
|
|
I am just sitting here running some manual queries against some DB tables and thought that it would be GREAT if we could get a token that somehow meant the reverse of what * means in a select query.
SELECT * FROM [TableName]
Gets us ALL the columns, so if we want to grab a majority, but just skim off a few we need to enter in all of them leaving off the few we don't want.
Why not allow us to do something like this?
SELECT * EXCLUDE FieldName1, ... FROM [TableName]
This seems much cleaner to me. Oh yeah, and it is less typing.
I know, I should be using a tool that allows me to do my queries in a GUI and then it would just be drag and drop to get the columns I want, but we don't always have that luxury. It seems to me that the intent would be much more explicit to the DB engine and thus provide better optimization also.
|
|
|
|
|
You know, you ain't getting that
Generally speaking it is better to list all your columns instead of using SELECT * . It does not have to do unneccessery lookup and your contract is fixed. You know what you will be getting. So, one disadvantage of
SELECT * EXCLUDE col1, col2 FROM tableName
is that, it will be forced to do a lookup then remove, Again, much efficient to list all the columns.
|
|
|
|
|
|
Ray Cassick wrote: popped into my head
Go wash out you mind, preferably with beer is is starting to stray into fantasy land again.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Why not write a query that returns all fieldnames ex the ones you specify, and copy/paste the result into your script?
(Second hint, you could automate that task if it's important enough to spend time on)
I are Troll
|
|
|
|
|
Sheesh, sounds like work
yeah, yeah... I know... Just ramblings of a tired mind on a Friday afternoon.
|
|
|
|