|
If there is a possibilty that the master table changes frequently go for cursors instead of views...
Gautham
|
|
|
|
|
gauthee wrote: If there is a possibilty that the master table changes frequently go for cursors instead of views...
 How would cursors solve the problem?
|
|
|
|
|
hai everybody
Am using sqlserver2000 as my back end. i want to combine 5 tables in a view and then got report from it. Is it possible? Already am combining 2 tables in a view. it was working fine. one table is master it has a composite primary key all the other containing same as a foreign key and also they have thier own primary key(Serial no)
Please Help me
|
|
|
|
|
Then just add the joins to the SELECT statement used to define the view. e.g.
SELECT *
FROM TableA
INNER JOIN TableB ON TableA.Key = TableB.ForeignKey
INNER JOIN TableC ON TableA.Key = TableC.ForeignKey
-- etc...
|
|
|
|
|
Hi,
I have a table named "FirstNames", "LastNames" and "Main". I want to enter data into the First and LastNames tables and then the identity number must be added to the Main.
I know it looks something like this but I cannot get the values into the main table.
Create Procedure dbo.Test
(
@F_FirstName varchar(20)
)
As
Insert Into FirstNames
(
F_FirstName
)
Values
(
@F_FirstName
)
Select Scope_Identity()
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
I'll let you fill in the gaps.
-- Declare a variable to hold the id
DECLARE @FirstNameID int
...
-- Get the ID after inserting the FirstName row
SELECT @FirstNameID = SCOPE_IDENTITY()
...
-- Insert the ID into the main table.
INSERT INTO Main(FirstNameID) VALUES(@FirstNameID)
|
|
|
|
|
5+7
5&7
what is the output
plz let me know
|
|
|
|
|
ravikiranreddydharmannagari wrote: 5+7
5&7
what is the output
plz let me know
SELECT 5+7
Result: 12
SELECT 5&7
Result: 5
+ is an addition operation
& is a logical AND operation
|
|
|
|
|
I am using SQL Server 2000 reporting services.I created a report in VS.net 2003.Report is showing in preview pane properly.But when i press start it gives a messege "No Reprot Server was found at http://localhost/ReprotServer".
Is reprot server required for the reprot.whats other alternative to show the report on web browser?
|
|
|
|
|
Please help...I think I'm losing my mind.
I'm using an OleDB connection in C# to connect to a database. The table I'm trying to access tracks user session information, including the date & time of logging in and primary key of the session. I start to run into problems when the user has logged in over 68 times.
Here is the section of code:
int LastLoginRow = userDS.Tables["UserSessionInfo"].Rows.Count - 2;
sring LastLoginDateTime = userDS.Tables["UserSessionInfo"].Rows[LastLoginRow]["SessionDate"].ToString();
I checked, and it does accurately tell me what the LastLoginRow is (i.e. 90). However when it goes to grab the SessionDate information, it ALWAYS grabs row 68.
Before it gets to row 68, it works fine. Does anyone know what is going on?
~~~~~~~~~~~~
 Over-worked, under-paid, glorified "secretary" 
|
|
|
|
|
The code looks OK. Might help to see the query.
Other than that, maybe all the rows after 68 have the same value in SessionDate?
Perform the query with some other tool and see what you get.
|
|
|
|
|
Here's some more information I've come across. There are two tables in my database, UserInfo & SessionInfo and they are connected through the field UserID, with a one-to-many relationship. When I look at the SessionInfo table alone, the sessions look as they're supposed to, the SessionPkey is ordered correctly.
However, when I expand the UserInfo row to show the related SessionInfo, at some point (around row 68 in SessionInfo) each subsequent sessioninfo is pushed on the top of the table, instead of being inserted into the bottom of the table.
So, when looking at just the SessionInfo table, the sessionPkey looks like this:
227 - 228 - 229 - 230 - ... 321 - 322 - 323 - 324
But when looking at the sessionPkey table as a subtable of the userInfo, it looks like this:
296 - 297 - 298 - 299 ... 321 - 322 - 323 - 324 - 227 - 228 - 229 - 230 ... 293 - 294 - 295
Does anyone know why this happens? I haven't changed the code used to insert into the table. Thanks.
~~~~~~~~~~~~
Over-worked, under-paid, glorified "secretary"
|
|
|
|
|
Hi ..
I want to use DataGridView as a data entry table << and I like to make alot of things like data verificatoin and don't move between rows before complete the existing fow...
I know that it isn't easy to do .. but if any one have any book or site which demonstrate the DataGridView as good .. I will be so happy ..
jooooo
|
|
|
|
|
Hi ..
I'm using SQL Server 2000 with VB6, vb.net ,,, and some times ( rarely ) on add new record , I found that the row is added to the table more than one time ..
and when I go to Edit or Delete this row , the next message is appear:
Key Column is Insufficient or Incorrect
Too many Rows were affected by update.
Notice:
There're no any constraitns or releations between that table and any other table
Really this problem is exhausting me
jooooo
|
|
|
|
|
kindman_nb wrote: Key Column is Insufficient or Incorrect
Too many Rows were affected by update.
The key (primary key) on a row must be unique. If there are no constraints you are not enforcing this most basic requirement of a key. The application is therefore getting confused when the update affects multiple rows.
To fix the problem you need to:
* Remove any existing duplicates from the table
* Put a proper constraint on the primary key (defining the primary key would be enough)
* Find and fix the part of the application that is producing the multiple inserts.
|
|
|
|
|
Hi
Thanks my friend ...
now it's good
jooooo
|
|
|
|
|
In the web site that I am building ( in C# language ), a hypothetic customer who would buy something would then be redirected to a Secure payments company where he would make the payment and then the company would send back to my web site, information about this transaction.
My program would then save this info in a Microsoft SQL database. The problem is that this company uses to send the same info several times repeatedly and I do not want to store the same info more than once.
So I want a SQL procedure where it takes the invoice number of the customer ( contained in the string of info that this company sends back ) and looks inside a table to see if it was already stored there. If it is there ( or not ), it would return a value, which could be false/true or 0/1 so my program could use this value to save a new info ( or not ) and then activate ( or not ) some related tasks.
I am still learning SQL and I tried the below procedure but it is not working. Which alternative procedure could solve the problem ?
~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE VerifyIfInvoiceExists
(@Invoice VARCHAR(50))
AS
SELECT COUNT(*) FROM IPN_received
WHERE Invoice = @Invoice
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
|
|
|
In your stored procedure you can do something like this:
IF NOT EXISTS (SELECT * FROM IPN_received WHERE Invoice = @Invoice)
BEGIN
-- The row does not exist already. Insert the new row here!
END
|
|
|
|
|
Hi Colin
What I really need is the procedure simply returning TRUE if a given invoice is there or FALSE if it is not.
What lines of code should I add after “IF NOT EXISTS (SELECT * FROM IPN_received WHERE Invoice = @Invoice)” ?
( Sorry if my question is too elementar. I am still learning SQL. )
|
|
|
|
|
tedhill13 wrote: What I really need is the procedure simply returning TRUE if a given invoice is there or FALSE if it is not.
You can use something like:
SELECT 1
or
SELECT 0
Then you can pick up the value from your application with ExecuteScalar()
int existsAlready = (int)theSqlCommand.ExecuteScalar()
tedhill13 wrote: Sorry if my question is too elementar. I am still learning SQL.
Then you may wish to lean more about constraints so that SQL Server can handle this scenario itself - that is what it is good at.
|
|
|
|
|
I would likely have a ProcessState column. Initialized to something like a "Pending" code.
When I receive a response (approve or decline), if the transaction is still "Pending" I'd set the ProcessState as appropriate. Then other methods would take the transactions from there.
Technical note: Use integers for the code, not strings; have a separate table to map the values to strings. Then, if you like, add referential integrity.
Extra credit: If you don't know about finite state machines, you might want to do some reading.
|
|
|
|
|
Can someone post a link for some sample app. in vb2005 and with sql db (mdf)
Thanks!
|
|
|
|
|
There are plenty of samples at Microsoft's web site including their "coding4fun" samples. Make a search there.
modified 1-Aug-19 21:02pm.
|
|
|
|
|
Hi All,
I'm calling a SQL2000 SP from my VB.NET app with the following code:
<br />
Dim sqlCmd As SqlCommand<br />
sqlCmd = New SqlCommand("JHU_SAS_IDOC_Process", conn)<br />
sqlCmd.CommandType = CommandType.StoredProcedure<br />
<br />
Dim adapter As SqlDataAdapter = New SqlDataAdapter(sqlCmd)<br />
<br />
adapter.Fill(tableReturned)<br />
This SP performs a SELECT that causes the tableReturned (a DataTable) to be filled. The SP also returns an INT (0 or 1, depending on whether an error occurred). The SP code is basically this (much simplified for this question):
<br />
CREATE PROCEDURE dbo.JHU_SAS_IDOC_Process<br />
<br />
AS<br />
<br />
BEGIN<br />
<br />
SELECT * FROM JHU_SAS_IDOC<br />
<br />
RETURN 1 -- or 0, depending on an error condition<br />
<br />
END<br />
My question is: How do I read that return value in the app? I think I could read it using the SqlCommand.ExecuteScalar() func, but that wouldn't work to fill my table. Any ideas?
Thanks!
Justin
|
|
|
|
|
Justin Cooke wrote: How do I read that return value in the app? I think I could read it using the SqlCommand.ExecuteScalar() func, but that wouldn't work to fill my table.
And it wouldn't work to get the value because ExecuteScalar just retrieves the value in the first column of the first row in the result set and discards anything else.
You need to add a SqlParameter[^] to your SqlCommand object to get the ReturnValue.
For example:
Dim sqlCmd As SqlCommand
sqlCmd = New SqlCommand("JHU_SAS_IDOC_Process", conn)
sqlCmd.CommandType = CommandType.StoredProcedure
Dim parameter As New SqlParameter("ReturnValue", SqlDbType.Int, 4)
parameter.Direction = ParameterDirection.ReturnValue
sqlCmd.Parameters.Add(parameter)After you've got the data you can then check the value of the parameter with parameter.Value
DISCLAIMER: I typed that off the top of my head, and I'm also not a VB.NET developer, so there may be errors
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
