10 Replies - 4130 Views - Last Post: 20 August 2013 - 04:03 PM Rate Topic: -----

#1 golla  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 31-July 13

Oledbcommand insert into

Posted 14 August 2013 - 06:51 AM

Hello,

im trying to figure it out what is missing in the code, it used to work for me but i didnt code for a lot of time in asp.net so i dont remember what im doing wrong...

im trying to add a simple value into my database...

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Database21.accdb");
        conn.Open();
        OleDbCommand cmd = new OleDbCommand("insert into Users (user, password) values ('" + TextBox2.Text + "','" + (TextBox3.Text) + "')", conn);
        cmd.ExecuteNonQuery();


and im getting this error:

Syntax error in INSERT INTO 


and

Line 42:         conn.Open();
Line 43:         OleDbCommand cmd = new OleDbCommand("insert into Users (user, password) values ('" + TextBox2.Text + "','" + (TextBox3.Text) + "')", conn);
[color="#FF0000"]Line 44:         cmd.ExecuteNonQuery();[/color]


Is This A Good Question/Topic? 0
  • +

Replies To: Oledbcommand insert into

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9493
  • View blog
  • Posts: 35,838
  • Joined: 12-June 08

Re: Oledbcommand insert into

Posted 14 August 2013 - 07:02 AM

Why is textxbox3 in parentheses? Are you wrapping this in a try/catch? Was your connection opened?
Was This Post Helpful? 0
  • +
  • -

#3 golla  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 31-July 13

Re: Oledbcommand insert into

Posted 14 August 2013 - 07:08 AM

yeah i didnt found the edit here, i removed the parentheses, nothing help's... i get the same error on the "INSERT INTO" syntax...and the cmd.ExecuteNonQuery...

im doing it only for training...and yes the connection is open
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9493
  • View blog
  • Posts: 35,838
  • Joined: 12-June 08

Re: Oledbcommand insert into

Posted 14 August 2013 - 07:11 AM

.. and are you sure your INSERT statement is right? If you plop a breakpoint on your execute and *look* at what is attempted to be inserted it appears okay?
Do the columns take those datatypes?
Any particular reason you are _NOT_ using parameters?
Is there any message in the inner exception of the try/catch you should be wrapping that with?
Was This Post Helpful? 0
  • +
  • -

#5 golla  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 31-July 13

Re: Oledbcommand insert into

Posted 14 August 2013 - 07:21 AM

View Postmodi123_1, on 14 August 2013 - 07:11 AM, said:

.. and are you sure your INSERT statement is right? If you plop a breakpoint on your execute and *look* at what is attempted to be inserted it appears okay?
Do the columns take those datatypes?
Any particular reason you are _NOT_ using parameters?
Is there any message in the inner exception of the try/catch you should be wrapping that with?


1.and are you sure your INSERT statement is right - yep its right

the thing is even when i dont enter data into the textbox's and i press the "add" button i still get the same error for the cmd.ExecuteNonQuery

the syntax is in the right order user and then password, yes then both can get string value...

why it's marking me the cmd.ExecuteNonQuery in RED at the error ?
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9493
  • View blog
  • Posts: 35,838
  • Joined: 12-June 08

Re: Oledbcommand insert into

Posted 14 August 2013 - 07:29 AM

Quote

1.and are you sure your INSERT statement is right - yep its right

When you say this do you mean you actually put a break point down and looked, or just waved your hand and said it was fine?

Quote

why it's marking me the cmd.ExecuteNonQuery in RED at the error ?

That's a good question. With the limited code and responses it may be a mystery.
Was This Post Helpful? 0
  • +
  • -

#7 golla  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 31-July 13

Re: Oledbcommand insert into

Posted 14 August 2013 - 07:37 AM

Well there is not much of a code im my test page but this is what i got:

<configuration>
  <connectionStrings>
    <add name="ConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database21.accdb" providerName="System.Data.OleDb"/>
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.5"/>
    <httpRuntime targetFramework="4.5"/>
  </system.web>
</configuration>



    protected void Button1_Click(object sender, EventArgs e)
    {
        OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Database21.accdb");
        conn.Open();
        OleDbCommand cmd = new OleDbCommand("insert into Users (user, password) values ('" + TextBox2.Text + "','" + TextBox3.Text + "')", conn);
        cmd.ExecuteNonQuery();
    }


If do you want to write a basic code to insert data into the database, how do you do that ? what do you write ? can u post a simple code ?
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9493
  • View blog
  • Posts: 35,838
  • Joined: 12-June 08

Re: Oledbcommand insert into

Posted 14 August 2013 - 07:42 AM

Quote

If do you want to write a basic code to insert data into the database, how do you do that ?

I would use parameters.. and if using ASP.NET I would _NOT_ use Access. It's a local file storage, and directing a webpage to find it find a file when running in IIS is not as simple as saying "here's your directory".
Was This Post Helpful? 0
  • +
  • -

#9 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 445
  • View blog
  • Posts: 1,501
  • Joined: 28-April 09

Re: Oledbcommand insert into

Posted 15 August 2013 - 05:31 AM

Please, please, please do not inject values from textbox controls directly into a sql statement. You are opening yourself up to a world of trouble. If you don't know much about sql injection attacks please look it up. You need to be using parameterized sql statements.

protected void Button1_Click(object sender, EventArgs e)
{
    string query = "insert into Users (user, password) values (@user, @password)";
    OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Database21.accdb");
    conn.Open();
    OleDbCommand cmd = new OleDbCommand(query, conn);
    cmd.Parameters.AddWithValue("@user", TextBox2.Text);
    cmd.Parameters.AddWithValue("@password", TextBox3.Text);
    
    cmd.ExecuteNonQuery();
}



something along those lines, may need some tweaking
Was This Post Helpful? 0
  • +
  • -

#10 Zick Boy  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 33
  • Joined: 30-October 08

Re: Oledbcommand insert into

Posted 20 August 2013 - 08:41 AM

Try surrounding the word Users and user with brackets. I know "USER" is a reserved T-SQL keyword, I'm not sure about "Users". It's worth a shot.

string query = "insert into [Users] ([user], password) values (@user, @password)";


Was This Post Helpful? 1
  • +
  • -

#11 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 800
  • View blog
  • Posts: 1,688
  • Joined: 30-January 09

Re: Oledbcommand insert into

Posted 20 August 2013 - 04:03 PM

+1 Zick Boy, my first guess was that it was reserved word usage in the query
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1