add selected insert statement to users profile c#

Question

0.00/5 (No votes)

See more:

Hi guys im trying to write a statement to insert selected data into a users profile

So a user can select a dvd then when they click rent it will show in there profile what they have rented.

Code so far

C#

protected void Button3_Click(object sender, EventArgs e)
   {
       OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\ASPNetDB.mdb;Persist Security Info=True");
       {
           da.InsertCommand = new OleDbCommand("INSERT INTO UserProfile (Rented) VALUES (?) WHERE ([UserName] = ?);", conn);

           da.InsertCommand.Parameters.AddWithValue("@?", DG_Latest.SelectedRow.Cells[2].Text);

           conn.Open();

           da.InsertCommand.ExecuteNonQuery();

           conn.Close();
           conn.Dispose();
       }
   }

So this code is not working for me evry time i run it and click on the button i get

Missing semicolon (;) at end of SQL statement.

Posted 23-Dec-13 8:54am

Ben Oats

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-12-23T09:04:00

Solution 2

You can't use a WHERE clause with INSERT.
INSERT always create a new record, it can't change existing ones.
Probably you want an UPDATE command instead:

C#

UPDATE UserProfile SET Rented=@Rented WHERE [UserName]=@User

But, I wouldn't do it like that - I'd have a separate table for rented, which stored the DVD ID, the customer ID, the date of rental, and the date of return.
That way, the customer can borrow two DVDs for the weekend without your sytem having a fit, and you can "backtrack" to the previous renter if a customer comes in to say the disk is damaged and won't play, or is the wrong disk entirely.

Posted 23-Dec-13 9:04am

OriginalGriff

Comments

Ben Oats 23-Dec-13 15:18pm

hmm that way dose sound better, so if i made a new table (rented) then have a insert statement inserting the dvd title and user Id then query the information from that table to the users profile ?

OriginalGriff 23-Dec-13 15:31pm

Yes - add an extra rentalId field just to make it unique, but that can be an identity field because you aren't really interested in the value.
The userId and dvdId become foreign keys in the new table.
You can also add a "due by" date field to spot overdue returns with a simple query.

Ben Oats 23-Dec-13 15:45pm

Hmm how would i get the user who is loged in Id though ?

OriginalGriff 23-Dec-13 15:58pm

Sorry?
I'm not sure what you are asking.

Ben Oats 23-Dec-13 16:20pm

I can Obtain the DVDID via the DG_Latest.SelectedRow.Cells[2].Text but how would I obtain the logged in userID ?

OriginalGriff 23-Dec-13 16:45pm

:laugh:
How would I know? :)
What did you do with it when you logged him in?

Ben Oats 23-Dec-13 16:57pm

thats ok, im going to try and user string userName = Environment.UserName;