Last post Oct 13, 2016 11:03 AM by PatriceSc
Oct 13, 2016 06:32 AM|Capricon User|LINK
i have 2 tables
now i create update query
create procedure updateselect_Data
table2.Name =@Name ,
table2.School =@School ,
now when execute updateselect_Data then how i enter city name instead of ID
because in form
there is four text box
there is text box on city not drop down
when i execute like this THIS show error on UK
updateselect_Data '11','ABC Name','DEF School','UK'
when i execute like this
updateselect_Data '11','ABC Name','DEF School','2'
this execute successfully
Oct 13, 2016 06:51 AM|PatriceSc|LINK
It seems you try to update a numeric column with a string. It seems rather that your intent would be to check if the city type label is found in the city type table to reuse its id or to insert a new row and use this new id in your main update ? Or do you
want to just allow the user to select from existing "city types" ?
Or do you really need a city type table? You could store the cty type label directly in the main table as it seems your intent from the update statement.
Fine here but generally speaking it's best to never post about an error without posting the error message or the best English translation you can. It can help wrong guesses if the situation is a bit more complex than that (for example names are not matching
so if not telling about the statement that works my first guess could have been a column name mismatch).
Oct 13, 2016 06:55 AM|Capricon User|LINK
I am trying when user enter city name in textbox then this must be update because user enter city name .. User not select city name from dropdown ..
Oct 13, 2016 07:03 AM|PatriceSc|LINK
So it could be something such as :
SELECT @CityId=CityId FROM CityTable WHERE CityLabel=@CityLabel -- Find the city id if the text already exists
-- If not found insert the new label and get its id
IF @CityID IS NULL
INSERT INTO CityTable(CityLabel) VALUES (@CityLabel)
-- You can then use @CityId to update the main tableUPDATE etc...
Or from your update your intent is just to stote the label in the main table???
For now the issue is that your table structure is done one way but your update statement is done as if you werre using another way (ie storing the city name directly in the main table or separately in a "reference" table).
Oct 13, 2016 07:06 AM|Capricon User|LINK
CityTable is table 1 or table 2? and what is citylabel?
Oct 13, 2016 11:03 AM|PatriceSc|LINK
This is table1 and CityLabel is your City_Type information. I always favor explaining what should be done rather than providing ready to paste code.
So in short my understanding is that table2.City_TypeID is a foreign key to table1. So you should find which row stores the entered City_Type, possibly creating a new row if not available already and then use this row id to update your table2.City_TypeID
column (which seems what you are trying to do when looking at your table2/table1 structure).
Another option would be to store directly this value in a table2.City_Type nvarchar column (which seems what you tried to do when looking at your current update statement though I guess using
table2.City_Type=@City_Type rather than
table2.City_TypeID=@City_Type is likely a typo).