|
I have problem with Pass through queries. The problem is as follows:
The scenario is Pass Through Query (PTQ) has to execute a stored procedure on Sql Server (versions should be a problem in this case). So when PTQ is connecting to the SqlServer 2005 which is local to the application is working fine and returning results. The same is not working fine when PTQ has to execute SP which is on remote SqlServer.
I checked the ODBC connections they are fine.
Please help me.
Intelligence is measured by common sense not by how many scholarly books you read.
|
|
|
|
|
hello frndz,
am creating an application using vb.net and sql server. my table contain eno field as a primary key. when ever i run that appliction and give some no tho that field. if that no is already exist it was shown not allowed duplicate and some error msg. but am n need of such thing in a msg box..(ie "Your entered eno is already exist please try different no"). i think this is done using a trigger for table in sql. But i dont get such things. if any one tell me how to write trigger for that criteria? please help me....
thank you..
|
|
|
|
|
Please don't use SMS speak in the forum.
Right - your primary key, is it meant to be a unique identifier? If so, why not use an identity column and then let the database worry about setting it?
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
hello,
am working with that identity function. but it is not helpful to me.cause in my application the user only give value for that primary key field(actually that is a 4 digit no and it is unique by nature) whwenever creating new files there is achance for giving same no. that time only we have to show some message that is a primary key so please try to fill someother no. am developing this application with sqlserver 2000 and vb.net 2005. i couldnt able to create trigger for primary key. please help me....
|
|
|
|
|
No trigger needed use an identity field
<br />
USE CREATE TABLE [dbo].[MyTalve](<br />
[ID] [int] IDENTITY(1,1) NOT NULL<br />
<br />
)<br />
<br />
You get the picture ?
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
sorry frnd. am a fresher for sql idont get such think identity field what is it? is it give some message (ie your entered no is already there) Please help me am in urgent. want to submit my proj today it self Please help me....
|
|
|
|
|
hai,
am working with that identity function. but it is not helpful to me.cause in my application the user only give value for that primary key field(actually that is a 4 digit no and it is unique by nature) whwenever creating new files there is achance for giving same no. that time only we have to show some message that is a primary key so please try to fill someother no. am developing this application with sqlserver 2000 and vb.net 2005. i couldnt able to create trigger for primary key. please help me....
|
|
|
|
|
Hello All,
I'm buildling an egovernment web application using ASP.NET (VS 2003) that will be used by a medium sized organization with 1000 users as a start.
My Code was in a single VB module which has all the functions that run the system, and the pages only call those functions.
I have built a wrapper class to ease communication with the database (in order words, to completely eliminate any worries about database and focus on the business objects)
so i ended up with functions dealing with business objects, and i can now really go highspeed in buildling the app.
However My problem is:
it's Easy to do the coding now, very easy.
but each function that deals with business objects will require calling another function that also deals with business objects.
so it's like (for example)
function RegisterUser(a,b,c,d,e)
if Func1(a,b,e)=true then
func2(a,b)
func3(b,c)
func4(a,b,c,d,e)
end if
func5(a,b,"ok")
end function
each one of these functions queries the database for some information.
so let me make it easier for u, when a user logs in, The application actually made 30 queries to the database and with each query, it's opening a connection and closing it afterwards.
when the user creates a new document, it's like 120 queries.
when the user clicks the search button, it's about 400 queries.
that's cause i made coding easier by wrappers.
the application itself works JUST fine.
but i got two issues, 1. what if there are 1000 users, and 2. what's the good approach to a better programming.
so, i really need your help guys.
i need to figure out, is Open/Query/Close connections worse than
Open/alot of queries/close. i think yes, but i need a professional opinion.
also, is it a good approach to do wrappers like that.
is the current situation harmless and SQL SERVER 2000 can deal with these too much connections and has some way of Caching?
i can explain more with code.
Thanks in Advance.
Anmar
|
|
|
|
|
Anmar_Code wrote: i need to figure out, is Open/Query/Close connections worse than
Open/alot of queries/close. i think yes, but i need a professional opinion.
No, it is not worse - it is the recommended design. (Aquire connection, execute query, release connection)
[ADDITIONAL]
The main thing that reduces performance is the round trips to the server, not the opening and closing of connections. You should reduce the number of queries you have to make.
[/ADDITIONAL]
Now, if you have a method that performs two queries one after another then keep the connection open a little longer to make the second query - if you can you can even put two SQL Queries into on SqlCommand object (just separate them with semi-colons) or even better use stored procedures.
However, if you are calling 400 queries for one action then I really do recommend that you look at what you are doing and seeing if they can be done in less queries. Stored Procedure can help becuase they can do many things at once (including conditional stuff, so you INSERT in to table A, and if some value is this then INSERT also in Table B, but if not then UPDATE table C, etc.)
It looks to me if you are doing stuff like
func2(a,b)
func3(b,c)
func4(a,b,c,d,e)
And all those methods do is run SQL Queries then you could rationalise them into Stored Procedures.
|
|
|
|
|
Thank u for ur information
i will trace the queries and see what i can do...
i might however, ask some other questions although they might sound a little silly...
Thanks alot...
Anmar
|
|
|
|
|
You may also want to look at the Data Application blocks for your connection to the database which gives you an instant DAL (Data Application Layer), some people link them some people don't
Search the MS site for "Data Application Block" under patterns and practices.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
I've a DataSet with lots of DataTableAdapters which all work fine, but i'm getting a very strange behavior when merging two unchanged DataSets:
pseudo code:
MyDataSet ds1 = new MyDataSet();
new MyTableAdapter().Fill(ds1.MyTable);
MyDataSet ds2 = new MyDataSet();
new MyTableAdapter().Fill(ds2.MyTable);
ds1.Merge(ds2, true, MissingSchemaAction.Error) // Need to preserve changes because there might be some, but not in this test case.
The Select statement isn't anything special either: 'SELECT Column1, Column2, ... FROM MyTable' and the Database entries are garanted to be unchanges between queries.
Problem:
After merging those two MyDataSets all rows of MyTable are marked as changed, but really aren't (i'm absolutly positive about that).
On the other hand modifying one row in ds1.MyTable before merging results in one changed entry afterwards, which is correct...
I'm disecting this for hours now, but can't find a solution for it.
Funny that nobody else does seem to have such problems, all articles and forum entries i've found releate to changes NOT reflected within the DataTable, which works perfectly well in my case...
Does anyone have an idea or suggestions on how this problem came to be?
Thank's in advance!
-- modified at 11:40 Wednesday 6th December, 2006
Variables won't; constants aren't. (Osborne's Law)
|
|
|
|
|
Who can give me some advice about the following:
I have to make a table for keeping information about clients. Because a minority of the clients have two addresses e.g. invoice address and delivering address, i thought that for reason of efficiecy it would be better to put the necessary fields for keeping the address information in a separate table,so i built three tables :
1) tblClients with the fields : ClientID, Name, Client since
2) tblClientAddresses with the fields: ClientId, AddressId, Street, Postcode, City
3) tblAddressTypes with the fields: AddressID, Description.
AddressID = 1 stands for DeleveringAddress , AddressID = 2 stands for invoicing address and 3 stands for delvering address and invoicing address is the same.
There's a one to many relation between tblClients and tblClientAddresses based on the field ClientID.
There's also a one to many relation between tblAddressTypes and tblClientAddresses.
I would like to build a class Clients with the fields :ClientID, Name and Client Since, and within this class two other classes : class ClientAddresses and Addrestypes ( both nested classes)
If all the neccerary fields for keeping the information of the client would be in on table and thus in one single class the the following function would be possible te make and work:
Public shared Function GetClient() As List (of Client)
Dim cnn As SqlConnection = GetConnection()
Dim selectstatement As String = "Select ... From tblClients"
Dim selectCommand As New SqlCommand(Selectstatement, cnn)
cnn.Open()
Dim reader As SqlDataReader = selectCommand.ExecuteReader()
Dim client As New List (Of Client)
Do While Reader.Read
Dim c As new Client
c.ClientId = reader("ClientId").ToString
...
The question is : How can i do the same with this three tables instead of one
in order to fill a list with all the fields out of this three tables?
ODRAP
|
|
|
|
|
There are many ways. You could use a DataSet with two tables and a data relation, or you could loop through the outer list and then read in matching items from the inner list or... You get the idea anyway.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi
I am trying to copy SQL Schema to ORACLE and other RDBMS using C# without using any third party tools. Right now i can able to copy SQL schema to SQL can anybody help me to sort out this problem please very urgent. Also by mistake I posted my question in articles(.NET) tell me how to delete my posted message from the article(I am sorry).
With Regards
Kesavan
kesavan
|
|
|
|
|
NK7 wrote: Also by mistake I posted my question in articles(.NET) tell me how to delete my posted message from the article(I am sorry).
You will see a delete button on each of your posts. Press it.
|
|
|
|
|
Colin Angus Mackay wrote:
You will see a delete button on each of your posts. Press it.
 Could be a candidate for signature material :->
If you try to write that in English, I might be able to understand more than a fraction of it. - Guffa
|
|
|
|
|
PaulC1972 wrote: Could be a candidate for signature material
Go for it! 
|
|
|
|
|
Colin Angus Mackay wrote: You will see a delete button on each of your posts. Press it.
Consider it done
You will see a delete button on each of your posts. Press it. - Colin Angus Mackay
|
|
|
|
|
PaulC1972 wrote: Consider it done
Woo hoo!!! I'm famous! 
|
|
|
|
|
Colin Angus Mackay wrote: Woo hoo!!! I'm famous!
I'd like to help but I don't feel like Googling it for you.
|
|
|
|
|
PaulC1972 wrote: I'd like to help but I don't feel like Googling it for you.
Ah.... Fame is such a fickle thing indeed. I guess that's my 15 minutes over with.
|
|
|
|
|
Colin Angus Mackay wrote: Ah.... Fame is such a fickle thing indeed. I guess that's my 15 minutes over with.
No, you're still famous. The sig is on my laptop with CPHog and the message I just posted is in my faculty office using IE 6 which has an old sig. You'll see more of the other sig when I am using my laptop
I'd like to help but I don't feel like Googling it for you.
|
|
|
|
|
hello guys my main problem is how to copy schema. could you reply to that please
kesavan
|
|
|
|
|
hi,
anyone know how to search the date by "dd/mm/yyyy" in sql query!
my code is :
Select datamonth from datemonthtest where datamonth >= #" & strdatefrom & "# and datamonth <=#" & strdateto "#
and data type i set it to date/time!
The result is not efficient to select the month!
Example: when i select the date is from 01/10/2006 to date 30/10/2006 but all the previous month will be come out! 09/08/2006......
Please help!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
