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:";
} 9 1514
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" <ba****@nojunkblueyonder.co.uk> wrote in message
news:Hg*********************@news-text.cableinet.net... 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:";
}
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" <ba****@nojunkblueyonder.co.uk> wrote in message
news:Hg*********************@news-text.cableinet.net... 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,
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:OE**************@tk2msftngp13.phx.gbl... 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" <ba****@nojunkblueyonder.co.uk> wrote in message news:Hg*********************@news-text.cableinet.net... 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:";
}
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" <ba****@nojunkblueyonder.co.uk> wrote in message news:<Hg*********************@news-text.cableinet.net>... 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:";
}
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" <jo*********@goldcoinc.com> wrote in message
news:bd**************************@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" <ba****@nojunkblueyonder.co.uk> wrote in message
news:<Hg*********************@news-text.cableinet.net>... 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:";
}
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" <ba****@nospamblueyonder.co.uk> wrote in message
news:KD*********************@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" <jo*********@goldcoinc.com> wrote in message news:bd**************************@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" <ba****@nojunkblueyonder.co.uk> wrote in message news:<Hg*********************@news-text.cableinet.net>... 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:";
}
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$***************@TK2MSFTNGP11.phx.gbl... 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" <ba****@nospamblueyonder.co.uk> wrote in message news:KD*********************@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" <jo*********@goldcoinc.com> wrote in message news:bd**************************@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" <ba****@nojunkblueyonder.co.uk> wrote in message news:<Hg*********************@news-text.cableinet.net>... > 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:"; > > }
Hi Andrew,
Yes, it should.
--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com www.rthand.com
"Andrew Banks" <ba****@nospamblueyonder.co.uk> wrote in message
news:zt*********************@news-text.cableinet.net... 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$***************@TK2MSFTNGP11.phx.gbl... 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" <ba****@nospamblueyonder.co.uk> wrote in message news:KD*********************@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" <jo*********@goldcoinc.com> wrote in message news:bd**************************@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" <ba****@nojunkblueyonder.co.uk> wrote in message news:<Hg*********************@news-text.cableinet.net>... > > 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:"; > > > > }
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:eC**************@TK2MSFTNGP09.phx.gbl... Hi Andrew,
Yes, it should.
-- Miha Markic - RightHand .NET consulting & software development miha at rthand com www.rthand.com
"Andrew Banks" <ba****@nospamblueyonder.co.uk> wrote in message news:zt*********************@news-text.cableinet.net... 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$***************@TK2MSFTNGP11.phx.gbl... 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" <ba****@nospamblueyonder.co.uk> wrote in message news:KD*********************@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" <jo*********@goldcoinc.com> wrote in message > news:bd**************************@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" <ba****@nojunkblueyonder.co.uk> wrote in message > news:<Hg*********************@news-text.cableinet.net>... > > > 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:"; > > > > > > } > >
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: perplexed |
last post by:
How do you convert a user inputted date to a unix timestamp before
insterting it into your database? I have a form, with a textfield for
a date that the user inputs in the format mm-dd-yyyy and...
|
by: Sparrow |
last post by:
I have created a table with the following columns...
Date(datetime),Actual (Int),Planned (Int)
I need to insert weekending dates starting from 23/04/04 looping
thru'for the next 52weeks...
|
by: Tim Davidge |
last post by:
Hi folks, been a while since I have posted a plea for help and I think I
have forgotten everything I learnt from the helpful contributors to this
newsgroup, that said however :
I'm trying to...
|
by: Khurram |
last post by:
I have a problem while inserting time value in the datetime Field.
I want to Insert only time value in this format (08:15:39) into the SQL
Date time Field.
I tried to many ways, I can extract...
|
by: suslikovich |
last post by:
Hi all,
I am getting this error when insert values from one table to another in
the first table the values are varchar (10). In the second they are
datetime. The format of the data is mm/dd/yyyy...
|
by: rugger81 |
last post by:
I am currently working in the sql server 2000 environment and I want to
write a function to pull all dates within a given date range. I have
created several diferent ways to do this but I am...
|
by: AAJ |
last post by:
Hi
Does anyone know of any good publically available set of standards for
managing dates when dealing with a database server (in my case SQL Server
2000 and c# VS2005).
At the moment, if I...
|
by: Paul |
last post by:
I'm sure this isn't a difficult question, but I've been struggling trying to
insert the current date into a sql server field. I've tried numerous
suggestions that I've seen in newsgroups and...
|
by: RP |
last post by:
I have a DateTimePicker with format dd-MM-yyyy. While attempting to
insert this date in SQL Server Date column, following exception is
thrown:
The conversion of a char data type to a datetime...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |