Oracle Tables

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
    - 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 Tables Tag: 91308
  - 2
    - "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 Tables Tag: 91302
  - 3
    - datagrid columns from different datatables help Sorry to the mods: I posted this in asp.net forum but I think its more appropriate here? Anyways: Hi. I'm rather new to vs.net so explaining things in detail would be of great help to me. I'm working on a WebForms Project. Basically I have a datagrid I need to populate. I have it set up so that it will connect to my database (did not use the wizard btw) and I can make it display things. However I've run into a problem. I have 8 columns I need to populate but each one is from a different table. The auto populate at runtime does not do what I need it to do. Given any 8 tables (table1 through table 8) located in Database1, how would I set up my 8 columns and populate each one from a different table? In addition, how would I populate them with conditions, i.e. fill this table with values only when in the january through june months. The datatables do not share values so I dont think inner join will work Thank you. Posted Via Usenet.com Premium Usenet Newsgroup Services ---------------------------------------------------------- ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY ** ---------------------------------------------------------- http://www.usenet.com Tag: Oracle Tables Tag: 91297
  - 4
    - Lookup tables Hi There I have 3 dataadapters each relating to a table - daCompany, daAccountant, daCompanyType. These tables have foreign constraints set in the dataset schema. (Company many to Accountant, CompanyType many to CompanyType) I've databound all relevant text boxes and comboboxes however when modifying the textboxes etc. the dataset does not detect any changes. Please help! Tag: Oracle Tables Tag: 91288
  - 5
    - Slef Referencing Data Relation Problem.. I've got a strongly typed dataset, one of the tables in the dataset has a UID column ( integer, primary key ) and a TIER_ID column ( integer, nullable ). TIER_ID references UID, but when I add a data relation to do this the fill method hangs.. The top level record has a TIER_ID of NULL it seems that this is causing the problem, if I update TIER_ID on this row so its not null it solves the problem, but causes alot of junk in the dataset as somewhere along the line you have to have a row with the same UID and TIER_ID, which means you end up withodd rows in the dataset. Has anyone ever come across this ? is there a solution ? TIA Mark Tag: Oracle Tables Tag: 91286
  - 6
    - Server Error in '/test' Application. Hi, I am using an SQL parameter in my SQL statement, I have never done this before ans it seems to be retuning an error saying that the parameter '@ID' must be declared, I know I am close, could someone have a look at it form me please? cheers lads Moe Dim DS As New DataSet() Dim sqlCMD as new sqlCommand sqlCMD.parameters.add(New SqlParameter("@ID", SqlDbType.Int, 4)) sqlCMD.parameters("@ID").value = PersonID Dim strConnect As New SqlConnection(ConfigurationSettings.AppSettings("connectionString")) Dim objSQLAdapter As New SQLDataAdapter("SELECT FROM table WHERE person= @ID", strConnect) objSQLAdapter.Fill(DS, "table") Server Error in '/test' Application. ---------------------------------------------------------------------------- ---- Must declare the variable '@ID'. Tag: Oracle Tables Tag: 91283
  - 7
    - Run ado.net application from network share requires FullTrust permission set? This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C462E0.D5FA17F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is there any way to get an assembly to run from a network share with = less than FullTrust permission set? I've tried adding SQL Client = permission to the permission set that's assigned to the LocalIntranet = but that I still get a SecurityException thrown for SqlClientPermission. Here's the error: An unhandled exception of type 'System.Security.SecurityException' = occurred in system.data.dll Additional information: Request for the permission of type = System.Data.SqlClient.SqlClientPermission, System.Data, = Version=3D1.0.5000.0, Culture=3Dneutral, = PublicKeyToken=3Db77a5c561934e089 failed. I've also tried signing the assembly and granting it LocalIntranet + SQL = Client, Everything or FullTrust permissions. The only way it will run = correctly is if I give it the FullTrust permission set. Is it possible = to run an ado.net application without assigning the FullTrust permission = set? ------=_NextPart_000_0008_01C462E0.D5FA17F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.3790.118" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#d8d0c8 background=3D""> <DIV><FONT face=3DArial size=3D2>Is there any way to get an assembly to = run from a=20 network share with less than FullTrust permission set?  I've tried = adding=20 SQL Client permission to the permission set that's assigned to the = LocalIntranet=20 but that I still get a SecurityException thrown for=20 SqlClientPermission.<BR><BR>Here's the error:<BR>An unhandled exception = of type=20 'System.Security.SecurityException' occurred in = system.data.dll<BR>Additional=20 information: Request for the permission of type=20 System.Data.SqlClient.SqlClientPermission, System.Data, = Version=3D1.0.5000.0,=20 Culture=3Dneutral, PublicKeyToken=3Db77a5c561934e089 failed.<BR><BR>I've = also tried=20 signing the assembly and granting it LocalIntranet + SQL Client, = Everything or=20 FullTrust permissions.  The only way it will run correctly is if I = give it=20 the FullTrust permission set.  Is it possible to run an ado.net = application=20 without assigning the FullTrust permission set?</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV></BODY></HTML> ------=_NextPart_000_0008_01C462E0.D5FA17F0-- Tag: Oracle Tables Tag: 91281
  - 8
    - Business Layer and Data Layer application blocks Hi, A doc about a set of components making a "Business Layer and Data Layer application blocks" is available at http://www.sabbasoft.com/myBlog/PermaLink.aspx?guid=070a8c55-b885-4b89-8f3a-a23801fb732e You might be interested in reading it to get new ideas for your development process. As return I'll appreciate if you post me back any comment you might have regarding this doc. Best Regards-- Enrico Sabbadin Mail: sabbadin@sabbasoft.com Website: http://www.sabbasoft.com ******** MTS - COM+ - VBCOM - Enterprise Services - Security FAQ .NET & COM+ books selected list ******** Blog: http://www.sabbasoft.com/myblog Affiliated Sites http://www.vb2themax.com http://www.codearchitects.com Tag: Oracle Tables Tag: 91274
  - 9
    - Example Search Form Required Hi, I have a table named "Person" in a MSDE (2000) database, and am building a search form comprising of search fields on the top section (forename, surname, telephonenumber, etc) which all correspond to columns to search in the database. I'm relatively new to ADO.net and haven't the foggiest about where to start, and need a good example to take apart and learn relatively quickly. Does anyone have code or know of a good place to look? I've checked out MSDN and the free vb code sites, but no-one has a flexible search form that I could reuse. Any ideas? Kind Regards, -- Liddle Feesh *fap fap fap fap* <>< <>< <>< <>< ><> <>< <>< <>< <>< <>< <>< Tag: Oracle Tables Tag: 91273
  - 10
    - Revisiting the issue of "Filling the dataset with a table with same name as in another class" My original post asked the question: what happens when you declare a datatable with the same name in Class1 as you used in Class2? As Miha pointed out, "DataSet won't be gc-ed until you drop all references to it .. while you have at least one living reference DataSet instance will be there." And Kevin Yu answered that, "If you still hold some reference to the object, you have to fill the DataTable with other names, because the original table is still in it." Excellent advice and duly noted. Here's the issue however. I fill the DS with a dtSupply table in lets say Class1. I clear this table out of the DataSet in Class2 before filling the DataSet with a table with the the same dtSupply name as I used in Class1. The schema for these two tables differs ... but the schema for the Class1 table stays in the DataSet ALSO! So while I can populate a table in Class2 with different data for the same table, the table SCHEMA itself remains in the DataSet despite being cleared from the DataSet. From a practical standpoint, this is a non-issue with what I am currently working on, as it is easy enough to simply use a different name for the tables. But I am curious if there is a "schema-clear" method for the datatables within the dataset? Tag: Oracle Tables Tag: 91272
  - 11
    - Opening a backend database from two different applications I have a front end database application in MS Access 2000, which for purposes of discussion has an administrative part and a user part. I recently decided to experiment with trying to move the user part of the front-end, which services about 70 people to a .net application. I've implemented both a form and web version. My intent for the time being is to leave the administrative part which is more involved in MS Access which is used by only a couple of people. The problem I'm running into is that multiple instances of each application (.net or MS Access) can access the backend database using individual connections as long as only that application is used, however if either application tries to open the backend database while the other application has an open connection it will complain about the backend being locked (or file already in use). Does anybody understand why the files would only be locked files when accessed by different applications? Have I encountered a limitation with MS Access or is there a setting or work around that would allow me to use both front-end applications simultaneously? Thanks for any input Steven W Tag: Oracle Tables Tag: 91271
  - 12
    - Problems configuring DataAdapter to use stored procedure This is a multi-part message in MIME format. ------=_NextPart_000_002D_01C46297.CB232B80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am trying to use the Data Adapter Configuration Wizard to configure a = sqlDataAdapter to access a SQL Server database via the "Use existing = stored procedures" option. On the "Bind Commands to Existing Stored = Procedures" page of the wizard, setting the "Select:" dropdown to this = stored procedure does not bring up a schema, and pressing the "Finish" = button to complete the configuration generates the following messages: = "Generated SELECT statement" and "Failed to get schema for this stored = procedure." =20 Is the fact that the stored procedure uses dynamic commands built from a = string (executed via EXEC) the problem here?=20 =20 Thanks, RD ------=_NextPart_000_002D_01C46297.CB232B80 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1400" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV> <P class=3DMsoNormal style=3D"MARGIN: 0in 0in 0pt"><FONT face=3DArial = size=3D2>I am=20 trying to use the Data Adapter Configuration Wizard to configure a=20 sqlDataAdapter to access a SQL Server database via the "Use existing = stored=20 procedures" option. On the "Bind Commands to Existing Stored Procedures" = page of=20 the wizard, setting the "Select:" dropdown to this stored procedure does = not=20 bring up a schema, and pressing the "Finish" button to complete the=20 configuration generates the following messages: "Generated SELECT = statement=94 and=20 <SPAN style=3D"mso-spacerun: yes"> </SPAN>"Failed to get schema for = this=20 stored procedure."</FONT></P> <P class=3DMsoNormal style=3D"MARGIN: 0in 0in 0pt"><?xml:namespace = prefix =3D o ns =3D=20 "urn:schemas-microsoft-com:office:office" /><o:p><FONT face=3DArial=20 size=3D2> </FONT></o:p></P> <P class=3DMsoNormal style=3D"MARGIN: 0in 0in 0pt"><FONT face=3DArial = size=3D2>Is the=20 fact that the stored procedure uses dynamic commands built from a string = (executed via EXEC) the problem here? </FONT></P> <P class=3DMsoNormal style=3D"MARGIN: 0in 0in 0pt"><o:p><FONT = face=3DArial=20 size=3D2> </FONT></o:p></P> <P class=3DMsoNormal style=3D"MARGIN: 0in 0in 0pt"><FONT face=3DArial = size=3D2>Thanks,=20 RD</FONT></P></DIV></BODY></HTML> ------=_NextPart_000_002D_01C46297.CB232B80-- Tag: Oracle Tables Tag: 91269
  - 13
    - Solid ODBC Connection thru XML Web Service I'm testing a SOLID connection using the ODBC .Net Provider, from a Windows Application i put two buttons, Local and Web Service. In local i put this code: Dim sCNX As String "dsn=WebODBC;uid=xxxx;pwd=xxxx;" Dim oC As New OdbcConnection(sCNX) oC.Open() oC.Close() This code executes without problems, from the Web Service button i create a proxy pointing to a XML WS with only one method, the code is: Dim sCNX As String = "dsn=tcp localhost 1313;uid=xxxx;pwd=xxxx;" Dim oC As New OdbcConnection(sCNX) oC.Open() oC.Close() But the open method generates the next error: Microsoft.Data.Odbc.OdbcException: ERROR [IM002] Couldn't find data source name or driver not specified. Please, if somebody can, helpme. T.I.A. *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! Tag: Oracle Tables Tag: 91268
  - 14
    - Data Type Help I am wirting a VB program, and use the following code in a form load event Dim prm38 As OleDb.OleDbParameter = _ dap3.UpdateCommand.Parameters.Add("@total_points", _ System.Data.OleDb.OleDbType.Integer, "total_points") prm38.SourceColumn = "total_points" And Receive the following Error: An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll Additional information: Cast from string "total_points" to type 'Integer' is not valid. I am using an Access database field total_points is a number field type integer Help. Tag: Oracle Tables Tag: 91267
  - 15
    - DataSet DataTable Question I have a winform with a text field in it. I would like to be able to save the value in the text field to a XML file so that the next time I open the Winform I can get the value from the XML file and populate my field. I am guessing as to exactly what to do but what I have done is as follows: I created a dataset in my project by right clicking on the project and selecting add, new dataset. I called it SaveParams01.XSD. There I added an element, called it CompanyName and called it a string. I put a button on my form where my intention is to get the value in a textbox on the form called TextBox1 and then write that value out to and XML file Called SaveParam.XML. I know how to get the value from the text box and how to write the dataset to an xml file. The code for doing so I list below. But I do not know how to do the rest. Do I need to create a table and somehow associate it with the dataset? Either to the dataset or to the datatable associated with the dataset I need to add a row I suppose and assign the value from TextBox1.text to it, but even after looking at the books I do not know how to write the code to actually do this. Could someone help me. Dim CompanyName As String CompanyName = TextBox1.Text ??????? DS.WriteXml("SaveParam.xml", XmlWriteMode.WriteSchema) Tag: Oracle Tables Tag: 91254
  - 16
    - Help with Index is out of the bounds of the array issue. Hi There, I have been batleing with this now that I'm at the end of my rop here. What I'm doing here is this; 1 - retrieving some data from the database with a data reader (with a search criteria) 2 - Filling a listbox with the data retrieved with the data reader 3 - draging items out of the listbox and dropping them into a text box (simple names) The error I'm getting is on the mouse down event. When a press the left mouse button to start the drag event, it gives an error saying that the "index is outside the bounds of the array" now this is the code I'm using; Private Sub lstResultScheduler_MouseDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles lstResultScheduler.MouseDown If e.Button = MouseButtons.Left Then If lstResultScheduler.Items.Count = 0 Then Exit Sub 'invoke the drag and drop operation This is the line of code where is failing **** lstResultScheduler.DoDragDrop(lstResultScheduler.SelectedItem.ToString, DragDropEffects.Move Or DragDropEffects.Copy) ***** End If End Sub Does anybody knows why I'm getting this Problem? Thanks for your help. Manny Tag: Oracle Tables Tag: 91253
  - 17
    - Naming the Tables in the DataSet returned by stored procedure Greetings I have a SQL Server stored procedure that performs several queries and therefore creates several "result sets". Wiring it up via ADO.NET I get a DataSet with a number of items in the Tables collection. This is exactly what I want, but I'd like to assign names to these "result sets" in the stored procedure so that I don't have to access these Tables by index number in the code. Apologies if this is really a SQL Server question. Thanks Stuart (gingerbbm) Tag: Oracle Tables Tag: 91250
  - 18
    - Problem with updating database from a dataset (row recently deleted) Hi I'm using ASP.Net (VS2003). I think this is more an ADO Question. I've just deleted a row from a dataset and I want to update the database from the recently altered dataset. It's not working.... Can you skim down the code, look at the comments below please? Many thanks Alex ' 'SqlDeleteProducts ' Me.SqlDeleteProducts.CommandText = "DELETE FROM [Products Assigned to Contacts] WHERE (ProductContactID = @ProductContactID)" Me.SqlDeleteProducts.Connection = Me.SqlConnection1 Me.SqlDeleteProducts.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ProductContactID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ProductContactID", System.Data.DataRowVersion.Original, Nothing)) CType(Me.DsContactsX1, System.ComponentModel.ISupportInitialize).EndInit() CType(Me.DsContactIndex1, System.ComponentModel.ISupportInitialize).EndInit() CType(Me.DsProductsAssignedToContacts1, System.ComponentModel.ISupportInitialize).EndInit() '''''''''''''''''' 'Find position in dataset Dim rowDelete As DSProductsAssignedToContacts.Products_Assigned_to_ContactsRowrowDelete = DsProductsAssignedToContacts1.Products_Assigned_to_Contacts.FindByProduct_ProductContactID(e.Item.Cells(2).Text) ' Find position in dataset rowDelete.Delete() DGProducts.DataBind() ' This works so far, row has now visibliy deleted from my datagrid DGProducts 'From here downwards it doesn't work, I am merely trying to update the ' database from the dataset 'I get error "Prepared statement '(@ProductContactID int)DELETE FROM '[Products Assigned to Contact' expects parameter @ProductContactID, 'which was not supplied." 'It seems to ignore the very next line, 'I've tried chopping it out and it doesn't work either. 'I'm supplying the correct parameter aren't I? SqlDeleteProducts.Parameters("@ProductContactID").Value = CInt(e.Item.Cells(2).Text.ToString) DAProductsAssignedToContacts.Update(DsProductsAssignedToContacts1) Please help! Thanks Alex Tag: Oracle Tables Tag: 91249
  - 19
    - ADO.Net locking rows on read I have a VB .Net COM+ component running a stored procedure on a SQL Server 2000 DB. If the stored procedure is executed within Query Analyser it takes in the order of three seconds to complete, however when accessed from my component it hits a 5 minute timeout when retrieving the rows (in the order of 30,000 rows). Further investigation points the finger at ADO .NET locking/unlocking each row of data its returning (which Query Analyser isnâ??t doing). An example profiler from the database followsâ?¦ Lock:Acquired .Net SqlClient Data Provider user1 dom\user1 0 0 57 2004-07-05 13:29:03.380 Lock:Acquired .Net SqlClient Data Provider user1 dom\user1 0 0 57 2004-07-05 13:29:03.380 Is there some way of turning off this locking â?? it only needs to be a read-only query. Thanks Gavin Tag: Oracle Tables Tag: 91245
  - 20
    - I need advice on disconnected clients Hi all I want to develop a client which only connects to a server in order to synchronize data. The idea is that the user will see a smaller view of the whole database (only items relevant to themself) and may then add new items, update, delete, etc. Eventually connecting to a middle tier which checks their changes and sync's with the main database. I don't want the user to be online and have to "check out" items first, they should just be able to edit them immediately and then go through some kind of reconciliation process. Is ADO .NET the technology to use? Can I enforce some minor business constraints on the client using ADO .NET? Does ADO .NET have a "prohibit" for a delete rule, I see cascade etc but no prohibit? Does anyone know of any good resources I can look at? Articles, components, etc? Thank you very much -- Pete ------- Read/Write .NET articles http://www.howtodothings.com/showarticles.asp?subcategory=132 Tag: Oracle Tables Tag: 91238
  - 21
    - Full DataTime field not returned in DataSet Hi, I am filling a DataSet with a SQL command the selects certain columns from a table. One of the columns is a DataTime in SQL with a precision of 8. In the SQL Server database, the complete datatime structure is filled including hours, minutes and seconds. The problem is when I fill the dataset, the DateTime column's value is short datetime (i.e. no hours, mins, secs - only month, day , year) . Does anyone know why I'm not getting the complete datatime value? Thanks Tag: Oracle Tables Tag: 91230
  - 22
    - connection.dispose() must i call connection.dispose() or the close() method is enough, on my sql server i see many connections created. TIA, z. Tag: Oracle Tables Tag: 91228
  - 23
    - running msde200a When I attempted to run an example proved by FrameWork.NET SDK 1.1, I received this from the compiler: SQL Server does not exist or access denied. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied. Source Error: Line 38: Line 39: // Open the connection. Line 40: myConnection.Open(); Line 41: Line 42: // Assign the connection property. Source File: C:\Program Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\QuickStart\howto\samples\adoplus\executingacommand\cs\executingacommand.cs Line: 40 Stack Trace: [SqlException: SQL Server does not exist or access denied.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +484 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 HowTo.Samples.ADONET.executingacommand.Run() in C:\Program Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\QuickStart\howto\samples\adoplus\executingacommand\cs\executingacommand.cs:40 ASP.executingacommand_aspx.Page_Load(Object Src, EventArgs E) in C:\Program Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\QuickStart\howto\samples\adoplus\executingacommand\cs\executingacommand.aspx:12 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +731 This is what I receive when attempting run example. I am using virtual machine on a xp professional os. What must I do to have the the sample program see the sql database ? -- cool Tag: Oracle Tables Tag: 91221
  - 24
    - Combo Box Problem Hi Guys, I Have some strange problem while adding items to the Combo Box=2E= it shows "System=2EData=2EDataRow" as diplay Memeber=2E I am getting= data into a DataTable from Database through query "Seelct= Source,SourceID from SourceTable" and setting Valuemember as= "SourceID" and Display member as "Source" of combobox through= VB=2ENET Designer=2E then I am adding data to Combo box through a loop for that= Datatbale because I have to add a Blank row first =2E=2Elike=2E=2E combobox=2Eitems=2Eadd("") for i=3D0 to dt=2Erows=2Ecount-1 ComboBox=2Eitems=2Eadd(dt=2Erows(i)) Next after that=2E I see three rows in ComboBox with display member as= "System=2EData=2EDataRow" Any help appreciated=2E Thanks in Advance Sudhir ----------------------- Posted by a user from =2ENET 247 (http://www=2Edotnet247=2Ecom/) <Id>CME5ItGPMEe1eDw7EX4Ngw=3D=3D</Id> Tag: Oracle Tables Tag: 91218
  - 25
    - Unable to load DLL (oci.dll) I installed oracle client 9.2.0.1 on winxp.But when I open a connection to oracle server, there is an exception:DllNotFoundException: Unable to load DLL (oci.dll). I found oci.dll in "D:\oracle\ora92\bin". Can anyone help me out? Thanks. Tag: Oracle Tables Tag: 91214
- Next
  - 1
    - How to populate DataTable/DataSet with Array? Hi, How do I populate a DataTable (or a DataSet) with values from an Array? [code] Dim myObject as String Dim myTable As New DataTable ReDim myObject (10, 10) ' Populate myObject with values here ' How to do the equivalent of the following command ' myTable.Data = myObject [/code] Is the only way to do this is using a loop to collect every element and update a DataRow and insert the DataRow into the DataTable? Thanks in advance. Madan ----------------------- Posted by a user from .NET 247 (http://www.dotnet247.com/) <Id>HHnqyc0th0KY59pfgiuMBQ==</Id> Tag: Oracle Tables Tag: 91211
  - 2
    - Parameter Error I am writing a VB program, and on one particular form, I want to update a database based on a calculation, and the user inputting a number in a combo box on a form. In my table, the field names are name, 1-17, and total. Here is a sample of the parameter definitions Dim prm4 As OleDb.OleDbParameter = _ dap2.UpdateCommand.Parameters.Add("@name", _ System.Data.OleDb.OleDbType.VarChar, 25, "name") Dim prm5 As OleDb.OleDbParameter = _ dap2.UpdateCommand.Parameters.Add("@1", _ System.Data.OleDb.OleDbType.Integer, "1") all the way to 17, and total. 1st question: When I run the program, the user enters 1 in the combo box, I receive: Parameter ?_2 has no defaut value. I want the program to leave the value in field 2 in the database alone. 2nd question: I dont want to write 17 sets of code for the 17 options a user can choose. I want to write a statement something like: drw2("1") = xx but I want the 1 to be cboChoice.Text, but I do not know how to format the command. Please help. Tag: Oracle Tables Tag: 91209
  - 3
    - "Internal Error, not fully constructed" error Hello, I am having a problem connecting to SQL server from Visual Studio.NET. When making a data connection, in the "Data Link Properties" window I add the name of the server, my username and password and I select my database from the drop-down list. I click on "Test Connection" and it says "Test Connection succeeded". Good! I click "OK" and get the SQL Server Login window. I enter my username and password. Then when I click OK I get a message box saying: "Internal error, not fully constructed". I am running Visual Studio.Net Professional on Windows XP Home Edition. Any clues very much appreciated. Andrew. Tag: Oracle Tables Tag: 91206
  - 4
    - unable to connect to Database over a network Hi, I am trying to connect to a remote SQL Server 2000 on a local lan in C#. I have tried the following connection string: connectionString = "DSN=empex;SERVER=SARK011/Test;DATABASE=empexchange;Network=DBMSSOCN;Address=172.21.1.11;UID=anair;PWD=anair;"; and connectionString = "Provider=SQLOLEDB;Data Source=empex;Initial Catalog=panchayatNetwork=DBMSSOCN;;User Id=panchayat;Password=panchayat" With both of this I get the following error: Index#0 Message:[DBNETLIB][Connection open(Connect()).]Specified SQL server not found. Native:6 Source:Microsoft OLE DB Provider for SQL Server SQL:08001 I am able to connect to both the database thru the ODBC Data Source Administrator tool. What am i doing wrong? I able to connect to the NorthWind database with any problem. I am able to connect to the other two databases from VB6 and Java thanks, Arun Tag: Oracle Tables Tag: 91202
  - 5
    - DataRelation does not support LEFT JOIN. Is this true? Hello folks, we have been trying to use DataRelation that returns LEFT JOIN result instead of INNER JOIN which seems to be the default behaviour. Is there a way to do this? TIA, Toni Tag: Oracle Tables Tag: 91200
  - 6
    - determine metadata for detached DataRow is it possible to determine the metadata of a DataRow for the case DataRow.RowState = DataRowState.Detached Of course, in other cases DataRow.Table can be accessed .... Tag: Oracle Tables Tag: 91199
  - 7
    - identities and dataset... Hi all... got a quick question. I read in XML to a application dataset variable. since i dont have access to SQL server i decided to avoid Access and go strait to XML. Problem i'm having is that when adding a new row i get a constraint issue. I havent found how to get the PK stored in XML to the point the dataset recognizes so i do the following after the XML load. x[0] = theDataStore.Tables["tblArticles"].Columns["ArticleID"]; x[0].AutoIncrement = true; x[0].AutoIncrementSeed = 0; x[0].AutoIncrementStep = 1; theDataStore.Tables["tblArticles"].PrimaryKey = x; When i try a "newrow()" i get the following. Column 'ArticleID' is constrained to be unique. Value '1' is already present. I didnt get this when i added articleID 1, or when i did articleID 0... but it seems that the dataset object doesnt see that ArticleID 1 is already in the DataSet... Here is the insert code... public zArticle addArticle(int UserID, int CategoryID) { DataRow DR; zArticle theArticle; DR = theDataStore.Tables["tblArticles"].NewRow(); DR["UserID"] = UserID; DR["CategoryID"] = CategoryID; DR["Headline"] = ""; DR["Body"] = ""; DR["Date"] = DateTime.Now.ToString(); theArticle = new zArticle(int.Parse(DR["ArticleID"].ToString()), int.Parse(DR["UserID"].ToString()), int.Parse(DR["CategoryID"].ToString()), DR["Headline"].ToString(), DR["Body"].ToString(), (DateTime) DR["Date"]); theArticle.update += new zArticle.updateObject(this.updateArticle); theDataStore.Tables["tblArticles"].Rows.Add(DR); saveDataStore(); return theArticle; } Tag: Oracle Tables Tag: 91198
  - 8
    - Do TableStyles and DataView conflict?? Using design i created several tablestyles for a datagrid whose source was originally set to DataSet. Later i decided to put in a dataView instead as the source, but now the designer does not allow for selecting among different tableMappings as long as the DataView is the Source (the original ones are still there). Will this create a problem? should the dataview be bound programmatically instead? Tag: Oracle Tables Tag: 91197
  - 9
    - Strange Index out of range exception Hi all, i have a strange index out of range exception Stack Trace "System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index at System.Collections.ArrayList.get_Item(Int32 index) at System.Data.DataTable.RecordStateChanged(Int32 record1, dataViewRowState oldState1, DataViewRowState newState1, Int32 record2, DataViewRowState oldState2, DataViewRowState newState2) at System.Data.DataTable.SetNewRecord(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge) at System.Data.DataTable.DeleteRow(DataRow row) at System.Data.DataRow.Delete() How Im using a win grid that is bound to a datatable with a child related to it, it happens when i modify something in the row of the grid the execution enter in an event handler of the aftercellupdate, this sub delete all the rows of the child and after insert new rows with the data related to the selection that was made. The Code /***********************************************************/ grdRubros.UpdateData() Try For i = Me.ds.ItemsTable.Count - 1 To 0 Step -1 If Me.ds.ItemsTable(i).RowState <> DataRowState.Deleted Then Me.ds.ItemsTable(i).Delete() End If Next ' Then i get the items from the database For contUN = 0 To dsMetodoDistribucion.MetodoUnidadNegocio.Count - 1 Dim drUnidad As ItemsTableRow = ds.ItemsTable.NewItemsTableRow drUnidad.SetParentRow(drAbonoRubro) drUnidad.IdAbonoRubro = drAbonoRubro.Id drUnidad.IdUnidadNegocio = Me.dsMetodoDistribucion.MetodoUnidadNegocio(contUN).IdUnidadNegocio drUnidad.Porcentaje = Me.dsMetodoDistribucion.MetodoUnidadNegocio(contUN).Porcentaje drUnidad.SubTotal = Decimal.Round(CType(drUnidad.Porcentaje * TotalLinea / 100, Decimal), 2) drUnidad.TotalImpuestoPorPagar = 0 drUnidad.TotalImpuestoPorRetener = 0 drUnidad.TotalPagado = 0 drUnidad.TotalPagadoBase = 0 drUnidad.Total = drUnidad.SubTotal drUnidad.TotalPorPagar = drUnidad.SubTotal drUnidad.FechaRegistro = Now drUnidad.Usuario = SIEnviroment.Login Me.ds.ItemsTable.AddItemsTable(drUnidad) Next Catch ex As Exception Throw ex End Try Strange!!!!!! When i debug this code the table always is in range, but sometimes throw an exception in the delete. when the exception is thrown the state of row that is actually deleting change from added to detached Thank for the help Tag: Oracle Tables Tag: 91194
  - 10
    - Binding to custom data class I am relatively new to .net programming and am developing a client/server app for a client and have a question concerning databinding controls. My question is can I write a custom class to handle database operations and still be able to bind controls to the datasets contained in the custom class much the same way that I can bind a control to a dataset that I have embedded in the form. TIA, Bill Youngman Tag: Oracle Tables Tag: 91193
  - 11
    - Dataset disappearing?? why?? after making changes in the properties window of a dataset.table, i lose dataset. " Could not find type 'DataSet1'. Please make sure that the assembly that contains this type is referenced. If this type is a part of your development project, make sure that the project has been successfully built." Tag: Oracle Tables Tag: 91192
  - 12
    - manipulating DataTable for manipulating a (disconnected) DataTable , how to iterate through the rows ? how to insert a row ? update a row ? delete a row ? how to perform queries of the DataTable , such as filters and sorts , to return another DataTable ? Tag: Oracle Tables Tag: 91186
  - 13
    - Catching A Db Timeout Exception Is there a specific exception thrown for timeouts occurring from a database call? If not, does anyone have a recommendation on how I can write a loop to run say three times if a timeout does occur. Thanks Tag: Oracle Tables Tag: 91181
  - 14
    - SQL Desktop Engine and ADO.NET question Hello, I recently installed MSDE and the .NET QuickStart tutorials. I'm trying to write an app that uses the Northwinds database installed on my local host. As I understand it, I should be able to drag the database from the Server Explorer to my form. Is this incorrect? I've also tried dragging the SqldataAdapter object from the tool box to my form and opening a connection that way, but the wizard is never able to find the database. I'm using the SQL OLEDB provider. What am I missing or doing wrong? Thanks in advance. Tag: Oracle Tables Tag: 91175
  - 15
    - datetime Hi How can I separate a datetime field value into date & time values? Conversely how can I combine date & time values into a value for a datetime field? Thanks Regards Tag: Oracle Tables Tag: 91171
  - 16
    - HERE WE GO AGAIN, Visual Web Developer 2005 Express SCREWS UP YOUR CODE on PURPOSE...STOP TOUCHING MY CODE, MICROSOFT!!!! DEAR MICROSOFT: STOP TOUCHING MY CODE!!!!!! Stop ADDING these attributes to my tags meta:resourcekey="LiteralResource3" Your Web Developer 2005 express added that crappy and unneeded attribute to all my tags on my aspx page...you even added that attribute to the @page directive. The people who actually do work might have to to bounce back to a better HTML editor and NOT use 2.0 OR I don't even want that attribute in the first place as I don't place to use .resx files anyway..... GET IT THROUGH YOUR STUPID HEAD THAT YOU ARE NEVER TO PUT CODE WHEN I DON"T KNOW ABOUT IT, NOR SHOULD I HAVE TO GO ON A SCAVENGER HUNT TO FIND IT IN THE SETTINGS JUNGLE. Who has responsible for doing this on the Microsoft team? ONe of number one things Microsoft market was trumpeting was that VS.NET 2005 wasn't going to touch your code............... DON'T DO THAT!!! GOT IT!!!!!! NOW I HAVE TO GO AND REMOVE IT FOR ALL THE TAGS ON MY PAGE IF I WANT TO USE .NET 1.1 COMPRENDE!!!!!!! IT'S NO WONDER WHY ASP.NET hasn't been embraced by a significant number of asp developers, Old ASP developers can get more done using NON-MICROSOFT TOOLS..... DROP THE CRAPPY WEB PORT AS WELL..that's stupid, takes up resources and just make debugging that much more difficult. LOCALHOST:12345 <-- do we have to do it this way? Just show them how to set up IIS correctly. PHP, ASP, ColdFusion, Java all know how to do this manually, Why does microsoft have to screws things up again.. Tag: Oracle Tables Tag: 91160
  - 17
    - Datetime to date & time & back Hi I am using and access datetime field to store both date & time. How can I split it into date & time after reading the field and then combine the date & time back into datetime when storing it? Thanks Regards Tag: Oracle Tables Tag: 91158
  - 18
    - Populating a dataset with a single select (with join) I have the following datamodel (implemented in SQL Server) header -< detail That is, I have two tables, "header" and "detail". There is a foreign key from detail to header so that for each header there can be many details. I have created a corresponding (strongly typed) dataset, with the two tables and the relationship. (To be perfectly clear: one dataset containing two tables) Now, due to programmatic and performance reasons, I would like to populate BOTH the tables in the dataset with a SINGLE sql select statement (containing a join and further conditions). That is, I would like to populate both tables using a single select statement similar to this: SELECT Header.*, Detail.* FROM Header INNER JOIN Detail ON Header.ID = Detail.ID WHERE (Detail.Foo > 52) Is this possible using ADO.NET (or DAL for that matter)? How? Thanks, Klas Mellbourn Tag: Oracle Tables Tag: 91154
  - 19
    - Find out which row/constraint triggers the constraint exception When a dataset is modified and a constraint is violated, then there seems to be no way to find out which row/constraint violated the constraint. I get a System.Data.ConstraintException. However, there is no Row or Constraint property that informs which row violated which constraint. This is especially hard, when you need to debug complex queries that are used in a DataAdapter. Is there a workaround to find out, which row/constraint triggers the constraint exception? It would be a great addition for the new ADO.NET v2.0 when this information will become available. -- Greetings, Ramon de Klein Tag: Oracle Tables Tag: 91151
  - 20
    - Static methods and local data (Again) Hi everyone, I'm still abit confused about static methods accessing locally declared variables. For example public static bool executeNonQuery(){ SqlCommand cmd; SqlConnection con; .... } Is the above threadsafe? Can the cmd or con object be accessed by more than one thread at the same time. What if the code was: public static bool executeNonQuery(SqlCommand cmd){ SqlConnection con; cmd.Connection = con; .... } Where the command object is also created in a static calling method? Many thanks all Simon Tag: Oracle Tables Tag: 91149
  - 21
    - connection pooling on NT 4.0 Hi, we have a VB.NET application that connects to an Oracle 9i database with no problems using windows XP. On an NT 4.0 (sp6a) box however we get an error in the application "ResourcePool not supported on this platform". We have worked around this problem by using pooling=false in our connection string. 1) Is there any way we can "enable" pooling to work on NT4? (i.e. any addon software we are missing for NT4). 2) Are there any other known problems deploying to NT4 that we should be aware of? Many Thanks Carlo *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! Tag: Oracle Tables Tag: 91147
  - 22
    - ADO.NET DataSet to ADO RecordSet I have an DLL developed in VB.NET which returns Dataset and DataSet with Relations. Through InterOp I am using that DLL in VB 6 and the Return Parameter for the DLL function showing as UNKNOWN ( Actually DataSet ). Can any one help me how to assign DataSet returning from function to Record Set. Thanks in advance Sreekanth D Tag: Oracle Tables Tag: 91143
  - 23
    - Visual Web Developer 2005 Express SCREWS UP YOUR CODE on PURPOSE...STOP TOUCHING MY CODE, MICROSOFT!!!! DEAR MICROSOFT: STOP TOUCHING MY CODE!!!!!! Stop ADDING these attributes to my tags meta:resourcekey="LiteralResource3" Your Web Developer 2005 express added that crappy and unneeded attribute to all my tags on my aspx page...you even added that attribute to the @page directive. The people who actually do work might have to to bounce back to a better HTML editor and NOT use 1.1 OR I don't even want that attribute in the first place as I don't place to use .resx files anyway DON'T DO THAT!!! GOT IT!!!!!! NOW I HAVE TO GO AND REMOVE IT FOR ALL THE TAGS ON MY PAGE IF I WANT TO USE .NET 1.1 COMPRENDE!!!!!!! Tag: Oracle Tables Tag: 91141
  - 24
    - update using relationships Hi all, I have 2 tables and a relatioship. Clients, house, clientshouse I also have a form with many text controls bound to the relationship. THis controls are fields of table house. After I made changes to the data I want to update the table house this.daHouse.Update(this.dsCM1,"house") But it didn't update the table??? am I missing something???? Tag: Oracle Tables Tag: 91136
  - 25
    - Exceptions in IConfigurationSectionHandler.Create() Hi all, I'm writing my own custom config section handler. Since I'm parsing my custom part of web.config in Create(), I'd like to throw some meaningful errors if a user has made a mistake. However, no matter what kind of exception I throw, the ASP.NET application will always show some generic error message, such as: "Exception in configuration section handler" Is this by design? Thanks. -Oleg. Tag: Oracle Tables Tag: 91135

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

Top

Re: Oracle Tables by Roy

Roy
Wed Jul 07 06:19:34 CDT 2004

Paula,

yes, you can create tables using SQL DDL commands.

Oracle does not have an auto increment data type as found in SqlSever.
Instead, with Oracle you would use a trigger and a sequence to achieve the
same results.

regards
roy fine

"Paula" <baila_pau@hotmail.com> wrote in message
news:8f340d3b.0407061058.152592e9@posting.google.com...
> 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

Top