run an SQL subsequently after another.

[candice]

Hi, friends
I got 2 SQL here and the second must be excuted after the execution of the first.
SQL1 : alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL2: select...from...where... and b.changedt >= '2007-03-01 00:00:00' and b.changedt < '2007-03-16 00:00:00'...;

In sqlplus these two above work well.
But in my code, I am not able to let SQL2 excute after SQL1. Maybe becuase SQL1 executed in another enviroment different from SQL2, or maybe SQL1 didn't execute at all.

[SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data[/SIZE]
[SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.OracleClient[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Public [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] Form1[/SIZE]
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] bindingSource1 [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] BindingSource()[/SIZE]
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] dataAdapter [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleDataAdapter()[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataTable[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] connectionString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = _[/SIZE]
[SIZE=2][COLOR=#800000]" Data Source=singdb;User ID=mfg_rpt;Password=******;Unicode=True;"[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myConnection [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleConnection(connectionString)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Public [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] CreateMyOracleCommand([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] myExecuteQuery [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]myConnection.Open()[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleCommand(myExecuteQuery, myConnection)[/SIZE]
[SIZE=2]myCommand.ExecuteNonQuery()[/SIZE]
[SIZE=2]dt = GetData([/SIZE][SIZE=2][COLOR=#800000]"SQL 2"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]myConnection.Close()[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]Public [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][SIZE=2] GetData([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] selectCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataTable[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable()[/SIZE]
[SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dataAdapter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleDataAdapter(selectCommand, connectionString)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] commandBuilder [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleCommandBuilder([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dataAdapter)[/SIZE]
[SIZE=2]dt.Locale = System.Globalization.CultureInfo.InvariantCulture[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dataAdapter.Fill(dt)[/SIZE]
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception[/SIZE]
[SIZE=2]MessageBox.Show(ex.Message)[/SIZE]
[SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Return[/COLOR][/SIZE][SIZE=2] dt[/SIZE]
[SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]Private [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button1_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button1.Click[/SIZE]
[SIZE=2][COLOR=#0000ff]Call[/COLOR][/SIZE][SIZE=2] CreateMyOracleCommand([/SIZE][SIZE=2][COLOR=#800000]" SQL1"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
 
[COLOR=#0000ff]End Class[/COLOR]

The exception being caught is "literal does not match format string". It's the same error if I directly run SQL2 in sqlplus without SQL1.
Could any body help me out? Thank you in advance!

 

[cjard]

If you wrote your queries properly, you wouldnt need to do that..

First, you DO THIS ONE TIME when your app starts up:

Dim oc as OracleCommand = new OracleCommand("select...from...where... and b.changedt >= :pFromDate and b.changedt < :pToDate", oConn)
oc.Parameters.Add("pFromDate", OracleType.DateTime)
oc.Parameters.Add("pToDate", OracleType.DateTime)

then EVERY TIME you want to run the query:

oc.Parameters("pFromDate").Value = my_vb_datetime_representing_first_mar_2007
oc.Parameters("pToDate").Value = my_vb_datetime_representing_16_mar_2007
oc.ExecuteXXX..

naturally, you replace my_vb_datetime_representing_first_mar_2007 with a VB DateTime object representing 1st mar 2007 etc..

Never, ever, ever pass numbers, dates, and other things that are not strings, as strings.. and certainly dont alter the date format for the database (think of it like losing weight; you eat healthier and excercise more, you dont change the gravitational constant of the earth so that your existing mass weighs less)

Take a read of the DW2 link in my sig - youll find a way of having the IDE write all this data access code for you. It makes a better, more thorough, well encapsulated job of it in about 15 seconds than any human programmer could do in a week..