Oracle date problem

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Ado
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - sqlcommand stored procedure returning date Parameter Hi all, Trying to just return a date, keep getting vb.net error on "Dim DataReader As SqlDataReader = EusCmd.ExecuteReader()" ***VB.net Private Function getLastDate() As DateTime FunctionMainConnection() Dim EusCmd As New SqlCommand("getlastdate", MainSQLConn) MainSQLConn.Open() Dim DataReader As SqlDataReader = EusCmd.ExecuteReader() EusCmd.CommandType = CommandType.StoredProcedure EusCmd.CommandTimeout = 20 EusCmd.Connection = MainSQLConn EusCmd.Parameters.Add("@out", SqlDbType.DateTime) ' Output Parameter EusCmd.Parameters("@out").Direction = ParameterDirection.Output While DataReader.Read getLastDate = DataReader.GetString(0) End While DataReader.Close() MainSQLConn.Close() End Function **********SQL stored procedure CREATE PROCEDURE getlastdate @lastd datetime out AS set @lastd = (select CONVERT( CHAR(12), max(incrementDate),101) from EusBatchIncrement) GO Thanks for your help Gerry Tag: Oracle date problem Tag: 91487
  - 2
    - Update access file from datagrid Look my prog. I have combobox where is have all tables names of access file. The user select one table and the content is show in datagrid. The user can add, erase or modify some record or row. Until now is ok but went I want to update access file, VB send me error. Some body can help me Dim myCnn As New OleDb.OleDbConnection Dim objDataAdapteur As OleDbDataAdapter Dim objDataSet As New Data.DataSet Dim objDataTable As DataTable ---------------------------------------------------------------------------- --------------------------------------------------------------- Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged myCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AccessFile & ";" myCnn.Open() Dim strSQL As String = "SELECT * FROM [" & TableName & "]" objDataAdapteur = New OleDb.OleDbDataAdapter(strSQL, myCnn) Dim objCB As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(objDataAdapteur) objDataAdapteur.Fill(objDataSet, TableName) objDataTable = objDataSet.Tables(TableName) DataGrid1.SetDataBinding(objDataTable, "") End Sub ---------------------------------------------------------------------------- -------------------------------------------------------------- 'Click on button to copy modify on datagrid in access file Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click objDataAdapteur.Update(objDataSet, TableName) myCnn.Close() End Sub Tag: Oracle date problem Tag: 91485
  - 3
    - Loaddatarow adds a row even when pk exists in the dataset Here is the code. I know I am doing something wrong, but for the life of me I can't see it. I keeps adding a new row to the table rather than updating the current row. This is being called from a web form. I am really stuck and I need some guidance. Private Sub updateuser() Dim username As String Dim xmdr(5) As Object On Error GoTo handler xmdr(0) = recordid xmdr(1) = Trim(Me.email.Text) xmdr(2) = Trim(Me.loginid.Text) If Me.passwrd.Text <> Nothing Then xmdr(3) = FormsAuthentication.HashPasswordForStoringInConfigFile(Trim(Me.passwrd.Text), "sha1") Else Me.passwrd.Text = DataGrid1.SelectedItem.Cells(3).Text xmdr(3) = FormsAuthentication.HashPasswordForStoringInConfigFile(Trim(Me.passwrd.Text), "sha1") End If xmdr(4) = Me.company.Text If CheckBox1.Checked = True Then xmdr(5) = "A" Else xmdr(5) = "S" End If Dim userrow As DataRow DataSet11.User_table.BeginLoadData() DataSet11.User_table.LoadDataRow(xmdr, False) DataSet11.User_table.EndLoadData() 'DataSet11.User_table.AcceptChanges() Me.OleDbDataAdapter1.Update(DataSet11) Me.DataSet11.Clear() Me.OleDbDataAdapter1.Fill(DataSet11) DataGrid1.DataBind() Exit Sub handler: Dim errorval As String errorval = Err.Description 'MsgBox(errorval, MsgBoxStyle.Information, "Error") Resume Next End Sub --- Posted using Wimdows.net NntpNews Component - Post Made from http://www.DotNetJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching. Tag: Oracle date problem Tag: 91484
  - 4
    - Loaddatarow adds a row even when pk exists in the dataset Here is the code. I know I am doing something wrong, but for the life of me I can't see it. I keeps adding a new row to the table rather than updating the current row. This is being called from a web form. I am really stuck and I need some guidance. Private Sub updateuser() Dim username As String Dim xmdr(5) As Object On Error GoTo handler xmdr(0) = recordid xmdr(1) = Trim(Me.email.Text) xmdr(2) = Trim(Me.loginid.Text) If Me.passwrd.Text <> Nothing Then xmdr(3) = FormsAuthentication.HashPasswordForStoringInConfigFile(Trim(Me.passwrd.Text), "sha1") Else Me.passwrd.Text = DataGrid1.SelectedItem.Cells(3).Text xmdr(3) = FormsAuthentication.HashPasswordForStoringInConfigFile(Trim(Me.passwrd.Text), "sha1") End If xmdr(4) = Me.company.Text If CheckBox1.Checked = True Then xmdr(5) = "A" Else xmdr(5) = "S" End If Dim userrow As DataRow DataSet11.User_table.BeginLoadData() DataSet11.User_table.LoadDataRow(xmdr, False) DataSet11.User_table.EndLoadData() 'DataSet11.User_table.AcceptChanges() Me.OleDbDataAdapter1.Update(DataSet11) Me.DataSet11.Clear() Me.OleDbDataAdapter1.Fill(DataSet11) DataGrid1.DataBind() Exit Sub handler: Dim errorval As String errorval = Err.Description 'MsgBox(errorval, MsgBoxStyle.Information, "Error") Resume Next End Sub --- Posted using Wimdows.net NntpNews Component - Post Made from http://www.DotNetJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching. Tag: Oracle date problem Tag: 91483
  - 5
    - CurrencyManager_ItemChanged Question. Hi There, I have a windows form that uses binding through a currency manager. A dataset fills tan Adapter and everything thing is good in life. A list box gets populated with a list of names and that works good, whenever I click on a item on the list box the data on the side updates. This is the problem that I'm getting though, When I change some data on the right, let's say the lastname or first name that does not get reflected on the listbox, for that, I have included this code on the currencymanager_itemchanged event. Private Sub mCurrencyManager_ItemChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.ItemChangedEventArgs) Handles mCurrencyManager.ItemChanged Dim combined_name As String Dim data_row As DataRow combined_name = txtLastName.Text & ", " & txtFirstName.Text data_row = mCurrencyManager.Current.Row If data_row.Item("FullName") & "" <> combined_name Then data_row.Item("FullName") = combined_name End If End Sub The thing is, this code should fire up whenever I change and item on the currencymanager right? but It does not, it ignores it totally. Can anybody tell me what Am I doing wrong here? give me some hints on how I can accomplish this? Thanks in advance. Manny Tag: Oracle date problem Tag: 91480
  - 6
    - Data Access Application Block - Using UPDATEDATASET and Returning an Identity? Hi, I am tring to use the Microsoft Data Access Application Block v2 to update the database. However, i need the stored procedure to return the Identity value to the client application so it can know the ID of the inserted record. Example: Stored Procedure: CREATE PROC Person_Insert @LASTNAME VARCHAR(30), @FIRSTNAME VARCHAR(30), @PersonID BIGINT OUTPUT as INSERT INTO PERSON (LASTNAME, FIRSTNAME) VALUES (@LASTNAME, @FIRSTNAME) SET @PersonID = (SELECT SCOPE_IDENTITY()) C# Code: public void SavePerson(DataSet ds) { SqlConnection CurrentConnection = new SqlConnection connString); CurrentConnection.Open(); SqlCommand insertCommand = SqlHelper.CreateCommand CurrentConnection, "Person_Insert", "LASTNAME", "FIRSTNAME"); SqlCommand updateCommand = SqlHelper.CreateCommand(CurrentConnection, "Person_Update", "PersonID", "LASTNAME", "FIRSTNAME"); SqlCommand deleteCommand = SqlHelper.CreateCommand(CurrentConnection, "Person_Delete", "PersonID"); SqlParameter outParam = new SqlParameter(); outParam = updateCommand.Parameters.Add("@PersonID",SqlDbType.BigInt); outParam.Direction = ParameterDirection.Output; SqlHelper.UpdateDataset(insertCommand, deleteCommand, updateCommand, ds, "table"); Console.WriteLine("UPDATE COMMAND OUTPUT PARAM:"); Console.WriteLine(outParam.Value.ToString()); } Console Output: UPDATE COMMAND OUTPUT PARAM: The program '[2120] SmartClient.exe' has exited with code 0 (0x0). This isn't working though. Any thoughts??? {Thanks in advance} Tag: Oracle date problem Tag: 91471
  - 7
    - DataRelation - update parent and child at same time? Hello ADO.NET gurus :) I am fairly new to database work and am hoping someone can help me with a DataRelation question. Anyway, I am working with an orders database. I have a DataTable of orders, and another DataTable of order details, and need to get new orders into my database. I was able to set up a DataRelation between the two tables, however I have an unfortunate limitation: I need to add the new orders and their details in the same database transaction. (This is due to the limitations of my database, which is proprietary and not relational.) Therefore I can't follow David Sceppa's recommendation of first adding the order and then updating the line items. I think the best solution would be to pull in the details table when I update the master (or vice versa), but I'm not sure if this is possible. Is it? The other alternative I see is to maintain a cache, have the details table update to this cache, and then pull in the cache when you update the master table. However, I'd rather avoid this for a variety of reasons. Can anyone offer some suggestions? Thank you! Mike Milner Tag: Oracle date problem Tag: 91468
  - 8
    - IDataParameter.ParameterName Hi all, Excerpt from the docs: "The ParameterName is specified in the form @paramname". What's the so called "form" supposed to be? It's a string starting with '@', what else? radi Tag: Oracle date problem Tag: 91459
  - 9
    - ADO.NET C#: varchar() fields are padded with spaces on some records Hi, I'm reading a table with varchar() strings. For some reason this fields is sometimes padded up to the max length with spaces. To be sure that the length is correct in the MSSQL 2000 database I added a len(MyField) in the query. SELECT MyField, LEN(MyField) FieldLen FROM MyTable If I watch the contents of SqlDataReader on a 'good' record I see: _comBuf[0] "Estna" _comBuf[1] "5" In case the field is padded I see: _comBuf[0] "Kos " _comBuf[1] "3" So the length that SQL returns is 3, but in ADO the length is 20 (which is in fact the maximum length). I create a workaround to trim the field in the code, but it seems incorrect behaviour. Any comments? Regards, Rene Tag: Oracle date problem Tag: 91458
  - 10
    - ConnectionState issue I'm seeing something weird and I must just be overlooking something. I have a connection and I open it(connection is named 'cn'). I close it in my execution block but I have it wrapped in a Try/Catch/Finally and in finally I check if the connectionState <> ConnectionState.Closed - if it's not then I call .Close. However, even though it successfully closes and the debugger tells me it's closed in the If Statement, it still runs to Close. This isn't a big deal in that it doesn't cause any problems but I've verified that the connection is closed and I'm wondering what's up with that? Finally Debug.Assert(cn.State = ConnectionState.Closed) 'The assertion doesn't fail indicating that it's in fact close If cn.State <> ConnectionState.Closed Then cn.Close() If I put my cursor here (On cn.State) it indicates closed as well, but it always calls Close anyway. Any ideas? End Try -- W.G. Ryan, eMVP Have an opinion on the effectiveness of Microsoft Embedded newsgroups? Let Microsoft know! https://www.windowsembeddedeval.com/community/newsgroups Tag: Oracle date problem Tag: 91457
  - 11
    - Modificar filas añadidas a un DataSet Hola a todos: Tengo un DataSet al cual se le han añadido unos registros. Antes de actualizar la BD accedo a las filas añadidas y modifico un campo a partir de unos cálculos y después hago el AccepChanges del dataset temporal, tras el cual hago los INSERT con un ExecuteNonQuery de esas filas. With ds If .HasChanges Then Dim tempDS As DataSet = .GetChanges(DataRowState.Added) If Not IsNothing(tempDS) Then Dim iContador As Integer = 0 For Each row as DataRow In tempDS.Tables(0).Rows tempDS.Tables(0).Rows(iContador)("Calculo") =iContador*........ iContador += 1 Next tempDS.AcceptChanges() For Each row In tempDS.Tables(0).Rows InsertaDatos(row) Next El caso es que funciona correctamente pero no se me actualizan los datos del campo "Calculo" en el DataSet ds, con lo que tengo que recargar el Dataset cada vez. ¿Como puedo recuperar la alguna referencia a la fila en el dataset ds desde la fila añadida en el dataset temporal de filas añadidas para poder actualizar el campo "Calculo"? Gracias por anticipado. Tag: Oracle date problem Tag: 91454
  - 12
    - Apply DataView.Sort once, for sorting snapshot, data update should not resort Hi. Is there anyone who knows how I can use a datatable, dataview (and datagrid) and have the sorting that I specify, DataView.Sort="col dir", to only be evaluated when set (once) and not have the view resorted constantly. Currently I'm drowned with Moved events. If I try to do this smart stuff dv.Sort = "colA ASC"; dv.Sort = ""; the view immediately goes back to some default sort. (primary key in table) The datatable holds dynamic data that is update continously but I don't want my grid rows to fly around.. sigh Regards & TIA /Stefan Tag: Oracle date problem Tag: 91452
  - 13
    - Using DataView or Select expression on a DataTable to filter distinct records From the brief research that I have done, nothing is mentioned about being able to filter out distinct records from a DataTable using DataView or or using the select method like: someDataTable.Select() = someExpression. Because I need my DataTable to contain all the records, but I only want to show distinct records, I have a for loop and some register variables that test whether current record is distinct or not. This method works the way I want to, but is there a better way to do it using a DataView or the select method as mentioned above? Thank You, John Tag: Oracle date problem Tag: 91447
  - 14
    - Problem with binding data retrieve from oracle DB to textbox Did anyone know how to bind the data retrieve from Oracle Database to textbox control. I try with following code but it display this error message: "An unhandled exception of type 'System.ArgumentException' occurred in system.windows.forms.dll Additional information: Cannot bind to property or column dName on DataSource." This error indicate this code"txtDept.DataBindings.Add("Text", ds, "dName");" I call these function when form load. private void fillDataSet() { try { staff = new Staff(); da = staff.GetStaffDetail(); ds = new DataSet(); da.Fill(ds,"Staff"); dt = new DataTable("Staff"); dv = new DataView(dt); dgStaff.DataSource = ds.Tables["Staff"].DefaultView; cm = (CurrencyManager)(BindingContext[dv]); ds = null; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } private void bindFields() { txtDept.DataBindings.Clear(); txtDeptId.DataBindings.Clear(); txtStaffId.DataBindings.Clear(); txtStaffName.DataBindings.Clear(); txtTel.DataBindings.Clear(); txtDept.DataBindings.Add("Text", ds, "dName"); txtDeptId.DataBindings.Add("Text", ds, "dId"); txtStaffId.DataBindings.Add("Text", ds, "sId"); txtStaffName.DataBindings.Add("Text", ds, "sName"); txtTel.DataBindings.Add("Text", ds, "sContact"); } This is a Staff class which responsible to retrieve data from oracle database. public class Staff { private string connStr = "Provider=MSDAORA;Data Source=HONG;User ID=scott;Password=tiger"; private System.Data.OleDb.OleDbConnection conn; private System.Data.OleDb.OleDbDataAdapter da; private System.Data.OleDb.OleDbCommand comm; private System.Data.OleDb.OleDbDataReader dr; private string strSQL; private int maxId; public OleDbDataAdapter GetStaffDetail() { strSQL = "SELECT s.sId, s.sName, s.dId, s.sContact, d.dName " + "FROM Staff s, Department d WHERE s.dId = d.dId"; conn = new OleDbConnection(connStr); conn.Open(); da = new OleDbDataAdapter(strSQL, conn); return da; } } Tag: Oracle date problem Tag: 91440
  - 15
    - PHP and ASP.NET go HEAD to HEAD PHP and ASP.NET Go Head-to-Head By Sean Hull http://otn.oracle.com/pub/articles/hull_asp.html SUMMARY at the BOTTOM Speed and efficiency. As I mentioned earlier, ASP.NET is a framework allowing you to use various programming languages. In addition, it is touted as having a great object-oriented model. All this is true, but it becomes a detriment as far as speed is concerned. For all that advantage, there is a lot more code to run through to execute the same ASP page than you have to execute in the PHP engine for an equivalent PHP page. PHP is the quick-and-dirty type of solution, the one to get the job done. And though a lot of robustness has been added to it since its 2.0 and 3.0 days, it still retains that core optimized high-speed approach. Speed is not the only consideration. Memory usage is also important. SECURITY COMPARISON ASP.NET officially requires that you use IIS. Unfortunately, IIS has a long history of vulnerabilities, which makes many administrators reluctant to deploy it to handle their web site. Whether these weaknesses are because of Microsoft's ineptness or because IIS is a real red flag to hackers is irrelevant: Those systems have a history of being hacked and compromised. PHP runs on Apache, too, which is fast and open source and has a good security track record. Also, as I mentioned, Apache runs on many platforms. So is PHP really faster than ASP.NET or is that for certain unoptimized pages? And are they comparing this against the DataGrid instead of the repeater control or even the fastest way using asp.net's inline code render block, <% %> comments? Tag: Oracle date problem Tag: 91438
  - 16
    - How to insert an "object" into a DataRow? Hi all, I have a problem adding an object or a structure (made with "struct" keyword) into a DataRow object?? How can I do so? The problem is that I can add it but when trying to read the content, the compiler tries to read the data as STRING?!??! I don't know why? Can anyone help me? I'd highly appreciate your help, Veszko ---------------------------------------------------------------------------- ----------------------------- Example ---------------------------------------------------------------------------- ----------------------------- public struct UserData { public string sSerial; public int iSUP_Id; public Image imgPhoto; } . . . DataTable dtTable = new DataTable("Users"); dtTable .Columns.Add("iID"); dtTable .Columns.Add("sName"); dtTable .Columns.Add("oData"); UserData oUserData = new UserData(); oUserData.sSerial = "something"; oUserData.iSUP_Id = -1; oUserData.imgPhoto = PictureBox1.Image; DataRow drRow = dtTable.NewRow(); drRow["iID"] = -1; drRow["sName"] = "some name"; drRow["oData"] = oUserData; // Here the data is not stored correctly, when watching it inside the debugger you can cleary see that only the name of the structure is stored as String! . . . UserData oCurrentUserData = (UserData) drRow["oData"]; // Here an error occurs!! ---------------------------------------------------------------------------- ----------------------------- Tag: Oracle date problem Tag: 91436
  - 17
    - Force Requery I have a dropdownmenu which is databound to a table via a dataadapter and dataset. A control on another part of the form updates the same table in the database. On postback the dropdown does not show the new record. I have tried the autopost back on true or false. I am assuming the dataset is remaining disconnected. Is there an easy way to get the dataadapter to requery itself. Tag: Oracle date problem Tag: 91432
  - 18
    - Insert problem !! Hi all I am having an insert problem with values of different data types. I have a form that has text boxes accepting information that goes into a table with String int & decimal data types. I have used the convert.toInt and convert.toDecimal but i am having trouble building the SQL statement Here is a sample of my code ***************************************************** Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click Dim strSQL As String Dim strCnn As String 'create a connection string to connect to the database strCnn = BuildCnnStr("MainPC", "SDSDatabase") 'create the connection Dim cn As New OleDb.OleDbConnection(strCnn) 'conection is open cn.Open() 'set up the sql string to insert the values collected from the Text Boxes strSQL = "INSERT into inletCone( DrawingID, SupplierID, Internal_Code, " & _ "Model_Number, Description, Diameter, Angle_Of_Intake," & _ "Mass, Flange_Dimensions, No_Of_Holes, Notes) " & _ "Values( Convert.ToInt32(txtDrawingID.Text) & _ Convert.ToInt32(txtSupplierID.Text)& _ Convert.ToInt32(txtInternalCode.Text)& _ " '" & txtModelNumber.Text & " ' " , & _ " '" & txtDescription.Text & "' ", & _ Convert.ToDecimal(txtDiameter.Text) & ", " & _ Convert.ToDecimal(txtIntake.Text) & , & _ Convert.ToDecimal(txtMass.Text) & " , " & _ Convert.ToDecimal(txtFlangeDimension.Text) & " , " & _ Convert.ToDecimal(txtNo_Of_Holes.Text) & " , " & _ " '" & rtbNotes.Text & " ')" 'create a command object Dim cmd As OleDb.OleDbCommand ' set the object as create command cmd = cn.CreateCommand() ' strSQL is the insert statement cmd.CommandText = strSQL cmd.Connection = cn cmd = New OleDb.OleDbCommand(strSQL, cn) cmd.ExecuteNonQuery() *********************************************************************** The text books i have used seem to just show all string values in thier Query statements. Am i approaching this the right way or is there a better approach Thanks in advance Greg Tag: Oracle date problem Tag: 91429
  - 19
    - DataGrid - Catching backspace key click before its processed. Hi, I am trying to catch the user clicking the backspace key, Keys.Back. I need to do this as I cant allow the datagrid to add a new blank row when the user clicks the backspace key whilst they are in the last row in the grid, if the row is also empty. Anyway I've derived my own custom control from the DataGrid and want to pre process the backspace key. However I've found that I'm not catching the backspace with any of these overrides: public override bool PreProcessMessage(ref Message msg) protected override bool ProcessKeyMessage(ref Message m) protected override bool ProcessKeyEventArgs(ref Message m) protected override bool ProcessCmdKey(ref Message msg, Keys keyData) protected override bool ProcessDialogKey(Keys keyData) protected override bool ProcessKeyPreview(ref System.Windows.Forms.Message msg) The problem is none of these methods is getting called when I click the backspace, it seems like the DataGrid is immediately processing it. I'm a little confused over this as I also needed to handle the Delete key, Keys.Delete. I managed to do this as the ProcessKeyPreview is raised when the user clicks the Delete key so I have no idea why it isnt raised for the backspace key. Anyone got any suggestions? Ta, Colin Jack Tag: Oracle date problem Tag: 91428
  - 20
    - Filtering GetChildRows I have an xml file which has defintions for "event" elements. Each event (currently) has 2 standard fields "id" and "name", and a number of custom fields, the number and descriptions of which may be different for each event. An example file is below <event> <id>1</id> <name>Event One</name> <fields> <custom1>Custom Value 1</custom1> <custom2>Custom Value 2</custom2> </fields> </event> <event> <id>2</id> <name>Event Two</name> <fields> <custom3>Custom Value 3</custom3> <custom4>Custom Value 4</custom4> <custom5>Custom Value 5</custom5> </fields> </event> I can load this file into a DataSet and look at the "event" table. Taking the first DataRow in that table (event one) I Call GetChildRows. However The resulting table not only has the "custom1" and "custom2" columns (with the correct values) but custom3, custom4, custom5 (with null values). My question is, is there any way of filtering this so the only columns returned are the ones specific for that event, eg custom1 and custom2. Thanks in advance Dan Tag: Oracle date problem Tag: 91425
  - 21
    - Dataset/Datareader and filter Hi, when we worked with recordsets in VB6 we had the possibility to filter our recordset. Does this option still exist in a dataset or a datareader? If not, how can we have the same effect ? Thanks. Tag: Oracle date problem Tag: 91424
  - 22
    - Oracle Probs with ADO.NET Hi, I'm using Oracle Client 8.1.7 with VB.NET DotNet 2003. I'm trying to connect to an Oracle DB using Microsoft OLE DB Provider for Oracle. I'm being able to create the Data Connection but when i try to create the OracleConnection and the OracleDataAdapter, i'm getting the folowing message: Unable to find an entry point named OCIEnvCreate in DLL oci.dll I checked the permission on the Oracle Home folder (c:\orant) but it is set to full control for everyone. I tried to add authenticated users and set their permissions for read and execute but it didnt work. Can you help me please? Thanks Tag: Oracle date problem Tag: 91421
  - 23
    - Performance question - Output parameters vs single row? I'm aiming this question at Bill Vaughn but anyone is welcome to jump in. I have seen several articles claiming that it is faster to return a set of output parameter values rather than a single row. You make a reference to this for ADO on Page 269 of ADO.NET and ADO Best Practices. I don't see any numbers to back this up and attempted to run a simple test of my own. My test just ran a loop to populate the fields on a form based on a single record from the Northwind Orders table. I ran the loop 100,000 times using two different stored procedures. The first one used output parameters, the second returned a single row into a datareader and I looped through it to get the column values. I ran the test a half dozen times for each, just to see if I was experiencing any variation between tests. The single row test completed in approx 22 seconds and the output parameters test in approx 27 seconds each time. This was not what I was expecting so I'm looking for any comments on these two approaches. Joel Reinford Data Management Solutions LLC CREATE PROCEDURE dbo.dp_OrderSingle ( @OrderID int = NULL , @CustomerID nchar(5) = NULL OUTPUT , @EmployeeID int = NULL OUTPUT , @OrderDate datetime = NULL OUTPUT , @RequiredDate datetime = NULL OUTPUT , @ShippedDate datetime = NULL OUTPUT , @ShipVia int = NULL OUTPUT , @Freight money = NULL OUTPUT , @ShipName nvarchar(40) = NULL OUTPUT , @ShipAddress nvarchar(60) = NULL OUTPUT , @ShipCity nvarchar(15) = NULL OUTPUT , @ShipRegion nvarchar(15) = NULL OUTPUT , @ShipPostalCode nvarchar(10) = NULL OUTPUT , @ShipCountry nvarchar(15) = NULL OUTPUT ) AS SET NOCOUNT ON SELECT @OrderID = o.OrderID , @CustomerID = o.CustomerID , @EmployeeID = o.EmployeeID , @OrderDate = o.OrderDate , @RequiredDate = o.RequiredDate , @ShippedDate = o.ShippedDate , @ShipVia = o.ShipVia , @Freight = o.Freight , @ShipName = o.ShipName , @ShipAddress = o.ShipAddress , @ShipCity = o.ShipCity , @ShipRegion = o.ShipRegion , @ShipPostalCode = o.ShipPostalCode , @ShipCountry = o.ShipCountry FROM Orders o WHERE o.OrderID = @OrderID CREATE PROCEDURE dbo.dp_OrderSingleRow ( @OrderID int ) AS SET NOCOUNT ON SELECT o.OrderID , o.CustomerID , o.EmployeeID , o.OrderDate , o.RequiredDate , o.ShippedDate , o.ShipVia , o.Freight , o.ShipName , o.ShipAddress , o.ShipCity , o.ShipRegion , o.ShipPostalCode , o.ShipCountry FROM Orders o WHERE o.OrderID = @OrderID Tag: Oracle date problem Tag: 91415
  - 24
    - CommandTimeOut Problem Hello, I have a table with about 1 million rows. I need to scroll them and use sqldatareader for that. The TimeOut property of command object i use to create the reader is set to 0. I use MSDE 2000 on my local computer. The problem is that although my timeout is set to 0 which assumes that it is infinite it still gives me TimeOut exception after retrieving approximately 20000 rows (different each time). The interesting thing is that reader.Read() works fine and returns true, but reader.getvalue(<anyindex>) crashes. This is obviously a bug because if i set my command's timeout to 65000 it works fine. Is there any bugfix? Or an explanation to this probelm? Tag: Oracle date problem Tag: 91406
  - 25
    - ADO.NET and SQL Server 2000 XML We are converting an existing VB/SOAP 3/SQL 2000 app to VB.NET/Web Services/Remoting/SQL 2000. Currently stored procedures return data using "FOR XML RAW" and we use the DOM (MSXML 4.0) to populate fields and cached data on the client. All parameters are sent to SQL 2000 as an XML string and stored procedures process this XML string using OPENXML. This application needs to be a Windows Forms app as the functionality can not be provided via Web Forms. The app runs across LAN/WAN with some users accessing data stored in a central database situated 4000km away. The main reason for using SQL 2000 XML in the current version was to try and reduce network traffic and passing XML via SOAP was easier than trying to pass ADO Recordsets. From a scalability and performance perspective, should we: 1. Keep using FOR XML/OPENXML via ExecuteXMLReader and Command objects and pass data as xml between the client/web service/remoting object/database, or 2. Should we fully parameterised stored procedures and stick to using Datasets/Datareaders and command objects and pass data as datasets between the client/web service/remoting object/database. We want to move away from using the DOM and purely use datasets so even if we keep using option 1, we will need to convert the XML to a dataset at some stage. I realise that this question is hard to answer without a full understanding of the architecture of the app, so any information would be appreciated. Tag: Oracle date problem Tag: 91405
- Next
  - 1
    - ADO.NET and SQL Server 2000 "FOR XML" We are converting an existing VB/SOAP 3/SQL 2000 app to VB.NET/Web Services/Remoting/SQL 2000. Currently stored procedures return data using "FOR XML RAW" and we use the DOM (MSXML 4.0) to populate fields and cached data on the client. All parameters are sent to SQL 2000 as an XML string and stored procedures process this XML string using OPENXML. This application needs to be a Windows Forms app as the functionality can not be provided via Web Forms. The app runs across LAN/WAN with some users accessing data stored in a central database situated 4000km away. The main reason for using SQL 2000 XML in the current version was to try and reduce network traffic and passing XML via SOAP was easier than trying to pass ADO Recordsets. From a scalability and performance perspective, should we: 1. Keep using FOR XML/OPENXML via ExecuteXMLReader and Command objects and pass data as xml between the client/web service/remoting object/database, or 2. Should we fully parameterised stored procedures and stick to using Datasets/Datareaders and command objects and pass data as datasets between the client/web service/remoting object/database. We want to move away from using the DOM and purely use datasets so even if we keep using option 1, we will need to convert the XML to a dataset at some stage. I realise that this question is hard to answer without a full understanding of the architecture of the app, so any information would be appreciated. Tag: Oracle date problem Tag: 91404
  - 2
    - remove related rows from junction.. I have a form showing records from parent table - "Provider" (databound controls) I have a checkedlistbox bound to a child table - "Site", via a junction table -"PersonSite". Items in the checkedlistbox are checked if records exist for current parent record in the junction table. I am having problems removing rows from the junction table if the user deletes the parent record. Could someone tell me how to do this? The following code does not work, "there is no row at position 15" is the error i guess because i have removed a row in the middle of the for loop? ds.Tables("PersonSite").DefaultView.RowFilter = ("Provider_GUID = '" & ds.Tables("Provider").Rows(thisCurrencyManager.Position).Item("Provider_Guid ").ToString & "'") ds.Tables("PersonSite").DefaultView.Sort = "PersonSiteID" Dim y As String For i As Integer = 0 To ds.Tables("PersonSite").DefaultView.Count - 1 Dim s As String = ds.Tables("PersonSite").DefaultView.Item(i).Item("PersonSiteID").ToString 'gives us the personsiteid of current record For x As Integer = 0 To ds.Tables("PersonSite").Rows.Count - 1 y = ds.Tables("PersonSite").Rows(x).Item("PersonSiteID").ToString If y = s Then ds.Tables("PersonSite").Rows.RemoveAt(x) End If Next Next Similarly the following code causes "cannot access deleted row through row number" error Dim y As String For i As Integer = 0 To ds.Tables("PersonSite").DefaultView.Count - 1 Dim s As String = ds.Tables("PersonSite").DefaultView.Item(i).Item("PersonSiteID").ToString 'gives us the personsiteid of current record y = ds.Tables("PersonSite").Rows.Find(s).Item("PersonSiteID").ToString For x As Integer = 0 To ds.Tables("PersonSite").Rows.Count - 1 If ds.Tables("PersonSite").Rows(x).Item("PersonsiteID").ToString = y Then ds.Tables("PersonSite").Rows(x).Delete() End If Next Next Any chance of a helping hand here? -- Tag: Oracle date problem Tag: 91403
  - 3
    - Expression Aggregate Question I am using a Winform. If I understand things right I can create a new field in one table of a dataset and get the aggreate value of a field in another table of the dataset that is related. To test this I have a form with the orders and order_details tables of the NorthWind Database of SQL Server 2000. In the .xsd file called DataSet1.xsd I added a new row to the Orders table. The E field = element, the field to the right of it equals TotQuant and the field to the right of that equals Int. This table is related to the Order_Details table with a relation name DrMain. In the expression of the new element I wrote the following Sum(child(drMain).UnitPrice). When I run the form I see the new field in the orders table called TotQuant but it equals Null. Is it possible to do what I want? If so, what is the method? Tag: Oracle date problem Tag: 91400
  - 4
    - Trouble updating access database with dataset Here is my code. It should be updating the first field in the first record of the table. No errors occur when I run this but no data is updated either. Any ideas? Thanks. System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Password=pass;User ID=username;" + @"Jet OLEDB:System database=q:\system.mdw;" + @"Data source=q:\db.mdb"; conn.Open(); string query = "select * from table1"; OleDbDataAdapter da = new OleDbDataAdapter( query, conn ); DataSet ds = new DataSet(); da.Fill( ds ); ds.Tables[0].Rows[0].ItemArray[0] = "newvalue"; da.Update( ds ); Tag: Oracle date problem Tag: 91399
  - 5
    - Ado.Net and COM+ Hello I'm pretty new to ADO.Net and I'm still a little confused about some things, I hope I can make myself clear. I'm writing a data-centric web application using ASP.Net, based on a SQL Server 2000 database. I have a table called Person that I'll take as an example of how I access data. I build a dataadapter that access a stored procedure and returns the data into a dataset. i take the datatable and pass it to a custom business object that i use as a PersonCollection. If I want a certain person i query the table and return a datarow that I wrap inside a custon Person object, with properties that wrap the datarow fields. What i want wo know is: how do I update 2 or three of my wrapped tables inside the same transaction using COM+? I'm a litte confused about this. I can update the fields inside the datarows and the write back the changes using a dataadapter using its Updatestatement. But as far as I know a COM+ transaction abort will not affect the datarows rowstate attributes. I'm not sure I make mayself clear here, I guess I'm just looking for beste practices to update several datatables inside the same transaction using COM+, maybe someone knows what I'm talkong about and can help Thanks in advance, PL Tag: Oracle date problem Tag: 91397
  - 6
    - how to use mysql database or other db(not sql server or oracle) In asp.net, it uses ado.net to connect to mysql or other type database. But I don't know how. Are there some articles that illustrate example? Thanks. Tag: Oracle date problem Tag: 91389
  - 7
    - Is this a bad Idea? Can someone tell me what might go wrong with the following code. It seems to work fine for me but I'm doubtfull. I'm adding data to a MSAccess database with an autonumber column called "id". This is a single user app, so there will never be more than one connection to the database. I've seen posts where people queried "@@identity" within an RowUpdated event handler for the DataAdapter but I don't want that..so I just put the query for "@@identity" in line after the call to Update(). The code i'm curious about is just before the catch. public int CreateNewOrder(int operatorID,short typeID) { OleDbCommand dbc = null; OleDbDataAdapter da = null; OleDbCommandBuilder cb = null; DataSet dataSet = null; DataRow row = null; try { da = new OleDbDataAdapter("select * from [order]",m_dbConn); da.SelectCommand.Transaction = m_dbTrans; cb = new OleDbCommandBuilder(da); cb.QuotePrefix = "["; cb.QuoteSuffix = "]"; dataSet = new DataSet(); da.Fill(dataSet); row = dataSet.Tables[0].NewRow(); row["operator_id"] = operatorID; row["type_id"] = typeID; row["status_id"] = (short)Util.Constants.OrderStatus.OPEN; row["date_created"] = DateTime.Now; row["date_modified"] = DateTime.Now; dataSet.Tables[0].Rows.Add(row); DataRow[] dra = dataSet.Tables[0].Select(null, null, DataViewRowState.Added); da.Update(dra); da.Dispose(); dbc = new OleDbCommand("SELECT @@IDENTITY",m_dbConn); dbc.Transaction = m_dbTrans; return (int)dbc.ExecuteScalar(); } catch(Exception e) { m_bErrored = true; throw e; } finally { if(dbc != null) try{dbc.Dispose();} catch(Exception){} if(dataSet != null) try{dataSet.Dispose();} catch(Exception){} if(cb != null) try{cb.Dispose();} catch(Exception){} if(da != null) try{da.Dispose();} catch(Exception){} } } Tag: Oracle date problem Tag: 91384
  - 8
    - DataTable is Filled From DataSet each time a change is made Because I'm a beginner to ASP.NET, I may be not familiar with the way each ASP.NET page is processed. In the books that I have read (scanned), the purpose of DataSet is to keep your data in memory until you decide to commit the changes. Ok, so I create and fill my DataSet in the Page_Init section (I have tried in the Page_Load section as well), and then later use one of the tables from the DataSet in the DataGrid. But when I try to edit a row in the DataGrid (by pressing the Edit link in the EditCommandColumn) a request is made back to the server, thus reloading my page and reinitializing everything and making a request to the database and FILLING my DataSet AGAIN. I mean how is that different than just updating the database each time and skipping few steps? Am I doing something wrong? From my understaning it should reside in memory and not be reinitialized each time I make a request to the page. What would I have to do in order to keep it in memory and not reload it each time I make a request to the page? Would I have to keep my DataSet object in the Session variable or something? Thank you in advance. Tag: Oracle date problem Tag: 91381
  - 9
    - Moving data What's the easiest way to move a subset of data from one access database to another in .Net? Dave Tag: Oracle date problem Tag: 91375
  - 10
    - System.Data ExecuteReader requires an open and available Connection Hello everyone, I'm new to .net and I'm trying to figure out the ado.net. I'm creating a login page for a new asp.net app. But I'm getting an error: "System.Data ExecuteReader requires an open and available Connection. The connection's current state is Closed." with the "If DA.SelectCommand.ExecuteReader.HasRows = False Then" line of code if there is no data returned. If I don't check for no data, I get an error: "System.Data There is no row at position 0." Can you give me any hints with this? thanks, Jason Try Dim DS As New DataSet Dim DR As DataRow Dim strUsername As String Dim strpassword As String DA.SelectCommand.CommandText = "Select Username, Password from tblFarmers where username='" & txtusername.Text.ToString & "'" If DA.SelectCommand.ExecuteReader.HasRows = False Then lblLoginFailed.Text = "--Login Failed. Username or Password is invalid" Exit Sub End If DA.Fill(DS) DR = DS.Tables("tblFarmers").Rows(Session("Row1")) strUsername = DR("username") strpassword = DR("password") If txtusername.Text.ToString = strUsername And txtpassword.Text.ToString = strpassword Then txtusername.Text = "Passed" 'Response.Redirect(strPassed) Else lblLoginFailed.Text = "Login Failed. Username or Password is invalid" End If Catch lblLoginFailed.Text = Err.Source & " " & Err.Description & " " & Err.Erl Err.Clear() End Try Tag: Oracle date problem Tag: 91373
  - 11
    - Expression Clairification I posted two questions yesterday with regard to expressions titled "Expressions and their limitations" and "Regenerate the dataset and expressions" I must appologize. It has been some time since I visited the subject of Expressions and it appears I have my facts wrong. Let me start again. I don't understand what's going on. Perhaps my notes are incorrect or perhaps there has been a change from VS 2002 to VS 2003. I am trying to add a field called "Name" to a table in .xsd dataset. This field should contain an expression. The expression should concotenate the first name field and the last name field of this table into one field called Name. My notes indicate that I should be able to add a row to the table. When I do, this row contains three fields or columns in it. The first field is to have a value in it of element, the second to have a value of name and the third to have a value of string. Then my notes say something I can't explain. Maybe someone else reading this can. They say, "In the properties field of the new element there will be a property called expression". It is here, my notes say, that I am to write the expression. I see no such place to write an expression. Can anyone tell or can anyone explain here what I am missing? Tag: Oracle date problem Tag: 91371
  - 12
    - Specify Multiple Column Relationship I am trying to relate two tables in a dataset but cannot find the right syntax to specify that the relationship consists of two columns. The following code specifies a single column but I need to add a 2nd column ("SeqNbr") to that to specify a unique relation? Dim ShowSetsRel As DataRelation = ds1.Relations.Add("ShowSets", _ ds1.Tables("CGShows").Columns("EventID"), _ ds1.Tables("CGPerfSched").Columns("EventID")) Tag: Oracle date problem Tag: 91350
  - 13
    - DataSet.Merge / auto number on PK -> duplicated rows problem Hi! I create a new row in dataset "ds" and press "save" where following is done: ds = the full dataset, that was loaded at the start up of the form dsChanges = that is simply ds.GetChanges(). this dataset is the one sent down to "data layer" Step 1. 'We can see that the new row is created - note the status "Added": ds.tables("PriceLists").rows.count ---> 21 Integer ds.Tables("PriceLists").Rows(20).Rowstate ---> Added System.Data.DataRowState 'Note that I have not entered ID for PK column - that is automaticly generated in "data layer": ds.Tables("PriceLists").Rows(20).Item("PriceListID") {System.DBNull} Object 'We can also see that the "difference dataset" contains the new row: dsChanges.Tables("PriceLists").Rows.Count ---> 1 Integer '...and of course is the PK column still correctly null: dschanges.tables("PriceLists").Rows(0).Item("PriceListID") {System.DBNull} Object Step 2. 'I send down dsChanges to data layer. The data layer returns a dsChanges that update is done on (using stored procedures...). 'We can no see that the row has successfully been added to the database, 'with a "Identity" (MSSQL) value as PK. Perfect!: dschanges.tables("PriceLists").Rows(0).Item("PriceListID") 1635 {Integer} Object Step 3. 'Let me now simply do a "ds.Merge(dsChanges)" and have a new look at the values. 'What! - Merge has added a NEW row instead of updating the newly created one. ds.Tables("PriceLists").Rows.Count ---> 22 Integer 'The row that was added by me was not touched by the Merge (status still "Added") - see: ds.Tables("PriceLists").Rows(20).Rowstate ---> Added System.Data.DataRowState ds.Tables("PriceLists").Rows(20).Item("PriceListID") {System.DBNull} Object 'But the - NOT WANTED - new row that Merge created has the new PK value. ds.Tables("PriceLists").Rows(21).Item("PriceListID") 1635 {Integer} Object Merge fails to understand how to merge the newly created row that was bounced to data layer and complemented with the "automaticly created unique primary key value" (quote from help file above). Can you understand my problem? Working with the combination of "GetChanges" / "Merge" / "MSSQL - Identity column" must be a common scenario. The scenario should really work - MS is describing it in the help file referred to above ;-) I really hope I am doing something wrong - but what ? Can it be the lack of .PrimaryKey constraint on the DataTable that makes it impossible for Merge to find the row to update? BUT I can NOT have a .PrimaryKey constraint - as I have understood - because the client dataset MUST ALLOW the PriceListID column to be NULL until complemented with "Identity generated PK" by data layer. Coding in VisualBasic. Best regards Benjamin Tengelin, Sweden Tag: Oracle date problem Tag: 91343
  - 14
    - DataTable.NewRow and DataTable.LoadDataRow Hi ADO.NET gurus, What is the difference between DataTable.NewRow and DataTable.LoadDataRow? Actually I want to find a row in the datatable. If the row is not there in the datatable then I will create a row and add it to the datatable, but I am not going to specify all the values of the columns in that newly added Datarow. In this scenario which method should I use? Also when should I use the BeginDataLoad and EndDataLoad method of the DataTable? -- regards, Ashish Sheth Tag: Oracle date problem Tag: 91340
  - 15
    - DataSet.ReadXmlSchema and DataColumn.DefaultValue problem Hi! When in the schema I have such element: <xsd:attribute name="id" type="xsd:long" use="optional" default="1"/> after reading this shema to DataSet column "id" have property DefaultValue = 1, and everything is fine. But now I have this element: <xsd:attribute name="typ" type="xsd:string" use="required" fixed="r"/> When use is "required" in schema you must set value by "fixed" attribute, not by default ! But why attribute fixed is NOT read by ReadXmlSchema ???!!!?? And column "typ" have DefaultValue empty !! How to read fixed attribute ? Help PLEASE ! Tag: Oracle date problem Tag: 91339
  - 16
    - datagrid blows out page width when in edit mode Hi There, I wanted to know if I alter the width of a datagrid when in edit mode? I have a datagrid which has a lot of columns and I waas wondering if I can make it smaller so that it fits into the page? cheers Moe <>< <asp:DataGrid runat="server" id="dgModifyListing" AutoGenerateColumns="False" CellPadding="2" width=8% HeaderStyle-BackColor="purple" HeaderStyle-ForeColor="White" HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="True" AllowPaging="True" OnPageIndexChanged="NewPage" OnCancelCommand="dgModifyListing_Cancel" OnEditCommand="dgModifyListing_Edit" OnUpdateCommand="dgModifyListing_Update"> <AlternatingItemStyle BackColor="White" /> <PagerStyle NextPageText="Next -->" PrevPageText="<-- Prev." HorizontalAlign="Right" /> <columns> <asp:EditCommandColumn EditText="Edit Info" ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" /> <asp:BoundColumn HeaderText="Listing" DataField="listingID" readonly=true visible=false /> <asp:BoundColumn HeaderText="Equipment Type" DataField="formname" /> <asp:BoundColumn HeaderText="Manufacturer" DataField="manufacturer" /> <asp:BoundColumn HeaderText="Model" DataField="model" /> <asp:BoundColumn HeaderText="Description" DataField="descri" /> <asp:BoundColumn HeaderText="Quantity" DataField="quantity" /> <asp:BoundColumn HeaderText="Price" DataField="listprice" /> <asp:BoundColumn HeaderText="Comments" DataField="comments" /> <asp:BoundColumn HeaderText="Location" DataField="location" /> <asp:BoundColumn HeaderText="username" DataField="username" /> <asp:BoundColumn HeaderText="lbl1" DataField="username" /> <asp:BoundColumn HeaderText="username" DataField="username" /> </columns> </asp:datagrid> Tag: Oracle date problem Tag: 91334
  - 17
    - Simple 3 tier stuff Sorry about this but I'm new to ADO.NET (finally coming from simple ADO, bless it) and I'm trying to create a simple three tier program. Ie, User interface Layer / Business object layer / Database layer I've got a simple program that is activilly populating and writing my satasets. They are currently only mapping the database tables.So 1 dataset, 5 datatables and several relationships. In this program I can't seem to hide the database information very well from the user interface. What I'm looking for is an example that hides all the connection and data objects in the middle tier, so that the user interface simply has to deal with collections and such like. Any examples please would be greatly appricated. All the examples I have found so far have been writing to datagrids and missing the point completely. Tag: Oracle date problem Tag: 91332
  - 18
    - Problem with HTA streaming data to client Hi, I've an .aspx page which I call from a .hta in IE6 (so I can get rid of the window control buttons etc) When I call the .aspx page directly and run the code below all is fine but when I call it from the .hta, the code below just hangs. Any hints are greatly appreciated! Thanks for your efforts! Martin Response.ContentType = "text/richtext" Response.AddHeader("content-disposition", "attachment; filename=""" & filename & """") 'Write the file directly to the HTTP output stream. Response.Write(RTFContent) Response.End() Tag: Oracle date problem Tag: 91330
  - 19
    - Regenerate the dataset and expressions I am struggling with the basics in Ado.net using VB.net for non web applications. I have been using expressions in the design mode of strongly typed datasets (.xsd files). However, I have discovered that if you regenerate the dataset, the expression goes away. This is not what I want. I am wondering how others deal with this issue. I can think of a couple of ways of dealing with it. Don't know which is best. One, don't regenerate the dataset. If you add a field in the underlying SQL Server table, for example, and you want your dataset to reflect it, add it explicitely in design mode of the .xsd file. Two, If you are going to use expressions do them in code as opposed to in the .xsd file. If this is bes, it becomes a matter of where to add them; that is, in what event? Any suggestions or thoughts would be appreciated. Thank you. Tag: Oracle date problem Tag: 91326
  - 20
    - Expressions and their limitations I am using VB.net in a non Web applications I have been reading up on expressions and they seem quite helpful. However, in one book I've read it talks about what appears to be a severe limitation. The book may have been written before VS 2003 so maybe the issue has been fixed. I don't know. What I am told is that "Using expressions in combination with DatRelations gives you the ability to create a user-friendly form for viewing data. However, you may find that in some situations the data is not editable". In some circumstances it appears that if you create a column with an expression you will not be able to edit that column, which is understandable; but you may not be able to edit any of the other columns in the table either. If this is the case, it would appear that expresessions are severly limited in that they can only be used (Practically) in forms where there is a view only capacity. Does someone else have some inforamtion on this that may enlighten me? Thank You Tag: Oracle date problem Tag: 91325
  - 21
    - Activate edit mode on CurrencyManager? Hi there, I have a WinForm with two textboxes binded to a CurrencyManager. What I want to do is this: to get the CurrencyManager's edit mode activated when the content of any of the binded textboxes is changed. Then, I would like to capture this change of state (in some event perhaps?) so I can enable the 'Save' and 'Cancel' buttons. Is this possible? Thanks in advance for any pointers, -Jorge Tag: Oracle date problem Tag: 91320
  - 22
    - Super-fast keyfile New to ADO.NET so seeking comments on strategy. I need to scan data, and build a "keyfile" on data I am extracting during the scan. Actual fields varies by job, and I dynamically create this from a description, and there may be 5-30 fields of integers and strings typically. Small jobs may be a few hundred records, but I could have up to 250,000. The machine this runs on is an extremely fast, dual processor, with green gobs of physical memory. I need to sort the data in alternate orders, make passes over it, updating and filling in more fields as I go. My thought was to use an Dataset. I understand I can make tables programmatically, and populate programmatically, record at a time, field at a time. Then I can use a Dataview to make passes in alternate orderings, and make changes and fill in empty fields. After each pass I want to persist the major table to Jet, primarily for problem analysis. As each pass is done, replace the table content on disk with what I have in my in-memory dataset. I have already implemented this talking directly to JET, but have been dissappointed in the performance, even after some amount of tweaking. It would seem the new approach should be a real screamer. Seeking comments from those more experienced with ADO.NET on this approach. Tag: Oracle date problem Tag: 91318
  - 23
    - Oracle Tables Hello : Can you create a table in an Oracle data base using sentences in your .NET code? I explain: example: sqlSentence = "CREATE TABLE tableName (...)" command.commandtext = sqlSentence; command.ExecuteNonQuery(); If you can, how can you set auto_increment data types? Thanks Paula Tag: Oracle date problem Tag: 91313
  - 24
    - Second binding If I need to post code, I will do so, but I think explaining my question my result in a good answer. I have a form that asks a user for a unique id number. This then runs the data adapter's parametized query and then fills a dataset with the one resulting row. This works fine and dandy. On this same form I have a RESET button (in case the user determins that the wrong information has been entered). This clears the form and the dataset and present the user with the blank text box in which they need to enter another unique number. Here is the problem: when I try to run this, something seems to be going on with the databindings on the form's controls because the second time, nothing is being bound. One of the fields that is being passed is a date and this is where the program errors. It is tring to pass "" as a date. Any information or insite would be greatly appreciated. Thanks, Brad Tag: Oracle date problem Tag: 91308
  - 25
    - "IF" test in SP evals to True and False at same time using DataAdapter.FillSchema() The IF test in a Stored Procedure is evaluating to true and false at the same time for FillSchema() (See result below -- SELECT TOP 2 TOP 100 PERCENT) Any thoughts are appreciated. Dave Information on Problem =============================== Below is the output from the Fill() and FillSchema(). The commands were run back-to-back using the exact same SQL Command and DataAdapter. DataAdapter.Fill() =============================== DECLARE @request_list table (id uniqueidentifier not null); INSERT INTO @request_list (id ) SELECT id FROM (SELECT TOP 2 id FROM view_3 top_requests; DataAdapter.FillSchema() =============================== Incorrect syntax near the keyword 'SOME'. DECLARE @request_list table (id uniqueidentifier not null); SOME BOGUS TEXT INSERT INTO @request_list (id ) SELECT id FROM (SELECT TOP 2 TOP 100 PERCENT id FROM view_3 ) top_requests; Code Snip of Stored Procedure that is failing =============================== IF (@NEW_DEBUG_VAR <> 0) BEGIN SET @query = @query + N' SOME BOGUS TEXT ' END ELSE BEGIN SET @query = @query + N' INSERT INTO @request_list (id ) SELECT id FROM (SELECT' IF (@top1<> 0 ) BEGIN SET @query = @query + N' TOP ' + CAST(@top1 AS NVARCHAR(20)) END ELSE BEGIN SET @query = @query + N' TOP 100 PERCENT ' END END Product Information =============================== VS .NET 2003 V7.1.3088 .NET Framework v1.1 v1.1.4322 SQL Server 2000 - SP3 v8.00.761 Database Setup Information =============================== There is a SQL file for setting up the database, below. Sorry about the naming convention. I had to change the names of everything before I was authorized to send it. Stored Procedure up_sp_1 is the original. I have created up_sp_2 as a subset of up_sp_1 and added some additional debug code. They both exhibit the same behavior. Database information server localhost database FillSchemaDB uid sa pwd <not set> Demo =============================== Create a new Windows application and replace the Form1.cs code with the code included below. Form1.cs Code ==================================== using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace MyNamespace.FillSchema { /// <summary> /// Summary description for Form1. /// </summary> public class Form1 : System.Windows.Forms.Form { #region Windows Generated Stuff -- No Modifications Here private System.Windows.Forms.Button button1; private System.Windows.Forms.TextBox textBox1; private System.Windows.Forms.TextBox textBox2; private System.Windows.Forms.Label label1; private System.Windows.Forms.Label label2; private System.Windows.Forms.CheckBox checkBox1; private System.Windows.Forms.CheckBox checkBox2; private System.Windows.Forms.GroupBox groupBox1; private System.Windows.Forms.RadioButton radioButton2; private System.Windows.Forms.RadioButton radioButton1; /// <summary> /// Required designer variable. /// </summary> private System.ComponentModel.Container components = null; public Form1() { // // Required for Windows Form Designer support // InitializeComponent(); // // TODO: Add any constructor code after InitializeComponent call // } /// <summary> /// Clean up any resources being used. /// </summary> protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); } /// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main() { Application.Run(new Form1()); } #endregion #region Windows Form Designer generated code /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.button1 = new System.Windows.Forms.Button(); this.textBox1 = new System.Windows.Forms.TextBox(); this.textBox2 = new System.Windows.Forms.TextBox(); this.label1 = new System.Windows.Forms.Label(); this.label2 = new System.Windows.Forms.Label(); this.checkBox1 = new System.Windows.Forms.CheckBox(); this.checkBox2 = new System.Windows.Forms.CheckBox(); this.radioButton2 = new System.Windows.Forms.RadioButton(); this.groupBox1 = new System.Windows.Forms.GroupBox(); this.radioButton1 = new System.Windows.Forms.RadioButton(); this.groupBox1.SuspendLayout(); this.SuspendLayout(); // // button1 // this.button1.Location = new System.Drawing.Point(32, 568); this.button1.Name = "button1"; this.button1.TabIndex = 0; this.button1.Text = "Do Test"; this.button1.Click += new System.EventHandler(this.button1_Click); // // textBox1 // this.textBox1.Location = new System.Drawing.Point(24, 40); this.textBox1.Multiline = true; this.textBox1.Name = "textBox1"; this.textBox1.Size = new System.Drawing.Size(656, 192); this.textBox1.TabIndex = 1; this.textBox1.Text = ""; // // textBox2 // this.textBox2.Location = new System.Drawing.Point(24, 280); this.textBox2.Multiline = true; this.textBox2.Name = "textBox2"; this.textBox2.Size = new System.Drawing.Size(656, 192); this.textBox2.TabIndex = 2; this.textBox2.Text = ""; // // label1 // this.label1.Location = new System.Drawing.Point(24, 16); this.label1.Name = "label1"; this.label1.TabIndex = 3; this.label1.Text = "Fill()"; // // label2 // this.label2.Location = new System.Drawing.Point(24, 256); this.label2.Name = "label2"; this.label2.TabIndex = 4; this.label2.Text = "FillSchema()"; // // checkBox1 // this.checkBox1.Location = new System.Drawing.Point(32, 488); this.checkBox1.Name = "checkBox1"; this.checkBox1.Size = new System.Drawing.Size(416, 24); this.checkBox1.TabIndex = 5; this.checkBox1.Text = "Branch In SQL Server To Add \"SOME BOGUS TEXT\""; // // checkBox2 // this.checkBox2.Location = new System.Drawing.Point(32, 528); this.checkBox2.Name = "checkBox2"; this.checkBox2.Size = new System.Drawing.Size(336, 24); this.checkBox2.TabIndex = 6; this.checkBox2.Text = "Don\'t set @NEW_DEBUG_VAR value"; // // radioButton2 // this.radioButton2.Checked = true; this.radioButton2.Location = new System.Drawing.Point(16, 64); this.radioButton2.Name = "radioButton2"; this.radioButton2.TabIndex = 7; this.radioButton2.TabStop = true; this.radioButton2.Text = "up_sp_2"; this.radioButton2.CheckedChanged += new System.EventHandler(this.radioButton2_CheckedChanged); // // groupBox1 // this.groupBox1.Controls.Add(this.radioButton1); this.groupBox1.Controls.Add(this.radioButton2); this.groupBox1.Location = new System.Drawing.Point(480, 488); this.groupBox1.Name = "groupBox1"; this.groupBox1.TabIndex = 8; this.groupBox1.TabStop = false; this.groupBox1.Text = "Run Stored Procedure"; // // radioButton1 // this.radioButton1.Location = new System.Drawing.Point(16, 32); this.radioButton1.Name = "radioButton1"; this.radioButton1.TabIndex = 8; this.radioButton1.Text = "up_sp_1"; this.radioButton1.CheckedChanged += new System.EventHandler(this.radioButton1_CheckedChanged); // // Form1 // this.AutoScaleBaseSize = new System.Drawing.Size(8, 19); this.ClientSize = new System.Drawing.Size(704, 604); this.Controls.Add(this.groupBox1); this.Controls.Add(this.checkBox2); this.Controls.Add(this.checkBox1); this.Controls.Add(this.label2); this.Controls.Add(this.label1); this.Controls.Add(this.textBox2); this.Controls.Add(this.textBox1); this.Controls.Add(this.button1); this.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0))); this.Name = "Form1"; this.Text = "Form1"; this.groupBox1.ResumeLayout(false); this.ResumeLayout(false); } #endregion #region Radio Button Stuff private void radioButton1_CheckedChanged(object sender, System.EventArgs e) { checkBox1.Enabled = false; checkBox2.Enabled = false; checkBox1.Checked = false; checkBox2.Checked = false; } private void radioButton2_CheckedChanged(object sender, System.EventArgs e) { checkBox1.Enabled = true; checkBox2.Enabled = true; } #endregion private void button1_Click(object sender, System.EventArgs e) { string s; DataSet schemaDS = new DataSet(); DataSet fillDS = new DataSet(); SqlCommand cmd = null; SqlConnection sqlConn; SqlDataAdapter dataAdapter; string connstr; // *********************************************************** // Create DB Connection // *********************************************************** connstr = "server=localhost;database=FillSchemaDB;uid=sa"; sqlConn = new SqlConnection(connstr); // *********************************************************** // Create SQL Command // *********************************************************** cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; if (radioButton2.Checked == true) cmd.CommandText = "up_sp_2"; else cmd.CommandText = "up_sp_1"; cmd.Connection = sqlConn; // Create Command Parameters cmd.Parameters.Add("@top1", SqlDbType.Int); cmd.Parameters["@top1"].Value = 2; cmd.Parameters.Add("@where1", SqlDbType.NVarChar, 3500); cmd.Parameters["@where1"].Value = " "; if (radioButton2.Checked == true) { cmd.Parameters.Add("@NEW_DEBUG_VAR", SqlDbType.Int); if (checkBox2.Checked == false) { if (checkBox1.Checked == true) cmd.Parameters["@NEW_DEBUG_VAR"].Value = 1; else cmd.Parameters["@NEW_DEBUG_VAR"].Value = 0; } } cmd.Parameters.Add("@rcquery", SqlDbType.NVarChar, 4000); cmd.Parameters["@rcquery"].Direction = ParameterDirection.Output; // *********************************************************** // Create DataAdapter // *********************************************************** dataAdapter = new SqlDataAdapter(cmd); // *********************************************************** // Clear Text Boxes // *********************************************************** textBox1.Text = ""; textBox2.Text = ""; // *********************************************************** // CALL DataAdapter.Fill() Method // *********************************************************** try { // Do Fill() dataAdapter.Fill(fillDS); } catch (Exception ex) { // Display Error Message textBox1.Text = ex.Message; } finally { // Display SQL Command That Was Created In SQL Server s = cmd.Parameters["@rcquery"].Value.ToString(); textBox1.Text = textBox1.Text + "\r\n\r\n" + s; s = null; } // *********************************************************** // CALL DataAdapter.FillSchema() Method // *********************************************************** try { // Do FillSchema() dataAdapter.FillSchema(schemaDS, SchemaType.Source); } catch (Exception ex) { // Display Error Message textBox2.Text = ex.Message; } finally { // Display SQL Command That Was Created In SQL Server s = cmd.Parameters["@rcquery"].Value.ToString(); textBox2.Text = textBox2.Text + "\r\n\r\n" + s; s = null; } } } } ================================ SQL Code ================================ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_13_Table_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_13] DROP CONSTRAINT FK_Table_13_Table_1 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_13_Table_11]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_13] DROP CONSTRAINT FK_Table_13_Table_11 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_13_Table_12]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_13] DROP CONSTRAINT FK_Table_13_Table_12 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_14_Table_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_14] DROP CONSTRAINT FK_Table_14_Table_1 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_15_Table_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_15] DROP CONSTRAINT FK_Table_15_Table_1 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_20_Table_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_20] DROP CONSTRAINT FK_Table_20_Table_1 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_key_2_Table_2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_17] DROP CONSTRAINT FK_key_2_Table_2 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_12_Table_3]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_12] DROP CONSTRAINT FK_Table_12_Table_3 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_8_key_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_8] DROP CONSTRAINT FK_Table_8_key_1 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_8_Table_5]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_8] DROP CONSTRAINT FK_Table_8_Table_5 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_7_Table_6]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_7] DROP CONSTRAINT FK_Table_7_Table_6 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_10_Table_9]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_10] DROP CONSTRAINT FK_Table_10_Table_9 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_10_Table_91]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_10] DROP CONSTRAINT FK_Table_10_Table_91 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_11_Table_7]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_11] DROP CONSTRAINT FK_Table_11_Table_7 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_8_Table_7]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_8] DROP CONSTRAINT FK_Table_8_Table_7 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_10_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_10] DROP CONSTRAINT FK_Table_10_Table_8 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_12_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_12] DROP CONSTRAINT FK_Table_12_Table_8 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_13_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_13] DROP CONSTRAINT FK_Table_13_Table_8 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_14_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_14] DROP CONSTRAINT FK_Table_14_Table_8 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_15_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_15] DROP CONSTRAINT FK_Table_15_Table_8 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_16_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_16] DROP CONSTRAINT FK_Table_16_Table_8 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_17_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_17] DROP CONSTRAINT FK_Table_17_Table_8 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_18_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_18] DROP CONSTRAINT FK_Table_18_Table_8 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_19_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_19] DROP CONSTRAINT FK_Table_19_Table_8 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_key_3_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_20] DROP CONSTRAINT FK_key_3_Table_8 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_21_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Table_21] DROP CONSTRAINT FK_Table_21_Table_8 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Function_1]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[Function_1] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[up_sp_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[up_sp_1] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[view_3]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[view_3] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[view_1]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[view_1] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[view_4]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[view_4] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[view_2]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[view_2] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_10]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_10] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_12]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_12] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_13]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_13] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_14]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_14] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_15]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_15] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_16]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_16] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_17]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_17] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_18]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_18] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_19]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_19] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_20]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_20] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_21]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_21] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_11]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_11] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_8]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_8] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_7]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_7] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_1] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_2] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_3] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_4]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_4] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_5]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_5] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_6]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_6] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_9]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table_9] GO CREATE TABLE [Table_1] ( [id] [smallint] NOT NULL , [code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [field_1] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [id] ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [Table_2] ( [id] [bigint] NOT NULL , [name] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED ( [id] ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [Table_3] ( [id] [smallint] NOT NULL , [name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , CONSTRAINT [PK_Table_3] PRIMARY KEY CLUSTERED ( [id] ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [Table_4] ( [id] [int] NOT NULL , [name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , CONSTRAINT [PK_key_1] PRIMARY KEY CLUSTERED ( [id] ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [Table_5] ( [id] [smallint] NOT NULL , [name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , CONSTRAINT [PK_Table_5] PRIMARY KEY CLUSTERED ( [id] ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [Table_6] ( [id] [smallint] NOT NULL , [name] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , CONSTRAINT [PK_Table_6] PRIMARY KEY CLUSTERED ( [id] ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [Table_7] ( [id] [uniqueidentifier] NOT NULL , [field_2] [datetime] NOT NULL , [field_3] [smallint] NOT NULL , [field_4] [int] NOT NULL , [field_5] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [field_6] [smallint] NOT NULL , [field_7] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [field_8] [int] NOT NULL , [field_9] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [field_10] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [field_11] [uniqueidentifier] NULL , CONSTRAINT [PK_Table_7] PRIMARY KEY NONCLUSTERED ( [id] ) ON [PRIMARY] , CONSTRAINT [FK_Table_7_Table_6] FOREIGN KEY ( [field_3] ) REFERENCES [Table_6] ( [id] ) NOT FOR REPLICATION ) ON [PRIMARY] GO CREATE TABLE [Table_8] ( [reqid] [uniqueidentifier] NOT NULL , [seq_num] [int] NOT NULL , [type] [int] NOT NULL , [field_12] [datetime] NOT NULL , [field_13] [datetime] NOT NULL , [field_14] [smallint] NOT NULL , CONSTRAINT [PK_Table_8] PRIMARY KEY CLUSTERED ( [reqid], [seq_num] ) ON [PRIMARY] , CONSTRAINT [FK_Table_8_Table_7] FOREIGN KEY ( [reqid] ) REFERENCES [Table_7] ( [id] ) ON DELETE CASCADE NOT FOR REPLICATION , CONSTRAINT [FK_Table_8_Table_5] FOREIGN KEY ( [field_14] ) REFERENCES [Table_5] ( [id] ) NOT FOR REPLICATION , CONSTRAINT [FK_Table_8_key_1] FOREIGN KEY ( [type] ) REFERENCES [Table_4] ( [id] ) NOT FOR REPLICATION ) ON [PRIMARY] GO CREATE TABLE [Table_9] ( [id] [int] NOT NULL , [name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_Table_9] PRIMARY KEY CLUSTERED ( [id] ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [Table_10] ( [reqid] [uniqueidentifier] NOT NULL , [seq_num] [int] NOT NULL , [field_15] [int] NULL , [field_16] [int] NOT NULL , CONSTRAINT [PK_Table_10] PRIMARY KEY CLUSTERED ( [reqid], [seq_num] ) ON [PRIMARY] , CONSTRAINT [FK_Table_10_Table_9] FOREIGN KEY ( [field_15] ) REFERENCES [Table_9] ( [id] ) NOT FOR REPLICATION , CONSTRAINT [FK_Table_10_Table_91] FOREIGN KEY ( [field_16] ) REFERENCES [Table_9] ( [id] ) NOT FOR REPLICATION , CONSTRAINT [FK_Table_10_Table_8] FOREIGN KEY ( [reqid], [seq_num] ) REFERENCES [Table_8] ( [reqid], [seq_num] ) ON DELETE CASCADE NOT FOR REPLICATION ) ON [PRIMARY] GO alter table [dbo].[Table_10] nocheck constraint [FK_Table_10_Table_9] GO alter table [dbo].[Table_10] nocheck constraint [FK_Table_10_Table_91] GO CREATE TABLE [Table_11] ( [id] [uniqueidentifier] NOT NULL , [field_17] [datetime] NOT NULL , [field_18] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [IX_Table_11] UNIQUE CLUSTERED ( [id] ) ON [PRIMARY] , CONSTRAINT [FK_Table_11_Table_7] FOREIGN KEY ( [id] ) REFERENCES [Table_7] ( [id] ) ON DELETE CASCADE NOT FOR REPLICATION ) ON [PRIMARY] GO CREATE TABLE [Table_12] ( [reqid] [uniqueidentifier] NOT NULL , [seq_num] [int] NOT NULL , [field_19] [smallint] NULL , CONSTRAINT [PK_Table_12] PRIMARY KEY CLUSTERED ( [reqid], [seq_num] ) ON [PRIMARY] , CONSTRAINT [FK_Table_12_Table_3] FOREIGN KEY ( [field_19] ) REFERENCES [Table_3] ( [id] ) NOT FOR REPLICATION , CONSTRAINT [FK_Table_12_Table_8] FOREIGN KEY ( [reqid], [seq_num] ) REFERENCES [Table_8] ( [reqid], [seq_num] ) ON DELETE CASCADE NOT FOR REPLICATION ) ON [PRIMARY] GO alter table [dbo].[Table_12] nocheck constraint [FK_Table_12_Table_3] GO CREATE TABLE [Table_13] ( [reqid] [uniqueidentifier] NOT NULL , [seq_num] [int] NOT NULL , [field_20] [smallint] NULL , [field_21] [smallint] NULL , [field_22] [smallint] NULL , CONSTRAINT [PK_Table_13] PRIMARY KEY CLUSTERED ( [reqid], [seq_num] ) ON [PRIMARY] , CONSTRAINT [FK_Table_13_Table_1] FOREIGN KEY ( [field_20] ) REFERENCES [Table_1] ( [id] ) NOT FOR REPLICATION , CONSTRAINT [FK_Table_13_Table_11] FOREIGN KEY ( [field_21] ) REFERENCES [Table_1] ( [id] ) NOT FOR REPLICATION , CONSTRAINT [FK_Table_13_Table_12] FOREIGN KEY ( [field_22] ) REFERENCES [Table_1] ( [id] ) NOT FOR REPLICATION , CONSTRAINT [FK_Table_13_Table_8] FOREIGN KEY ( [reqid], [seq_num] ) REFERENCES [Table_8] ( [reqid], [seq_num] ) ON DELETE CASCADE NOT FOR REPLICATION ) ON [PRIMARY] GO alter table [dbo].[Table_13] nocheck constraint [FK_Table_13_Table_1] GO alter table [dbo].[Table_13] nocheck constraint [FK_Table_13_Table_11] GO alter table [dbo].[Table_13] nocheck constraint [FK_Table_13_Table_12] GO CREATE TABLE [Table_14] ( [reqid] [uniqueidentifier] NOT NULL , [seq_num] [int] NOT NULL , [field_23] [int] NOT NULL , [field_24] [smallint] NOT NULL , CONSTRAINT [PK_Table_14] PRIMARY KEY CLUSTERED ( [reqid], [seq_num] ) ON [PRIMARY] , CONSTRAINT [FK_Table_14_Table_1] FOREIGN KEY ( [field_24] ) REFERENCES [Table_1] ( [id] ) NOT FOR REPLICATION , CONSTRAINT [FK_Table_14_Table_8] FOREIGN KEY ( [reqid], [seq_num] ) REFERENCES [Table_8] ( [reqid], [seq_num] ) ON DELETE CASCADE NOT FOR REPLICATION ) ON [PRIMARY] GO alter table [dbo].[Table_14] nocheck constraint [FK_Table_14_Table_1] GO alter table [dbo].[Table_7] nocheck constraint [FK_Table_7_Table_6] GO CREATE TABLE [Table_15] ( [reqid] [uniqueidentifier] NOT NULL , [seq_num] [int] NOT NULL , [field_25] [int] NOT NULL , [field_26] [smallint] NOT NULL , [field_27] [smallint] NOT NULL CONSTRAINT [DF_Table_15_field_27] DEFAULT (0), CONSTRAINT [PK_Table_15] PRIMARY KEY CLUSTERED ( [reqid], [seq_num] ) ON [PRIMARY] , CONSTRAINT [FK_Table_15_Table_1] FOREIGN KEY ( [field_26] ) REFERENCES [Table_1] ( [id] ) NOT FOR REPLICATION , CONSTRAINT [FK_Table_15_Table_8] FOREIGN KEY ( [reqid], [seq_num] ) REFERENCES [Table_8] ( [reqid], [seq_num] ) ON DELETE CASCADE NOT FOR REPLICATION ) ON [PRIMARY] GO alter table [dbo].[Table_15] nocheck constraint [FK_Table_15_Table_1] GO CREATE TABLE [Table_16] ( [reqid] [uniqueidentifier] NOT NULL , [seq_num] [int] NOT NULL , [field_28] [int] NOT NULL , CONSTRAINT [PK_Table_16] PRIMARY KEY CLUSTERED ( [reqid], [seq_num] ) ON [PRIMARY] , CONSTRAINT [FK_Table_16_Table_8] FOREIGN KEY ( [reqid], [seq_num] ) REFERENCES [Table_8] ( [reqid], [seq_num] ) ON DELETE CASCADE NOT FOR REPLICATION ) ON [PRIMARY] GO alter table [dbo].[Table_8] nocheck constraint [FK_Table_8_Table_5] GO alter table [dbo].[Table_8] nocheck constraint [FK_Table_8_key_1] GO CREATE TABLE [Table_17] ( [reqid] [uniqueidentifier] NOT NULL , [field_29] [int] NOT NULL , [field_30] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [field_31] [bigint] NOT NULL , [field_32] [int] NOT NULL , [field_33] [int] NOT NULL , [field_34] [int] NOT NULL , [field_35] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_Table_17] PRIMARY KEY CLUSTERED ( [reqid], [field_29], [field_30], [field_31] ) ON [PRIMARY] , CONSTRAINT [FK_key_2_Table_2] FOREIGN KEY ( [field_31] ) REFERENCES [Table_2] ( [id] ) NOT FOR REPLICATION , CONSTRAINT [FK_Table_17_Table_8] FOREIGN KEY ( [reqid], [field_29] ) REFERENCES [Table_8] ( [reqid], [seq_num] ) ON DELETE CASCADE NOT FOR REPLICATION ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO alter table [dbo].[Table_17] nocheck constraint [FK_key_2_Table_2] GO CREATE TABLE [Table_18] ( [reqid] [uniqueidentifier] NOT NULL , [seq_num] [int] NOT NULL , [field_36] [int] NOT NULL , CONSTRAINT [PK_Table_18] PRIMARY KEY CLUSTERED ( [reqid], [seq_num] ) ON [PRIMARY] , CONSTRAINT [FK_Table_18_Table_8] FOREIGN KEY ( [reqid], [seq_num] ) REFERENCES [Table_8] ( [reqid], [seq_num] ) ON DELETE CASCADE NOT FOR REPLICATION ) ON [PRIMARY] GO CREATE TABLE [Table_19] ( [reqid] [uniqueidentifier] NOT NULL , [seq_num] [int] NOT NULL , [field_37] [int] NOT NULL , [field_38] [smallint] NOT NULL , CONSTRAINT [PK_Table_19] PRIMARY KEY CLUSTERED ( [reqid], [seq_num] ) ON [PRIMARY] , CONSTRAINT [FK_Table_19_Table_8] FOREIGN KEY ( [reqid], [seq_num] ) REFERENCES [Table_8] ( [reqid], [seq_num] ) ON DELETE CASCADE NOT FOR REPLICATION ) ON [PRIMARY] GO CREATE TABLE [Table_20] ( [reqid] [uniqueidentifier] NOT NULL , [seq_num] [int] NOT NULL , [field_39] [bigint] NOT NULL , [field_40] [smallint] NOT NULL , [field_41] [int] NOT NULL , [field_42] [int] NOT NULL , [field_43] [int] NOT NULL , [field_44] [int] NOT NULL , CONSTRAINT [PK_key_3] PRIMARY KEY CLUSTERED ( [reqid], [seq_num], [field_39] ) ON [PRIMARY] , CONSTRAINT [FK_key_3_Table_8] FOREIGN KEY ( [reqid], [seq_num] ) REFERENCES [Table_8] ( [reqid], [seq_num] ) ON DELETE CASCADE NOT FOR REPLICATION , CONSTRAINT [FK_Table_20_Table_1] FOREIGN KEY ( [field_40] ) REFERENCES [Table_1] ( [id] ) NOT FOR REPLICATION ) ON [PRIMARY] GO alter table [dbo].[Table_20] nocheck constraint [FK_Table_20_Table_1] GO CREATE TABLE [Table_21] ( [reqid] [uniqueidentifier] NOT NULL , [seq_num] [int] NOT NULL , [field_45] [int] NOT NULL , [field_46] [smallint] NOT NULL , CONSTRAINT [PK_Table_21] PRIMARY KEY CLUSTERED ( [reqid], [seq_num] ) ON [PRIMARY] , CONSTRAINT [FK_Table_21_Table_8] FOREIGN KEY ( [reqid], [seq_num] ) REFERENCES [Table_8] ( [reqid], [seq_num] ) ON DELETE CASCADE NOT FOR REPLICATION ) ON [PRIMARY] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE FUNCTION [dbo].[Function_1] (@reqId uniqueidentifier) RETURNS int AS BEGIN --status constant define declare @var_1 int SET @var_1 = 1 declare @var_2 int SET @var_2 = 2 declare @var_3 int SET @var_3 = 3 declare @var_4 int SET @var_4 = 4 --task constant define declare @var_5 int SET @var_5 = 1 declare @var_6 int SET @var_6 = 2 declare @var_7 int SET @var_7 = 4 declare @var_8 int SET @var_8 = 8 declare @var_9 int SET @var_9 = 16 declare @var_10 int SET @var_10 = 32 declare @var_11 int SET @var_11 = 64 declare @var_12 int SET @var_12 = 128 declare @var_13 int SET @var_13 = 256 declare @var_14 int SET @var_14 = 512 --get task mask declare @var_15 int SELECT @var_15 = field_4 FROM dbo.Table_7 WHERE id = @reqId declare @var_16 int --check tasks one by one IF @var_15 & @var_5 > 0 BEGIN SET @var_16 = @var_2 SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_5 IF @var_16 <> @var_3 return (@var_1) END IF @var_15 & @var_6 > 0 BEGIN SET @var_16 = @var_2 SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_6 IF @var_16 <> @var_3 return (@var_16) END /*** IF @var_15 & @var_7 > 0 BEGIN SET @var_16 = @var_2 SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_7 IF @var_16 <> @var_3 return (@var_16) END ***/ IF @var_15 & @var_8 > 0 BEGIN SET @var_16 = @var_2 SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_8 IF @var_16 <> @var_3 return (@var_16) END IF @var_15 & @var_9 > 0 BEGIN SET @var_16 = @var_2 SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_9 IF @var_16 <> @var_3 return (@var_16) END IF @var_15 & @var_10 > 0 BEGIN SET @var_16 = @var_2 SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_10 IF @var_16 <> @var_3 return (@var_16) END IF @var_15 & @var_11 > 0 BEGIN SET @var_16 = @var_2 SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_11 IF @var_16 <> @var_3 return (@var_16) END IF @var_15 & @var_12 > 0 BEGIN SET @var_16 = @var_2 SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_12 IF @var_16 <> @var_3 return (@var_16) END IF @var_15 & @var_13 > 0 BEGIN SET @var_16 = @var_2 SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_13 IF @var_16 <> @var_3 return (@var_16) END IF @var_15 & @var_14 > 0 BEGIN SET @var_16 = @var_2 SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_14 IF @var_16 <> @var_3 return (@var_16) END return (@var_3) END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.view_1 AS SELECT id AS reqid, dbo.Function_1(id) AS status FROM dbo.Table_7 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.view_2 AS SELECT * FROM dbo.Table_7 WHERE (id NOT IN (SELECT id FROM Table_11)) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.view_3 AS SELECT dbo.view_2.id, dbo.view_2.field_2, dbo.view_2.field_5, dbo.view_2.field_6, dbo.view_2.field_7, dbo.view_2.field_8, dbo.view_2.field_9, field_15, field_19, field_36, field_28, COALESCE(field_20, t20_field_40, t19_field_38, t14_field_24, t15_field_26, t21_field_46, 0) AS field_20, dbo.view_1.status, field_16, field_37, field_45, t20_field_40, field_44, t19_field_38, dbo.view_2.field_3, dbo.view_2.field_4, field_23, t14_field_24, field_25, t15_field_26, t21_field_46 FROM dbo.view_2 LEFT OUTER JOIN dbo.view_1 ON dbo.view_2.id = dbo.view_1.reqid LEFT OUTER JOIN (SELECT dbo.Table_8.reqid, max(field_15) AS field_15, max(field_19) AS field_19, max(field_36) AS field_36, max(field_28) AS field_28, max(field_20) AS field_20, max(field_16) AS field_16, max(field_37) AS field_37, max(field_45) AS field_45, max(field_23) AS field_23, max(field_25) AS field_25, max(field_44) AS field_44, max(dbo.Table_20.field_40) AS t20_field_40, max(dbo.Table_19.field_38) AS t19_field_38, max(dbo.Table_14.field_24) AS t14_field_24, max(dbo.Table_15.field_26) AS t15_field_26, max(dbo.Table_21.field_46) AS t21_field_46 FROM dbo.Table_8 LEFT OUTER JOIN dbo.Table_19 ON dbo.Table_8.reqid = dbo.Table_19.reqid AND dbo.Table_8.seq_num=dbo.Table_19.seq_num LEFT OUTER JOIN dbo.Table_20 ON dbo.Table_8.reqid = dbo.Table_20.reqid AND dbo.Table_8.seq_num=dbo.Table_20.seq_num LEFT OUTER JOIN dbo.Table_21 ON dbo.Table_8.reqid = dbo.Table_21.reqid AND dbo.Table_8.seq_num=dbo.Table_21.seq_num LEFT OUTER JOIN dbo.Table_10 ON dbo.Table_8.reqid = dbo.Table_10.reqid AND dbo.Table_8.seq_num=dbo.Table_10.seq_num LEFT OUTER JOIN dbo.Table_12 ON dbo.Table_8.reqid = dbo.Table_12.reqid AND dbo.Table_8.seq_num=dbo.Table_12.seq_num LEFT OUTER JOIN dbo.Table_13 ON dbo.Table_8.reqid = dbo.Table_13.reqid AND dbo.Table_8.seq_num=dbo.Table_13.seq_num LEFT OUTER JOIN dbo.Table_18 ON dbo.Table_8.reqid = dbo.Table_18.reqid AND dbo.Table_8.seq_num=dbo.Table_18.seq_num LEFT OUTER JOIN dbo.Table_16 ON dbo.Table_8.reqid = dbo.Table_16.reqid AND dbo.Table_8.seq_num=dbo.Table_16.seq_num LEFT OUTER JOIN dbo.Table_15 ON dbo.Table_8.reqid = dbo.Table_15.reqid AND dbo.Table_8.seq_num=dbo.Table_15.seq_num LEFT OUTER JOIN dbo.Table_14 ON dbo.Table_8.reqid = dbo.Table_14.reqid AND dbo.Table_8.seq_num=dbo.Table_14.seq_num GROUP BY dbo.Table_8.reqid ) t ON dbo.view_2.id = t.reqid GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.view_4 AS SELECT dbo.Table_8.*, Trys.define_1 AS define_1 FROM dbo.Table_8 INNER JOIN (SELECT reqid, field_29, COUNT(*) AS define_1 FROM Table_17 GROUP BY reqid, field_29) Trys ON dbo.Table_8.seq_num = Trys.field_29 AND dbo.Table_8.reqid = Trys.reqid GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE [dbo].[up_sp_1] ( @top1 as int, -- how many requests must return @where1 as nvarchar(3500), -- the search criteria, for example "WHERE.... ORDER BY field_2 DESC" -- Added For Debug Of FillSchema @rcquery as nvarchar(4000) output ) AS DECLARE @error_code int DECLARE @query as nvarchar(4000) SET @query = N'DECLARE @request_list table (id uniqueidentifier not null);' SET @query = @query + N' INSERT INTO @request_list (id ) SELECT id FROM (SELECT' IF (@top1 <> 0 ) SET @query = @query + N' TOP ' + CAST(@top1 AS NVARCHAR(20)) else SET @query = @query + N' TOP 100 PERCENT ' SET @query = @query + N' id FROM view_3 ' + @where1 + N' ) top_requests;' SET @query = @query + N' SELECT * FROM view_3 WHERE id in (SELECT id FROM @request_list);' SET @query = @query + N' SELECT * FROM view_4 WHERE reqid IN (SELECT id FROM @request_list)' -- Added For Debug Of FillSchema set @rcquery = @query select @query as myquery EXECUTE(@query) SET @error_code = @@error IF (@error_code<> 0) GOTO error_handler RETURN error_handler: --raise error RAISERROR (@error_code,11,1) WITH SETERROR GO GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE [dbo].[up_sp_2] ( @top1 as int, -- how many requests must return @where1 as nvarchar(3500), -- the search criteria, for example "WHERE.... ORDER BY field_2 DESC" -- Added For Debug Of FillSchema @NEW_DEBUG_VAR as int, @rcquery as nvarchar(4000) output ) AS DECLARE @error_code int DECLARE @query as nvarchar(4000) SET @query = N'DECLARE @request_list table (id uniqueidentifier not null);' --- --- NEW DEBUG CODE --- IF (@NEW_DEBUG_VAR <> 0) BEGIN SET @query = @query + N' SOME BOGUS TEXT ' END ELSE BEGIN -- -- ORIGINAL CODE -- NOT DEBUG -- SET @query = @query + N' INSERT INTO @request_list (id ) SELECT id FROM (SELECT' IF (@top1<> 0 ) BEGIN SET @query = @query + N' TOP ' + CAST(@top1 AS NVARCHAR(20)) END ELSE BEGIN SET @query = @query + N' TOP 100 PERCENT ' END END -- -- END NEW DEBUG CODE -- SET @query = @query + N' id FROM view_3 ' + N' ) top_requests;' -- Added For Debug Of FillSchema set @rcquery = @query select @query as myquery EXECUTE(@query) SET @error_code = @@error IF (@error_code<> 0) GOTO error_handler RETURN error_handler: --raise error RAISERROR (@error_code,11,1) WITH SETERROR GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ======================================================== Tag: Oracle date problem Tag: 91302

We are having problems while retrieving dates from the oracle provider. If
we make a query with SQLPLUS we obtain the exact date. Using the oracle
client provider included in ado.net we obtain dates with 1 or -1
differences. For example, in the table the date is 11/Jan/2004 , and it
returns 10/Jan/2004
The problem is with the time recorded in the field.

What can the problem be???

Thanks.

Top

Re: Oracle date problem by Angel

Angel
Tue Jul 13 20:07:21 CDT 2004

Carlos, can you post some more information? Oracle Client/server version,
table schema, database language setting (are you using UTF8) how was the
data inserted?

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/

"Carlos Hernandez" <carlos@integrasofthn.com> wrote in message
news:#b7NhkTZEHA.2776@TK2MSFTNGP10.phx.gbl...
> We are having problems while retrieving dates from the oracle provider. If
> we make a query with SQLPLUS we obtain the exact date. Using the oracle
> client provider included in ado.net we obtain dates with 1 or -1
> differences. For example, in the table the date is 11/Jan/2004 , and it
> returns 10/Jan/2004
> The problem is with the time recorded in the field.
>
> What can the problem be???
>
> Thanks.
>
>

Top