DB/Reporting :: .Net/Oracle Application Best Design When Building Sql Command Parameters

Oct 2, 2009

Currently working on a web-service with Oracle10g as back-end. Here is my problem.

1. The table I need to populate has 100 columns. I have no control over the design of the table.

2. I wanted to use the "deriveparameters" method of OracleCommandBuilder, but found out that this is "costly" in terms of performance. What I initially planned to do was to derive the parameters from an open connection, close the connection, then maybe cache the parameters obtained but I don't know if this is possible.

I am unsure how big of a hit the performance will get if I continue using the "deriveparameters" method WITHOUT caching the parameters driven. The environment is as follows

-intranet
-around 2000 users
-users spread out in different parts of the country

I am not familiar where they will put the web pages of the application, the web service and the database. I don't know if they'll be putting it in different servers or on one server, but that's something we cannot control too, that's why I am looking for the best approach.

I just don't want to hard-code all that parameters and if, for instance, we need to change some properties/parameters, it will be a pain to maintain the code. It would be nice if someone here can point a way to automate the whole thing w/o the performance of the system taking a hit.

View 3 Replies


ADVERTISEMENT

Sql - Select Oracle With Parameters

May 27, 2012

I've been looking for the right answer how to select with parameters and execute it in VB.NET.

View 1 Replies

Oracle - OracleCommand Parameters For In Clause?

May 8, 2012

In vb.net, how can I parameterise a list of values (strings in this case) to be used as an IN clause?

sSQL = "select foo_id, foo_type, foo_name from tbl_foos where foo_type in (:fooTypes) "`
`ocmd.CommandText = sSQL`
`ocmd.Parameters.Add("fooTypes", OracleType.VarChar).Value = "'Big','Small'"

View 1 Replies

Using Parameters With An Oracle ODBC Connection?

May 1, 2009

I'm connecting succesfully to an Oracle 10g DB with an the Microsoft ODBC for Oracle driver.Regular queries without parameters work fine, but parameterized queries act as if the parameters aren't getting passed in.ex.

--this works fine

Select * from tbl1 where column1 = 'test'

--this doesn't

select * from tbl1 where column1 = ?
--odbc string parameter 'test'

Here's what my connection string looks like:

"Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _

[code]....

View 3 Replies

Asp.net - Building A Valid SQL Query With Optional Parameters For Search Function?

Oct 20, 2011

I have a search function de build.We are using pure ASP.NET w VB.NET We have multiple DropDownLists and we're building a search query with whatever was selected in those DDLs. My question is, how can I handle the blank values (unselected dropdownlist values) with the SQL Query ? I'm using AND operators in the query so if anything is blank it'll fail the search. If the dropdownlist has no selected value, i don't want the value to be part of the search. It would be easy to code with just 2-3 parameters, but we're looking thru at least 10 items and doing a SWITCH CASE or multiple IFs would soon become mayhem.

View 1 Replies

DB/Reporting :: Get Data From Oracle And Sql In One Sql Statement

Nov 6, 2009

I will try and explain my problem as simply as I can

I have an Oracle database (external app) that contains a number of tables that I need to get data from, however I also have an SQL database that contains a number of tables that my app is using.

e.g. Oracle Table name PD_112

Structure:

CODE:

What I want to do is get all the data from the Oracle table to display in a list control however I want the values in the DISP_ID column of the Oracle table to be change to the corrosponding DESC values from the DispTbl e.g. 5 from the Oracle table would display Piping instead of 5.

I know how to do this if both the tables where in one database system e.g. Oracle or SQL, however I am not sure the best why to do this when the tables are in 2 different data sources.

Below is the code for doing this in the same datasource:

Code:

I am using VB.Net 2005, just for info

View 2 Replies

DB/Reporting :: Get Data From Oracle And Sql In One Sql Statement?

Feb 18, 2011

I have an Oracle database (external app) that contains a number of tables that I need to get data from, however I also have an SQL database that contains a number of tables that my app is using.

e.g. Oracle Table name PD_112

Structure:
ID DISP_ID
1 5
2 8
3 5

[code]....

View 2 Replies

DB/Reporting - Simple DB Steps - Building Two Forms With One To Many Relation

Aug 4, 2009

With a 1-many relation. Ok, I have constructed the schema, and now I want to make the appropriate forms in which a user would scroll for a customer and then by pressing a button, a new form would appear showing all the orders that he has made.

I am interested in the way of building two forms that would have a one to many relation. That is basically what I would like to know. Also, Do you have any good book to suggest in order to create a real world app, in VB2008, Emphasizing in data-binding and connecting controls with underlying DB, one to many forms, searching etc.

View 6 Replies

Oracle Command Creating Database?

Mar 15, 2012

I have this in creating table but did not work. Having invalid table name error.

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Public Class dump

[code]......

View 3 Replies

DB/Reporting :: Oracle Database Filtered Search?

May 14, 2009

Im writing a simple VB program that connects to an oracle DB which retreives and inserts data to a table I created.[code].....

I want to be able to search through a table I have created to store all the information.Rather than just searching for one variable which would be easy to do, I want to give the user the option to filter out the search from the form.So say the user hits "Find", they would be given access to 10 comboboxes, they would select the information they want to search for (They do not have to select every box) and then click "Do Find".Then the form would display all the results of the query in the form.

View 1 Replies

Oracle Update: SQL Command Not Properly Ended?

May 7, 2012

I have the following code in a VB .NET application. I am trying to update columns in an Oracle table with data with SQL data. When I run the application, I get (ORA-00933: SQL command not properly ended) for the 'or_cmd_3.ExecuteNonQuery()' line.

If I strip out the code and run it in TOAD or SQL Developer, replacing the temp varialve with some bogus data it updates fine. What am I missing?

ElseIf (oracle_summary_temp = ueio_tmpALM_Summary) And (oracle_request_ID_temp = ueio_tmpALM_ID) And added_to_alm = "1" AndAlso ({"Deferred", "Rejected", "Closed"}.Contains(ueio_tmpALM_Status)) Then
Dim update_oracle As String = Nothing

[Code]....

View 2 Replies

Passing Parameters To Usercontrol In Design Time?

Jan 10, 2011

I'm developing a form with usercontrols which have to be edited during disign.The project has a connectionstring in de my.settings.The usercontrol has a property which is also called connectionstring.Now, I want to pass the project connectionstring to the usercontrol connectionstring, when the usercontrol is dragged to a form. (All during design)

View 2 Replies

Getting A Free Reporting Tool Compatible With Oracle Db And Vs Express?

Feb 4, 2010

anyone know a good free reporting tool compatible with oracle database and visual studio express?

View 3 Replies

DB/Reporting :: Stored Procedure On An Oracle Database Called : GET_HVE_PACKAGE_VERSIONS

May 29, 2008

I have a stored procedure on an oracle database called : GET_HVE_PACKAGE_VERSIONS This basically calls versions of PLSQL packages as stores the versions into a table. I am having big problems trying to call this procedure. I have tried everything. I am currently able to create and update etc etc using the executeNonQuery method and I thought that I would be able to call a procedure them same way, obviously not

the whole project depends on me being able to call procedures against the database.

[Code]...

View 5 Replies

Insert,delete,update,and Create Command For VB2008+oracle 10g?

Apr 13, 2010

i have problem on how to insert the data into the oracle database 10g using visual basic 2008

My coding is like at below:Dim cmd211 As OleDbCommand

cmd211 = New OleDbCommand("INSERT INTO STUDENT_DETAIL(STUD_ID,STUD_NAME) VALUES(@STUD_ID,@STUD_NAME)", con)

.Add("@STUD_ID", OleDbType.VarChar).Value = txt_studId.Text

View 2 Replies

DB/Reporting :: Copy Oracle 11g Which Is Fully Installed - Work With VB Express 2008 ?

Oct 3, 2009

I have a copy Oracle 11g which is now fully installed. Will it work with Visual Basic Express 2008 or just Visual Studio 2008 Professional? I have both Visual Basic Express 2008 and Visual Studio 2008 Professional. I really would like to know if it works with Visual Basic Express 2008.

View 2 Replies

DB/Reporting :: Provider Cannot Be Found - Code Attempting To Access An Oracle Database

Mar 29, 2012

I recently upgraded my system to Windows 7 64bit and ever since then I have had nothing but issues with my code attempting to access an Oracle database. I was using msdaora for my connection string however that does not work in 64bit. I changed over to OraOLEDB.Oracle which everything I can find on Google says to use however it fails every time. I have installed Oracle 11g 2 times now and even tried reinstalling VB.net 2010 and still no luck.

Here is my connection string....

Code:
MyConn.ConnectionString = "Provider=OraOLEDB.Oracle;Server=emsp.grhq.XXXX.com;Data Source=emsp;User Id=XXXX;password=XXXX"

And I am inporting....

Code:
Imports System.Data.OracleClient

Yet when I try it says...

Quote:

Provider cannot be found. It may not be properly installed.

View 10 Replies

DB/Reporting :: Relation DB Design?

Apr 21, 2010

I am designing a system for our sales team to enter their sales expenses. I have designed an input form and need some advise on what exactly I should store in the expenses table VS lookup from the employee table.

First, the form has a text box where the user must enter their EmployeeID. Management asked for a text box VS a combo box because they do not want users looking at each others expenses. The form has a search feature where by after you enter your employee number, it will look for an existing expense ticket first before allowing you to create a new one. Anyway, here are some of the decisions I have to make.

When the user enters their employee number, I would like to display their employee name. The EmployeeID is saved in the Expense table but should I also store the name in the expsense table? If I do save it, it's duplication of data, if I do not, I have to look it up each time the record is loaded. But is there a point storing the name long term? Does this qualify as related data? If I was to perform some reporting, could this cause a problem if an employee changes their name etc.

The expense data is spread over 2 tables, the first for the header details including employeeID, expense date and Expense ID etc. The second table contains the expense lines information. Amount, customer, category etc. My next question is when the employee chooses an expense category, there is an associated general Ledger code with that category. Again, the expense category data is stored in a table. When the user chooses the expense category, should I store the categoryID or store the values associated with the ID (GLAcct, CategoryDescription etc.) Does this qualify as related data or should lookup values always be duplicated in the master table? It is possible that over time, the GL accounts associated with the Expense categories could change. So for accuracy purposes, should they be stored in the expense lines table?

View 2 Replies

DB/Reporting :: Use Parameters In Report?

Jun 22, 2008

Finally I can create a report using the crystal report viewer.How can I add a parameter or base a report on a query.

View 3 Replies

Adding Parameters To Sql Command?

Dec 1, 2009

I am running visual basic 2008 express. When I run the code that I have written it gives me the the following error message

- Incorrect syntax near '?'.

Does anyone know what causes this error. My code is as follows -

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb

[Codee].....

View 9 Replies

SQL Command Object Parameters?

Feb 10, 2010

I have been working on this all day and still haven't had any luck at getting it to work. I am using the SqlDataReader Object to retrieve data. I am using the code below to send a value to the stored procedure.The problem is that the value is sent enclosed in quotes so I dont get any data back. I have tried using .text.replace("""","")

View 9 Replies

DB/Reporting :: Using Parameters With Multiple SQL Statements?

Jun 25, 2009

I have a connection to a MS SQL database, which I understand is capable of handling multiple SQL statements in one Execution, ie

Code:
Using cmdExe As New System.Data.SqlClient.SqlCommand
sSQL = "Delete * from mytable where UniqueID=123;Insert into mytable (UniqueID, Field) values (123, 'something')"
cmdExe.CommandText = strSingleSQLLine
cmdExe.ExecuteNonQuery()
End Using

will delete from "mytable" and insert a new record all at once. This is great, but I want to expand upon that to use parameters because using string concatenation in my SQL queries is a bad idea. Now my question is this. If I add a parameter which is used in multiple SQL statements, do I have to add the parameter VALUE twice to the command object? ie, will the following code work?

Code:
Using cmdExe As New System.Data.SqlClient.SqlCommand
sSQL = "Delete * from mytable where UniqueID=@uniqueID;Insert into mytable (UniqueID, Field) values (@uniqueID, 'something')"
cmdExe.Parameters.AddWithValue("@uniqueID", 123)

[code]....

There are two questions here actually...can I add the parameters before setting the command text, and do I have to add the same parameter over and over again?

View 1 Replies

Created Checkboxes To Add Parameters To Command

Oct 16, 2009

i am running a dos command. now i created checkboxes to add parameters to the command. [code] but it only gives me a 1 or 0 but i want the parameter -h, -t, -v when the checkbox is checeked.

View 10 Replies

Splitting Command Line Parameters?

Apr 7, 2011

I am passing some comma separated parameters to console Application. I want to split those string for that I have written below script but it is giving error .

static void Main(string[] args)
{
Console.WriteLine("Command Parameters :" + Environment.CommandLine);

[code].....

View 2 Replies

Syntax For Shell Command With Parameters?

Sep 9, 2009

I am trying to setup a variable in VB, which will execute a shell command (autostatus.exe) with a parameter (-j JobName), and later display the output of the said variable. The code thus far is...

Dim JobName
Dim JobStatus
JobName = "asysr11_set_global_var_2day"
JobStatus = ShellExecute("autostatus.exe", -j, JobName)

What is the easiest way to achieve this? Currently, the above code returns error message "line:4, Type mismatch: 'ShellExecute', code: 800A000D"

View 1 Replies

VS 2008 How To Set Two Command Line Parameters

Dec 23, 2009

as i need add the two commandline parameter on debug window.
/url=
one is okay

[code].....

View 2 Replies

DB/Reporting :: Update Error: No Value Given For One Or More Required Parameters

Feb 24, 2008

I'm getting this error, Update error: No value given for one or more required parameters, which seems to indicate a variable is not making it to Access or is empty. My procedure is meant to update a product record in the database.

Code:
Private Sub btnChange_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChange.Click
Dim cmdOrderEntry As OleDb.OleDbCommand
Dim strSQL As String

[code].....

View 2 Replies

DB/Reporting :: Created Columns For The Datagridview At Design Time

Mar 5, 2009

Am using VB.net 2005, and I've created columns for the datagridview at design time. But when I run the code the fill method of the DataAdapter, the columns I created are not filled with data, but automatically generated columns appear. How can the columns I created at design time be filled with data?

View 1 Replies

DB/Reporting :: Making Design Changes To Table In Live Environment

Apr 12, 2012

In a program I am creating, i want to let the user add and remove fields on an entity, lets say "people". So I have a table named "People", with ID and Name columns. Now, I want to let the user choose "add field" in a webpage, define for instance "email" field, and then use "ALTER TABLE" from the codebehind to create the new field in the table. What I want to know is: is this recommended? It seems like a simple way to do this, but since I have never seen any other programs doing it like this (Using ALTER TABLE statements from codebehind in a live environment) - and since it changes the design of the DB -

View 3 Replies

Command Parameters Procedure Or Function Has Too Many Arguments Specified

Nov 4, 2009

I don't know where the extra character 'N' is coming from, there should only be one parameter coming from my SP which is the ID:

cmd1 = oStringConnection.SetCommand(sqlConnectMain, _
cmd1, "TestStoredProc", _
DBConnections.myType.cmdAsProc)

[code]....

When I ran the SQL Profiler, I found this:

exec TestStoredProc @StudentID=N'12345'

View 2 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved