Insert Subqueries

I have tried this insert comand and it errors out telling me that i
cannot use subqueries this way. INSERT INTO tblPartLocation
(PartLocation, Part)VALUES (999,(SELECT PartID FROM tblParts WHERE
PartName = 'test'))

how would i insert a value from a query?

thanks for any helpThe syntax you are looking for is:

INSERT INTO tblPartLocation (PartLocation, Part)
SELECT 999, PartID
FROM tblParts
WHERE PartName = 'test'

However, you may also want to reexamine your schema. Don't use tbl as
a prefix for your tables; it's redundant, and unnecessary. Also, you
have a column named Part in one table, but you're inserting the values
of PartID from another table. If the columns represent the same thing,
why don't you name them the same?

HTH,
Stu

pltaylor3@.gmail.com wrote:

Quote:

Originally Posted by

I have tried this insert comand and it errors out telling me that i
cannot use subqueries this way. INSERT INTO tblPartLocation
(PartLocation, Part)VALUES (999,(SELECT PartID FROM tblParts WHERE
PartName = 'test'))
>
how would i insert a value from a query?
>
thanks for any help

|||thanks for your reply...the names are legacy. Just trying to make it
more functional.
Stu wrote:

Quote:

Originally Posted by

The syntax you are looking for is:
>
INSERT INTO tblPartLocation (PartLocation, Part)
SELECT 999, PartID
FROM tblParts
WHERE PartName = 'test'
>
However, you may also want to reexamine your schema. Don't use tbl as
a prefix for your tables; it's redundant, and unnecessary. Also, you
have a column named Part in one table, but you're inserting the values
of PartID from another table. If the columns represent the same thing,
why don't you name them the same?
>
HTH,
Stu
>
>
pltaylor3@.gmail.com wrote:

Quote:

Originally Posted by

I have tried this insert comand and it errors out telling me that i
cannot use subqueries this way. INSERT INTO tblPartLocation
(PartLocation, Part)VALUES (999,(SELECT PartID FROM tblParts WHERE
PartName = 'test'))

how would i insert a value from a query?

thanks for any help

insert special characters in database

I am writing a web application and everytime I attempt to insert some special characters in the database I get errors. Is there a way around this?Depends what you mean. If you mean unicode and you are attempting to insert into CHar or VarChar fields then you need to use NVarChar or NChar. BoL has all the gory details. Otherwise you'll need to porovide more details.

HTH|||it seems everytime I include an apostrophe the system gives me an error..not sure why this is happening

my second question is-- when someone types this:

I cant wait
to leave
this place

--It come out like this
I cant wait to leave this place

How can I make the system print all the tab keys and return carriage?

thank you for any responses|||You need to escape apostrophes.

"I can't wait to leave this place"
must be submitted as
"I can''t wait to leave this place"

HTH|||The latter problem is probably a presentation issue - your FE needs to interpret the carriage returns & line feeds.|||hi everybody,

This is Rajen, hope it will be nice sharing ideas with u all. Feeling nice to join u all .:beer:

insert or update errors

Is there a way I can stop a form from inserting or updating a record when there is an error. I have an sql 2000 DB. I have noticed that if the db field can handle 50 characters and the form field has no limit on the number of characters no errors are displayed to the user if they try to use more than the 50 characters in the textbox. The record is not saved. None of the fields are saved. I do notice the autonumber generated is skipped by the db. That is, the next autonumber for a successful insert skips the logical next number. How do I capture this error, or any save error and return the user back to the form? Yes, I have limited the number of characters a user can type on the textbox now, but I would really like to catch save or insert errors. I use asp.net 2.0 and VB. I don't know C#.
thanks
Milton

You can add some validation within stored procedures but are just using raw SQL for insert and update? It is far better to use stored procedures for all insert, update and delete operations. (If possible all access should be via stored procedures)

If you include an output BIT parameter called say NAD (for No Abnormalities Detected) and only set to to 1 if the insert/update worked with no error. Use a command button to fire the insert/update and if there is an error, post a message to the user and stay on the same page.

|||Its always a good idea to do some basic validation at the front end (like data types, length etc) before passing all the data to the db and validate at the back end. Validation at the DB level should be for any constraint validation, PK-FK validation etc.|||

Is there an example here or elsewhere? I don't see how the form interacts with the sql server. Output BIT parameter? I have a linkbutton control on my form which has no code behind. I have the command name as "insert" for that link button in the properties of the linkbutton. I use VB not C#.
thanks
Milton

|||I will post some links for you tonight.|||Please look at my posting onhttp://forums.asp.net/thread/1687128.aspx. This will allow you to create a range of stored procedures plus wrapper code in either VB.NET or C#. The accompanying library is in C#. I will attempt to produce a VB.Net port this weekend.