Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
  1. Home
  2. General Programming
  3. C#
  4. C# Sql INSERT statment

C# Sql INSERT statment

Scheduled Pinned Locked Moved C#
helpdatabasecsharp
18 Posts 7 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • R Offline
    R Offline
    Rinventive
    wrote on last edited by
    #1

    I am trying to insert data into a Access db. I am able to connect to the db fine. I get an error in the catch telling me I have a problem with my sql statment. This is my sql statment. (I have checked the spelling of all variables and field, table names etc...) All information is comming from textboxes --except newNum -- uses a method to find the next avalible number (worked like this on a different db) string sqlString = "INSERT INTO Cafe (CustomerNumber, FirstName, LastName, Address, City, Province, PostalCode, PhoneNumber, UserName, Password, TimePurchased ) VALUES (" + newNum.ToString() + ",'" + FirstNameTxt.Text + "','" + LastNameTxt.Text + "','" + AddressTxt.Text + "','" + CityTxt.Text + "','" + ProvinceTxt.Text + "','" + PostalCodeTxt.Text.ToUpper() + "','" + PhoneNumTxt.Text + "','" + UserNameTxt.Text + "','" + PasswordTxt.Text + "'," + TimePurchaseTxt.Text.ToString() + ")"; the Access db is called Assignment3 the table is called Cafe CustomerNumber (autoNumber) PK FirstName (Text) LastName (Text) Address (Text) City (Text) Province (Text) PostalCode (Text) PhoneNumber (Number) UserName (Text) Password (Text) TimePurchased (Number) I am new to sql and would really like some help Thanks

    C C 2 Replies Last reply
    0
    • R Rinventive

      I am trying to insert data into a Access db. I am able to connect to the db fine. I get an error in the catch telling me I have a problem with my sql statment. This is my sql statment. (I have checked the spelling of all variables and field, table names etc...) All information is comming from textboxes --except newNum -- uses a method to find the next avalible number (worked like this on a different db) string sqlString = "INSERT INTO Cafe (CustomerNumber, FirstName, LastName, Address, City, Province, PostalCode, PhoneNumber, UserName, Password, TimePurchased ) VALUES (" + newNum.ToString() + ",'" + FirstNameTxt.Text + "','" + LastNameTxt.Text + "','" + AddressTxt.Text + "','" + CityTxt.Text + "','" + ProvinceTxt.Text + "','" + PostalCodeTxt.Text.ToUpper() + "','" + PhoneNumTxt.Text + "','" + UserNameTxt.Text + "','" + PasswordTxt.Text + "'," + TimePurchaseTxt.Text.ToString() + ")"; the Access db is called Assignment3 the table is called Cafe CustomerNumber (autoNumber) PK FirstName (Text) LastName (Text) Address (Text) City (Text) Province (Text) PostalCode (Text) PhoneNumber (Number) UserName (Text) Password (Text) TimePurchased (Number) I am new to sql and would really like some help Thanks

      C Offline
      C Offline
      codeprojectin
      wrote on last edited by
      #2

      Remove the autonumber, and it would be better controlled by Access itself instead of your function. Include the error message in the email. Also, provide the value of sqlString at runtime which would be easy for us to look into.

      R 1 Reply Last reply
      0
      • R Rinventive

        I am trying to insert data into a Access db. I am able to connect to the db fine. I get an error in the catch telling me I have a problem with my sql statment. This is my sql statment. (I have checked the spelling of all variables and field, table names etc...) All information is comming from textboxes --except newNum -- uses a method to find the next avalible number (worked like this on a different db) string sqlString = "INSERT INTO Cafe (CustomerNumber, FirstName, LastName, Address, City, Province, PostalCode, PhoneNumber, UserName, Password, TimePurchased ) VALUES (" + newNum.ToString() + ",'" + FirstNameTxt.Text + "','" + LastNameTxt.Text + "','" + AddressTxt.Text + "','" + CityTxt.Text + "','" + ProvinceTxt.Text + "','" + PostalCodeTxt.Text.ToUpper() + "','" + PhoneNumTxt.Text + "','" + UserNameTxt.Text + "','" + PasswordTxt.Text + "'," + TimePurchaseTxt.Text.ToString() + ")"; the Access db is called Assignment3 the table is called Cafe CustomerNumber (autoNumber) PK FirstName (Text) LastName (Text) Address (Text) City (Text) Province (Text) PostalCode (Text) PhoneNumber (Number) UserName (Text) Password (Text) TimePurchased (Number) I am new to sql and would really like some help Thanks

        C Offline
        C Offline
        Colin Angus Mackay
        wrote on last edited by
        #3

        You cannot insert into an AutoNumber column. Either make the column a regular number column, or do not include the column in the INSERT statement (allowing the database to assign it the next available number). Also, your code is extremely susceptable to a SQL Injection Attack. You should read SQL Injection Attackes and Tips on How To Prevent Them[^]


        My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

        R 1 Reply Last reply
        0
        • C codeprojectin

          Remove the autonumber, and it would be better controlled by Access itself instead of your function. Include the error message in the email. Also, provide the value of sqlString at runtime which would be easy for us to look into.

          R Offline
          R Offline
          Rinventive
          wrote on last edited by
          #4

          The autonumber is controled by Access. each feild does have a value when I debug.

          1 Reply Last reply
          0
          • C Colin Angus Mackay

            You cannot insert into an AutoNumber column. Either make the column a regular number column, or do not include the column in the INSERT statement (allowing the database to assign it the next available number). Also, your code is extremely susceptable to a SQL Injection Attack. You should read SQL Injection Attackes and Tips on How To Prevent Them[^]


            My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

            R Offline
            R Offline
            Rinventive
            wrote on last edited by
            #5

            I still have a problem....this is what I did to remove the CustomerNum string sqlString = "INSERT INTO Cafe ( FirstName, LastName, Address, City, Province, PostalCode, PhoneNumber, UserName, Password, TimePurchased ) VALUES (" + FirstNameTxt.Text + ",'" + LastNameTxt.Text + "','" + AddressTxt.Text + "','" + CityTxt.Text + "','" + ProvinceTxt.Text + "','" + PostalCodeTxt.Text.ToUpper() + "','" + PhoneNumTxt.Text + "','" + UserNameTxt.Text + "','" + PasswordTxt.Text + "'," + TimePurchaseTxt.Text + ")"; Error -- Syntax error in INSERT INTO statment Thanks for your help!!

            C V C 3 Replies Last reply
            0
            • R Rinventive

              I still have a problem....this is what I did to remove the CustomerNum string sqlString = "INSERT INTO Cafe ( FirstName, LastName, Address, City, Province, PostalCode, PhoneNumber, UserName, Password, TimePurchased ) VALUES (" + FirstNameTxt.Text + ",'" + LastNameTxt.Text + "','" + AddressTxt.Text + "','" + CityTxt.Text + "','" + ProvinceTxt.Text + "','" + PostalCodeTxt.Text.ToUpper() + "','" + PhoneNumTxt.Text + "','" + UserNameTxt.Text + "','" + PasswordTxt.Text + "'," + TimePurchaseTxt.Text + ")"; Error -- Syntax error in INSERT INTO statment Thanks for your help!!

              C Offline
              C Offline
              Carsten Zeumer
              wrote on last edited by
              #6

              what does the sqlString look like if you dump it to the console? Since you do not quote you parameters it is possible that they contain quotes or commas... /cadi 24 hours is not enough

              R 1 Reply Last reply
              0
              • C Carsten Zeumer

                what does the sqlString look like if you dump it to the console? Since you do not quote you parameters it is possible that they contain quotes or commas... /cadi 24 hours is not enough

                R Offline
                R Offline
                Rinventive
                wrote on last edited by
                #7

                I'm not sure what you mean "dump it to the console" and none of the text input has anything other then letters and numbers. I'm new to this...Thanks

                C 1 Reply Last reply
                0
                • R Rinventive

                  I still have a problem....this is what I did to remove the CustomerNum string sqlString = "INSERT INTO Cafe ( FirstName, LastName, Address, City, Province, PostalCode, PhoneNumber, UserName, Password, TimePurchased ) VALUES (" + FirstNameTxt.Text + ",'" + LastNameTxt.Text + "','" + AddressTxt.Text + "','" + CityTxt.Text + "','" + ProvinceTxt.Text + "','" + PostalCodeTxt.Text.ToUpper() + "','" + PhoneNumTxt.Text + "','" + UserNameTxt.Text + "','" + PasswordTxt.Text + "'," + TimePurchaseTxt.Text + ")"; Error -- Syntax error in INSERT INTO statment Thanks for your help!!

                  V Offline
                  V Offline
                  V 0
                  wrote on last edited by
                  #8

                  -> "... VALUES ('" not "... VALUES (" some on "')" instead of ")" good luck. (PS: Debug the code, quickwatch the sqlString variable when fully loaded, copy the statement and execute it manually, then you'll know what went wrong :-) or catch the execution and dump the error message.) No hurries, no worries.

                  1 Reply Last reply
                  0
                  • R Rinventive

                    I'm not sure what you mean "dump it to the console" and none of the text input has anything other then letters and numbers. I'm new to this...Thanks

                    C Offline
                    C Offline
                    Carsten Zeumer
                    wrote on last edited by
                    #9

                    Somthing like System.Console.Out.WriteLine(sqlString); . Have yout tried to paste the result in any DB-Mamangent tool (if you use SQL Server try the Query Analyzer)? You'll probably get a more detailed error description.... /cadi 24 hours is not enough

                    1 Reply Last reply
                    0
                    • R Rinventive

                      I still have a problem....this is what I did to remove the CustomerNum string sqlString = "INSERT INTO Cafe ( FirstName, LastName, Address, City, Province, PostalCode, PhoneNumber, UserName, Password, TimePurchased ) VALUES (" + FirstNameTxt.Text + ",'" + LastNameTxt.Text + "','" + AddressTxt.Text + "','" + CityTxt.Text + "','" + ProvinceTxt.Text + "','" + PostalCodeTxt.Text.ToUpper() + "','" + PhoneNumTxt.Text + "','" + UserNameTxt.Text + "','" + PasswordTxt.Text + "'," + TimePurchaseTxt.Text + ")"; Error -- Syntax error in INSERT INTO statment Thanks for your help!!

                      C Offline
                      C Offline
                      Colin Angus Mackay
                      wrote on last edited by
                      #10

                      You don't put a apostrophes around the the first value like you have on the other values. By the way, this is extremely bad practice and leaves your code open to attack. You should read the article that I gave you a link to in order to find out how to prevent attacks on your code. To summaries the immediate situation, you should use a parameterised query rather than injecting the values directly into the SQL statement.


                      My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

                      R 2 Replies Last reply
                      0
                      • C Colin Angus Mackay

                        You don't put a apostrophes around the the first value like you have on the other values. By the way, this is extremely bad practice and leaves your code open to attack. You should read the article that I gave you a link to in order to find out how to prevent attacks on your code. To summaries the immediate situation, you should use a parameterised query rather than injecting the values directly into the SQL statement.


                        My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

                        R Offline
                        R Offline
                        Rinventive
                        wrote on last edited by
                        #11

                        I read the article, Thank you...after I get it working I will try to make it safer

                        D 1 Reply Last reply
                        0
                        • C Colin Angus Mackay

                          You don't put a apostrophes around the the first value like you have on the other values. By the way, this is extremely bad practice and leaves your code open to attack. You should read the article that I gave you a link to in order to find out how to prevent attacks on your code. To summaries the immediate situation, you should use a parameterised query rather than injecting the values directly into the SQL statement.


                          My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

                          R Offline
                          R Offline
                          Rinventive
                          wrote on last edited by
                          #12

                          I used the System.Console.Out.WriteLine(sqlString); so I could read what valuse are in the statment durning debug...I can't seem to see anything wrong. Just inexperienced and need practice.

                          C R 2 Replies Last reply
                          0
                          • R Rinventive

                            I used the System.Console.Out.WriteLine(sqlString); so I could read what valuse are in the statment durning debug...I can't seem to see anything wrong. Just inexperienced and need practice.

                            C Offline
                            C Offline
                            Colin Angus Mackay
                            wrote on last edited by
                            #13

                            Rinventive wrote: I can't seem to see anything wrong. Post the SQL Statement on the forum then we can have a look and see what is wrong.


                            My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

                            1 Reply Last reply
                            0
                            • R Rinventive

                              I used the System.Console.Out.WriteLine(sqlString); so I could read what valuse are in the statment durning debug...I can't seem to see anything wrong. Just inexperienced and need practice.

                              R Offline
                              R Offline
                              Rinventive
                              wrote on last edited by
                              #14

                              Here is the INSERT statment I started with, this is from a previous project and it works fine. string sqlString = "INSERT INTO Members (MemberNum, FirstName, LastName, Address, City, Province, PostalCode, Status, Balance ) VALUES (" + newNum.ToString() + ",'" + FirstName.Text + "','" + LastName.Text + "','" + Address.Text + "','" + City.Text + "','" + comboBox1.Text + "','" + PostalCode.Text.ToUpper() + "','" + GetMemberStatus() + "'," + BalanceOwing.Text + ")"; and the db information MemberNum (autonumber) LastName (text) FirstName (text) Address (text) City (text) Province (text) PostalCode (text) Status (text) Balance (number) Sorry to be so much trouble and thanks for your time

                              R 1 Reply Last reply
                              0
                              • R Rinventive

                                Here is the INSERT statment I started with, this is from a previous project and it works fine. string sqlString = "INSERT INTO Members (MemberNum, FirstName, LastName, Address, City, Province, PostalCode, Status, Balance ) VALUES (" + newNum.ToString() + ",'" + FirstName.Text + "','" + LastName.Text + "','" + Address.Text + "','" + City.Text + "','" + comboBox1.Text + "','" + PostalCode.Text.ToUpper() + "','" + GetMemberStatus() + "'," + BalanceOwing.Text + ")"; and the db information MemberNum (autonumber) LastName (text) FirstName (text) Address (text) City (text) Province (text) PostalCode (text) Status (text) Balance (number) Sorry to be so much trouble and thanks for your time

                                R Offline
                                R Offline
                                Rinventive
                                wrote on last edited by
                                #15

                                Here is my current statment string sqlString = "INSERT INTO Cafe (CustomerNumber, FirstName, LastName, Address, City, Province, PostalCode, PhoneNumber, UserName, Password, TimePurchased ) VALUES (" + newNum.ToString() + ",'" + FirstNameTxt.Text + "','" + LastNameTxt.Text + "','" + AddressTxt.Text + "','" + CityTxt.Text + "','" + ProvinceTxt.Text + "','" + PostalCodeTxt.Text.ToUpper() + "','" + PhoneNumTxt.Text + "','" + UserNameTxt.Text + "','" + PasswordTxt.Text + "'," + TimePurchaseTxt.Text + ")"; and my current db CustomerNumber (autonumber) FirstName (Text) //Bill LastName (Text) //Ray Address (Text) //anystreet City (Text) //Hometown Province (Text) //Ont PostalCode (Text) //N5BY4G PhoneNumber (Number) //123456 UserName (Text) //Rbill Password (Text) //whatever TimePurchased (Number) //1 I how\pe this helps

                                R 1 Reply Last reply
                                0
                                • R Rinventive

                                  Here is my current statment string sqlString = "INSERT INTO Cafe (CustomerNumber, FirstName, LastName, Address, City, Province, PostalCode, PhoneNumber, UserName, Password, TimePurchased ) VALUES (" + newNum.ToString() + ",'" + FirstNameTxt.Text + "','" + LastNameTxt.Text + "','" + AddressTxt.Text + "','" + CityTxt.Text + "','" + ProvinceTxt.Text + "','" + PostalCodeTxt.Text.ToUpper() + "','" + PhoneNumTxt.Text + "','" + UserNameTxt.Text + "','" + PasswordTxt.Text + "'," + TimePurchaseTxt.Text + ")"; and my current db CustomerNumber (autonumber) FirstName (Text) //Bill LastName (Text) //Ray Address (Text) //anystreet City (Text) //Hometown Province (Text) //Ont PostalCode (Text) //N5BY4G PhoneNumber (Number) //123456 UserName (Text) //Rbill Password (Text) //whatever TimePurchased (Number) //1 I how\pe this helps

                                  R Offline
                                  R Offline
                                  Rinventive
                                  wrote on last edited by
                                  #16

                                  Here is the sql statment from System.Console.Out.WriteLine(sqlString); sql string = "INSERT INTO Cafe (CustomerNumber, FirstName, LastName, Address, City, Province, PostalCode, PhoneNumber, UserName, Password, TimePurchased) VALUES (2,'Bill','Ray','anystreet','Hometomwn','ont',N6BYK6','6727894','Rbill','123456',0)"

                                  V 1 Reply Last reply
                                  0
                                  • R Rinventive

                                    Here is the sql statment from System.Console.Out.WriteLine(sqlString); sql string = "INSERT INTO Cafe (CustomerNumber, FirstName, LastName, Address, City, Province, PostalCode, PhoneNumber, UserName, Password, TimePurchased) VALUES (2,'Bill','Ray','anystreet','Hometomwn','ont',N6BYK6','6727894','Rbill','123456',0)"

                                    V Offline
                                    V Offline
                                    vincent reynolds 0
                                    wrote on last edited by
                                    #17

                                    The value N6BYK6 is missing the first single quote. Risk of SQL injection attacks aside, it may make things a bit more readable to write a small function to quote text values. That way, instead of relying on the visually confusing "','", you could just say SingleQuote(firstName). Make sense? V

                                    1 Reply Last reply
                                    0
                                    • R Rinventive

                                      I read the article, Thank you...after I get it working I will try to make it safer

                                      D Offline
                                      D Offline
                                      Dave Kreskowiak
                                      wrote on last edited by
                                      #18

                                      The other point to that article is to make your code easier to debug and FAR less suseptible to your input ACCIDENTLY breaking your code. If the user happens to type an ' or " in those textboxes somewhere, it'll break your code when you try and insert that into the database. Parameterized queries remove that possiblity because the Parameter objects automatically escape these characters so they won't break your SQL code. SQL attacks, or failure exploits, are not all deliberate, most happen by pure accident. Learn to avoid situations where your code can break by accident now, before bad habits become your normal practice. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

                                      1 Reply Last reply
                                      0
                                      Reply
                                      • Reply as topic
                                      Log in to reply
                                      • Oldest to Newest
                                      • Newest to Oldest
                                      • Most Votes


                                      • Login

                                      • Don't have an account? Register

                                      • Login or register to search.
                                      • First post
                                        Last post
                                      0
                                      • Categories
                                      • Recent
                                      • Tags
                                      • Popular
                                      • World
                                      • Users
                                      • Groups