Inserting values in a datetime field

Hi All,
I have a datetime column in a table on the SQL database. I need to insert
values into the datetime column from vb.net code. Here is my code:
dim nameval, str, qry as string
nameval = "abc"
str = "2005/03/16 14:20"
qry = "insert into tab1(name,dateval) values(" & "'" & nameval & "'," & "'"
str & "')"
...
..
ocmd.ExecuteNonQuery()
...
...
The error message that I get is as follows:
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value. The statement has been terminated. .Net
SqlClient Data Provider"
The problem I think is due to passing a string for a datetime field. My
question is, if I convert the string to datetype using CDate(str), then I
would have to again convert the date to string in order to form the insert
statement. So, the ultimate result will be again passing a string for the
datetime field!
I know that this is a simple syntax problem, which I don't seem to get right
!
Would anybody be able to give me insert statement for the above?
Thanks.
kdkd
Format the parameter as 'YYYYMMDD'
"kd" <kd@.discussions.microsoft.com> wrote in message
news:FC6115C1-BD59-4B91-A237-6E10C38EB3A8@.microsoft.com...
> Hi All,
> I have a datetime column in a table on the SQL database. I need to insert
> values into the datetime column from vb.net code. Here is my code:
> dim nameval, str, qry as string
> nameval = "abc"
> str = "2005/03/16 14:20"
> qry = "insert into tab1(name,dateval) values(" & "'" & nameval & "'," &
"'"
> str & "')"
> ...
> ..
> ocmd.ExecuteNonQuery()
> ...
> ...
> The error message that I get is as follows:
> "The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value. The statement has been terminated. .Net
> SqlClient Data Provider"
> The problem I think is due to passing a string for a datetime field. My
> question is, if I convert the string to datetype using CDate(str), then I
> would have to again convert the date to string in order to form the insert
> statement. So, the ultimate result will be again passing a string for the
> datetime field!
> I know that this is a simple syntax problem, which I don't seem to get
right!
> Would anybody be able to give me insert statement for the above?
> Thanks.
> kd
>

Inserting the diff rows into a table

Hi,

I have two tables as follows

Table TempTab

{

CatId varchar(20),

lastupdate Datetime

}

Table MainTab

{

CatId varchar(20),

lastupdate Datetime

}

and the data in those tables are as follows

Table TempTab

{

CatId LastUpdate

Cat1 D1

Cat2 D2

Cat3 D3

Cat4 D4

}

Table MainTab

{

CatId LastUpdate

Cat1 D1

Cat3 D3

Cat5 D5

}

I need a query to insert the differences into the MinTab, i mean to say the fincal MainTab should look like as follows

Table MainTab

{

CatId LastUpdate

Cat1 D1

Cat2 D2

Cat3 D3

Cat4 D4

Cat5 D5

}

can any one please let me know the query

Thanks alot

~Mohan

INSERT INTO MainTab(CatID, LastUpdate)
SELECT CatID, LastUpdate FROM TempTab
WHERE CatID NOT IN(SELECT CatID FROM MainTab WHERE atID IS NOT NULL)
|||

Here it is,

Code Snippet

Create Table #temptab (

[CatId] Varchar(100) ,

[LastUpdate] Varchar(100)

);

Insert Into #temptab Values('Cat1','D1');

Insert Into #temptab Values('Cat2','D2');

Insert Into #temptab Values('Cat3','D3');

Insert Into #temptab Values('Cat4','D4');

Create Table #maintab (

[CatId] Varchar(100) ,

[LastUpdate] Varchar(100)

);

Insert Into #maintab Values('Cat1','D1');

Insert Into #maintab Values('Cat3','D3');

Insert Into #maintab Values('Cat5','D5');

Code Snippet

Insert Into #maintab

Select * from #temptab t

Where not exists

(

select 1 from #maintab m

where m.catid=t.catid

)

inserting null values into smalldatetime

hi
how can we insert null into a small datetime field (from client application VB)Insert into table values (null);|||Originally posted by r123456
Insert into table values (null); that only works if the field in question happens to be the only field in the table ;)

insert into thetable (foo, bar, datetimefld, qux, fap)
values ( 21, 'fred', null, 42, 'okay')

Inserting NULL into Datetime field

I have a datetime field in a database which I am programmatically inserting values into. The field can be null or not null. However, I am having problems inserting NULL as part of my SQLCommand.

The value is pulled from a text box (linked to a calendar extender) and when I select a value it is being inserted fine. If I just leave the box blank though, I want the field to be set to NULL. I have tried adding in ,DBNULL.Value, as part of my VALUES(…) string, but this throws an exception. I Have tried just inserting '' but that also throws an exception ("The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"), so I don't know how I can insert this value when the field is blank?

Can anyone shed some light please?

Thanks

Check to see if the last answer of this other post helps:http://forums.asp.net/t/996855.aspx

|||

You can also check if this post helps:http://dotnetjunkies.com/WebLog/dinakar/articles/74221.aspx

inserting NULL into Datetime and int

Hello,

I have an asp.net page with different textboxes that put text into an sql database.
When I try to leave the textboxes blank that correspond with datatypes of int and datetime
in sql server it gives an error : I need to fill in these textboxes.

How can I solve this problem so I can leave the boxes blanc? What is the best sollution for this? I know I can set the types to String but then the user could fill in what he wants :)
Could you give me some advice on this please? All ideas are welcome.
Thanks !if you strictly want to enforce the user to enter the proper data and not mess up the system, use customvalidators for the textboxes.

for date, you can prbly have three textboxes for month, yr and day and validate it to numeric and when you add the values to db, concatenate them with a "/" in between.

or if the user leaves it blank, check if the txtDate.text = "" then use system.dbnull to enter null into the db.

HTH.|||Hey thanks ndinakar :)

Ok I've looked in the .net classes for this dbnull. There's no good example of how to do that.
Do I use this dbnull on the page script or in the database (for example in a stored procedure?)
Could you give an example please?(I'm very noob)

Thanks again for replying !