Saving datetime data in a stored procedure

Hello everyone

I have a stored procedure that I am trying to save datetime data to at the moment I can create a string that will output
25/05/2007 18:30
Does anyone know the best way to get this into the correct format for my SP parameter

cmdPublish.Parameters.Add(newSqlParameter("@.datPublishDate", System.Data.SqlDbType.DateTime));

cmdPublish.Parameters["@.datPublishDate"].Value = ??

Thanks

Use the DateTime.ParseExact( ) method and pass your date value as an argument

ParseExact(Value,Format,FormatProvide) - Format provide can be passed as null and provide the Format value as string of whatever format you want it will return DateTime object which can be assigned to your Parameter

http://msdn2.microsoft.com/en-us/library/w2sa9yss.aspx

|||

If you are doing this from C# there is no "format" you need - you need to use a DateTime variable:

cmdPublish.Parameters.Add("@.datPublishDate", SqlDbType.DateTime ).Value = DateTime.Now;

If you are trying to get some string value from something like a webform, then I would use something like this:

DateTime dt;cmdPublish.Parameters.Add("@.datPublishDate", SqlDbType.DateTime ).Value = ( DateTime.TryParse( yourStringValue,null, System.Globalization.DateTimeStyles.None,out dt ) ) ? (object) dt : (object) DBNull.Value;

You can also use TryParseExact:

cmdPublish.Parameters.Add("@.datPublishDate", SqlDbType.DateTime ).Value = ( DateTime.TryParseExact( yourStringValue,"dd/MM/yyyy hh:mm",null, System.Globalization.DateTimeStyles.None,out dt ) ) ? (object) dt : (object) DBNull.Value;

|||

Thanks for the help - in the end I didn't need to do any of that.
I used
Convert.ToDateTime(str)

and it worked. turned out that there was something ELSE wrong with the SP.

How embarrassing.

Thanks

Saving datetime data in a stored procedure

Hello everyone

I have a stored procedure that I am trying to save datetime data to at the moment I can create a string that will output
25/05/2007 18:30
Does anyone know the best way to get this into the correct format for my SP parameter

cmdPublish.Parameters.Add(newSqlParameter("@.datPublishDate", System.Data.SqlDbType.DateTime));

cmdPublish.Parameters["@.datPublishDate"].Value = ??

Thanks

Use the DateTime.ParseExact( ) method and pass your date value as an argument

ParseExact(Value,Format,FormatProvide) - Format provide can be passed as null and provide the Format value as string of whatever format you want it will return DateTime object which can be assigned to your Parameter

http://msdn2.microsoft.com/en-us/library/w2sa9yss.aspx

|||

If you are doing this from C# there is no "format" you need - you need to use a DateTime variable:

cmdPublish.Parameters.Add("@.datPublishDate", SqlDbType.DateTime ).Value = DateTime.Now;

If you are trying to get some string value from something like a webform, then I would use something like this:

DateTime dt;cmdPublish.Parameters.Add("@.datPublishDate", SqlDbType.DateTime ).Value = ( DateTime.TryParse( yourStringValue,null, System.Globalization.DateTimeStyles.None,out dt ) ) ? (object) dt : (object) DBNull.Value;

You can also use TryParseExact:

cmdPublish.Parameters.Add("@.datPublishDate", SqlDbType.DateTime ).Value = ( DateTime.TryParseExact( yourStringValue,"dd/MM/yyyy hh:mm",null, System.Globalization.DateTimeStyles.None,out dt ) ) ? (object) dt : (object) DBNull.Value;

|||

Thanks for the help - in the end I didn't need to do any of that.
I used
Convert.ToDateTime(str)

and it worked. turned out that there was something ELSE wrong with the SP.

How embarrassing.

Thanks

Saving date problem

I get the following error when I try to save a date:

When converting a string to DateTime, parse the string to take the date before putting each variable in the DateTime object.
I was using a calendar control thinking that might have been the problem so I am using just a text box for entry. In the ASP code for the Insert statement, I have this:

<asp:controlParameterName="InDate"Type="DateTime"ControlID="txtInDate"/>

I have double checked the SQL table to make sure that the field is DateTime and it is. Also, if I have not selected a date or the textbox contains no information then it does not error and just saves the field blank.

What do I need to do to store this date?
Thanks for the information.

It's missing the attribute PropertyName="Text" I believe.|||Nope, that wasn't it. And the calendar control does not have a "text" property so I changed it to PropertyName="SelectedDate" and then I still get the same error.

Saving Arraylist to a Database

I have 3 strings delimitted by a * character that I would like to save to a database. Each string will always have the same number of elements.

strUserID = "1*2*3"

strCompanyID="12931*12937*12945"

strCompanyName="International Business Machines, Inc*Ford Motor Company*Delta Airlines"

What I need to do is to save each record like this

INSERT INTO tableA (UserID, CompanyID, CompanyName) VALUES (1, 12931, 'International Business Machines, Inc')

INSERT INTO tableA (UserID, CompanyID, CompanyName) VALUES (2, 12937, 'Ford Motor Company')

INSERT INTO tableA (UserID, CompanyID, CompanyName) VALUES (3, 12945, 'Delta Airlines')

I've done something like this before with a single string, but don't know how to handle 3 of them.

Dim strAlerts As String() = Nothing
strAlerts = values.Split(",")
Dim s As String
For Each s In strAlerts
SqlText = "INSERT INTO tableA (UserID, CompanyID, CompanyName) VALUES (" & lblUserID.Text & ", '" & lblCompanyID.Text & "','" & s & "')"
cmd = New SqlCommand(SqlText, strSQLConn)
cmd.ExecuteNonQuery()
Next s

Thanks for any help

I am just not clear on one thing, why you are using Labels in Insert statement. I hope you are looking for something like this :

Dim

arrUserID()AsString = strUserID.Split("*")Dim arrCompanyID()AsString = strCompanyID.Split("*")Dim arrCompanyName()AsString = strCompanyName.Split("*")Dim iAsIntegerDim SqlTextAsString =""For i = 0To 2

SqlText =

"INSERT INTO tableA (UserID, CompanyID, CompanyName) VALUES (" & arrUserID(i) &", '" & arrCompanyID(i) &"','" & arrCompanyName(i) &"')"

cmd =

New SqlCommand(SqlText, strSQLConn)

cmd.ExecuteNonQuery()

Next

Hope this helps
Cheers
Ritesh|||

Yes, this is exactly what I'm looking for. Thanks.

The only line that will not work is

For i = 0To 2

There could be any number of elements (not just 2) in the arrays so it can't be hard code at 2. How do I get the length of the arrays?

Thanks

|||Hey...I guess you said "Each string will always have the same number of elements."
Anyways...you can get the array length arrCompanyID.Length (for i = 0 to arrCompanyID.Length )

Ritesh|||Let me know, incase this thing doesnt work for you :)|||

Thank you Ritish. You're answer helped me immensely and I marked it as the answer.

I have another problem related to this same array if you happen to have any suggestions

http://forums.asp.net/thread/1672057.aspx

Saving a custom object in a sql database table.

Hi,
Using VB.net I have created a custom object (hope my terminology is correct here), it's just a class that defines a few string, integer, and hash table variables. After creating an instance of this class and populating it with data I need to be able to store this instance of my object in a sql server data base table. How do I do this? I saw an article that used the image data type to achieve this (code was in java unfortunately) is this the correct approach. Could you please give me some code examples

Thanks

If you give your class the Serializable attribute, you can bsaically save it to, and load it from a stream, (or string). This means it can be easily saved to a file, database, or even copied across the internet without fear of it being blocked. You can also save it as binary too.

Since you are in effect creating an XML data string when you serialize, you can either save it in an SQL Server 2005 xml field, or into a text field in SQL Server 2000

Have a look athttp://msdn2.microsoft.com/en-us/library/et91as27.aspx

Save xml in sql

Hi...

I want to save xml string in sql, but, i have problem, the length of the string is 19,000 - 24000 chars, and nVarChar can contain 4000 chars, and binary can contain 8000 chars...

so, how can i solve this?

thank you...

I generally use a text field for storing html/xml info

|||

Hi there,

Use datatype Object to store that info.

Convert it into bytes, and store it on that Objecto field.

To retrieve it, obtain the bytes from the SQL Server, and convert to string again. It's the same way you strore files like pictures, etc..

gonzzas

|||

HI

You can use ntext type to store the string.

|||

Hi,

Beside the above solutions, you also can focus on SQL(Yukon). It provides a rich set of tools for storing and manipulating XML in databases, without treating it as a blob of binary data or an arbitrary string. When you want XML, just use the xml data type, which is a fundamental part of the product.

The link may be helpful to you. http://msdn.microsoft.com/msdnmag/issues/04/02/XMLinYukon/default.aspx

Thanks.

Save password as MD5

A friend of my self asked me how he can save a password not as clear text. He wanted to encrypt the password and save the encrypted string in the database.

How can he do this. I heard from somebody that he might do this with SSIS. Unfortunately I doesn't understand what he ment. Maybe somebody can help me here.

Regards Markus

I'm also not sure what password he means, and what exactly he wants to encrypt? By the way, MD5 is not encryption algorithm, it is "message digest" (secure hash) algorithm.

If you have bigger picture, we could try to suggest how SSIS or new SQL Server features may help.

SQL Server 2005 has extensive support for encryption and hashing inside the database, you may consult at
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1

Save a large string in database

hi

is there a datatype thatcan handel a very large string ( i dont want to save it in a file and save the file in the bd ) i want to save it as it is

thanx a lot

Varchar can hold 8000 characters now. Use Text for longer strings.|||Hi,

SQL Server 2005 introduced VARCHAR(MAX) which can hold up to 2^31 byte of data.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

i use sql server 2000 and its has text and it can hold up to 2^31-1

but i need a bigger datatype

|||

TEXT and IMAGE columns both have the ~2GB limit that you have identified and there are, unfortunately, no other options available for you to store larger data directly in SQL Server 2000. This same limit will be hit with the varchar(max) column types in SQL Server 2005 that Jens mentioned previously.

If it's feasible, it would obviously be helpful to segment the data such that it can be stored in columns of this size. Failing that, I think you've identified a workable solution to store some kind of URI (UNC path, URL, etc.) and allow the client app to take care of retrieving the file as a separate "query".

|||+1 for the file approach as if you hit the limit you will have no other chance, but consider this paper for storing a BLOB in the database or on the file system.

http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de