|
kripa ostwal wrote: where ammCode in (2,3)
That is because you are asking for any cars with ammCode of 2 OR 3.
Try a join instead between two result sets.
First set:
SELECT carCode from carAmm WHERE ammCode = 2
Second Set:
SELECT carCode from carAmm WHERE ammCode = 3
Something like this:
SELECT c1.carCode
FROM carAmm AS c1
INNER JOIN carAmm AS c2 ON c1.carCode = c2.carCode
WHERE
c1.ammCode = 2
AND
c2.ammCode = 3
|
|
|
|
|
s,
bt i wrote store procedure for it coz there will be n number of aminities in future for search criteria.
& I passed a string as '2,3'
Can I split it in store procedure & create joins?
or any other way to do this?
|
|
|
|
|
kripa ostwal wrote: s,
kripa ostwal wrote: I passed a string as '2,3'
If you are using SQL Server 2008 you can pass tables. That way you have no crazy string manipulation to get the data you need.
kripa ostwal wrote: Can I split it in store procedure & create joins?
Yes, but the splitting operation is a PITA.
kripa ostwal wrote: or any other way to do this?
You must already be using dynamic SQL, so just variations on a theme (unless you are using SQL Server 2008)
|
|
|
|
|
i m using sql server 2000 with asp.net
|
|
|
|
|
I'm certain this is about as noob-ish as it gets, but I'd like to know what typical solutions to this issue are.
2 people (on two computers) get passed their own dataset instances, representing 1 table in the database. They both want to add a row to the db table. They both click on the new row in the datagridview, and it happily inserts a new row on their monitors, with a new Primary Key value (identity column). Problem is, the number is the same on both computers. So the first person can save just fine, but the second person naturally receives an PK violation error when he or she saves.
How is this issue supposed to be dealt with (i.e. avoided)? Avoid creating the PK value at all on the client side,allowing the database to create the PK when insert has been performed, and just refresh the datagridview with that value (created by the database)?
Some other approach?
Thanks for ideas,
cdj
|
|
|
|
|
I generally avoid PK creation on the client-side. The database alone should be responsible for that. However, if that isn't possible, then my next preferred solution is to use a GUID. That way the two machines will generate different numbers.
Of course, your DBA may baulk at the use of GUIDs as the data will be inserted randomly (and since the PK is often the clustered index, that can slow the performance of the database if you are doing a lot of inserts or do searches on sequential [or near-sequential] ranges of PK values)
|
|
|
|
|
Yep - as I was typing the question, I realized that the answer was to simply not create the pk client side.
Thanks!
cdj
|
|
|
|
|
Hi all,
I need to make a copy of a SQL Server (2000, I think) database and a friend told me the easiest way to do this was to back up the database and then restore it to a different name. Does anyone know a good source for a step by step guide on how to do this? The database I have to back up is a production database and then I'm going to restore it to a different name to make a test copy of it and I'm just nervous working with production stuff. So a step by step guide would be very helpful.
Also... if the hosting provider is backing up the server every night, does that also back up the SQL Server database? I'm just wondering if I need to tell him some specific steps to backing up the database, but if backing up the whole server does the trick then I don't need to.
Thanks!
Denise "Hypermommy" Duggan
|
|
|
|
|
Hypermommy wrote: The database I have to back up is a production database and then I'm going to restore it to a different name to make a test copy of it and I'm just nervous working with production stuff.
You should be restoring it onto a non-production server. That way you are isolated from the production server and it would be near impossible to damage the production system while you were working on your newly created test/development system.
Hypermommy wrote: Also... if the hosting provider is backing up the server every night, does that also back up the SQL Server database?
Most likely, but that is a question for your hosting provider.
SQL Backups work differently from file backups because the SQL Server will be on-line and modifying its files. The hosting provider would have to be taking specific SQL backups. They are likely to be doing this already.
|
|
|
|
|
I appreciate it. Yes, ideally I'd be using a different server. Unfortunately, with this client that's not do-able. It's something I hope to be able to convince them of the necessity of in the future but for now.....
As far as SQL Server being backed up... I doubt it. If they have to do something different from a regular Windows backup then I don't believe it's happening. Just something else I need to talk to them about.
Figuring that the hosting provider (a small company) is not doing a separate SQL Server backup, is it possible to point me to some quick literature of just a couple pages that I could send him to say "do this"?
Denise "Hypermommy" Duggan
|
|
|
|
|
Hello there!
My code is highlighted below.I have five tabpages(two is shown here!) on a form called vwpatient that runs each time a tabpage is selected.The code below works but my problem is this: there is a textbox called sachPatient.patID from another form called sachPatient, the code pulls data from the database on clicking one or two pages, but if more than two is clicked, an exception fires saying "Make sure you have not released a resource before attempting to use it". I have tried endlessly to fix this problem to no avail. And am still trying!!.
I would appreciate if someone can help me.
Thanks in advance!!!
Private Sub TabControl1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabControl1.SelectedIndexChanged
For Each tabcont As Control In Me.Controls
If TypeOf tabcont Is TabControl Then
For Each tp As Control In tabcont.Controls
If TypeOf tp Is TabPage Then
Select Case tp.Name
Case "vwProfTabPg"
Try
LoadData()
' Configure and execute the command.
cmd = New System.Data.SqlClient.SqlCommand("SELECT * FROM Patient_profile where patient_Id = @Pat_ID")
cmd.Connection = cn
Dim paraPatId As SqlParameter = cmd.Parameters.Add("@Pat_ID", sachPatient.patID.Text)
' Load the DataTable.
datadapt = New System.Data.SqlClient.SqlDataAdapter(cmd)
datadapt.Fill(ds)
ds.Tables.Add(dt)
' Bind the DataGrid.
vwProfDG.DataSource = ds.Tables(0)
vwProfDG.Visible = True
Catch ex As SqlException
MsgBox(ex.Message.ToString())
Finally
cn.Close()
End Try
Case "vwLabTabPg"
Try
LoadData()
' Configure and execute the command.
cmd = New System.Data.SqlClient.SqlCommand("SELECT * FROM lab where patient_Id = @PatLa_ID")
cmd.Connection = cn
Dim paraPatId As SqlParameter = cmd.Parameters.Add("@PatLa_ID", sachPatient.patID.Text)
' Load the DataTable.
datadapt = New System.Data.SqlClient.SqlDataAdapter(cmd)
datadapt.Fill(ds)
ds.Tables.Add(dt)
' Bind the DataGrid.
vwLabDatGrid.DataSource = ds.Tables(0)
vwLabDatGrid.Visible = True
Catch ex As SqlException
MsgBox(ex.Message.ToString())
Finally
cn.Close()
End Try
End Select
End If
Next
End If
Next
|
|
|
|
|
nedusmile wrote: if more than two is clicked, an exception fires saying "Make sure you have not released a resource before attempting to use it".
On what line of code is this exception being raised?
Also, this exception does not sound like a SQL exception to me, it sounds more like you are attempting to use an object that's been disposed. That being the case, this is not a database question and you would be more likely to get the assistance you need if you posted in the correct forum (e.g. .NET Framework or VB.NET)
|
|
|
|
|
hi all,
let suppose :
@ch = ''
what does it's means
it is null
it is empty
it has some value
if i use ch as
.........
............
...
case
@ch
when '' then .........
...........................
i want run above case statement than what would be value of ch when i execute the SP from winform
|
|
|
|
|
I would read that as ch has a value of the empty set; it is a zero length string.
|
|
|
|
|
In youer example @ch would be an empty (zero length) string. If you want it to be null then
SET @ch = NULL
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
a string of length zero
Niladri Biswas
|
|
|
|
|
Hi I need to create a stored procedure that changes the column name of a column in a datatable. Can someone help please? Thanks
|
|
|
|
|
You might want to give more information. As it is, changing the name of a column as you have it stated would be a one time operation.
Alter Column [column_name]
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
There is a stored procedure available in the System Stored Procedures in
Sql Server. It is called sp_rename
It accepts 3 parameters viz.
@objname nvarchar(1035), -- up to 4-part "old" name
@newname sysname, -- one-part new name
@objtype varchar(13) = null -- identifying the name
For renaming a column, execute the following
EXEC sp_rename
@objname= 'TABLE_NAME.OLD_COLUMN_NAME',
@newname = 'NEW_COLUMN_NAME',
@objtype = 'COLUMN'
N.B.~ For your reference, the @objecttype correspond to
system tables which track of the folowing type:
'column' 'database' 'index' 'object' 'userdatatype'
Hope this helps
Vote me
Niladri Biswas
|
|
|
|
|
Hi,
How to return value in the following format
Name Name Name Name
N1...N2....N3....N4
1....2 .... 4 ....7
3....1 .... 8 ....7
4....8..... 5.... 7
7....5......3.....8
the numbers can be consider as the list of tokens for the N1,N2..etc
The table structure is
N1.1
N1.3
N1.4
N1.7
N2.2
N2.1
N2.8
N2.5
N3.4
N3.8
N3.5
N3.3
.
.
.
Thankyou
YPKI
|
|
|
|
|
It's called a pivot table and SQL 2005+ supports it here is an article [^]about it
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
PIVOT expects aggregate function
Niladri Biswas
modified on Tuesday, June 30, 2009 12:01 AM
|
|
|
|
|
Use MAX() or MIN() as the aggregate 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try this
Create a table
CREATE TABLE mytable (
Name1 VARCHAR(10)
,Name2 VARCHAR(10)
,Name3 VARCHAR(10)
,Name4 VARCHAR(10)
)
Next insert some records
INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( 'N1', 'N2', 'N3', 'N4' )
INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( '1', '2', '4', '7' )
INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( '3', '1', '8', '7' )
INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( '4', '8', '5', '7' )
INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( '7', '5', '3', '8' )
Fire the below query
SELECT *
FROM mytable
;
WITH unpvt
AS ( SELECT col1,
col2
FROM mytable UNPIVOT ( col2 FOR col1 IN ( [Name1], [Name2], [Name3], [Name4] ) ) AS unpvt
) ,
Result1
AS ( SELECT col1 AS NameList_pre,
col2 AS NameList
FROM unpvt
WHERE col2 LIKE 'N%'
) ,
Result2
AS ( SELECT col1 AS NameList_pre,
col2 AS Tokens
FROM unpvt
WHERE col2 NOT LIKE 'N%'
)
SELECT Result1.NameList,
Result2.Tokens
FROM Result2
INNER JOIN Result1 ON Result1.NameList_pre = Result2.NameList_pre
ORDER BY Result1.NameList,
Result2.Tokens
Output:
Name1 Name2 Name3 Name4
N1 N2 N3 N4
1 2 4 7
3 1 8 7
4 8 5 7
7 5 3 8
Niladri Biswas
modified on Monday, June 29, 2009 11:56 PM
|
|
|
|
|
HI,
I am a newbie to programming,I am developing a webapplication in order to learn about sql and asp.net/C#.In my application there are five users which can access the application my application will be running on IIS on lan, The application is simply inserting,deleting,modifying record in a table.Now let us suppose:
1)three users all of them on different systems are inserting different records in the table simultaneously.
2)fourth and fifth are viewing the same record simultaneously(easily identified by a unique database id)but fourth is modifying the record whereas fifth is simply deleting the same record at the same time,what will happen?will an error occur?how to handle that error?
also please tell what are stored procedures used for(in this context)?
thanks
with regards
shankbond
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
