|
Sorry, I don't understand what you want.
To update a row in a table you can use
UPDATE MyTable SET Qty = 70 WHERE saleno = 5
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi Balu,
Here's your reply.
If you have a proper sequence with no missing links use the following:
Select a.saleno,b.saleno, coalesce(a.qty,0)+coalesce(b.qty,0) as Qty
from tbl_Sale a left join tbl_sale b
ON a.saleno=b.saleno+1
Else use the following:-
Select a.saleno,coalesce(a.qty,0)+COALESCE((Select b.Qty from tbl_Sale b where b.saleno=
(Select max(c.saleno) from tbl_sale c where c.saleno
|
|
|
|
|
Oh! I see what he was getting at now. I really do wish people would say what they want rather than have everyone guess what they want through some cryptic IQ test what-is-the-next-number-in-the-sequence style question.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
-- modified at 5:22 Wednesday 19th April, 2006
|
|
|
|
|
|
how to lock an individual table in sql server database ?
krishna
|
|
|
|
|
Is there any way to get the existing Oracle service names(Stored in TNSNAMES.ORA) using C#.
|
|
|
|
|
What is TNSNAMES.ORA? A table, a file or something else?
phi
phimix.com
|
|
|
|
|
TNSNAMES.ORA is An Oracle Network Configuration File.The following is a sample file:
<br />
# TNSNAMES.ORA Network Configuration File: D:\oracle\ora81\network\admin\tnsnames.ora<br />
# Generated by Oracle configuration tools.<br />
<br />
CLCX =<br />
(DESCRIPTION =<br />
(ADDRESS_LIST =<br />
(ADDRESS = (PROTOCOL = TCP)(HOST = 200.22.10.6)(PORT = 1521))<br />
)<br />
(CONNECT_DATA =<br />
(SID = ORCL) (SERVER = DEDICATED)<br />
)<br />
)<br />
|
|
|
|
|
Then you should at least be able to use the System.IO functions like ReadLine(). I don't know if there are any methods specifically for working with Oracle files, but it's quite easy to implement some file reading with System.IO.
phi
phimix.com
|
|
|
|
|
(new replyers please refer previous msg (after signature))
thanks for your help. but we need more information.
in our project we have one table say t1.in that columns are c1, c2 ,c3,c4 .outwhich c4 is a column such that it is another table say nestedtabl1.in that also nc1,nc2,nc3 likewise columns.where nc3 is varrying array.so we are trying to access data of these tables through ado.net but but it is giving error.we are using oledb provider
does it useful to access nested table of oracle.is it important to use "oracle provider" only?
and also we are using disconnected approach then how to read the data of tables ,and bring it into dataset.
please help us as early as possible.
kam_ps
---------------------------------
new replyers please refer following----------
In a database management project ,we have used Oracle at back-end and VB.Net at front-end.
In Oracle ,we used object-oriented features and created nested tables.Now we want to accesss those nested tables using ADO.Net .We are in search of answers of following questions:
1.Does Dataset class of ADO.Net support nested tables ?
2.If yes,can XML be used to specify schema of nested dataset ?
3.If not,how to manipulate nested tables of Oracle in ADO.Net?
As a temporary solution , we are retrieving main table and child table in two different tables;but this mechanism of database handling is affecting preformance of project badly.So we are in need of some other efficient solution.We will be thankful for your response if you will be able to help us to get out of the problem .Please reply as early as possible.
kam_ps
-- modified at 16:21 Tuesday 18th April, 2006
|
|
|
|
|
there is nothing like nested table in ado.net. But, the solution is there where the relational features of dataset comes in picture.
fetch both the tables separately and add a foreign key relation between them. Then using methods like getchildrows, etc, you can get ur prob solved.
Best Regards,
Darpan Gogia
|
|
|
|
|
hi
i went create new data base from my computer to server
when i creat in local is ok
this my code to create data
Private Sub ButCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButCreate.Click
ButCreate.Enabled = False
btnExit.Enabled = False
Dim dmoServer As New SQLDMO.SQLServer
Dim filSqlScript As IO.StreamReader
Dim strPathToFile As String
Dim I As Integer
Dim StrDataScript0 As String
Dim StrDataScript1 As String
Dim StrDataScript2 As String
Dim strDrive As String
If TxtDBName.Text <> "" Then
If txtDBChar.Text <> "" Then
'If IsNumeric(txtDBChar.Text) = False Then
dmoServer.LoginSecure = True
Try
dmoServer.Start(True, MainMod.serverName)
Try
dmoServer.Connect(MainMod.serverName)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch ex As System.Runtime.InteropServices.COMException
Try
dmoServer.Connect(MainMod.serverName)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch ex As System.UnauthorizedAccessException
Dim objDbLogin As New frmDbLogin
If objDbLogin.ShowDialog(Me) = DialogResult.OK Then
Try
dmoServer.Start(True, MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch exc As System.Runtime.InteropServices.COMException
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exce As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch exce As System.UnauthorizedAccessException
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
End Try
End If
End Try
filSqlScript = IO.File.OpenText(Application.StartupPath & "\DataScript.sql")
StrDataScript0 = Replace(filSqlScript.ReadToEnd, "Amazon", TxtDBName.Text)
strDrive = dmoServer.Properties.Application.FullName.Substring(0, 3)
StrDataScript1 = Replace(StrDataScript0, "C:\", strDrive)
StrDataScript2 = Replace(StrDataScript1, "rplc", txtDBChar.Text)
'Try
dmoServer.ExecuteImmediate(StrDataScript2)
filSqlScript.Close()
22222222222
|
|
|
|
|
hi
i went create new data base from my computer to server
when i creat in local is ok
this my code to create data
Private Sub ButCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButCreate.Click
ButCreate.Enabled = False
btnExit.Enabled = False
Dim dmoServer As New SQLDMO.SQLServer
Dim filSqlScript As IO.StreamReader
Dim strPathToFile As String
Dim I As Integer
Dim StrDataScript0 As String
Dim StrDataScript1 As String
Dim StrDataScript2 As String
Dim strDrive As String
If TxtDBName.Text <> "" Then
If txtDBChar.Text <> "" Then
'If IsNumeric(txtDBChar.Text) = False Then
dmoServer.LoginSecure = True
Try
dmoServer.Start(True, MainMod.serverName)
Try
dmoServer.Connect(MainMod.serverName)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch ex As System.Runtime.InteropServices.COMException
Try
dmoServer.Connect(MainMod.serverName)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch ex As System.UnauthorizedAccessException
Dim objDbLogin As New frmDbLogin
If objDbLogin.ShowDialog(Me) = DialogResult.OK Then
Try
dmoServer.Start(True, MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch exc As System.Runtime.InteropServices.COMException
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exce As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch exce As System.UnauthorizedAccessException
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
End Try
End If
End Try
PrgBarCreate.Value = 10
Label3.Text = PrgBarCreate.Value & "%"
filSqlScript = IO.File.OpenText(Application.StartupPath & "\DataScript.sql")
PrgBarCreate.Value = 20
Label3.Text = PrgBarCreate.Value & "%"
StrDataScript0 = Replace(filSqlScript.ReadToEnd, "Amazon", TxtDBName.Text)
strDrive = dmoServer.Properties.Application.FullName.Substring(0, 3)
StrDataScript1 = Replace(StrDataScript0, "C:\", strDrive)
StrDataScript2 = Replace(StrDataScript1, "rplc", txtDBChar.Text)
PrgBarCreate.Value = 30
Label3.Text = PrgBarCreate.Value & "%"
'Try
dmoServer.ExecuteImmediate(StrDataScript2)
PrgBarCreate.Value = 40
Label3.Text = PrgBarCreate.Value & "%"
filSqlScript.Close()
PrgBarCreate.Value = 50
Label3.Text = PrgBarCreate.Value & "%"
For I = 51 To 100
Thread.Sleep(100)
PrgBarCreate.Value = I
Label3.Text = PrgBarCreate.Value & "%"
Next
22222222222
|
|
|
|
|
And the problem/question is.....?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi , I have a problem ! I have interviewers working on cases and I need to calculate the following:
Calculation per case :
1. wait time between two cases for each interviewer ( I am clueless on this one )
Summary Calculation by group:
2. Average wait time for each interviewer
3. Total cases worked by each interviewer
My table has this information : ID,CaseId,CaseStarttime,CaseEndTime,InterviewerId
Primary Key is ID, only that is Unique in table.
For each InterviewerId I want to calculate the wait time.
Wait time= CaseEndTime(of last case) - CaseStartTime(of next case)
Minoo S
|
|
|
|
|
Minoo,
The answer is:
1) Convert (CaseEndTime-CaseStartTime) to get the seconds/minutes as integer.
2) Then you can simply get the average(minutes/seconds) by grouping the interviewers.
If you still need further help I will send the query, do let me know.
Best Regards,
Darpan Gogia
|
|
|
|
|
Hi Darpan,
I got the others thank you. Just the wait time I need more information on.
So if I group cases by interviewid, how do I calculate wait time, if wait time is (CaseEndTime of first case/first row in group by Interviewer)-(CaseStarttime of next case/second row in group by Interviewer)
If you could give me the full query that will certainly help.
thx
Minoo S
|
|
|
|
|
Help anyone ?? I still need to calculate the wait time.
Minoo S
|
|
|
|
|
Hi
how to make one varable local for some store procedure pleas give me example??
good bye
best regards
|
|
|
|
|
To make a local variable inside a stored procedure use DECLARE , e.g.
DECLARE @myLocalVar INT
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
|
Hi!!
How do return the error code and description from a stored procedure to the .cs code? Lets say an INSERT statement was not successful then I want to display the error code, and error description to the user on the.aspx page.
Hope some one can help me.
Regards,
ma se
|
|
|
|
|
The SqlException that is thrown will contain this information. The Message will, naturally, contain the error message and the Number will contain the number of the error. However, it is possible for the SQL Server data provider to return multiple errors. In which case you can use the Errors property to get a collection of SqlError objects.
You can find more information on MSDN: SqlException[^]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
In T-SQL (e.g. in a trigger) , if you use RAISERROR with an error severity of 16 or higher it gets returned to the client application.
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
Aloha to all,
I have thrown together some code that generates a file with a lot of data (something like 200,000 rows). I would like to load all this data in a SQL Server table, but my attempt to execute BCP from C# doesn't want to behave.
I do as follows (more of less stealing everything from http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/08/19/22566.aspx ):
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.EnableRaisingEvents = false;
proc.StartInfo = new System.Diagnostics.ProcessStartInfo();
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.FileName = "bcp";
proc.StartInfo.Arguments = @"DATABASE_NAME.dbo.TABLE_NAME in c:\inetpub\wwwroot\project\bcpData.txt -c -Uusername -Ppassword -t',' -Sservername";
proc.EnableRaisingEvents = true;
proc.Start();
string s1 = proc.StandardOutput.ReadLine();
string s2 = proc.StandardOutput.ReadLine();
All I get from this code is:
s1 = "SQLState = 28000, NativeError = 18456";
s2 = "Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'username'."
I have off course checked the username/password a thousand times and it is correct. If I copy my Arguments string to the command prompt, I can execute BCP without any problem
Thank you and roger over, Mads
phi
phimix.com
|
|
|
|
|