ASP.NET SQL problems and code help

I have written the code below for adding products to a basket, then outputting them as well as making multiple requests to the server to insert and select data.

I posted a question yesterday about assigning the result of an SQL command to a variable but nothing seems to work, probably due to the code I have written, if there is a solution for this I would be very grateful.

Also is there anyway to simplify the SQL commands I seem to be making quite a few

I know about the vulnerability for SQL injection attacks, this is just a Uni project and I doubt the lecturer even knows about them! Nevertheless I will sort these out once I get the basic functionality working :)

string CurrentUser="";
if (User.Identity.IsAuthenticated) {
    CurrentUser = Membership.GetUser(HttpContext.Current.User.Identity.Name).ProviderUserKey.ToString(); //Get the current user
}

//Insert the current user into the DB
BasketPage.InsertCommand = "INSERT INTO tblBasket(UserID, CreatedDate) VALUES ('" + CurrentUser + "'), CONVERT (DATETIME, '2010-11-20 00:00:00', 102))"; 

//Select the Basket ID for this user which is an auto increment hence why I inserted the user first
BasketPage.SelectCommand = "SELECT BasketID FROM tblBasket WHERE (UserID = '" + CurrentUser + "')"; 

var basketID= //Result of the pr开发者_JS百科evious select command

if (Session["CartSess"] != null) {
    List<BasketClass> cart = (List<BasketClass>)Session["CartSess"];

    foreach (BasketClass BookID in (List<BasketClass>)Session["CartSess"]) {
        BasketPage.InsertCommand = "INSERT INTO tblBasketDetails(BasketID, BookID) VALUES (" + 
                                   basketID + "," + BookID + ")"; //Inserts each book into the DB and the Basket ID
        BasketPage.Insert();
    }
}

//Outputs the Basket for the current user
BasketPage.SelectCommand = "SELECT tblBasket.UserID, tblBasket.BasketID, tblBooks.Title, tblBasketDetails.Quantity " +
                           "FROM tblBasket " +
                           "INNER JOIN tblBasketDetails ON tblBasket.BasketID = tblBasketDetails.BasketID " + 
                           "INNER JOIN tblBooks ON tblBasketDetails.BookID = tblBooks.BookID " + 
                           "WHERE (tblBasket.UserID = '" + CurrentUser + "')"; 

---

On the line:

BasketPage.InsertCommand = "INSERT INTO tblBasket(UserID, CreatedDate) VALUES ('" + CurrentUser + "'), CONVERT (DATETIME, '2010-11-20 00:00:00', 102))"; //Insert the current user into the DB

Substituting 'SomeValue' for your CurrentUser variable, your SQL is:

INSERT INTO tblBasket(UserID, CreatedDate) 
VALUES ('SomeValue'), CONVERT (DATETIME, '2010-11-20 00:00:00', 102))

Try running that in a SQL window. In SQL Server You'll get:

There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Your problem is with the first clsing bracket on the VALUES LINE. The code should be:

INSERT INTO tblBasket(UserID, CreatedDate) 
VALUES ('SomeValue', CONVERT (DATETIME, '2010-11-20 00:00:00', 102))

As a general bit of advise, try testing your queries standalone before runing them as part of your code.

Casting aside the SQL Injection problems that you mentioned, this is another reason for moving all your code to a stored procedure and calling it with parameters from your code.

---

I would suggest you put your SQL code into Stored procedures rather than writing them inline like this. This kind of coding becomes difficult to maintain very quickly. And of course, as you mention, there are SQL Injection attacks.

I've posted some boilerplate SQL code specific to SQL Server. You may need to make some changes for MySQL or some other RDBMS).

E.g.

On the DB.

CREATE PROCEDURE BasketInsert
    @UserId INT
AS
BEGIN
    INSERT INTO tblBasket
    (UserId, CreatedDate)
    VALUES
    (
        @UserId,
        GetDate() -- built in SQL function to return the current datetime.
    )

SELECT Scope_Identity() AS BasketId -- returns latest basketId ie. the one you have just inserted.

END



CREATE PROCEDURE BasketDetailInsert
    @BasketId INT,
    @BookId INT
AS
BEGIN
    INSERT INTO
        tblBasketDetails(BasketID, BookID) 
    VALUES
        (
            @BasketId,
            @BookId
        )
END




CREATE PROCEDURE BasketSelect
    @CurrentUserId INT
AS
BEGIN
    SELECT
        tblBasket.UserID, 
        tblBasket.BasketID,
        tblBooks.Title,
        tblBasketDetails.Quantity 
    FROM tblBasket 
    INNER JOIN tblBasketDetails ON tblBasket.BasketID = tblBasketDetails.BasketID
    INNER JOIN tblBooks ON tblBasketDetails.BookID = tblBooks.BookID
    WHERE (tblBasket.UserID = @CurrentUserId
END

And modify your server-side code to use these stored-procedures instead of 'freehand SQL'.

HTH>

[Incidentally I would be very surprised if your tutors were unaware of SQL Injection.]

EDIT:

Sorry forgot to mention - the Scope_Identity() function is SQL Server-specific. It just returns the most recent ID from the table in question. You can either get it as I've done using a SELECT statement or you can include an Output parameter in your procedure, assign it via Scope_identity() and get the value on the serverside. This article https://web.archive.org/web/20211020134930/https://www.4guysfromrolla.com/articles/062905-1.aspx might help.