|
I was affraid so. So I guess the solution is putting the Cities and Cities_Loc. together. Well the good thing is that new cities are not being added by users. We have once every while an update from "localizers/dataentry" and after checking we update the production tables. So no need to use triggers.
It's a shame there is no subtle solution...
Thanks for your help.
Gidon
|
|
|
|
|
Have you considered using a textbox to input part of the name then Ajax to dynamically pull matching names from the database?
|
|
|
|
|
That comment made me think. Because that's exactly the purpose of the stored procedure.
But I designed the stored procedure in such a generic way that i can also use it for datasets where paging has to be used. (input of the sproc is : CityName, PageIndex, PageSize, TotalRecords OUTPUT)
But actually it will mostly be used by the Ajax thing, so than i don't need paging (it always returns the top x records from page 1), and can use your fast solution.
If page 2 is wanted, i use the slower solution i have right now.
Thanks for that comment!!!
|
|
|
|
|
I need the syntax of using Stored Procedure in several cases Using C# code. and also how to create table using stored procedure in SQL server
I perform below systax in my project but I want to replace them using Stored procedure in C#.
DELETE
SqlCommand cmd=new SqlCommand("delete from logininfo where uid='"+user_id.Text+"'",con);
cmd.ExecuteNonQuery();
UPDATE
SqlCommand cmd2=new SqlCommand("update logininfo set pwd ='"+ newpwd.Text) +"' where uid='"+user1+"'",con); cmd2.ExecuteNonQuery();
SELECT
SqlCommand cmd=new SqlCommand("select b from logininfo where uid='"+user+"'",con);
INSERT
SqlCommand cmd=new SqlCommand("insert into vendor_master values('"+ name.Text +"')",con);
cmd.ExecuteNonQuery();
|
|
|
|
|
This is the code that creates the stored procedure
CREATE PROCEDURE DeleteUser
@userId int
AS
DELETE FROM LoginInfo WHERE uid = @UserId;
GO
Here is the snipped that calls the stored procedure from C#
int userId = int.Parse(user_id.Text);
SqlCommand cmd = new SqlCommand("DeleteUser");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = myConnection;
cmd.Parameters.AddWithValue("@UserId", userId);
cmd.ExecuteNonQuery();
You should, hopefully, be able to figure the rest out from this example.
|
|
|
|
|
Yes ,I think i can do the rest.
Thank u very much.
|
|
|
|
|
Hello,
is there a way on how to insert a new row into an MS SQL table that only contains an autogenerated primary key column?
Thanks,
Michal
|
|
|
|
|
What are you inserting into the row? You might want to reconsider the design of only having one column and it being auto.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
Paul,
thanks for the reply.
It's exactly what my design is. There's just one column and it is being autogenerated.
Let's say this table is named Table_A. The sole auto int PK column in Table_A is named Table_A_Id.
Let's say there's another table, Table_B. This Table_B contains, among other things, a column named Table_A_Id. There is a foreign key relationship between Table_A.Table_A_Id and Table_B.Table_A_Id. By way of this FK relationship, the unique Table_A.Table_A_Id rows are being linked to many Table_B.Table_A.Id rows.
Each time I insert a new row into Table_A, I get the last autogenerated PK by issuing SELECT @@IDENTITY. I take this last PK from Table_A and insert multiple rows to Table_B that link to this Table_A.Table_A_Id.
The problem is that if Table_A only contains one autogenerated PK column, there's actually nothing to insert (no columns, no values) into Table_A. My current workaround is that I've added a text column named Comment to Table_A. I'm generating a new row in Table_A by issuing INSERT INTO Table_A (Comment) VALUES ('') then.
But I'd like to find out how to generate a new row in Table_A with no workarounds.
Thanks much,
Michal
|
|
|
|
|
Why on earth do you want a table with only an autogenerated column on it? That's just begging for somebody to point out that you've failed the database normalisation test here. Plus, don't use @@IDENTITY to get the identity of a column. Use SCOPE_IDENTITY() instead.
|
|
|
|
|
Pete O'Hanlon wrote: Why on earth do you want a table with only an autogenerated column on it?
He explained it to me in his reply to my post. But he still makes no sense and yes, he really blew the database normalization here.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
Is there also a Table_C which has as a column, Table_C.Table_A_Id? I've come across this a few times and other than adding an extra column as you suggest, you can also go without using Auto. But instead you write a trigger that peforms the logic that an Auto would do you for you normally.
|
|
|
|
|
You really should redesign your tables, because like Pete said, this is a failure at database normalization.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
Table_A contains no data. All it contains is a surrogate key, a key to look up nothing.
If you really need the list of values that you expect in Table_A, then a select distinct(Table_A_Id) from Table_B will serve its purpose.
If you want to constrain the column in Table_B to a set of values, then use a constaint.
|
|
|
|
|
hi
Please Explain in Detail on what you want to achieve.
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi, Vuyiswa Maseko,
thanks for your reply. I described the coveted structure in my reply to Paul. Any help will be greatly appreciated.
Thanks much,
Michal
|
|
|
|
|
In SQL Server 2005,
If not caring about normalization, NEWID() will autogenerate a uniqueidentifier
INSERT INTO [GSTDB].[dbo].[TableName]
([Field1])
VALUES
(NEWID())
GoodLuck!!
|
|
|
|
|
Hello Friends,
I want to get the information of the record(s) where SmallImage(Field Name) must be unique. For this i'm using the following query. Is it right or not if not plz make it correct
Select * From Gallery1 Where SmallImage in (Select Distinct SmallImage From Gallery1 Where Active=1)
|
|
|
|
|
hi Friend
try this
select * from Gallery1 where SmallImage = "whatever" And
Exists (Select Distinct(SmallImage) From Gallery1 Where Active=1)
Remember you have to Assign a value in the First select statement.Think of it this was. you want to select all the records in a table called "Gallery1" where Value is equals to "that", and from the above Query display the records Distinctively.
hope it helps
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Sorry Friend,
The code you sent is not working properly
Select * From Gallery1 Where SmallImage='~/Gallery/Bhaiji in USA/Small/IMG_0028.jpg' and SmallImage in
(Select Distinct SmallImage From Gallery1 Where Active=1)
The code is given above only giving the records that include the SmallImage='~/Gallery/Bhaiji in USA/Small/IMG_0028.jpg'
|
|
|
|
|
hi Friend, here is your Mistake. i have sent you the Query that looks like this
select * from Gallery1 where SmallImage = '~/Gallery/Bhaiji in USA/Small/IMG_0028.jpg' And
Exists (Select Distinct(SmallImage) From Gallery1 Where Active=1)
And you sent me that give you a Problem now ,that looks like this
Select * From Gallery1 Where SmallImage='~/Gallery/Bhaiji in USA/Small/IMG_0028.jpg' and SmallImage in
(Select Distinct SmallImage From Gallery1 Where Active=1)
if you can look at the Above Queries, they dont look the same. the First Query is what i recommended and the second query, its yours and you did not use the "Exists" and you used SmallImage again, and SQL does not know what to do with it, its like an used variable but declared.
try the first one and tell me what you get
Hope it helps
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Sorry Friend again i got same result............
It's bringing the records Where SmallImage-"....given below...."
Select * From Gallery1 where SmallImage = '~/Gallery/Bhaji in Lodhi Garden/Small/5.JPG' And Exists (Select Distinct(SmallImage) From Gallery1 Where Active=1)
|
|
|
|
|
My requirement is to read data from Oracle using BizTalk (XML) and write to SQL Server.
Looking for recommendations.
Thanks.
The Phantom.
|
|
|
|
|
|
Do a google search first.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
