|
I understand what you are saying but am not sure how to do this...
Can you point me to an example
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
There's some example code in here:
http://www.codeproject.com/cs/database/sqldawithoutsqlcb.asp?[^]
The code is not so great, but basically there's an overload when creating the paramters for a command (insert, update or delete) that specifies the column name from where it's value will be taken.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
Hi guys
i m having a table which primary key is char(7). i want to have last inserted row's id value but @@IDENTITY returns numeric rather character. I dnt hv ne field like time timestamp too 
Thanks in advance
Utsav
|
|
|
|
|
do i need 2 mention thaT CHAR primery key is not identity (i.e. autoincrement)??
|
|
|
|
|
i think it was d stupid most question a person can ask, got d solution guys, dnt boter 4 that stupidness
Utsav
|
|
|
|
|
hi there,
if u want to use @@Identity to get the last inserted record, u've to use the int field with auto increament attribute.
Then, u can use this identity to retrive the info of the last inserted record
<< >>
|
|
|
|
|
Hi,
I have a field in a table that can have one of three values; the field is multiple choice, in other words. I was wondering how I can represent this in a DataTable. The closest I've been able to come is to create one table with one field and three records representing the three values. In a second table, I create a data relation between the multiple choice field and the table with the three records. Therefore, the field in the second table must have one of the three values. But I'm guessing there is probably a better way? Thanks!
|
|
|
|
|
Leslie Sanford wrote:
to create one table with one field and three records representing the three values. In a second table, I create a data relation between the multiple choice field and the table with the three records. Therefore, the field in the second table must have one of the three values.
My preference would be to normalise that. Unless the possible values are integers or single characters I'd want to kave an id column.
The Main table has a column that is an id to the Lookup table. The Lookup table has two columns, an ID column (a tiny int, because that is all you need) and a Value column (of what ever type you need).
The reason for the tiny int is so that the reference takes up as little room in the Main table as possible (I'm guessing there is going to be a lot more rows in there than in the Lookup table) which will improve database performance if a lot of rows have to be accessed as they will take less space and the database will be able to read them from disk faster (and disk access is usually the bottle neck in database systems)
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Colin Angus Mackay wrote:
Does this help?
Yes, absolutely! Thank you very much.
|
|
|
|
|
That sounds OK to me.
As far as I can see, you can either create a table listing all the choices available and link it to another table (like what you did), or if the selection numbers are fixed (e.g. always 3 selections) then you can have one table with 3 columns each containing a value.
|
|
|
|
|
I have a problem with the stored procedure that does the union on columns in from one table to another.
I have table1 which has the following column names:
A11, A12, A13, A21, A22, A23
and what the union does is to insert the records from table1 to table like this:
insert into table2
as
select A11 as a1, A12 as a2, a13 as a3
from table1
union
select a21 as a1, a22 as a2 , a23 as a3
from table1
The procedure is working but what it does, if I have one record in table1 it duplicates the record after the union in table2.
That is for one record in table, I get two records of the same record in table2.
What might cause this?
Please help as this gives me a lot of work in identifying the duplicates after running the procedure.
Thanks
phokojoe
|
|
|
|
|
You are unioning a different set of columns on the same table so it sees them as distinct. If you were unioning the same set of columns then it would remove the duplicate rows.
If you do not want the same row being represented twice then you need to put in a WHERE statement to each to define what is permitted and what isn't.
Alternatively you could perform two INSERT statements*. e.g.
INSERT INTO table2
SELECT [id], A11 AS A1, A12 AS A2, A13 AS A3
FROM table1
INSERT INTO table2
SELECT [id], A21 AS A1, A22 AS A2, A23 AS A3
FROM table1
RIGHT OUTER JOIN table2 ON table1.[id] = table2.[id]
WHERE table2.[id] IS NULL
However, you still need to define conditions as to what is acceptable and what isn't for these two inserts as the second INSERT will currently not add anything as each row will already be represented. Also, table2 needs a primary key that is shared with table1. I've assumed that to be a column called [id], you may need to change that.
* DISCLAIMER: I've not tested this so it may not be 100% correct. I tend to get my right and left outer joins mixed up
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
this is a standard line code for mysql database connection
i've created a database, fisically i don't know where is the file of my database, so i don't know how indicate it in this row.
I think i need to put it here "server=serverurl" but what insert at the place of serverurl?
SqlConnection myConnection = new SqlConnection("user id=username;" +
"password=password;server=serverurl;" +
"Trusted_Connection=yes;" +
"database=database; " +
"connection timeout=30");
|
|
|
|
|
At the Enterprise Manager, when I click on a particular database, the error message was:
Internet Explorer Script Error
An error has occurred in the script on this page
Line 307
Char 2
Error: Unspecified error
code: 0
url: res//C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Binn\Resources\1033\sqlmmc.rll\Tabs.html
Clicking on other databases doesn't yield this problem. I have tried deleting the database, restoring it. Even tried using wizard to create a new database and add back all the files but the error still persist.
Any way I can get rid of the error. I still can work on the tables but it is irritating as it will pop up now and then.
|
|
|
|
|
Dear friends
i have a database which has a lot of columns and unfortunately all columns has a some null input.
Database has a lot of columns and it is not easy to write in where criteria all field.
I need if is possible a query like this all of in database table which data is null change it with "NO INPUT" string.
If i can take your valuable advice(s) i will be very glad.
Thanks a lot
|
|
|
|
|
MAGICIANMERLIN wrote:
I need if is possible a query like this all of in database table which data is null change it with "NO INPUT" string.
That's not an easy query. Why not try your first option?
MAGICIANMERLIN wrote:
write in where criteria all field
With a little lateral thinking this can be achieved in less than 5 minutes.
SELECT 'UPDATE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] SET ['+COLUMN_NAME+
']=''NO INPUT'' WHERE ['+COLUMN_NAME+'] IS NULL;'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE='YES' AND DATA_TYPE IN('char','nchar','varchar','nvarchar')
Basically, this is a query that will generate a SQL script. Run it in Query Analyser and remember to set the Results in Text (Ctrl+T) then run it. You can then copy the contents of the results pane to use as the query that will update your data.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Thank you Collin,
i am still trying that solution which u can advice to me.
Still i can not solve my trouble but i try and i think i ll do it.
Thanks a lot
|
|
|
|
|
Hai Guys I want to Insert,Update,and Delete records into a single table using views in MS SQL 2000. I read this is possible unsig Visual C++.
Can one help. If not views then how can I retrieve data from more than one table and be able to manipulate it in Visual C++ 6.0.
|
|
|
|
|
Hi,
I have a table on SQL Server where one column allows Null values (docMName) and I'm trying to perform a like query using one parameter. When executed the query fails to include rows with Null values for the docMName column. Is there a way to include these missing rows? Any help would be greatly appreciated.
SELECT docIndex, docLName, docFName, docMName
FROM tblStaffPhysicians
WHERE (docFName + ' ' + docMName + ' ' + docLName LIKE '%' + ? + '%')
-- modified at 18:09 Friday 16th September, 2005
|
|
|
|
|
I'm not really sure why you'd create a search like the this intentionally. It's going to wreak havoc on any indexes the execution planner might have taken advantage of.
Why not just use separate arguments for the first, middle and last names?
SELECT <br />
docIndex, docLName, docFName, docMName<br />
FROM <br />
tblStaffPhysicians<br />
WHERE<br />
(docFName LIKE '%' + ? + '%') AND<br />
((docMName IS NULL) OR (docMName LIKE '%' + ? + '%')) AND<br />
(docLName LIKE '%' + ? + '%')
If there is a compelling reason why the statement must use a single parameter value, there are other ways of approaching this, but since you're concatenating a string with spaces right in the SQL statement, it seems like it would be equally sensible to split the parameter value in code on the location(s) of spaces and provide multiple parameters to the command.
Hope this helps,
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
Thank you for responding to my query. When I setup the stored procedure for the application I was unaware that there would be a Null value issue. This is just one view on the data and the concept here was to provide a single textbox to search. I'd rather rewrite the stored procedure than the application if possible. Thanks again.
|
|
|
|
|
This was the reply I got Tom on the Microsoft SQL Forum and it works like a charm.
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
Toms Code=================================================
SELECT docIndex, docLName, docFName, docMName
FROM tblStaffPhysicians
WHERE (docFName + ' ' + ISNULL(docMName, '') + ' ' + docLName LIKE '%' + ? + '%')
|
|
|
|
|
So, if you have a first name and last name, but no middle name in one of your rows, and a user enters text of the form [first name]<space>[last name], how exactly is that going to work, since you will be comparing against a concatenated string value of [first name]<space>''<space>[last name]?
I would say that the application really does need to change, or you need to make your stored procedure a little smarter about how the comparison string is concatenated (basically, smarter about where to insert spaces).
Good luck,
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
The example you gave did not look like a stored procedure. It looked like a text SQL command. Stored procedures don't use wildcards (?) as parameter tokens.
If you would like to provide the stored procedure source, I will try to help you. I'd prefer to see what you've got versus rewriting something and missing some behavioral nuance.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
Sorry, just got back in town; here is the stored procedure. Looks like you're right, it doesn't pull if I query both a First and Last name.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE ia.SP_StaffPhysicianSelectCommand
(
@Param2 varchar(35)
)
AS
SET NOCOUNT ON;
SELECT docIndex, docLName, docFName, docMName FROM dbo.tblStaffPhysicians
WHERE (docFName + ' ' + ISNULL (docMName, '') + ' ' + docLName LIKE '%' + @Param2 + '%')
ORDER BY docLName, docFName
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- modified at 20:49 Sunday 18th September, 2005
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
