Tech Off Thread

24 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Sql won't insert :S

Back to Forum: Tech Off
  • User profile image
    Scott Bryen

    Hi, im trying to insert a record into a database, it compiles fines. But for some reason it wont insert into the database.

    Code:

    public void setBookLoan()

    {

    // Set the cmdString to the SQL satement needed for this query.

    cmdString = "INSERT dbo.book_loan (book_id, user_id, title, date_taken) VALUES (" + "'" + txtLoanBookID.Text + "'" + "'" + txtLoanUserID.Text + "'" + "'" + txtLoanTitle.Text + "'" + "'" + dateOutTimePicker.Value.Date + "'" + ")";

    // Create a new connection to the database and also a new command.

    SqlConnection dbConn = new SqlConnection(conn);

    SqlCommand cmd = new SqlCommand(cmdString);

    // Set the command properties.

    cmd.CommandType = System.Data.CommandType.Text;

    cmd.CommandText = cmdString;

    cmd.Connection = dbConn;

    // Open a connection to the database.

    dbConn.Open();

    // Execute query.

    cmd.ExecuteNonQuery();

    // Close connection to the database;

    dbConn.Close();

    }

    Its like its missing some sort of commit statement. I dont think its my connection string because i can get data from the data base just cannot inseert any thing! Any ideas?

    Thanks.

    Scott B. 

  • User profile image
    W3bbo

    No exception was raised?

  • User profile image
    MattShepherd

    isnt the correct SQL syntax "Insert INTO.."  ?

    also, try using parameters for inserting data and stepping through to see whats being passed in...

  • User profile image
    MattShepherd

    Add a break and run the debugger..  an incorrect SQL statement wont throw an exception

  • User profile image
    W3bbo

    MattShepherd wrote:
    isnt the correct SQL syntax "Insert INTO.."


    According to the SQL Server 2000 manual / T-SQL reference[1] the "INTO" bit is optional.

    [1] See mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_ia-iz_5cl0.htm

  • User profile image
    Scott Bryen

    Webbo:

    Nope. I'm calling this method when i click the save button. Once i click it i can still use my application and it doenst force VS to go into debugging mode.

    Matt:

    I wasnt sure if INSERT INTO was just oracle's sntax or MS's aswell perhaps its ANSI standard. but i did try INSERT INTO aswell and yet that didnt work either.

  • User profile image
    MattShepherd

    try

    VALUES ("'" + txtLoanBookID.Text + "','" + txtLoanUserID

    However, id still use Parameters

  • User profile image
    Scott Bryen

    Would the lack of commas sperating the values affect it lo Tongue Out, how do u do params?

    EDIT:

    chnaged line to this

    VALUES ('" + txtLoanBookID.Text + "','" + txtLoanUserID.Text + "', '" + txtLoanTitle.Text + "', '" + dateOutTimePicker.Value.Date + "')";

    but still no luck. its as if its adding it to memmory or something and waiting to be written to the table.

  • User profile image
    W3bbo

    Scott Bryen wrote:

    Would the lack of commas sperating the values affect it lo Tongue Out, how do u do params?

    EDIT:

    chnaged line to this

    VALUES ('" + txtLoanBookID.Text + "','" + txtLoanUserID.Text + "', '" + txtLoanTitle.Text + "', '" + dateOutTimePicker.Value.Date + "')";

    but still no luck. its as if its adding it to memmory or something and waiting to be written to the table.



    "Proper" Params only work with Stored Procedures, otherwise you can use anonymous params for simple injection-proofing. More on that later.

    If you're using SQL Server, have you tried using the Profiler tool to see if the database is receiving the commands and how it's executing them?

    Located here: "%programfiles%\Microsoft SQL Server\80\Tools\Binn\profiler.exe"

  • User profile image
    Scott Bryen

    W3bbo wrote:
    
    If you're using SQL Server, have you tried using the Profiler tool to see if the database is receiving the commands and how it's executing them?

    Located here: "%programfiles%\Microsoft SQL Server\80\Tools\Binn\profiler.exe"


    I cant seem to find this tool, guess its not bundeled with express lol Tongue Out

    About the params, i tried this:

    cmdString = "INSERT INTO dbo.book_loan (book_id, user_id, title, date_taken)VALUES (@loanBookIDParam, @loanUserIDParam, @loanTitleParam, @loanDateTakenParam)";

    // Create a new connection to the database and also a new command.

    SqlConnection dbConn = new SqlConnection(conn);

    SqlCommand cmd = new SqlCommand(cmdString);

    // Some parameters for sql query.

    SqlParameter param1 = new SqlParameter("@loanBookIDParam", SqlDbType.Int);

    param1.Direction = ParameterDirection.Input;

    param1.Value = int.Parse(txtLoanBookID.Text.ToString());

    SqlParameter param2 = new SqlParameter("@loanUserIDParam", SqlDbType.Int);

    param2.Direction = ParameterDirection.Input;

    param2.Value = int.Parse(txtLoanUserID.Text.ToString());

    SqlParameter param3 = new SqlParameter("@loanTitleParam", SqlDbType.NVarChar, 150);

    param3.Direction = ParameterDirection.Input;

    param3.Value = txtLoanTitle.Text;

    SqlParameter param4 = new SqlParameter("@loanDateTakenParam", SqlDbType.SmallDateTime);

    param4.Direction = ParameterDirection.Input;

    param4.Value = dateOutTimePicker.Value.Date;

    // Put params into an array.

    SqlParameter[] cmdParams = { param1, param2, param3, param4 };

    // Add aparemeters to command.

    cmd.Parameters.AddRange(cmdParams);

    But yet again it still doenst seem to add any thing to the data base. very strange.

  • User profile image
    PerfectPhase

    Is this a winforms project?  Where is the database?  What's your connection string?

    I've seen people have the db in their project dir, which gets copied into the build directory and the insert is done in the copy and when you stop and restart the changes are lost.  They to spent ages looking for a reason why records do not insert Smiley

  • User profile image
    Scott Bryen

    PerfectPhase wrote:
     Is this a winforms project?  Where is the database?  What's your connection string?

    I've seen people have the db in their project dir, which gets copied into the build directory and the insert is done in the copy and when you stop and restart the changes are lost.  They to spent ages looking for a reason why records do not insert Smiley


    yep its winforms. I designed my datab ase using the datbase explorer in c# express. Then i aded a data source to my project it did say something about copying it to my porject and of course i pressed yes Tongue Out lol so now i have a lil data base in my solution.

    this is my connection string (the one from settings.settings).

    Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\lms_db.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

     if i click in the little box i have this one.

    Visual Studio 2005\Projects\lms\LibraryManagementSystem\LibraryManagementSystem\lms_db.mdf

    so i guess its doing what u said ?

  • User profile image
    W3bbo

    Scott Bryen wrote:
    W3bbo wrote:
    If you're using SQL Server, have you tried using the Profiler tool to see if the database is receiving the commands and how it's executing them?

    Located here: "%programfiles%\Microsoft SQL Server\80\Tools\Binn\profiler.exe"


    I cant seem to find this tool, guess its not bundeled with express lol Tongue Out


    Yep, you're right, it only comes with SQL Server 2000 Standard or above.

    Which is a shame, because it's a very nifty little program.

  • User profile image
    PerfectPhase

    Scott Bryen wrote:
    
    PerfectPhase wrote:  Is this a winforms project?  Where is the database?  What's your connection string?

    I've seen people have the db in their project dir, which gets copied into the build directory and the insert is done in the copy and when you stop and restart the changes are lost.  They to spent ages looking for a reason why records do not insert Smiley


    yep its winforms. I designed my datab ase using the datbase explorer in c# express. Then i aded a data source to my project it did say something about copying it to my porject and of course i pressed yes Tongue Out lol so now i have a lil data base in my solution.

    this is my connection string (the one from settings.settings).

    Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\lms_db.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

     if i click in the little box i have this one.

    Visual Studio 2005\Projects\lms\LibraryManagementSystem\LibraryManagementSystem\lms_db.mdf

    so i guess its doing what u said ?


    Have a look in your bin/debug directory, if you have a copy in there to, that's your problem.

    If you want to preserve changes between debug runs, in the Solution Explorer, select the db file attached to the app and change the "Copy to output directory" property to "Do Not Copy".  You just have to remember to copy the version from the solution directory to the debug if you make any changes.

    Other options are to change the connection string so it's hard coded to the DB in your solution dir (remember to change it back when you do a release!), our to create a hosted database in SQL and attach to that rather than doing a file attach.

    Good tool for mangaging SQL Express is http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

  • User profile image
    Maurits

    MattShepherd wrote:
    isnt the correct SQL syntax "Insert INTO.."


    Yup.  Standard SQL is "INSERT INTO Table VALUES ..." and "DELETE FROM Table WHERE ...".  T-SQL, as a proprietary extension, allows you to say "INSERT Table" and "DELETE Table WHERE ..." instead.

  • User profile image
    Scott Bryen

    Ok so i have deleted the database from my solution and the data set etc. So now every time i click the save button it should insert directly to the database. But when i go to the database explorer and view table data it still shows nothing. lol its starting to wind me up now coz i spent all week last week doing the same thing in j# and it didnt work either so i switched to c# and yet it still dont work. Mad lol Tongue Out.

    I dont think its my syntax becuase it all compiles fine but just wont insert!!! Any more solutions?

  • User profile image
    PerfectPhase

    can you zip the project up and stick it on the web so we can have a look?

  • User profile image
    Scott Bryen

    Can i send u on msn as its my coursework for computing  so i dont think i should go posting it all over the internet lol.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.