Forum Stats

  • 3,815,693 Users
  • 2,259,068 Discussions
  • 7,893,208 Comments

Discussions

Categories

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

993069
993069 Member Posts: 5
edited Mar 24, 2013 3:27AM in Oracle Provider for OLE DB
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
· Share on TwitterShare on Facebook

Answers

  • Tridus
    Tridus Member Posts: 594 Silver Badge
    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.
    · Share on TwitterShare on Facebook
  • 993069
    993069 Member Posts: 5
    edited Mar 19, 2013 2:36AM
    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
    · Share on TwitterShare on Facebook
  • Tridus
    Tridus Member Posts: 594 Silver Badge
    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.
    · Share on TwitterShare on Facebook
  • 993069
    993069 Member Posts: 5
    edited Mar 23, 2013 3:09AM
    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
    · Share on TwitterShare on Facebook
  • 993069
    993069 Member Posts: 5
    I could able to change the table name at runtime with "+number+".

    Eliminated '' from the query.

    Thanks,
    Abhi
    · Share on TwitterShare on Facebook
This discussion has been closed.