Re: C# Sql INSERT statment - C# Discussion Boards

Rinventive8-Jun-05 23:33

8-Jun-05 23:33

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

Re: C# Sql INSERT statment

codeprojectin8-Jun-05 23:47

codeprojectin

8-Jun-05 23:47

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.

Re: C# Sql INSERT statment

Rinventive9-Jun-05 0:25

Rinventive

9-Jun-05 0:25

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

Re: C# Sql INSERT statment

Colin Angus Mackay8-Jun-05 23:56

Colin Angus Mackay

8-Jun-05 23:56

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

Re: C# Sql INSERT statment

Rinventive9-Jun-05 0:30

Rinventive

9-Jun-05 0:30

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!!

Re: C# Sql INSERT statment

Carsten Zeumer9-Jun-05 0:54

Carsten Zeumer

9-Jun-05 0:54

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

Re: C# Sql INSERT statment

Rinventive9-Jun-05 0:57

Rinventive

9-Jun-05 0:57

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

Re: C# Sql INSERT statment

Carsten Zeumer9-Jun-05 1:03

Carsten Zeumer

9-Jun-05 1:03

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

Re: C# Sql INSERT statment

V.9-Jun-05 1:02

9-Jun-05 1:02

-> "... 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 Smile | :)

or catch the execution and dump the error message.)

No hurries, no worries.

Re: C# Sql INSERT statment

Colin Angus Mackay9-Jun-05 1:06

Colin Angus Mackay

9-Jun-05 1:06

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

Re: C# Sql INSERT statment

Rinventive9-Jun-05 1:13

Rinventive

9-Jun-05 1:13

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

Re: C# Sql INSERT statment

Dave Kreskowiak9-Jun-05 6:38

Dave Kreskowiak

9-Jun-05 6:38

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

Re: C# Sql INSERT statment

Rinventive9-Jun-05 1:28

Rinventive

9-Jun-05 1:28

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.

Re: C# Sql INSERT statment

Colin Angus Mackay9-Jun-05 1:31

Colin Angus Mackay

9-Jun-05 1:31

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

Re: C# Sql INSERT statment

Rinventive9-Jun-05 1:34

Rinventive

9-Jun-05 1:34

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

Re: C# Sql INSERT statment

Rinventive9-Jun-05 1:42

Rinventive

9-Jun-05 1:42

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

Re: C# Sql INSERT statment

Rinventive9-Jun-05 1:53

Rinventive

9-Jun-05 1:53

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)"

Re: C# Sql INSERT statment

vincent.reynolds9-Jun-05 5:50

vincent.reynolds

9-Jun-05 5:50

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

ListView multiline items?

george ivanov8-Jun-05 22:58

george ivanov

8-Jun-05 22:58

Is it possible the items in ListView control to be set as multiline, so the text to be displayed in two lines for one item?

thanks.

Re: ListView multiline items?

Philip Price8-Jun-05 23:18

Philip Price

8-Jun-05 23:18

If \r\n doesnt work, then you'll have to inherit the control and handle the paint events.

Re: ListView multiline items?

Dave Kreskowiak9-Jun-05 6:32

Dave Kreskowiak

9-Jun-05 6:32

The managed ListView control doesn't support multiline rendering. You'll have to subclass it and render the ListView yourself.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

Please Help! Function on Separate Form

DJdC8-Jun-05 22:56

DJdC

8-Jun-05 22:56

I have 2 forms, form1 and form2. form1 has a function called components. I need now, for form2 to execute this function on form1 as per normal. I have no clue on form linking, please help me! Thanks!

Re: Please Help! Function on Separate Form

Carsten Zeumer8-Jun-05 23:19

Carsten Zeumer

8-Jun-05 23:19

You need to pass somehow form1 to form2.
Create a property of type From1 in Form2. Then, after creating as Form2 assign Form1 to form2.Form1. (I hope this sentence is understandable Wink | ;)

)

public class Form2 : System.Windows.Forms
{
// ...
  private From1 m_Form1 = null;
  public Form1 Form1
  {
     get {return this.m_Form1;}
     set {this.m_Form1 = value;}
  }

  public void SomeFunctionUsingComponentsOnForm1()
  {
      if (this.m_Form1 == null)
         throw new ArgumentException("Form1 is not assigned.");
      this.m_Form1.components();
  }
}

public class Form1 : System.Windows.Forms
{
 // ....
 public void ShowForm2()
 {
   Form2 form2 = new Form2();
   form2.Form1 = this;
   form2.Show();
 }
}

/cadi

24 hours is not enough

Cadi's Solution

DJdC8-Jun-05 23:45

DJdC

8-Jun-05 23:45

Thanks Cadi for the swift reply! Yes I understand what you were saying on the top but your program doesn't really go with my understanding, especially the bottom part, because it looks like its reopening form2? But both forms are open already and I need for form2 to get form1 to run its function called components. Maybe you could explain your program abit more? Thanks!

Re: Cadi's Solution

Carsten Zeumer9-Jun-05 0:03

Carsten Zeumer

9-Jun-05 0:03

Ok... so form1 is not the owner of form2 (ok, if it would be like this the Owner property would have been ok...)
Hmm... this leeds me to a second aproach.
If both forms have the same owner then you could search for form1 using something like this: this.FindForm().OwnedForms

If there is always only ONE instance of form1 you could try somthing like this:

public class Form1 : System.Windows.Form
{
   private static Form1 m_GlobalInstance;
   public Form1()
   { 
     // set global instance
     m_GlobalInstance = this;
   }
 
  protected override void OnClosed(EventArgs e)
   {
     // invalidate global instance
      m_GlobalInstance = null;
      base.OnClosed (e);
  }

  // ....
   
   // property to access global instance
   public static Form1 GlobalInstance
   {
     return m_GlobalInstance;
   }

}

public class Form2 : System.Windows.Form
{
  // ....
  public void SomeFunctionUsingComponentsOnForm1()  
  {
      if (Form1.GlobalInstance == null)
         throw new ArgumentException("Form1 is not yet created.");
      Form1.GlobalInstance.components();  
   }
}

/cadi

24 hours is not enough

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.