Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > asp .net > answers

Inserting dates into SQL Server DB

Andrew Banks
Guest
  
Posts: n/a
#1: Nov 18 '05
I'm running the following code in a C#.NET page and it doesn't enter the
values into the DB. I'm certain the problem is to do with the txtBirth
field. It allows users to enter a DOB as dd/mm/yyyy and I think it's the
slashes(/) that are causing the problem. If I don't enter a DOB in this
field then all the data enters into the database without a problem.

Any ideas?

SQL Server 2000, VS.NET, C#

if (Page.IsValid)

{

// Save the new user to the database

SqlConnection con;

string sql;

SqlCommand cmd;

StringBuilder sb = new StringBuilder();

ArrayList values = new ArrayList();

sb.Append("INSERT INTO [User] ");

sb.Append("(UserID, Login, Password, FirstName, LastName, ");

sb.Append("PhoneNumber, Email, IsAdministrator, Address, ");

sb.Append("CellNumber, DateOfBirth) ");

sb.Append("VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}',
");

// Optional values without quotes as they can be null

sb.Append("{8}, {9}, {10})");

// Add required values to replace

values.Add(Guid.NewGuid().ToString());

values.Add(txtLogin.Text);

values.Add(txtPwd.Text);

values.Add(txtFName.Text);

values.Add(txtLName.Text);

values.Add(txtPhone.Text);

values.Add(txtEmail.Text);

values.Add(0);

// Add the optional values or Null

if (txtAddress.Text != string.Empty)

values.Add("'" + txtAddress.Text + "'");

else

values.Add("Null");

if (txtMobile.Text != string.Empty)

values.Add("'" + txtMobile.Text + "'");

else

values.Add("Null");

if (txtBirth.Text != string.Empty)

values.Add("'" + txtBirth.Text + "'");

else

values.Add("Null");

// Format the string with the array of values

sql = String.Format(sb.ToString(), values.ToArray());

// Connect and execute the SQL

con = new SqlConnection("data source=127.0.0.1;initial catalog=Friends; user
id=sa;");

cmd = new SqlCommand(sql, con);

con.Open();

bool doredirect=true;

try

{

cmd.ExecuteNonQuery();

}

catch

{

doredirect = false;

this.lblMessage.Visible = true;

//this.lblMessage.Text = "Insert couldn't be performed. Username may already
be taken.";

this.lblMessage.Text = sql;

}

finally

{

con.Close();

}

if (doredirect)

Response.Redirect("Login.aspx");

}



else

lblMessage.Text = "Fix the following errors and retry:";

}
Miha Markic
Guest
  
Posts: n/a
#2: Nov 18 '05

re: Inserting dates into SQL Server DB

Hi Andrew,

You might consider using parametrised commands.
See SqlCommand.Parameters property..

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Andrew Banks" <banksy@nojunkblueyonder.co.uk> wrote in message
news:HglKb.7672$hd5.76609805@news-text.cableinet.net...[color=blue]
> I'm running the following code in a C#.NET page and it doesn't enter the
> values into the DB. I'm certain the problem is to do with the txtBirth
> field. It allows users to enter a DOB as dd/mm/yyyy and I think it's the
> slashes(/) that are causing the problem. If I don't enter a DOB in this
> field then all the data enters into the database without a problem.
>
> Any ideas?
>
> SQL Server 2000, VS.NET, C#
>
> if (Page.IsValid)
>
> {
>
> // Save the new user to the database
>
> SqlConnection con;
>
> string sql;
>
> SqlCommand cmd;
>
> StringBuilder sb = new StringBuilder();
>
> ArrayList values = new ArrayList();
>
> sb.Append("INSERT INTO [User] ");
>
> sb.Append("(UserID, Login, Password, FirstName, LastName, ");
>
> sb.Append("PhoneNumber, Email, IsAdministrator, Address, ");
>
> sb.Append("CellNumber, DateOfBirth) ");
>
> sb.Append("VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}',
> ");
>
> // Optional values without quotes as they can be null
>
> sb.Append("{8}, {9}, {10})");
>
> // Add required values to replace
>
> values.Add(Guid.NewGuid().ToString());
>
> values.Add(txtLogin.Text);
>
> values.Add(txtPwd.Text);
>
> values.Add(txtFName.Text);
>
> values.Add(txtLName.Text);
>
> values.Add(txtPhone.Text);
>
> values.Add(txtEmail.Text);
>
> values.Add(0);
>
> // Add the optional values or Null
>
> if (txtAddress.Text != string.Empty)
>
> values.Add("'" + txtAddress.Text + "'");
>
> else
>
> values.Add("Null");
>
> if (txtMobile.Text != string.Empty)
>
> values.Add("'" + txtMobile.Text + "'");
>
> else
>
> values.Add("Null");
>
> if (txtBirth.Text != string.Empty)
>
> values.Add("'" + txtBirth.Text + "'");
>
> else
>
> values.Add("Null");
>
> // Format the string with the array of values
>
> sql = String.Format(sb.ToString(), values.ToArray());
>
> // Connect and execute the SQL
>
> con = new SqlConnection("data source=127.0.0.1;initial catalog=Friends;[/color]
user[color=blue]
> id=sa;");
>
> cmd = new SqlCommand(sql, con);
>
> con.Open();
>
> bool doredirect=true;
>
> try
>
> {
>
> cmd.ExecuteNonQuery();
>
> }
>
> catch
>
> {
>
> doredirect = false;
>
> this.lblMessage.Visible = true;
>
> //this.lblMessage.Text = "Insert couldn't be performed. Username may[/color]
already[color=blue]
> be taken.";
>
> this.lblMessage.Text = sql;
>
> }
>
> finally
>
> {
>
> con.Close();
>
> }
>
> if (doredirect)
>
> Response.Redirect("Login.aspx");
>
> }
>
>
>
> else
>
> lblMessage.Text = "Fix the following errors and retry:";
>
> }
>
>[/color]
William Ryan
Guest
  
Posts: n/a
#3: Nov 18 '05

re: Inserting dates into SQL Server DB

I'm with Miha. You can accomplish the same thing with Parameters and not
add another line of code. You'll get better performance and you won't have
to worry about injection attacks or names like O'Malley.

Since you're using SQL Server..this mod will start it

sb.Append("VALUES (@FirstParamName, @SecondParamName, @ThirdParamName etc)

THen, add the parameters...

cmd.Parameters.Add("@FirstParamName", SqlDbType.DateTime).Value = (Date)
txtBirthday.Text;
(I know Birthday doesn't correspond with the first paramater, but just
wanted to show you how it works.) From what I can see, it's all upside for
using Parameters instead (and this looks ripe for a Stored Procedure).....
http://www.knowdotnet.com/articles/storedprocsvb.html

Good Luck,

Bill


"Andrew Banks" <banksy@nojunkblueyonder.co.uk> wrote in message
news:HglKb.7672$hd5.76609805@news-text.cableinet.net...[color=blue]
> I'm running the following code in a C#.NET page and it doesn't enter the
> values into the DB. I'm certain the problem is to do with the txtBirth
> field. It allows users to enter a DOB as dd/mm/yyyy and I think it's the
> slashes(/) that are causing the problem. If I don't enter a DOB in this
> field then all the data enters into the database without a problem.
>
> Any ideas?
>
> SQL Server 2000, VS.NET, C#
>
> if (Page.IsValid)
>
> {
>
> // Save the new user to the database
>
> SqlConnection con;
>
> string sql;
>
> SqlCommand cmd;
>
> StringBuilder sb = new StringBuilder();
>
> ArrayList values = new ArrayList();
>
> sb.Append("INSERT INTO [User] ");
>
> sb.Append("(UserID, Login, Password, FirstName, LastName, ");
>
> sb.Append("PhoneNumber, Email, IsAdministrator, Address, ");
>
> sb.Append("CellNumber, DateOfBirth) ");
>
> sb.Append("VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}',
> ");
>
> // Optional values without quotes as they can be null
>
> sb.Append("{8}, {9}, {10})");
>
> // Add required values to replace
>
> values.Add(Guid.NewGuid().ToString());
>
> values.Add(txtLogin.Text);
>
> values.Add(txtPwd.Text);
>
> values.Add(txtFName.Text);
>
> values.Add(txtLName.Text);
>
> values.Add(txtPhone.Text);
>
> values.Add(txtEmail.Text);
>
> values.Add(0);
>
> // Add the optional values or Null
>
> if (txtAddress.Text != string.Empty)
>
> values.Add("'" + txtAddress.Text + "'");
>
> else
>
> values.Add("Null");
>
> if (txtMobile.Text != string.Empty)
>
> values.Add("'" + txtMobile.Text + "'");
>
> else
>
> values.Add("Null");
>
> if (txtBirth.Text != string.Empty)
>
> values.Add("'" + txtBirth.Text + "'");
>
> else
>
> values.Add("Null");
>
> // Format the string with the array of values
>
> sql = String.Format(sb.ToString(), values.ToArray());
>
> // Connect and execute the SQL
>
> con = new SqlConnection("data source=127.0.0.1;initial catalog=Friends;[/color]
user[color=blue]
> id=sa;");
>
> cmd = new SqlCommand(sql, con);
>
> con.Open();
>
> bool doredirect=true;
>
> try
>
> {
>
> cmd.ExecuteNonQuery();
>
> }
>
> catch
>
> {
>
> doredirect = false;
>
> this.lblMessage.Visible = true;
>
> //this.lblMessage.Text = "Insert couldn't be performed. Username may[/color]
already[color=blue]
> be taken.";
>
> this.lblMessage.Text = sql;
>
> }
>
> finally
>
> {
>
> con.Close();
>
> }
>
> if (doredirect)
>
> Response.Redirect("Login.aspx");
>
> }
>
>
>
> else
>
> lblMessage.Text = "Fix the following errors and retry:";
>
> }
>
>[/color]
Andrew Banks
Guest
  
Posts: n/a
#4: Nov 18 '05

re: Inserting dates into SQL Server DB

Miha,

Thanks for your inout. I'm not too sure what to do with this. I'm quite new
to .NET and am working through a Wrox book - it's actually one of their
tutorials thats causing me the error!

Would you mind giving a little more explanation please.

Thanks

"Miha Markic" <miha at rthand com> wrote in message
news:OEFgjn90DHA.4064@tk2msftngp13.phx.gbl...[color=blue]
> Hi Andrew,
>
> You might consider using parametrised commands.
> See SqlCommand.Parameters property..
>
> --
> Miha Markic - RightHand .NET consulting & development
> miha at rthand com
> www.rthand.com
>
> "Andrew Banks" <banksy@nojunkblueyonder.co.uk> wrote in message
> news:HglKb.7672$hd5.76609805@news-text.cableinet.net...[color=green]
> > I'm running the following code in a C#.NET page and it doesn't enter the
> > values into the DB. I'm certain the problem is to do with the txtBirth
> > field. It allows users to enter a DOB as dd/mm/yyyy and I think it's the
> > slashes(/) that are causing the problem. If I don't enter a DOB in this
> > field then all the data enters into the database without a problem.
> >
> > Any ideas?
> >
> > SQL Server 2000, VS.NET, C#
> >
> > if (Page.IsValid)
> >
> > {
> >
> > // Save the new user to the database
> >
> > SqlConnection con;
> >
> > string sql;
> >
> > SqlCommand cmd;
> >
> > StringBuilder sb = new StringBuilder();
> >
> > ArrayList values = new ArrayList();
> >
> > sb.Append("INSERT INTO [User] ");
> >
> > sb.Append("(UserID, Login, Password, FirstName, LastName, ");
> >
> > sb.Append("PhoneNumber, Email, IsAdministrator, Address, ");
> >
> > sb.Append("CellNumber, DateOfBirth) ");
> >
> > sb.Append("VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}',[/color][/color]
'{7}',[color=blue][color=green]
> > ");
> >
> > // Optional values without quotes as they can be null
> >
> > sb.Append("{8}, {9}, {10})");
> >
> > // Add required values to replace
> >
> > values.Add(Guid.NewGuid().ToString());
> >
> > values.Add(txtLogin.Text);
> >
> > values.Add(txtPwd.Text);
> >
> > values.Add(txtFName.Text);
> >
> > values.Add(txtLName.Text);
> >
> > values.Add(txtPhone.Text);
> >
> > values.Add(txtEmail.Text);
> >
> > values.Add(0);
> >
> > // Add the optional values or Null
> >
> > if (txtAddress.Text != string.Empty)
> >
> > values.Add("'" + txtAddress.Text + "'");
> >
> > else
> >
> > values.Add("Null");
> >
> > if (txtMobile.Text != string.Empty)
> >
> > values.Add("'" + txtMobile.Text + "'");
> >
> > else
> >
> > values.Add("Null");
> >
> > if (txtBirth.Text != string.Empty)
> >
> > values.Add("'" + txtBirth.Text + "'");
> >
> > else
> >
> > values.Add("Null");
> >
> > // Format the string with the array of values
> >
> > sql = String.Format(sb.ToString(), values.ToArray());
> >
> > // Connect and execute the SQL
> >
> > con = new SqlConnection("data source=127.0.0.1;initial catalog=Friends;[/color]
> user[color=green]
> > id=sa;");
> >
> > cmd = new SqlCommand(sql, con);
> >
> > con.Open();
> >
> > bool doredirect=true;
> >
> > try
> >
> > {
> >
> > cmd.ExecuteNonQuery();
> >
> > }
> >
> > catch
> >
> > {
> >
> > doredirect = false;
> >
> > this.lblMessage.Visible = true;
> >
> > //this.lblMessage.Text = "Insert couldn't be performed. Username may[/color]
> already[color=green]
> > be taken.";
> >
> > this.lblMessage.Text = sql;
> >
> > }
> >
> > finally
> >
> > {
> >
> > con.Close();
> >
> > }
> >
> > if (doredirect)
> >
> > Response.Redirect("Login.aspx");
> >
> > }
> >
> >
> >
> > else
> >
> > lblMessage.Text = "Fix the following errors and retry:";
> >
> > }
> >
> >[/color]
>
>[/color]
Joey Powell
Guest
  
Posts: n/a
#5: Nov 18 '05

re: Inserting dates into SQL Server DB

Andrew, dates must be enclosed within single quotes in all SQL
statements. Parameter {10} below is for DateofBirth and is not
enclosed in single quotes. You should wrap it in single quotes like
you did for the first few parameters (i.e. '{10}'). HTH JP

"Andrew Banks" <banksy@nojunkblueyonder.co.uk> wrote in message news:<HglKb.7672$hd5.76609805@news-text.cableinet.net>...[color=blue]
> I'm running the following code in a C#.NET page and it doesn't enter the
> values into the DB. I'm certain the problem is to do with the txtBirth
> field. It allows users to enter a DOB as dd/mm/yyyy and I think it's the
> slashes(/) that are causing the problem. If I don't enter a DOB in this
> field then all the data enters into the database without a problem.
>
> Any ideas?
>
> SQL Server 2000, VS.NET, C#
>
> if (Page.IsValid)
>
> {
>
> // Save the new user to the database
>
> SqlConnection con;
>
> string sql;
>
> SqlCommand cmd;
>
> StringBuilder sb = new StringBuilder();
>
> ArrayList values = new ArrayList();
>
> sb.Append("INSERT INTO [User] ");
>
> sb.Append("(UserID, Login, Password, FirstName, LastName, ");
>
> sb.Append("PhoneNumber, Email, IsAdministrator, Address, ");
>
> sb.Append("CellNumber, DateOfBirth) ");
>
> sb.Append("VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}',
> ");
>
> // Optional values without quotes as they can be null
>
> sb.Append("{8}, {9}, {10})");
>
> // Add required values to replace
>
> values.Add(Guid.NewGuid().ToString());
>
> values.Add(txtLogin.Text);
>
> values.Add(txtPwd.Text);
>
> values.Add(txtFName.Text);
>
> values.Add(txtLName.Text);
>
> values.Add(txtPhone.Text);
>
> values.Add(txtEmail.Text);
>
> values.Add(0);
>
> // Add the optional values or Null
>
> if (txtAddress.Text != string.Empty)
>
> values.Add("'" + txtAddress.Text + "'");
>
> else
>
> values.Add("Null");
>
> if (txtMobile.Text != string.Empty)
>
> values.Add("'" + txtMobile.Text + "'");
>
> else
>
> values.Add("Null");
>
> if (txtBirth.Text != string.Empty)
>
> values.Add("'" + txtBirth.Text + "'");
>
> else
>
> values.Add("Null");
>
> // Format the string with the array of values
>
> sql = String.Format(sb.ToString(), values.ToArray());
>
> // Connect and execute the SQL
>
> con = new SqlConnection("data source=127.0.0.1;initial catalog=Friends; user
> id=sa;");
>
> cmd = new SqlCommand(sql, con);
>
> con.Open();
>
> bool doredirect=true;
>
> try
>
> {
>
> cmd.ExecuteNonQuery();
>
> }
>
> catch
>
> {
>
> doredirect = false;
>
> this.lblMessage.Visible = true;
>
> //this.lblMessage.Text = "Insert couldn't be performed. Username may already
> be taken.";
>
> this.lblMessage.Text = sql;
>
> }
>
> finally
>
> {
>
> con.Close();
>
> }
>
> if (doredirect)
>
> Response.Redirect("Login.aspx");
>
> }
>
>
>
> else
>
> lblMessage.Text = "Fix the following errors and retry:";
>
> }[/color]
Andrew Banks
Guest
  
Posts: n/a
#6: Nov 18 '05

re: Inserting dates into SQL Server DB

Joey, dose this statement further down the code cover that?

if (txtBirth.Text != string.Empty)
values.Add("'" + txtBirth.Text + "'");
else
values.Add("Null");

"Joey Powell" <joey.powell@goldcoinc.com> wrote in message
news:bdaf8387.0401052114.7b75b7d6@posting.google.c om...[color=blue]
> Andrew, dates must be enclosed within single quotes in all SQL
> statements. Parameter {10} below is for DateofBirth and is not
> enclosed in single quotes. You should wrap it in single quotes like
> you did for the first few parameters (i.e. '{10}'). HTH JP
>
> "Andrew Banks" <banksy@nojunkblueyonder.co.uk> wrote in message[/color]
news:<HglKb.7672$hd5.76609805@news-text.cableinet.net>...[color=blue][color=green]
> > I'm running the following code in a C#.NET page and it doesn't enter the
> > values into the DB. I'm certain the problem is to do with the txtBirth
> > field. It allows users to enter a DOB as dd/mm/yyyy and I think it's the
> > slashes(/) that are causing the problem. If I don't enter a DOB in this
> > field then all the data enters into the database without a problem.
> >
> > Any ideas?
> >
> > SQL Server 2000, VS.NET, C#
> >
> > if (Page.IsValid)
> >
> > {
> >
> > // Save the new user to the database
> >
> > SqlConnection con;
> >
> > string sql;
> >
> > SqlCommand cmd;
> >
> > StringBuilder sb = new StringBuilder();
> >
> > ArrayList values = new ArrayList();
> >
> > sb.Append("INSERT INTO [User] ");
> >
> > sb.Append("(UserID, Login, Password, FirstName, LastName, ");
> >
> > sb.Append("PhoneNumber, Email, IsAdministrator, Address, ");
> >
> > sb.Append("CellNumber, DateOfBirth) ");
> >
> > sb.Append("VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}',[/color][/color]
'{7}',[color=blue][color=green]
> > ");
> >
> > // Optional values without quotes as they can be null
> >
> > sb.Append("{8}, {9}, {10})");
> >
> > // Add required values to replace
> >
> > values.Add(Guid.NewGuid().ToString());
> >
> > values.Add(txtLogin.Text);
> >
> > values.Add(txtPwd.Text);
> >
> > values.Add(txtFName.Text);
> >
> > values.Add(txtLName.Text);
> >
> > values.Add(txtPhone.Text);
> >
> > values.Add(txtEmail.Text);
> >
> > values.Add(0);
> >
> > // Add the optional values or Null
> >
> > if (txtAddress.Text != string.Empty)
> >
> > values.Add("'" + txtAddress.Text + "'");
> >
> > else
> >
> > values.Add("Null");
> >
> > if (txtMobile.Text != string.Empty)
> >
> > values.Add("'" + txtMobile.Text + "'");
> >
> > else
> >
> > values.Add("Null");
> >
> > if (txtBirth.Text != string.Empty)
> >
> > values.Add("'" + txtBirth.Text + "'");
> >
> > else
> >
> > values.Add("Null");
> >
> > // Format the string with the array of values
> >
> > sql = String.Format(sb.ToString(), values.ToArray());
> >
> > // Connect and execute the SQL
> >
> > con = new SqlConnection("data source=127.0.0.1;initial catalog=Friends;[/color][/color]
user[color=blue][color=green]
> > id=sa;");
> >
> > cmd = new SqlCommand(sql, con);
> >
> > con.Open();
> >
> > bool doredirect=true;
> >
> > try
> >
> > {
> >
> > cmd.ExecuteNonQuery();
> >
> > }
> >
> > catch
> >
> > {
> >
> > doredirect = false;
> >
> > this.lblMessage.Visible = true;
> >
> > //this.lblMessage.Text = "Insert couldn't be performed. Username may[/color][/color]
already[color=blue][color=green]
> > be taken.";
> >
> > this.lblMessage.Text = sql;
> >
> > }
> >
> > finally
> >
> > {
> >
> > con.Close();
> >
> > }
> >
> > if (doredirect)
> >
> > Response.Redirect("Login.aspx");
> >
> > }
> >
> >
> >
> > else
> >
> > lblMessage.Text = "Fix the following errors and retry:";
> >
> > }[/color][/color]
Miha Markic
Guest
  
Posts: n/a
#7: Nov 18 '05

re: Inserting dates into SQL Server DB

Hi Andrew,

If I remember the format 'yyyymmdd' is the most universal for this (it isn't
regional settings dependent)

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"Andrew Banks" <banksy@nospamblueyonder.co.uk> wrote in message
news:KDuKb.7981$YY1.79762593@news-text.cableinet.net...[color=blue]
> Joey, dose this statement further down the code cover that?
>
> if (txtBirth.Text != string.Empty)
> values.Add("'" + txtBirth.Text + "'");
> else
> values.Add("Null");
>
> "Joey Powell" <joey.powell@goldcoinc.com> wrote in message
> news:bdaf8387.0401052114.7b75b7d6@posting.google.c om...[color=green]
> > Andrew, dates must be enclosed within single quotes in all SQL
> > statements. Parameter {10} below is for DateofBirth and is not
> > enclosed in single quotes. You should wrap it in single quotes like
> > you did for the first few parameters (i.e. '{10}'). HTH JP
> >
> > "Andrew Banks" <banksy@nojunkblueyonder.co.uk> wrote in message[/color]
> news:<HglKb.7672$hd5.76609805@news-text.cableinet.net>...[color=green][color=darkred]
> > > I'm running the following code in a C#.NET page and it doesn't enter[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > values into the DB. I'm certain the problem is to do with the txtBirth
> > > field. It allows users to enter a DOB as dd/mm/yyyy and I think it's[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > slashes(/) that are causing the problem. If I don't enter a DOB in[/color][/color][/color]
this[color=blue][color=green][color=darkred]
> > > field then all the data enters into the database without a problem.
> > >
> > > Any ideas?
> > >
> > > SQL Server 2000, VS.NET, C#
> > >
> > > if (Page.IsValid)
> > >
> > > {
> > >
> > > // Save the new user to the database
> > >
> > > SqlConnection con;
> > >
> > > string sql;
> > >
> > > SqlCommand cmd;
> > >
> > > StringBuilder sb = new StringBuilder();
> > >
> > > ArrayList values = new ArrayList();
> > >
> > > sb.Append("INSERT INTO [User] ");
> > >
> > > sb.Append("(UserID, Login, Password, FirstName, LastName, ");
> > >
> > > sb.Append("PhoneNumber, Email, IsAdministrator, Address, ");
> > >
> > > sb.Append("CellNumber, DateOfBirth) ");
> > >
> > > sb.Append("VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}',[/color][/color]
> '{7}',[color=green][color=darkred]
> > > ");
> > >
> > > // Optional values without quotes as they can be null
> > >
> > > sb.Append("{8}, {9}, {10})");
> > >
> > > // Add required values to replace
> > >
> > > values.Add(Guid.NewGuid().ToString());
> > >
> > > values.Add(txtLogin.Text);
> > >
> > > values.Add(txtPwd.Text);
> > >
> > > values.Add(txtFName.Text);
> > >
> > > values.Add(txtLName.Text);
> > >
> > > values.Add(txtPhone.Text);
> > >
> > > values.Add(txtEmail.Text);
> > >
> > > values.Add(0);
> > >
> > > // Add the optional values or Null
> > >
> > > if (txtAddress.Text != string.Empty)
> > >
> > > values.Add("'" + txtAddress.Text + "'");
> > >
> > > else
> > >
> > > values.Add("Null");
> > >
> > > if (txtMobile.Text != string.Empty)
> > >
> > > values.Add("'" + txtMobile.Text + "'");
> > >
> > > else
> > >
> > > values.Add("Null");
> > >
> > > if (txtBirth.Text != string.Empty)
> > >
> > > values.Add("'" + txtBirth.Text + "'");
> > >
> > > else
> > >
> > > values.Add("Null");
> > >
> > > // Format the string with the array of values
> > >
> > > sql = String.Format(sb.ToString(), values.ToArray());
> > >
> > > // Connect and execute the SQL
> > >
> > > con = new SqlConnection("data source=127.0.0.1;initial[/color][/color][/color]
catalog=Friends;[color=blue]
> user[color=green][color=darkred]
> > > id=sa;");
> > >
> > > cmd = new SqlCommand(sql, con);
> > >
> > > con.Open();
> > >
> > > bool doredirect=true;
> > >
> > > try
> > >
> > > {
> > >
> > > cmd.ExecuteNonQuery();
> > >
> > > }
> > >
> > > catch
> > >
> > > {
> > >
> > > doredirect = false;
> > >
> > > this.lblMessage.Visible = true;
> > >
> > > //this.lblMessage.Text = "Insert couldn't be performed. Username may[/color][/color]
> already[color=green][color=darkred]
> > > be taken.";
> > >
> > > this.lblMessage.Text = sql;
> > >
> > > }
> > >
> > > finally
> > >
> > > {
> > >
> > > con.Close();
> > >
> > > }
> > >
> > > if (doredirect)
> > >
> > > Response.Redirect("Login.aspx");
> > >
> > > }
> > >
> > >
> > >
> > > else
> > >
> > > lblMessage.Text = "Fix the following errors and retry:";
> > >
> > > }[/color][/color]
>
>[/color]
Andrew Banks
Guest
  
Posts: n/a
#8: Nov 18 '05

re: Inserting dates into SQL Server DB

Thanks Miha.

Will a Date/Time field in a SQL Server 2000 database accept this format?


"Miha Markic" <miha at rthand com> wrote in message
news:u$%23o2QD1DHA.556@TK2MSFTNGP11.phx.gbl...[color=blue]
> Hi Andrew,
>
> If I remember the format 'yyyymmdd' is the most universal for this (it[/color]
isn't[color=blue]
> regional settings dependent)
>
> --
> Miha Markic - RightHand .NET consulting & software development
> miha at rthand com
> www.rthand.com
>
> "Andrew Banks" <banksy@nospamblueyonder.co.uk> wrote in message
> news:KDuKb.7981$YY1.79762593@news-text.cableinet.net...[color=green]
> > Joey, dose this statement further down the code cover that?
> >
> > if (txtBirth.Text != string.Empty)
> > values.Add("'" + txtBirth.Text + "'");
> > else
> > values.Add("Null");
> >
> > "Joey Powell" <joey.powell@goldcoinc.com> wrote in message
> > news:bdaf8387.0401052114.7b75b7d6@posting.google.c om...[color=darkred]
> > > Andrew, dates must be enclosed within single quotes in all SQL
> > > statements. Parameter {10} below is for DateofBirth and is not
> > > enclosed in single quotes. You should wrap it in single quotes like
> > > you did for the first few parameters (i.e. '{10}'). HTH JP
> > >
> > > "Andrew Banks" <banksy@nojunkblueyonder.co.uk> wrote in message[/color]
> > news:<HglKb.7672$hd5.76609805@news-text.cableinet.net>...[color=darkred]
> > > > I'm running the following code in a C#.NET page and it doesn't enter[/color][/color]
> the[color=green][color=darkred]
> > > > values into the DB. I'm certain the problem is to do with the[/color][/color][/color]
txtBirth[color=blue][color=green][color=darkred]
> > > > field. It allows users to enter a DOB as dd/mm/yyyy and I think it's[/color][/color]
> the[color=green][color=darkred]
> > > > slashes(/) that are causing the problem. If I don't enter a DOB in[/color][/color]
> this[color=green][color=darkred]
> > > > field then all the data enters into the database without a problem.
> > > >
> > > > Any ideas?
> > > >
> > > > SQL Server 2000, VS.NET, C#
> > > >
> > > > if (Page.IsValid)
> > > >
> > > > {
> > > >
> > > > // Save the new user to the database
> > > >
> > > > SqlConnection con;
> > > >
> > > > string sql;
> > > >
> > > > SqlCommand cmd;
> > > >
> > > > StringBuilder sb = new StringBuilder();
> > > >
> > > > ArrayList values = new ArrayList();
> > > >
> > > > sb.Append("INSERT INTO [User] ");
> > > >
> > > > sb.Append("(UserID, Login, Password, FirstName, LastName, ");
> > > >
> > > > sb.Append("PhoneNumber, Email, IsAdministrator, Address, ");
> > > >
> > > > sb.Append("CellNumber, DateOfBirth) ");
> > > >
> > > > sb.Append("VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}',[/color]
> > '{7}',[color=darkred]
> > > > ");
> > > >
> > > > // Optional values without quotes as they can be null
> > > >
> > > > sb.Append("{8}, {9}, {10})");
> > > >
> > > > // Add required values to replace
> > > >
> > > > values.Add(Guid.NewGuid().ToString());
> > > >
> > > > values.Add(txtLogin.Text);
> > > >
> > > > values.Add(txtPwd.Text);
> > > >
> > > > values.Add(txtFName.Text);
> > > >
> > > > values.Add(txtLName.Text);
> > > >
> > > > values.Add(txtPhone.Text);
> > > >
> > > > values.Add(txtEmail.Text);
> > > >
> > > > values.Add(0);
> > > >
> > > > // Add the optional values or Null
> > > >
> > > > if (txtAddress.Text != string.Empty)
> > > >
> > > > values.Add("'" + txtAddress.Text + "'");
> > > >
> > > > else
> > > >
> > > > values.Add("Null");
> > > >
> > > > if (txtMobile.Text != string.Empty)
> > > >
> > > > values.Add("'" + txtMobile.Text + "'");
> > > >
> > > > else
> > > >
> > > > values.Add("Null");
> > > >
> > > > if (txtBirth.Text != string.Empty)
> > > >
> > > > values.Add("'" + txtBirth.Text + "'");
> > > >
> > > > else
> > > >
> > > > values.Add("Null");
> > > >
> > > > // Format the string with the array of values
> > > >
> > > > sql = String.Format(sb.ToString(), values.ToArray());
> > > >
> > > > // Connect and execute the SQL
> > > >
> > > > con = new SqlConnection("data source=127.0.0.1;initial[/color][/color]
> catalog=Friends;[color=green]
> > user[color=darkred]
> > > > id=sa;");
> > > >
> > > > cmd = new SqlCommand(sql, con);
> > > >
> > > > con.Open();
> > > >
> > > > bool doredirect=true;
> > > >
> > > > try
> > > >
> > > > {
> > > >
> > > > cmd.ExecuteNonQuery();
> > > >
> > > > }
> > > >
> > > > catch
> > > >
> > > > {
> > > >
> > > > doredirect = false;
> > > >
> > > > this.lblMessage.Visible = true;
> > > >
> > > > //this.lblMessage.Text = "Insert couldn't be performed. Username may[/color]
> > already[color=darkred]
> > > > be taken.";
> > > >
> > > > this.lblMessage.Text = sql;
> > > >
> > > > }
> > > >
> > > > finally
> > > >
> > > > {
> > > >
> > > > con.Close();
> > > >
> > > > }
> > > >
> > > > if (doredirect)
> > > >
> > > > Response.Redirect("Login.aspx");
> > > >
> > > > }
> > > >
> > > >
> > > >
> > > > else
> > > >
> > > > lblMessage.Text = "Fix the following errors and retry:";
> > > >
> > > > }[/color]
> >
> >[/color]
>
>[/color]
Miha Markic
Guest
  
Posts: n/a
#9: Nov 18 '05

re: Inserting dates into SQL Server DB

Hi Andrew,

Yes, it should.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"Andrew Banks" <banksy@nospamblueyonder.co.uk> wrote in message
news:ztvKb.7998$Nl2.80053398@news-text.cableinet.net...[color=blue]
> Thanks Miha.
>
> Will a Date/Time field in a SQL Server 2000 database accept this format?
>
>
> "Miha Markic" <miha at rthand com> wrote in message
> news:u$%23o2QD1DHA.556@TK2MSFTNGP11.phx.gbl...[color=green]
> > Hi Andrew,
> >
> > If I remember the format 'yyyymmdd' is the most universal for this (it[/color]
> isn't[color=green]
> > regional settings dependent)
> >
> > --
> > Miha Markic - RightHand .NET consulting & software development
> > miha at rthand com
> > www.rthand.com
> >
> > "Andrew Banks" <banksy@nospamblueyonder.co.uk> wrote in message
> > news:KDuKb.7981$YY1.79762593@news-text.cableinet.net...[color=darkred]
> > > Joey, dose this statement further down the code cover that?
> > >
> > > if (txtBirth.Text != string.Empty)
> > > values.Add("'" + txtBirth.Text + "'");
> > > else
> > > values.Add("Null");
> > >
> > > "Joey Powell" <joey.powell@goldcoinc.com> wrote in message
> > > news:bdaf8387.0401052114.7b75b7d6@posting.google.c om...
> > > > Andrew, dates must be enclosed within single quotes in all SQL
> > > > statements. Parameter {10} below is for DateofBirth and is not
> > > > enclosed in single quotes. You should wrap it in single quotes like
> > > > you did for the first few parameters (i.e. '{10}'). HTH JP
> > > >
> > > > "Andrew Banks" <banksy@nojunkblueyonder.co.uk> wrote in message
> > > news:<HglKb.7672$hd5.76609805@news-text.cableinet.net>...
> > > > > I'm running the following code in a C#.NET page and it doesn't[/color][/color][/color]
enter[color=blue][color=green]
> > the[color=darkred]
> > > > > values into the DB. I'm certain the problem is to do with the[/color][/color]
> txtBirth[color=green][color=darkred]
> > > > > field. It allows users to enter a DOB as dd/mm/yyyy and I think[/color][/color][/color]
it's[color=blue][color=green]
> > the[color=darkred]
> > > > > slashes(/) that are causing the problem. If I don't enter a DOB in[/color]
> > this[color=darkred]
> > > > > field then all the data enters into the database without a[/color][/color][/color]
problem.[color=blue][color=green][color=darkred]
> > > > >
> > > > > Any ideas?
> > > > >
> > > > > SQL Server 2000, VS.NET, C#
> > > > >
> > > > > if (Page.IsValid)
> > > > >
> > > > > {
> > > > >
> > > > > // Save the new user to the database
> > > > >
> > > > > SqlConnection con;
> > > > >
> > > > > string sql;
> > > > >
> > > > > SqlCommand cmd;
> > > > >
> > > > > StringBuilder sb = new StringBuilder();
> > > > >
> > > > > ArrayList values = new ArrayList();
> > > > >
> > > > > sb.Append("INSERT INTO [User] ");
> > > > >
> > > > > sb.Append("(UserID, Login, Password, FirstName, LastName, ");
> > > > >
> > > > > sb.Append("PhoneNumber, Email, IsAdministrator, Address, ");
> > > > >
> > > > > sb.Append("CellNumber, DateOfBirth) ");
> > > > >
> > > > > sb.Append("VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}',[/color][/color][/color]
'{6}',[color=blue][color=green][color=darkred]
> > > '{7}',
> > > > > ");
> > > > >
> > > > > // Optional values without quotes as they can be null
> > > > >
> > > > > sb.Append("{8}, {9}, {10})");
> > > > >
> > > > > // Add required values to replace
> > > > >
> > > > > values.Add(Guid.NewGuid().ToString());
> > > > >
> > > > > values.Add(txtLogin.Text);
> > > > >
> > > > > values.Add(txtPwd.Text);
> > > > >
> > > > > values.Add(txtFName.Text);
> > > > >
> > > > > values.Add(txtLName.Text);
> > > > >
> > > > > values.Add(txtPhone.Text);
> > > > >
> > > > > values.Add(txtEmail.Text);
> > > > >
> > > > > values.Add(0);
> > > > >
> > > > > // Add the optional values or Null
> > > > >
> > > > > if (txtAddress.Text != string.Empty)
> > > > >
> > > > > values.Add("'" + txtAddress.Text + "'");
> > > > >
> > > > > else
> > > > >
> > > > > values.Add("Null");
> > > > >
> > > > > if (txtMobile.Text != string.Empty)
> > > > >
> > > > > values.Add("'" + txtMobile.Text + "'");
> > > > >
> > > > > else
> > > > >
> > > > > values.Add("Null");
> > > > >
> > > > > if (txtBirth.Text != string.Empty)
> > > > >
> > > > > values.Add("'" + txtBirth.Text + "'");
> > > > >
> > > > > else
> > > > >
> > > > > values.Add("Null");
> > > > >
> > > > > // Format the string with the array of values
> > > > >
> > > > > sql = String.Format(sb.ToString(), values.ToArray());
> > > > >
> > > > > // Connect and execute the SQL
> > > > >
> > > > > con = new SqlConnection("data source=127.0.0.1;initial[/color]
> > catalog=Friends;[color=darkred]
> > > user
> > > > > id=sa;");
> > > > >
> > > > > cmd = new SqlCommand(sql, con);
> > > > >
> > > > > con.Open();
> > > > >
> > > > > bool doredirect=true;
> > > > >
> > > > > try
> > > > >
> > > > > {
> > > > >
> > > > > cmd.ExecuteNonQuery();
> > > > >
> > > > > }
> > > > >
> > > > > catch
> > > > >
> > > > > {
> > > > >
> > > > > doredirect = false;
> > > > >
> > > > > this.lblMessage.Visible = true;
> > > > >
> > > > > //this.lblMessage.Text = "Insert couldn't be performed. Username[/color][/color][/color]
may[color=blue][color=green][color=darkred]
> > > already
> > > > > be taken.";
> > > > >
> > > > > this.lblMessage.Text = sql;
> > > > >
> > > > > }
> > > > >
> > > > > finally
> > > > >
> > > > > {
> > > > >
> > > > > con.Close();
> > > > >
> > > > > }
> > > > >
> > > > > if (doredirect)
> > > > >
> > > > > Response.Redirect("Login.aspx");
> > > > >
> > > > > }
> > > > >
> > > > >
> > > > >
> > > > > else
> > > > >
> > > > > lblMessage.Text = "Fix the following errors and retry:";
> > > > >
> > > > > }
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]
Andrew Banks
Guest
  
Posts: n/a
#10: Nov 18 '05

re: Inserting dates into SQL Server DB

Thanks Miha,

I'm at work at the moment snowed under with PHP so will give this a try when
I get home tonight.

"Miha Markic" <miha at rthand com> wrote in message
news:eCENUuD1DHA.2872@TK2MSFTNGP09.phx.gbl...[color=blue]
> Hi Andrew,
>
> Yes, it should.
>
> --
> Miha Markic - RightHand .NET consulting & software development
> miha at rthand com
> www.rthand.com
>
> "Andrew Banks" <banksy@nospamblueyonder.co.uk> wrote in message
> news:ztvKb.7998$Nl2.80053398@news-text.cableinet.net...[color=green]
> > Thanks Miha.
> >
> > Will a Date/Time field in a SQL Server 2000 database accept this format?
> >
> >
> > "Miha Markic" <miha at rthand com> wrote in message
> > news:u$%23o2QD1DHA.556@TK2MSFTNGP11.phx.gbl...[color=darkred]
> > > Hi Andrew,
> > >
> > > If I remember the format 'yyyymmdd' is the most universal for this (it[/color]
> > isn't[color=darkred]
> > > regional settings dependent)
> > >
> > > --
> > > Miha Markic - RightHand .NET consulting & software development
> > > miha at rthand com
> > > www.rthand.com
> > >
> > > "Andrew Banks" <banksy@nospamblueyonder.co.uk> wrote in message
> > > news:KDuKb.7981$YY1.79762593@news-text.cableinet.net...
> > > > Joey, dose this statement further down the code cover that?
> > > >
> > > > if (txtBirth.Text != string.Empty)
> > > > values.Add("'" + txtBirth.Text + "'");
> > > > else
> > > > values.Add("Null");
> > > >
> > > > "Joey Powell" <joey.powell@goldcoinc.com> wrote in message
> > > > news:bdaf8387.0401052114.7b75b7d6@posting.google.c om...
> > > > > Andrew, dates must be enclosed within single quotes in all SQL
> > > > > statements. Parameter {10} below is for DateofBirth and is not
> > > > > enclosed in single quotes. You should wrap it in single quotes[/color][/color][/color]
like[color=blue][color=green][color=darkred]
> > > > > you did for the first few parameters (i.e. '{10}'). HTH JP
> > > > >
> > > > > "Andrew Banks" <banksy@nojunkblueyonder.co.uk> wrote in message
> > > > news:<HglKb.7672$hd5.76609805@news-text.cableinet.net>...
> > > > > > I'm running the following code in a C#.NET page and it doesn't[/color][/color]
> enter[color=green][color=darkred]
> > > the
> > > > > > values into the DB. I'm certain the problem is to do with the[/color]
> > txtBirth[color=darkred]
> > > > > > field. It allows users to enter a DOB as dd/mm/yyyy and I think[/color][/color]
> it's[color=green][color=darkred]
> > > the
> > > > > > slashes(/) that are causing the problem. If I don't enter a DOB[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> > > this
> > > > > > field then all the data enters into the database without a[/color][/color]
> problem.[color=green][color=darkred]
> > > > > >
> > > > > > Any ideas?
> > > > > >
> > > > > > SQL Server 2000, VS.NET, C#
> > > > > >
> > > > > > if (Page.IsValid)
> > > > > >
> > > > > > {
> > > > > >
> > > > > > // Save the new user to the database
> > > > > >
> > > > > > SqlConnection con;
> > > > > >
> > > > > > string sql;
> > > > > >
> > > > > > SqlCommand cmd;
> > > > > >
> > > > > > StringBuilder sb = new StringBuilder();
> > > > > >
> > > > > > ArrayList values = new ArrayList();
> > > > > >
> > > > > > sb.Append("INSERT INTO [User] ");
> > > > > >
> > > > > > sb.Append("(UserID, Login, Password, FirstName, LastName, ");
> > > > > >
> > > > > > sb.Append("PhoneNumber, Email, IsAdministrator, Address, ");
> > > > > >
> > > > > > sb.Append("CellNumber, DateOfBirth) ");
> > > > > >
> > > > > > sb.Append("VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}',[/color][/color]
> '{6}',[color=green][color=darkred]
> > > > '{7}',
> > > > > > ");
> > > > > >
> > > > > > // Optional values without quotes as they can be null
> > > > > >
> > > > > > sb.Append("{8}, {9}, {10})");
> > > > > >
> > > > > > // Add required values to replace
> > > > > >
> > > > > > values.Add(Guid.NewGuid().ToString());
> > > > > >
> > > > > > values.Add(txtLogin.Text);
> > > > > >
> > > > > > values.Add(txtPwd.Text);
> > > > > >
> > > > > > values.Add(txtFName.Text);
> > > > > >
> > > > > > values.Add(txtLName.Text);
> > > > > >
> > > > > > values.Add(txtPhone.Text);
> > > > > >
> > > > > > values.Add(txtEmail.Text);
> > > > > >
> > > > > > values.Add(0);
> > > > > >
> > > > > > // Add the optional values or Null
> > > > > >
> > > > > > if (txtAddress.Text != string.Empty)
> > > > > >
> > > > > > values.Add("'" + txtAddress.Text + "'");
> > > > > >
> > > > > > else
> > > > > >
> > > > > > values.Add("Null");
> > > > > >
> > > > > > if (txtMobile.Text != string.Empty)
> > > > > >
> > > > > > values.Add("'" + txtMobile.Text + "'");
> > > > > >
> > > > > > else
> > > > > >
> > > > > > values.Add("Null");
> > > > > >
> > > > > > if (txtBirth.Text != string.Empty)
> > > > > >
> > > > > > values.Add("'" + txtBirth.Text + "'");
> > > > > >
> > > > > > else
> > > > > >
> > > > > > values.Add("Null");
> > > > > >
> > > > > > // Format the string with the array of values
> > > > > >
> > > > > > sql = String.Format(sb.ToString(), values.ToArray());
> > > > > >
> > > > > > // Connect and execute the SQL
> > > > > >
> > > > > > con = new SqlConnection("data source=127.0.0.1;initial
> > > catalog=Friends;
> > > > user
> > > > > > id=sa;");
> > > > > >
> > > > > > cmd = new SqlCommand(sql, con);
> > > > > >
> > > > > > con.Open();
> > > > > >
> > > > > > bool doredirect=true;
> > > > > >
> > > > > > try
> > > > > >
> > > > > > {
> > > > > >
> > > > > > cmd.ExecuteNonQuery();
> > > > > >
> > > > > > }
> > > > > >
> > > > > > catch
> > > > > >
> > > > > > {
> > > > > >
> > > > > > doredirect = false;
> > > > > >
> > > > > > this.lblMessage.Visible = true;
> > > > > >
> > > > > > //this.lblMessage.Text = "Insert couldn't be performed. Username[/color][/color]
> may[color=green][color=darkred]
> > > > already
> > > > > > be taken.";
> > > > > >
> > > > > > this.lblMessage.Text = sql;
> > > > > >
> > > > > > }
> > > > > >
> > > > > > finally
> > > > > >
> > > > > > {
> > > > > >
> > > > > > con.Close();
> > > > > >
> > > > > > }
> > > > > >
> > > > > > if (doredirect)
> > > > > >
> > > > > > Response.Redirect("Login.aspx");
> > > > > >
> > > > > > }
> > > > > >
> > > > > >
> > > > > >
> > > > > > else
> > > > > >
> > > > > > lblMessage.Text = "Fix the following errors and retry:";
> > > > > >
> > > > > > }
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]
Closed Thread

« previous question | next question »

Similar ASP.NET bytes

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,295 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved