I had written my project geared for MySQL... 15,000 lines of code. We hire a new IT manager who decides that MSSQL is the way to go. No amount of bribery will sway this man, not even a case of beer. So now I have to re-write portions of my code to work with MSSQL. Problem is, I haven't learned MSSQL, although I learned very quickly that my inserts and queries and database connections all went FUBAR. I fixed the db conns, but I still have issues with inserts and queries. For example, when trying to insert a new record, I get an error

Warning: MS SQL message: Cannot insert explicit value for identity column in table 'tad_casenum' when IDENTITY_INSERT is set to OFF.

because I am trying to insert a line that has an ID. First, I don't even know if I CAN insert a line with an ID, if I am to leave part of it blank (although I think not, because when I tried to leave part of it out it complained that there were not enough variables to match columns) or what the heck I am supposed to do.

Then, it doesn't like my query which is to select * from table_name.

Warning: MS SQL: Query failed in c:\blah\blah.php on line 20

My MSSQL book is somewhat useless, all my PHP books are geared towards MySQL, and after this afternoon, Google asked for a divorce.

Can someone explain how this MySQL query should look in MSSQL?

$sqlquery = "INSERT INTO casenum VALUES('','". $_POST['casenum'] ."','". $_POST['forumst'] ."','". $_POST['county'] ."','". $_POST['plaintiff'] ."','". $_POST['defendant'] ."')";
$queryresult = mysql_query($sqlquery) or die(" Could not execute mysql query !");

$sqlquery = "SELECT * from casenum";

Thanks 😉

    I feel your pain. went through something similar in my previous job. Except I had to drop PHP too...thus the reason for it being my previous job...🙂

    As for your query, it looks fine to me--you may want to explicitly name the fields though...not sure if that would affect anything--

    so it would look like: insert into casenum(field1,field2)values('$field1','$field2')
    also, if casenum is an int, drop the quotes...

      There be gods... I got the insert query to work.

      Now, my only problem is echoing the id. I use dto use the

      echo mysql_insert_id()

      to display the ID... Sure enough, MSSQL doesn't like that a bit.

      Trying to find the fix for this one....

      maxpup: Yep, that was the correct way to insert data. After slamming my head into the book, the blood and sweat on my forehead stuck to a page which showed the syntax.

      Does anyone know the MSSQL version of mysql_insert_id?

        SELECT @@IDENTITY
        Check the MSSQL manual, theres a bit more to that ...

          That's the directional boot to the arse I need!

          Thank you kindly!

            in my opinion
            not in most people's opinion

            any SQL is a pain in PHP a$s

            so I stick happily to my own chosen method: [right]Pure PHP Mini Data Base Class
            storing data
            in my own created data files
            with total control
            of any syntax used            [/right]
            should halojoy feel sorry

            for those
            putting themselves
            into troubles

            they are asking for
            when mixing pure PHP
            with other external programs

            whether it is data storage related or not

            No!
            I do not think halojoy
            should bother pity them

            they should know better if they are smart
            shouldnt they?

            🆒

            halojoy - a sensible man - take action and conclusion from what he see is happening

            sweden

              I've nearly learned to enjoy the pain in the ass known as MySQL. MSSQL looks too brutal though. At least with MySQL I'm playing with a known entity.

                does anyone have experience of how using database abstraction classes (such as the one in PEAR ) makes this kind of thing easier?

                I always use this class, just in case I need to change databases in a hurry, but I've never actually had to switch, so I've never been able to test it out in the "real world".

                  badgoat wrote:

                  I've nearly learned to enjoy the pain in the ass known as MySQL. MSSQL looks too brutal though. At least with MySQL I'm playing with a known entity.

                  Like everything MickeySoft, sqlserver is crap. Your new IT manager is either sticking to what he knows, or another mug who buys into the MS myth cos he knows f'all technical. You will have to buy the book - and not one published by MS - cos as usual they break every 'standard' in the attempt to lock you in.

                    Write a Reply...