Can't save date from a textbox to a database.

Question

_{Wednesday, May 11, 2011 1:07 PM}

Hi, I have a textbox where I input data using popup calendar. Date is in format dd/mm/yyyy and I need to save days and months to different columns in the database. Iv'e got thiscode bt it's not working (alhough It doesn't show me any errors):

protected void AppointmentDatetxt_TextChanged(object sender, EventArgs e)
    {
        string AppointmentDate = AppointmentDatetxt.Text;
        string day = AppointmentDate.Substring(AppointmentDate.Length - 10, 2);
        string month = AppointmentDate.Substring(4, 2);

        string ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\databaseDB.mdf;Integrated Security=True;User Instance=True";
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            SqlCommand command = new SqlCommand("INSERT INTO Bookings (Month) VALUES (@month)", connection);
            command.Parameters.Add("@month", System.Data.SqlDbType.VarChar, 50).Value = AppointmentDatetxt.Text;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
    }

At the moment I have query only for month. What's wrong with this and how to add a second query?

All replies (20)

_{Thursday, May 12, 2011 10:12 PM ✅Answered}

Hi:)
My addition, you can have a try like this:

using (SqlConnection connection = new SqlConnection(ConnectionString)) {
SqlCommand command = new SqlCommand("INSERT INTO Bookings (Month, Day) VALUES (@month, @day)", connection);

                DateTime d = DateTime.Parse(TextBox1.Text);
**                command.Parameters.Add("@month", SqlDbType.VarChar, 50).Value = d.Month.ToString();
                command.Parameters.Add("@day", SqlDbType.VarChar, 50).Value = d.Day.ToString();**
                connection.Open();
                command.ExecuteNonQuery();
        }

PS：You'd better use type of numeric for the two columns because they belong to numeric type for futher usage easily...

_{Wednesday, May 11, 2011 2:06 PM}

Hi Luke,

I have easy solution if you want to save day and month of appointmentDate.

Use computed column as in below example, so you do not need to insert it from c# code.

CREATE TABLE TEST_1
(
    ID INT IDENTITY(1,1),
    AppointmentDate DATETIME,
    [Day] as day(AppointmentDate),
    [month] as month(AppointmentDate)
)

insert into TEST_1(AppointmentDate) values(GETDATE()-2)

select * from TEST_1

[Day] as day(AppointmentDate),
[month] as month(AppointmentDate) are computed column.

for more information of computed column read series of blog from pinal dave on sql authority http://blog.sqlauthority.com/2010/08/22/sql-server-computed-columns-index-and-performance/

_{Wednesday, May 11, 2011 2:09 PM}

Thanks for your answer but I'm doing this for my assignment and I think taht substring will be better to do that. The only thing I need is to get it work.

_{Wednesday, May 11, 2011 3:13 PM}

First off, you need to but a button on the page that causes a postback for the value of the textbox to be checked so the system can determine if it should fire the TextChanged event at all. So add a button to your page and this code in the code behind:

protected void Button1_Click(object sender, System.EventArgs e)
{
    Array AppointmentDate = Strings.Split(AppointmentDatetxt.Text, "/");
    string day = AppointmentDate(0);
    string month = AppointmentDate(1);

    string ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\databaseDB.mdf;Integrated Security=True;User Instance=True";
    using (SqlConnection connection = new SqlConnection(ConnectionString)) {
        SqlCommand command = new SqlCommand("INSERT INTO Bookings (Month, Day) VALUES (@month, @day)", connection);
        command.Parameters.Add("@month", SqlDbType.VarChar, 50).Value = month;
        command.Parameters.Add("@day", SqlDbType.VarChar, 50).Value = day;
        connection.Open();
        command.ExecuteNonQuery();
    }
}
Also, you do not need to call connection.close().  Enclosing the code in a Using block does this for you.

_{Thursday, May 12, 2011 1:54 AM}

Now I'm getting errors in this three lines:

Array AppointmentDate = Strings.Split(AppointmentDatetxt.Text, "/"); /*The name 'Strings does not exist in the current content*/
        string day = AppointmentDate(0); /*'AppointmentDate' is a 'variable' but is used like a method*/
        string month = AppointmentDate(1); /*'AppointmentDate' is a 'variable' but is used like a method*/

and this: 'System.Linq.Strings' is inaccessible due to its protection level

_{Thursday, May 12, 2011 2:06 AM}

hi try this one,

protected void AppointmentDatetxt_TextChanged(object sender, EventArgs e)
    {
       string AppointmentDate = AppointmentDatetxt.Text;
       string day = AppointmentDate.Split('/')[0];
       string month = AppointmentDateSplit('/')[1];

        string ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\databaseDB.mdf;Integrated Security=True;User Instance=True";
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            SqlCommand command = new SqlCommand("INSERT INTO Bookings (Month) VALUES (@month)", connection);
            command.Parameters.Add("@month", System.Data.SqlDbType.VarChar, 50).Value = AppointmentDatetxt.Text;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
    }

_{Thursday, May 12, 2011 2:18 AM}

viralpatel it doesn't work. the solution posted by ryanbesko seems to be ok but I don't know why I'm getting this errors. I work in Visual Studio 2008 and c#.

_{Thursday, May 12, 2011 3:13 AM}

in addition to ryanbesko,do this

string strDate = "22/05/2011";
string strDay = strDate.Substring(0, 2);
string strMonth = strDate.Substring(3, 2);

_{Thursday, May 12, 2011 4:30 AM}

Your sending in the AppointmentDatetxt.Text instead of your created substring month.

command.Parameters.Add("@month", System.Data.SqlDbType.VarChar, 50).Value = AppointmentDatetxt.Text;

_{Thursday, May 12, 2011 11:21 AM}

viralpatel it doesn't work. the solution posted by ryanbesko seems to be ok but I don't know why I'm getting this errors. I work in Visual Studio 2008 and c#.

Those errors are most likely because I'm a VB programmer. I used a code coverter to get it to C# for this post. Look into how C# requires Arrays to be used and that should help.

_{Thursday, May 12, 2011 2:24 PM}

I've changed it to this but again it doesn't show any errors and doesn't save to the database:

protected void Button1_Click(object sender, EventArgs e)
    {
        string AppointmentDate = "22/05/2011";
        string day = AppointmentDate.Substring(0, 2);
        string month = AppointmentDate.Substring(3, 2);

        string ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\HealthCentreDB.mdf;Integrated Security=True;User Instance=True";
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            SqlCommand command = new SqlCommand("INSERT INTO Bookings (BookingNum, Month, Day) VALUES (@day, @month, @day)", connection);
            command.Parameters.Add("@day", SqlDbType.VarChar, 50).Value = day;
            command.Parameters.Add("@month", SqlDbType.VarChar, 50).Value = month;
            command.Parameters.Add("@day", SqlDbType.VarChar, 50).Value = day;
            connection.Open();
        }
    }

_{Thursday, May 12, 2011 3:11 PM}

Did you put a button called Button1 on your aspx page?

<asp:Button ID="Button1" runat="server" Text="Button" />

_{Thursday, May 12, 2011 3:45 PM}

Ofcourse I did put the button. It postbacks coz the page refreshes but the date doesn't appear in the database :(

_{Thursday, May 12, 2011 3:54 PM}

I see that your SqlCommand has BookingNum in the field list. The values list contains the @day parameter twice, and you assign a value to it twice. Is this intended?

_{Thursday, May 12, 2011 3:56 PM}

You are also missing Command.ExecuteNonQuery() after Connection.Open().

_{Thursday, May 12, 2011 4:49 PM}

Yes, it was intendent to use @day twice coz i must fill in bookingnum column so i thouht that I will use existing @day. Its just for testing purposes. I'll change it when everything work. I'll add Command.ExecuteNonQuery() first thing in the morning and let you know if anything changed.

_{Friday, May 13, 2011 2:04 AM}

Decker Dong I used your code and it works .I just needed to add "using System.Timers;" to the top of the page. Thanks everyone for help with this.

_{Friday, May 13, 2011 4:49 AM}

Have you written anthing in page load regarding this.
write between
If(!Ispostback) { }

_{Friday, May 13, 2011 5:05 AM}

No, it didn't require to modify page load.

_{Friday, May 13, 2011 1:44 PM}

Hi again,

It looks like I still have a problem with this. I'm using an AJAX extender to popup a calendar from a textbox. When the data is chosen it apperas in the textbox and then I confirm it with button. It would be ok but there are a problem:

When the textbox readonly is set to true and when I submit the date I'm getting error:

**Exception Details: **System.FormatException: String was not recognized as a valid DateTime.

Line 47:             DateTime d = DateTime.Parse(AppointmentDatetxt.Text);

which is strange coz the date is in format dd/mm/yyyy. If I change readonly to false everything is ok.

Last updated on 2011-05-11

Share via

Can't save date from a textbox to a database.

Question

All replies (20)

Additional resources