Oracle, MySQL, Sybase, Informix and other databases

Importing data from Excel

2013-06-18T14:50:45.297-04:00

Hi all.

I'm importing some data from Excel. I open it as an ole database and save the data into a dataset using an sql adaptor.

However, some values I'm expecting to be integers (1) are coming up as dates "#1/1/1900#". The cells in the excel spreadsheet are set as general and are just numbers and not dates.

Any ideas on resolving this?

My code:

Dim SQL As String = "SELECT TOP 25 * FROM [Total$]"
        Dim MyOleDBCommand As New OleDbCommand(SQL, con)

        Dim adaptor As New OleDbDataAdapter(MyOleDBCommand)

        Dim MyDataSet As New DataSet

        adaptor.Fill(MyDataSet, "Total")

         
        For i = 0 To MyDataSet.Tables(0).Rows().Count - 1
            If i > 10 Then
                Dim StoreNo As String = MyDataSet.Tables(0).Rows(i).Item("F2").ToString()
                Dim CustomerName As String = MyDataSet.Tables(0).Rows(i).Item("F3").ToString()
                Dim OrderReceivedDate As Date = ConvertExcelDateType(MyDataSet.Tables(0).Rows(i).Item("F4").ToString())

                Dim PONo As String = MyDataSet.Tables(0).Rows(i).Item("F5").ToString()
                Dim InvoiceNo As String = MyDataSet.Tables(0).Rows(i).Item("F6").ToString()
                Dim ContainerNo As String = MyDataSet.Tables(0).Rows(i).Item("F7").ToString()
                Dim ShipmentDepart As Date = ConvertExcelDateType(MyDataSet.Tables(0).Rows(i).Item("F8"))
                Dim ShipmentArrive As Date = ConvertExcelDateType(MyDataSet.Tables(0).Rows(i).Item("F9"))
                Dim ProductionTime As Decimal = Convert.ToDecimal(MyDataSet.Tables(0).Rows(i).Item("F10"))
                Dim OrderToPC As String = MyDataSet.Tables(0).Rows(i).Item("F11").ToString()
                Dim OrderOption As String = MyDataSet.Tables(0).Rows(i).Item("F12").ToString()
                Dim OrderColour As String = MyDataSet.Tables(0).Rows(i).Item("F13").ToString()

                Dim OrderID As Integer = Orders.AddOrder(OrderReceivedDate, New Date, ProductionTime, 0, PONo, ShipmentDepart, ShipmentArrive, 0, StoreNo, CustomerName, OrderToPC, InvoiceNo, ContainerNo, 1)
                Dim OrderedProductID As Integer = Orders.OrderProduct(OrderID, OrderOption, OrderColour)

                Dim ModuleHT As New Hashtable

                Dim CurrentModules As Modules = Modules.GetModules()

                For j = 0 To CurrentModules.ModuleIDs.Count - 1
                    'Modules
                    If Not IsDBNull(MyDataSet.Tables(0).Rows(i).Item(CurrentModules.ModuleCelRefs.Item(j))) Then
                        If Integer.TryParse(MyDataSet.Tables(0).Rows(i).Item(CurrentModules.ModuleCelRefs.Item(j)), 0) Then
                            If MyDataSet.Tables(0).Rows(i).Item(CurrentModules.ModuleCelRefs.Item(j)) > 0 Then
                                Orders.AddOrderedModule(OrderedProductID, CurrentModules.ModuleIDs.Item(j), CInt(MyDataSet.Tables(0).Rows(i).Item(CurrentModules.ModuleCelRefs.Item(j))))
                            End If
                        End If
                    End If
                Next
            End If

Data Overflow The parameter data is too large to fit into the host field

2013-06-18T17:47:29.873-04:00

<div>

I think this issue may be a DB Sql issue rather than a data control issue so I'm going to post the question in here as well. The OleDb connection is to a DB2 database running on an IBM iSeries server...

I'm doing parameter substitution into an sql statement which is being used to change the SelectCommand in a SqlDataSource being used to load a GridView. The connection is an OleDb connection.

The exception reads... {"Conversion failed for command parameter[0] '' because the data value overflowed the type used by the provider.
"}

...and the Inner exception reads... CWBZZ5012 Data overflow - the parameter data is too large to fit into the host field

...the SQL statement is...

SqlDataSource1.SelectCommand ="select order, recalled, edte, acct, sub, name, add1, add2, add3, code from lib.tablename t1 where code in ? and edte >= ? and edte <= ? order by t1.order desc"

...and the parameter substitutions are...

SqlDataSource1.SelectParameters.Add("@codelist", CodeList)

SqlDataSource1.SelectParameters.Add("@startingdate", startingdate)

SqlDataSource1.SelectParameters.Add("@endingdate", endingdate)

...with the fields defined in code as...

Dim CodeList As String

Dim startingdate As String
Dim endingdate AsString

...and viewing the contents of the fields in debug reveals...

CodeList value is ('CCM', 'CCS', 'CDR', 'CIR', 'CLM', 'CLP', 'CMC', 'CML', 'CMS', 'CNI', 'CRB', 'CRD', 'CRW', 'CTP', 'CTU')

startingdate value is 6/3/2013

endingdate value is 6/7/2013

Looks like it is failing on CodeList substition...can someone tell me why and what I need to do to make it work?

As well, the assembled SQL statement runs sucessfully when I run it on the server.

Thanks much in advance for any help, Roscoe

</div>

deploying website

2013-06-17T13:22:30.177-04:00

i created a website using oracle everything was working fine on my local machine

when i tried to publish it to a remote server i could not so i copy and past the directory to the iis wwroot directory when i try to run it

it gave me the following error what could i do

Server Error in '/' Application.

Configuration Error

Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Could not load file or assembly 'Oracle.Web, Version=2.112.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file specified.

Source Error:

Line 58: 				<add assembly="System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" />
Line 59: 				<add assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
Line 60: <add assembly="Oracle.Web, Version=2.112.1.0, Culture=neutral, PublicKeyToken=89B483F429C47342" /> Line 61: 				<add assembly="System.Data.Entity, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" /></assemblies>
Line 62: 			<buildProviders>

Source File: C:\inetpub\wwwroot\web.config Line: 60

Assembly Load Trace: The following information can be helpful to determine why the assembly 'Oracle.Web, Version=2.112.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342' could not be loaded.

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

Version Information: Microsoft .NET Framework Version:2.0.50727.4016; ASP.NET Version:2.0.50727.4016

Passwords..

2013-06-15T19:35:36.437-04:00

Hi I get an error connecting to the database server, wonder what is the reason

Authentication with old password no longer supported, use 4.1 style passwords.

http://caddeworld.net/

and the solution ??

Working with EF for Oracle database. “Schema specified is not valid”

2013-06-13T06:33:15.333-04:00

The field in Oracle database is of NUMBER(5), and when I generate EF models from the oracle database, it converts the field into "Int16". There are some of the rows for that field having values such as "50000", etc, so Int16 is throwing error for obvious reasons. Now I tried changing it to Int32, and also Decimal in EF models, but it is now giving an error saying -

Member Mapping specified is not valid. The type 'Edm.Decimal[Nullable=True,DefaultValue=,Precision=,Scale=]' of member 'Field1' in type 'MyApp_Models.MyTable' is not compatible with 'OracleEFProvider.number

Any idea what is wrong here, and what is possible solution of this?

Any help on this much appreciated.

[MySQL] Exclude all the rows with the same ID that are within an hour

2013-06-13T14:24:00.587-04:00

Hi guys, I need suggestion for show from my mysql table only the first row grouped by unique tblID and to exclude all the rows with the same tblID that are within an hour compared to the previous row.

E.g.:

+----------+---------------------+
| tblID    | theDate             |
+----------+---------------------+
| 77002221 | 2013-06-12 11:17:29 |
| 56009055 | 2013-06-12 11:17:29 |
| 77002221 | 2013-06-12 11:47:29 |
| 77002221 | 2013-06-12 12:17:29 |
| 77002221 | 2013-06-12 12:47:29 |
+----------+---------------------+

In this example I need show only this output:

+----------+---------------------+ 
| tblID    | theDate             | 
+----------+---------------------+ 
| 77002221 | 2013-06-12 11:17:29 | 
| 56009055 | 2013-06-12 11:17:29 | 
| 77002221 | 2013-06-12 12:47:29 | 
+----------+---------------------+

Because this rows compared to the first row with tblID 77002221 fall within one hour:

+----------+---------------------+ 
| tblID    | theDate             | 
+----------+---------------------+ 
| 77002221 | 2013-06-12 11:47:29 | 
| 77002221 | 2013-06-12 12:17:29 | 
+----------+---------------------+

Can you help me?

Thank you in advance.

DROP TABLE IF EXISTS `timediff`;
CREATE TABLE `timediff` (
  `tblID` int(10) DEFAULT NULL,
  `theDate` datetime DEFAULT NULL,
  `id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of timediff
-- ----------------------------
INSERT INTO `timediff` VALUES ('77002221', '2013-06-12 11:17:29', '1');
INSERT INTO `timediff` VALUES ('56009055', '2013-06-12 11:17:29', '2');
INSERT INTO `timediff` VALUES ('77002221', '2013-06-12 11:47:29', '3');
INSERT INTO `timediff` VALUES ('77002221', '2013-06-12 12:17:29', '4');
INSERT INTO `timediff` VALUES ('77002221', '2013-06-12 12:47:29', '5');

Splitting comma separated string inside function

2013-05-10T10:27:52.233-04:00

How to split comma separated string into an array which is passed to an oracle function as a parameter and then iterating

through every element of array.....

ORA-01858: a non-numeric character was found where a numeric was expected

2013-06-13T16:36:39.32-04:00

I have a function that takes in a collection of SearchCriteria Objects: columnName or Key, operator (<, <=, like, etc), and value.

The function builds up a Command Object. I made the value a command parameter and now my Unit Tests will not work for Dates. But all of my Unit Tests work against all other datatypes like varchar.

In the debugger, one of my date unit tests that fail end up with the cmd looking like this:

SELECT * FROM (SELECT DocumentId
               FROM idx1_AuthLetters a
               INNER JOIN Documents b ON a.DocumentId = b.Id
               WHERE Status in ('L','S','V') AND letter_date <= :1
               ORDER BY DOCUMENTID )
WHERE RowNum <= 14 I did have the parameter named like :letter_date. But I might have :letter_date >= ### && :letter_date <= ### where I am looking between two dates. I cannot have the same parameter name twice so I use an i++ counter as parameter name while I am looping through all of my SearchCriteria Objects. Odd to see a parameter named like this I know but it is working for the most part.

If I take this and put in my Query Window, and inspect the param value and plug that in:

SELECT * FROM (SELECT DocumentId
               FROM idx1_AuthLetters a
               INNER JOIN Documents b ON a.DocumentId = b.Id
               WHERE Status in ('L','S','V') AND
                     letter_date <= TO_DATE('2013-1-21', 'yyyy-mm-dd')
               ORDER BY DOCUMENTID )
WHERE RowNum <= 14 it works fine. But it will not work from the C# code from my Unit Test. Again this works for all other data types. And it use to work before I parameterized the value in the select statement.

Exact error is:

{"ORA-01858: a non-numeric character was found where a numeric was expected"}

mysql connector problem

2013-06-13T22:49:51.447-04:00

Hi;
I using asp.net 4.0 and mysql for web service.Firstly I download latest version of mysql connector 6.6.5 .After that I install MySQL Library 6.2.5.Because when I used 6.6.5 connector,I encountered trust level problem I succesfully create both of them.When I add reference for 6.2.5 mysql.data.dll to project on host.

Could not load file or assembly 'MySql.Data, Version=6.5.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

error in host.I used C:\Program Files (x86)\MySQL\MySQL Connector Net 6.2.5\Assemblies\mysql.data.dll for import project.I checked the GAC folder only exist this folder name v4.0_6.6.5.0__c5687fc88969c44d
no file name 6.2.5:
Which dll add reference to Vs2012 for succesfully work?
My web service doesn't work correctly on host.How can I solve this problem ?

Thanks in advance

CreateUserWizard Control using Oracle

2013-06-13T20:01:04.777-04:00

I am trying to extend the registration page (using this tutorial ) to add more fields to a separate database.

I am using Oracle and but the code below uses MS SQL hence sqlDataSource...

Can anyone help me traslate this into oracle? I really don't understand what's going on here. Looks like its inserting a 'UserId' but in which table??

protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
{
    TextBox UserNameTextBox =  (TextBox)CreateUserWizardStep2.ContentTemplateContainer.FindControl("UserName");
    SqlDataSource DataSource = (SqlDataSource)CreateUserWizardStep2.ContentTemplateContainer.FindControl("InsertExtraInfo");

    MembershipUser User = Membership.GetUser(UserNameTextBox.Text);
    object UserGUID = User.ProviderUserKey;

    DataSource.InsertParameters.Add("UserId", UserGUID.ToString());
    DataSource.Insert();
}

The way I usually insert items into Oracle using ASP.net is this way (below) but the above code don't look anything like it.

DataTable dt = new DataTable();
        using (OracleConnection con = new OracleConnection(connectionString))
        {
            OracleCommand cmd = new OracleCommand();
            
            string sql = "INSERT INTO UserAddresses (UserId) VALUES (:UserId)";
            cmd.Parameters.AddWithValue("userid", uselbl.text);

            cmd.Connection = con;
            cmd.CommandText = sql;
            con.Open();

            try
            {
                cmd.ExecuteNonQuery();
                ErrorMessage.Text = "Successful!";
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

ORA-01017: invalid username/password; logon denied

2013-06-11T21:59:37.44-04:00

Hi,

I'm trying to obtain data from an Oracle 10g DB, but I've been found a trouble series and now i'm stucked. This is all i've done:

1. Install Oracle 10g client (http://www.oracle.com/technetwork/database/windows/install10104-087102.html)

Got:

ORA-12154: TNS:could not resolve the connect identifier specified

It was 'cause i've missing to configure tnsnames, so...

2. Configure TNSNAMES.ORA and sqlnet.ora (both in the same path C:\oracle\product\10.1.0\Client_1\network\ADMIN )

TNSNAMES.ORA

etpschp3=(
	DESCRIPTION =(
		ADDRESS=(PROTOCOL=tcp)
			(HOST=ip_address)
			(PORT=1528)
		)
	(CONNECT_DATA=
		(SERVICE_NAME =etpschp3)
	)
)

sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

3. Add environment variables ORACLE_HOME and TNS_ADMIN

4. Check "Allow inprocess" on OraOLEDB.Oracle DataProvider

Now the error was different:

ORA-12545: Connect failed because target host or object does not exist

<div>5. I've got to add server in HOST file</div> <div></div> <div>Got new error:</div> <div>

ora-01017 invalid username/password logon denied

<div>Actually there's other server with a SQL Server with a linked server with the same user/password that i'm using. Also i've been configured an UDL in my machine, and connection test was successful.</div> </div> <div></div> <div>I don't get where's my mistake. I get this error in a linked server (in my machine) and in an ASP application using System.Data.OleDb and System.Data.OracleClient.</div>

string str = "Data Source=etpschp3;Persist Security Info=True;"
                    + "User ID=usr;Password=passwd;";

                DataSet ds = new DataSet();
                OracleConnection oraCnn = new OracleConnection(str);   //("Data Source=etpschp3; User Id=OS_APP; Password=OSS55USER;");
                OracleCommand cmd = new OracleCommand("select count(*) from FROM tboss.osst_cust", oraCnn);

                OracleDataAdapter da = new OracleDataAdapter(cmd);

OleDbConnection cn = new OleDbConnection("Provider=MSDAORA;" +
                      "Data Source = etpschp3 ;" +
                      "User ID= usr;" +
                      "Password = passwd;");

                OleDbCommand cmd = new OleDbCommand("select count(*) from FROM tboss.osst_cust", cn);
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);


                da.Fill(ds);

Somebody help!

Thanks in advance

ODBC Parameter Error

2013-06-06T05:59:58.347-04:00

Hi all,

I am getting this error: "The OdbcParameter is already contained by another OdbcParameterCollection." in my DAL class file.

I have made website which uses MySQL database and for that I have used ODBC. When I call below mentioned method 'GetValueBySQL' which calls 'PrepareCommand' in which I am getting error at the last line.

I tried for solution on google but can't find it. Please help.

public string GetValueBySQL(string strSQL, params OdbcParameter[] ocmdParms)
        {
            OdbcCommand ocmd = new OdbcCommand();
            PrepareCommand(ocmd, om_conn, null, strSQL, ocmdParms);

            string Value = string.Empty;
            object o = ocmd.ExecuteScalar();
            if (!(o == null || o == System.DBNull.Value))
            {
                Value = o.ToString();
            }

            CloseConnect();
            return Value;
        }


private void PrepareCommand(OdbcCommand ocmd, OdbcConnection conn, OdbcTransaction trans, string ocmdText, OdbcParameter[] ocmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            ocmd.Connection = conn;
            ocmd.CommandText = ocmdText;
            if (trans != null)
                ocmd.Transaction = trans;
            ocmd.CommandType = CommandType.Text;
            if (ocmdParms != null)
            {
                ocmd.Parameters.Clear();

                foreach (OdbcParameter parameter in ocmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    ocmd.Parameters.Add(parameter);
                }
            }
        }

Advance thanks,
Suraj Joshi.

how to take mys sql backup between two dates using asp.net c#

2013-06-08T07:11:11.13-04:00

Hi community,

i am implemting backup functionality in web application complete database backup is sucessfully done using c# but i

I am facing problem as user defined backup date for e.g if want to take backup from 5-june-2013 to 8-june 2013

how can i am implement this backup functionality..

Thanks...

Sql Query

2013-06-11T09:20:54.477-04:00

hello i have the following error :ORA-01036: illegal variable name/number

in below query

cmdMain.CommandText = " SELECT :CL_CLASS " & _
" FROM TABLE1 " & _

                        " WHERE COUNTRY = 'Brazil'          " & _
                          " AND    POLICYSEQ = 1            " & _
                                               " AND    ROWNUM = 1                          "
            cmdMain.Parameters.Add(":CL_CLASS", OracleType.NVarChar, 1).Value = CL_CLASS

please help i think the error when using the symbol :

textbox/checkbox/dropdownlist values, insert into Oracle db

2013-06-10T18:37:54.813-04:00

I have created my Oracle DB, and established the connection within my visual studio. (call it TEST_DB)...

Now say I have 1 textbox (Name), 1 checkbox (gender), and 1 dropdownlist (state),

What is the c# code to take the entered values after SubmitButton was clicked, and insert them into my oracle DB Columns?...

thanks in advance

Reading phpBB posts

2013-06-09T17:43:00.963-04:00

Hi all,

My question put simply: I have a phpBB board on my server. I want to read the latest news announcement from it and display it on my web site's main page. Everything's fine and dandy; topic title, timestamps, reply counts etc. all work, except for the actual post contents.

Let's say the post text is something along the lines of "Lorem ipsum dolor sit amet, consectetur bla bla". What my code actually returns is not that but some garbled version like "57656C6C2C2049206775657373207468697320".

Code (see below) in short: Grab the last topic's (sorting by timestamp, descending, limit 1 = latest topic) metadata from the phpbb_topics table, get the post ID of the topic's first post, and then look that post's text up in the phpbb_posts table.

`phpbb_posts`.`post_text` is a mediumtext utf8_bin (this is the column where the reading apparently goes wrong). The only other text field I'm reading from is `phpbb_topics`.`topic_title`, which is a varchar(255) utf8_general_ci. I'm lead to believe that it has something to do with the post_text column having a binary collation, but I don't know how to fix the reading problem.

Using MySQL 5.

VB.NET code:

Public Function GetLastNewsPost() As System.Collections.Generic.List(Of Object)
            Dim Result As New System.Collections.Generic.List(Of Object)
            Dim NewsTitle As String = "", NewsID As Integer, NewsTime As Long, NewsReplies As Integer
            Dim NewsText As String = "", NewsFirstPostID As Integer

            Try
                ' Generate SQL database connection and open.
                Dim SQLConn As New OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=xxxxx;Uid=xxxxx;Pwd=xxxxx;")
                SQLConn.Open()

                Dim SQLCommand As New OdbcCommand("SET NAMES 'utf8';", SQLConn)
                SQLCommand.ExecuteNonQuery()
                SQLCommand.Dispose()

                ' Create a reader
                Dim Reader As OdbcDataReader = Nothing

                ' Create SQL query.
                SQLCommand = New OdbcCommand("SELECT `topic_id`, `topic_title`, `topic_time`, `topic_replies`, `topic_first_post_id` FROM `phpbb_topics` WHERE `forum_id`=2 ORDER BY `topic_time` DESC LIMIT 1", SQLConn)
                Try
                    ' Generate a reader
                    Reader = SQLCommand.ExecuteReader

                    If Reader.HasRows AndAlso Reader.Read Then
                        ' read only the latest post
                        NewsID = Reader.GetInt32(0)
                        NewsTitle = Reader.GetString(1)
                        NewsTime = Reader.GetInt64(2)
                        NewsReplies = Reader.GetInt32(3)
                        NewsFirstPostID = Reader.GetInt32(4)
                        SQLCommand.Dispose()
                        Reader.Close()

                        ' Create SQL query.
                        SQLCommand = New OdbcCommand("SELECT `post_text` FROM `phpbb_posts` WHERE `post_id`=" & NewsFirstPostID & " LIMIT 1", SQLConn)
                        Reader = SQLCommand.ExecuteReader

                        If Reader.HasRows AndAlso Reader.Read Then
                            NewsText = Reader.GetString(0)
                        End If
                        SQLCommand.Dispose()
                    End If
                Finally
                    SQLConn.Close()
                    If Reader IsNot Nothing Then Reader.Close()
                End Try

            Catch ex As Exception
                ' DB error
                NewsTitle = "<Database error occurred: " & ex.GetType.FullName & ">"
            End Try

            Result.Add(NewsID)
            Result.Add(HttpUtility.HtmlEncode(NewsTitle))
            Result.Add(NewsTime)
            Result.Add(NewsReplies)
            Result.Add(HttpUtility.HtmlEncode(NewsText))
            Return Result
        End Function

If anyone could give me some ideas, that would be awesome.

Thanks!

Form in ASPX to MySQL DB

2013-05-30T16:17:42.457-04:00

I have started a website on Visual Studio using Visual C#. ASP.NET Web Forms Site. I also have a MySQL database that I would like the form I create to alter. I have the form, here's a snippet:

<form id="form1" runat="server" method="post" action="dbconnect.cs">
    <div>
        <label>Type the project title here</label>
        <br />
        <textarea id="projectTitle"></textarea>

I know I don't have the code right for the action because I'm new to this, although I have gotten the dbconnect.cs to change the MySQL db on it's own by hardcoding it. I just don't know how to make the connection between the form and the dbconnect.cs.

I'd like to be able to do an INSERT for my database when I go to dbconnect.cs.

dbconnect.cs snippet:

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using System.IO;
//Add MySql Library
using MySql.Data.MySqlClient;

namespace ConnectCsharpToMysql
{
    class DBConnect
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        //Constructor
        public DBConnect()
        {
            Initialize();
        }

        //Initialize values
        private void Initialize()
        {
            server = "servername";
            database = "databasename";
            uid = "username";
            password = "password";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);
        }


        //open connection to database
        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                //1045: Invalid user name and/or password.
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;
                }
                return false;
            }
        }

        //Close connection
        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        //Insert statement
        public void Insert()
        {
            string query = "INSERT INTO TestTable (idTestTable, itemA, itemB) VALUES(5, 'Hey Smith', '22')";

            //open connection
            if (this.OpenConnection() == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //Execute command
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
        }

selecting Checkbox Items Using Values from a Database

2013-05-30T18:16:11.777-04:00

My Database has a column called "interests" with the following values (colon separed) stored on the database:
art; reading; music; sports; nature; travel.

However, the value of "currentCheckBox" in code below is always empty. How do I load the values from the database onto the edit form? Is this the right way to do this? THANK YOU!

CODE BEHIND:
private void Page_Load(object sender, System.EventArgs e)
{
   if (!IsPostBack)
   {
      SqlConnection objConn = new SqlConnection(ConfigurationSettings.AppSettings["DSN"]);

      objCmd = new SqlCommand("select interest from member_interest where name= 'John'",objConn);
      objConn.Open();
      SqlDataReader objReader = objCmd.ExecuteReader();

      while (objReader.Read())
      {
         ListItem currentCheckBox = CheckBoxlist1.Items.FindByValue(objReader["interest"].ToString());
         response.write(currentCheckBox); //**** This returns empty.
         if (currentCheckBox != null)
         {
            currentCheckBox .Selected = true;
         }
      }
   }
}

// ASPX PORTION:
<asp:CheckBoxList ID="CheckBoxList1" runat="server" AutoPostBack="true">
<asp:ListItem Value="art"> </asp:ListItem>
<asp:ListItem Value="reading"> </asp:ListItem>
<asp:ListItem Value="music"> </asp:ListItem>
<asp:ListItem Value="sports"> </asp:ListItem>
<asp:ListItem Value="nature"></asp:ListItem>
</asp:CheckBoxList>

reading from excel Best approach in .net

2013-06-03T10:34:58.507-04:00

I know approaches like oledb, Excel object model.

Wanted to know whats the best approach

Calling an Oracle Stored Procedure with ODP

2013-06-06T12:56:53.317-04:00

Hello everybody !!!

I have this oracle stored Procedure :

PROCEDURE sp_cree_planning (
 pi_id_trait   IN T_TRAIT.ID_TRAIT%TYPE,
 pi_f_actif   IN T_PLANNING.F_ACTIF%TYPE,
 pi_id_si    IN T_PLANNING.ID_SI%TYPE,
 pi_lst_loc   IN TABLE_NUMBER,
 pi_lst_par_nom IN table_varchar2_50,
 pi_lst_par_val IN table_varchar2_200,
 pi_d_modif   IN DATE,
 pi_id_user   IN T_USERS.ID_USER%TYPE,
  po_c_erreur   OUT NUMBER,
 po_des_erreur  OUT VARCHAR2 )
IS

l_nom_param   T_PLANNING_PARAM.NOM_PARAM%TYPE;
l_valeur_param T_PLANNING_PARAM.VALEUR_PARAM%TYPE;
l_id_planning  T_PLANNING.ID_PLANNING%TYPE;
l_tab_par    table_par;

l_err_fonc     EXCEPTION;
po_c_erreur_trait  NUMBER;
po_des_erreur_trait VARCHAR2(2000);
BEGIN
po_c_erreur := Pck_Erreur.CST_OK;

 sp_tab_param(pi_lst_par_nom,pi_lst_par_val ,l_tab_par,po_c_erreur_trait,po_des_erreur_trait);
 IF po_c_erreur_trait <> Pck_Erreur.CST_OK THEN
  po_c_erreur := po_c_erreur_trait;
  po_des_erreur := po_des_erreur_trait;
  RAISE l_err_fonc;
 END IF;



IF pi_lst_loc.COUNT <> 0 THEN
 FOR i IN pi_lst_loc.FIRST..pi_lst_loc.LAST  LOOP
  -- création du planning : lien job-lieu
  SELECT SEQ_PLANNING.NEXTVAL INTO l_id_planning  FROM dual;
  INSERT INTO T_PLANNING(ID_PLANNING,ID_TRAIT,ID_LOC,ID_SI,F_ACTIF,ID_USER,D_MODIF)
  VALUES (l_id_planning,pi_id_trait,pi_lst_loc(i),pi_id_si,pi_f_actif,pi_id_user,pi_d_modif);

  IF l_tab_par.COUNT <>0 THEN
    FOR j IN l_tab_par.FIRST..l_tab_par.LAST LOOP
     IF l_tab_par(j).par_nom IS NOT NULL THEN
       INSERT INTO T_PLANNING_PARAM(ID_PLANNING,NOM_PARAM,VALEUR_PARAM,ID_USER, D_MODIF) VALUES
       (l_id_planning,l_tab_par(j).par_nom,l_tab_par(j).par_val,pi_id_user,pi_d_modif);
     END IF;
    END LOOP;
  END IF;
 END LOOP;
END IF;

Somebody may tell me how to call this procedure using C#?

The dificulty is here :

 pi_lst_loc   IN TABLE_NUMBER,
 pi_lst_par_nom IN table_varchar2_50,
 pi_lst_par_val IN table_varchar2_200,

I don't know how to use it here for example :

            Parameter piIdTrait = new Parameter { Name = ParamIdRequest, Type = OracleDbType.Int64, Direction = ParameterDirection.Input };
            piIdTrait.Value = (Int64?)Int64.Parse(idRequest);
            proc.Parameters.Add(piIdTrait );

Thanx very much