|
I have no idea where to start. I have a table names Pages. A field PageID, PageNumber, etc.. (those only two matters). I delete some page and need to take the one at the end of the book(?) and bring it to the place where the page where deleted (Change the PageNumber). I need to do this in a stored proc of sql function. I don't know where to start ! 
sebastien.lachance.blogspot.com
|
|
|
|
|
Here is something to get you started. It really needs to be wrapped in a transaction with a 'SERIALIZABLE' isolation level. Brutal if you have a lot of users.
DECLARE PROCEDURE DeletePage
(
DECLARE @PageID INTEGER
)
AS
DECLARE @MaxPageNumber INTEGER
SELECT
@MaxPageNumber = MAX(PageNumber)
FROM
Pages
DECLARE @PageNumber INTEGER
SELECT
@PageNumber = PageNumber
FROM
Pages
WHERE
PageID = @PageID
IF @PageNumber IS NULL
BEGIN
-- Deal with Bad PageId
RAISERROR('Bad PageID',16,1)
RETURN 1
END
DELETE
FROM
Pages
WHERE
PageID = @PageID
IF @MaxPageNumber <> @PageNumber
BEGIN
UPDATE
Pages
SET
PageNumber = @PageNumber
WHERE
PageNumber = @MaxPageNumber
END
RETURN 0
|
|
|
|
|
i want to have a oledb connection in my module so that all the forms in my project can use it
how to build it
|
|
|
|
|
This is not a good approach. First of all, you would have to write extra code to make sure the connection is closed before the application terminates. Secondly, you cannot create the connection WithEvents so you cannot trap errors such as the connection being lost.
You should use a class instead. That way you can trap events and most important, make sure to close the connection by addding one line of code in the class destructor.
Robert
|
|
|
|
|
Happy Saint Patrick's Day everyone. I'm stuck here at work, (wish i was watching basketball) but on to my problem. I need to run a query that will return a list of applications. I think I need to do a left join(?) as not all apps will have a person listed. I still need to display the rest of the info though, even if there isn't a person for it. Here is an example what I have so far:
(BTW: I'm using oracle 8, and sql is not my specialty "obviously")
Select d.application_name,
f.first_name,
f.last_name,
d.servlet_context,
d.after_hours_contact,
d.context_description,
d.database_access,
d.test_link,
d.notes,
e.description,
f.user_id,
d.app_id,
a.team_name
from
web_team a,
web_developers b,
web_app_resp_dev c,
web_application_info d,
web_app_resources e,
user_profile f,
web_team_lead g
where
d.team_id = a.team_id and
d.app_id = c.app_id and
a.team_id = 1
and
f.user_id = c.dev_id and
(f.user_id = b.dev_id or
f.user_id = g.lead_id) and
d.app_id = e.app_id
order by d.application_name, e.description, f.last_name,
f.first_name
Brian Van Beek
|
|
|
|
|
Ooops...I forgot to add a little more info, i need all apps in table "d" even if they don't have a corresponding person in table "c"
Brian Van Beek
|
|
|
|
|
change d.app_id = c.app_id and
to d.app_id = c.app_id (+) and
|
|
|
|
|
Hi..,
Does any body have know about querys of take backup and restore of db with paraments like user name, password, connection?
Does any body no how export and import data to Access or any other db format with the same paraments as above?
Please help i am using C#.Net in programming.
|
|
|
|
|
WaqasButt wrote:
Does any body have know about querys of take backup and restore of db with paraments like user name, password, connection?
I am not entirely sure I understand your query - but I'll do my best.
I am guessing that you want to backup and restore from a C# application and you need to know what SqlConnection you need in order to perform that action.
According to the SQL Server books on-line to take a backup you need be a member of the sysadmin fixed server role or the db_owner and db_backupoperator fixed database roles for the database you are backing up.
Therefore the username that you use in the connection must be a user that is part of one of the above roles. For example "sa" is a member of the sysadmin role
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
hi ,
I am new ASP.NET Programmer and I am trying to access the data in the oracle database I wanted to access using Managed Provider can any one of you suggest what are the classes for this.IS their any other way to access the data from oracle database.
Thanking you.
Satish
|
|
|
|
|
How do you do insert on identity column with DTS?
Function Main()
DTSDestination("UIN") = ???IDENTITY???
DTSDestination("address") = DTSSource("Address")
Main = DTSTransformStat_OK
End Function
Thanks in advance!
Norman Fung
|
|
|
|
|
Hi, I need to do a lookup in my DTS package and the lookup will most probably return more than one value:
Dim names()<br />
names = DTSLookups("name_matching").Execute(DTSSource("param1"))
The DTS Lookup "name_matching" is as follows:
SELECT supplier_no<br />
FROM supplier_table<br />
WHERE (supplier_name LIKE ? + '%')
Is it possible for me to do this? Returning multiple values from a DTS lookup? I know lookups can return value consisting of multiple columns (http://www.sqljunkies.com/How%20To/C093CD3A-8695-4BD7-95C5-1B14E7C4D303.scuk[^]), but multiple rows... I'm not so sure.
Norman Fung
|
|
|
|
|
Insert data from a .txt file into a table, using the osql tool?
Assume I have table TableA with 3 columns named Column1, Column2 and Column3, and I have a text file 1.txt with the values -seperated by tabs.
Thanks,
Yaakov
|
|
|
|
|
Why do you want to use osql. You can import directly through Import Data with the Data Source your text file.
<italic>Work hard, Work effectively and a bit of luck is the key to success.
|
|
|
|
|
I dont have the Import Data tool.
I use Sql Express 2005.
|
|
|
|
|
I can't guarantee all of this code because I pulled it out of one of my programs and then edited it down to what I thought you could use, but I did not test it.
Private Sub loadDatagrid()
boolBeenToGatagrid = True
AddRecords()
strImportfolder = aPath
'this is the folder in which the file resides
strFilename = "selectdata.csv"
'this is the csv file to be imported
strConnstr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + strImportfolder + ";Extensions=asc,csv,tab,txt;Persist Security Info=False;"
Conn = New System.Data.Odbc.OdbcConnection(strConnstr)
da = New System.data.Odbc.OdbcDataAdapter("select * from [" + strFilename + "]", Conn)
Try
ds2.Clear()
da.Fill(ds2, "TableName")
Catch esc As Exception
System.Windows.Forms.MessageBox.Show(esc.Message, "")
End Try
With DataGrid1
.SetDataBinding(ds2, "TableName")
' Set DataGrid Background Color
.GridLineColor = System.Drawing.Color.Black
.BackgroundColor = System.Drawing.Color.Lavender
' Set DataGrid Caption Background Color
.CaptionBackColor = System.Drawing.Color.SlateBlue
' Set DataGrid Caption Foreground Color
.CaptionForeColor = System.Drawing.Color.White 'LemonChiffon
' Set DataGrid Parent Rows Background Color
.ParentRowsBackColor = System.Drawing.Color.Lavender
' Set DataGrid Parent Rows Foreground Color
.ParentRowsForeColor = System.Drawing.Color.SlateBlue
' Set DataGrid Caption Text
.CaptionText = "Study List"
' Clear DataGrid Table Styles
.TableStyles.Clear()
End With
' Set data grid Table Style
Dim tblCrrncMngr As System.Windows.Forms.CurrencyManager = CType(BindingContext(ds2.Tables.Item("TableName")), System.Windows.Forms.CurrencyManager)
Dim TblStyle As New System.Windows.Forms.DataGridTableStyle
With TblStyle
.MappingName = "TableName"
''''''.MappingName = "Studies"
.BackColor = System.Drawing.Color.White
.ForeColor = System.Drawing.Color.Black 'DarkSlateBlue
.GridLineColor = System.Drawing.Color.Black 'MediumSlateBlue
.HeaderBackColor = System.Drawing.Color.Lavender
.HeaderForeColor = System.Drawing.Color.DarkBlue 'MediumSlateBlue
.AlternatingBackColor = System.Drawing.Color.LightGray
.RowHeaderWidth = 10
' Set column styles
With .GridColumnStyles
' Set datagrid ColumnStyle for ID field
.Add(New DataGridPictureColumn)
With .Item(0)
.MappingName = "Wav"
.HeaderText = ""
.Width = 20
.NullText = String.Empty
End With
End With
End With
' Add TableStyle
DataGrid1.TableStyles.Add(TblStyle)
Conn.Close()
ds2.Dispose()
''''''data_table.Dispose()
da.Dispose()
end sub
There is another way to do this without using the QSQL.
|
|
|
|
|
Look into the BCP command line utility.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Hi,
I successfully exported one table from SQL server to an access file using
INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','DataSource="C:\TEST\test.mdb"')..............
But when I tried to export to a access file located on a different machine where I (Everyone)am having full rights it is giving following error:
Server: Msg 7399, Level 16, State 1, Line 1<br />
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. <br />
[OLE/DB provider returned message: Could not find installable ISAM.]<br />
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Any one please help me to sort out this issue.
Thanks in advance
Jay Krishna
|
|
|
|
|
Can anyone tell me How to determine whether MSDE or SQLSERVER edition is installed on my machine.
I want to know if this can be determined from Registry values??
Please In my application I need to determine this from registery only..
Pls help me??
RuchirDhar Dwivedi
Software Engineer
Windowmaker Software Pvt.Ltd.
Baroda, India.
|
|
|
|
|
SUBSTRING(CAST(Round(100.00 * (a.attempts - Sum(a.accepts)) - Round(100.00 * (b.attempts - Sum(b.accepts)) / Round(100.00 * (a.attempts - Sum(a.accepts AS CHAR),1,4) AS SUMMARY
The above is not complete. Could someone help?
Thanks
|
|
|
|
|
|
Hmmm
okay
4 colums with int values
I want a percent difference
A.Attempts A.Accepts B.Attempts B.Accepts
10000 9000 6500 4500
10000 - 9000 = 1000
Then
6500 - 4500 = 2000
Then difference and yes it may be negative
1000 - 2000 = -1000
Then
10000 - 9000 = 1000 / -1000 = -1
SUBSTRING(CAST(Round(100.00 * (a.attempts - Sum(a.accepts)) - Round(100.00 * (b.attempts - Sum(b.accepts)) / Round(100.00 * (a.attempts - Sum(a.accepts AS CHAR),1,4) AS SUMMARY
|
|
|
|
|
I don't see where your SQL matches the example you've given. Where is the SUM() coming from, what are you doing with the SUBSTRING.
From the example you gave this would appear to be the correct code:
SELECT cast((A.Attempts-A.Accepts) as float) / cast(((A.Attempts-A.Accepts) - (B.Attempts-B.Accepts)) as float)
FROM ...
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Sorry but what I was attempting to do with the substring function is return 4 characters in lentgh .
The code you provided works and thank you
|
|
|
|
|
Hi,
I am facing a problem while inserting a datarow with data in to a new dataTable.
can anybody plz help me how to add a row with data to a new data table.
regards,
Vinu
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
