C# Sql INSERT statment
-
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!!
-> "... 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.
-
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
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 -
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!!
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
-
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
I read the article, Thank you...after I get it working I will try to make it safer
-
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
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.
-
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.
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
-
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.
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
-
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
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
-
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
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)"
-
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)"
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
-
I read the article, Thank you...after I get it working I will try to make it safer
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