This discussion is archived
5 Replies Latest reply: Mar 24, 2013 12:27 AM by 993069 RSS

Create table using select statment using OLEDB in C# ASP.NET

993069 Newbie
Currently Being Moderated
Hi,

I am trying ctreate a table on oracle database from front end C# ASP.NET using OLEDB connection.

But I am not able to create table using select statment.

Can anyone please help me out with this and let me know how can I create a table using select statement. Select statment is having mote than one column which need to be inserted into new table.

I am trying below code but its not at all working and I guess I am doing it wrong.

OleDbCommand sql = new OleDbCommand("Select CUSTOMER_CITY, CUSTOMER_STATE, Region from Karen_Provided_Excel", con);
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
OleDbDataReader objReader;
objReader = sql.ExecuteReader();

//string strTemp = "Name Varchar(30), Address Varchar(30)";
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = con;
myCommand.CommandText = "CREATE TABLE Abhi_Test_DotNet(" + objReader.ToString() + ")";
myCommand.ExecuteNonQuery();
Label1.Text = "Table Created Successfully";
}

}


catch (Exception ex)
{
Label1.Text = "<br />ERROR WITH TEST METHOD: " + ex;
}

finally
{
if (con.State == ConnectionState.Open)
{
con.Close();

}

} ;

Thanks in adavnce for your help.

Regards,
Abhishek
  • 1. Re: Create table using select statment using OLEDB in C# ASP.NET
    Tridus Journeyer
    Currently Being Moderated
    Your syntax is wrong. You can just do this:
    CREATE TABLE Abhi_Test_DotNet AS Select CUSTOMER_CITY, CUSTOMER_STATE, Region from Karen_Provided_Excel;
    On an unrelated note -- why are you using OLE DB in .net code? ADO.net is generally a better way to go.
  • 2. Re: Create table using select statment using OLEDB in C# ASP.NET
    993069 Newbie
    Currently Being Moderated
    Thanks Tridus. Its working absolutly fine.

    Can you please tell me if at runtime I want to provide table name to create how can I do that.

    e.g.

    I am having a textbox named txbID on design window and at runtime if I put 101 in text box then my table name should be Abhi_Try_101, if I put 555 in text box then it need to be Abhi_Try_555.

    Thanks in advance.

    Regards,
    Abhi

    Edited by: 990066 on Mar 18, 2013 11:36 PM
  • 3. Re: Create table using select statment using OLEDB in C# ASP.NET
    Tridus Journeyer
    Currently Being Moderated
    Why are you doing that? You shouldn't need a bunch of identical tables created at runtime. There's a number of risks and problems with doing that. Most notably that you'll need the user account running this to have CREATE TABLE permission, which will let them create all sorts of tables other than the one you want them to.

    You could just have one table called Abhi_Try, and add a column where when you put data in, also add 101 or 555 into the new column so you know which one it's associated with. (Better still would be to create a second table to list those and make a foreign key, but you'll probably need assistance from someone with experience to do that.)

    If you really want to do it anyway, fundamentally it's just a query. You can change it the same way you'd change a SELECT query's parameters.
  • 4. Re: Create table using select statment using OLEDB in C# ASP.NET
    993069 Newbie
    Currently Being Moderated
    Hi Tridus,

    Thanks for the suggestion, but I am creating this application for internal company use where my team member whill have all the access to create, update and delete table and its on the basis of business need.

    You said I can modify the select query and I tried same but still I an getting error.

    As I said I am using a textbox on front screen with name textbox1 and it will have numeric values e.g. 101, 500, 202, 345 anything...

    when I am using in select statment is not working at runtime and getting below error,

    "
    ERROR WITH TEST METHOD: System.Data.OleDb.OleDbException: ORA-00922: missing or invalid option at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at _Default.Button1_Click(Object sender, EventArgs e) in c:\Users\jaiab01\Abhi\DotNet\Default.aspx.cs:line 313
    "

    my code and select query is as below,


    try
    {
    if (con.State == ConnectionState.Closed)
    {
    con.Open();
    OleDbCommand myCommand = new OleDbCommand();
    myCommand.Connection = con;
    string number = TextBox1.Text;
    myCommand.CommandText = "CREATE TABLE Abhi_Test_DotNet_'"+number+"' as Select CUSTOMER_CITY, CUSTOMER_STATE, Region from Karen_Provided_Excel";
    // plus symbols added at starting and end poing of number but dont know the reson why it is not displaying here i.e. '"plus number plus"'
    myCommand.ExecuteNonQuery();
    Label1.Text = "Table Created Successfully";
    }

    }


    catch (Exception ex)
    {
    Label1.Text = "<br />ERROR WITH TEST METHOD: " + ex;
    }

    finally
    {
    if (con.State == ConnectionState.Open)
    {
    con.Close();

    }

    }

    Hope you can help me in this as well.

    Thank you so much for your greate help.

    Regards,
    Abhi

    Edited by: 990066 on Mar 23, 2013 12:05 AM
  • 5. Re: Create table using select statment using OLEDB in C# ASP.NET
    993069 Newbie
    Currently Being Moderated
    I could able to change the table name at runtime with "+number+".

    Eliminated '' from the query.

    Thanks,
    Abhi

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points