VB.NET School Assignment? Project Due Tomorrow? Chat LIVE With A Programming Expert!

Programming Help

Web Development



What's Here?

  • Members: 300,441
  • Replies: 826,065
  • Topics: 137,464
  • Snippets: 4,419
  • Tutorials: 1,148
  • Total Online: 1,523
  • Members: 88
  • Guests: 1,435

Welcome to Dream.In.Code
Become a VB.NET Expert!

Join 300,441 VB.NET Programmers for FREE! Get instant access to thousands of VB.NET experts, tutorials, code snippets, and more! There are 1,523 people online right now. Registration is fast and FREE... Join Now!




Sql Date - 1900-01-01

 

Sql Date - 1900-01-01
Paul Washburn

30 Jun, 2009 - 11:31 AM
Post #1

D.I.C Head
**

Joined: 2 Oct, 2008
Posts: 65



Thanked: 2 times
My Contributions
I am creating a program that will use sql 2008 as a back end and have run into an issue passing the date from the program to the database, where the date is also listed as 1900-01-01 in the database. The data type in the database is smalldatetime and i am trying to only display the date.

If i change the datatype to date in the database i get the error that "sqlunhandled exception: Operand type clash: int is incompatible with date"

Ideally id like to display the date in a dd/mm/yyyy format.

Thanks for the help.

cboDate is a datetimepicker.

CODE

Dim addUser As String = "INSERT INTO tblCaseAdmin ( Digit, Day, Office, CasesReport) VALUES (" & cboDigit.Text & ", " & cboDate.Value.Date & " ,'" & cboOffice.Text & "','" & txtCasesReport.Text & "')"

Dim cmd As SqlCommand = New SqlCommand(addUser, cnn)

cmd.ExecuteNonQuery()


Ive also tried

CODE

cboDate.Value.toshortdatestring


This post has been edited by Paul Washburn: 30 Jun, 2009 - 11:55 AM

User is offlineProfile CardPM
+Quote Post


CamoDeveloper

RE: Sql Date - 1900-01-01

30 Jun, 2009 - 12:09 PM
Post #2

D.I.C Head
Group Icon

Joined: 12 Jun, 2009
Posts: 204



Thanked: 12 times
Dream Kudos: 200
My Contributions
Have you tried changing the SQL Data Type to DateTime? Also, how are you declaring cboDate? You can do something like this to get the current date.
CODE

Dim newdate As DateTime
newdate = Date.Now

That will give you the date in a MM/DD/YYYY format but you can format to how you like when you retrieve it from the database.

~Camo
User is offlineProfile CardPM
+Quote Post

Paul Washburn

RE: Sql Date - 1900-01-01

30 Jun, 2009 - 12:29 PM
Post #3

D.I.C Head
**

Joined: 2 Oct, 2008
Posts: 65



Thanked: 2 times
My Contributions
QUOTE(CamoDeveloper @ 30 Jun, 2009 - 12:09 PM) *

Have you tried changing the SQL Data Type to DateTime? Also, how are you declaring cboDate? You can do something like this to get the current date.
CODE

Dim newdate As DateTime
newdate = Date.Now


That will give you the date in a MM/DD/YYYY format but you can format to how you like when you retrieve it from the database.

~Camo

Thanks,

cboDate is a datetimepicker, by they default to the current date/time. I have also tried declaring

CODE

cboDate.value = now


in the form start up. The 1900-01-01 is the same even if i manually put in a date. I found online that this is the default value for the smalldatetime field in sql possible for the datetime field also, so is there a chance that the value is not being passed to sql at all and if so how would i test that?.
User is offlineProfile CardPM
+Quote Post

CamoDeveloper

RE: Sql Date - 1900-01-01

30 Jun, 2009 - 02:19 PM
Post #4

D.I.C Head
Group Icon

Joined: 12 Jun, 2009
Posts: 204



Thanked: 12 times
Dream Kudos: 200
My Contributions
Just do some Response.Writes to see the value of each item you're inserting into the DB. Do you have to use a datetimepicker or are you using it just to be using it?

~Camo
User is offlineProfile CardPM
+Quote Post

Paul Washburn

RE: Sql Date - 1900-01-01

2 Jul, 2009 - 04:49 AM
Post #5

D.I.C Head
**

Joined: 2 Oct, 2008
Posts: 65



Thanked: 2 times
My Contributions
I need it to auto format for date, so date picker would be ideal. Just trying to make it as idiot proof as possible.
User is offlineProfile CardPM
+Quote Post

Luc001

RE: Sql Date - 1900-01-01

2 Jul, 2009 - 05:19 AM
Post #6

D.I.C Head
**

Joined: 4 May, 2009
Posts: 147



Thanked: 12 times
My Contributions
Hi Paul,

I think you'll find some information about SQL Datatype here.
User is offlineProfile CardPM
+Quote Post

sithius92

RE: Sql Date - 1900-01-01

3 Jul, 2009 - 08:21 AM
Post #7

New D.I.C Head
*

Joined: 1 Aug, 2008
Posts: 35



Thanked: 2 times
My Contributions
Hi Paul,

Try this:

CODE
cboDate.text


This will take the text (the date) that is displayed in the datetimepicker control.
User is offlineProfile CardPM
+Quote Post

Paul Washburn

RE: Sql Date - 1900-01-01

6 Jul, 2009 - 07:23 AM
Post #8

D.I.C Head
**

Joined: 2 Oct, 2008
Posts: 65



Thanked: 2 times
My Contributions
QUOTE(sithius92 @ 3 Jul, 2009 - 08:21 AM) *

Hi Paul,

Try this:

CODE
cboDate.text


This will take the text (the date) that is displayed in the datetimepicker control.


Thanks gave that a shot and still getting 1900-01-01 on the database. I outputted the value to a text file and to a message box and it came out correct in both cases.

Is there way to test what value is being received by the database? The only thing i can think is that the data either isnt reaching the database correctly, or more likely its not being converted properly once it gets there.

Thanks again for the suggestions.

This post has been edited by Paul Washburn: 6 Jul, 2009 - 07:23 AM
User is offlineProfile CardPM
+Quote Post

Luc001

RE: Sql Date - 1900-01-01

6 Jul, 2009 - 08:43 AM
Post #9

D.I.C Head
**

Joined: 4 May, 2009
Posts: 147



Thanked: 12 times
My Contributions
QUOTE(Luc001 @ 2 Jul, 2009 - 05:19 AM) *

Hi Paul,

I think you'll find some information about SQL Datatype here.


Hi Paul,

Did you read this post?
User is offlineProfile CardPM
+Quote Post

bflosabre91

RE: Sql Date - 1900-01-01

6 Jul, 2009 - 09:14 AM
Post #10

go sabres
****

Joined: 22 Feb, 2008
Posts: 658



Thanked: 12 times
My Contributions
put a breakpoint on the program where the sql query is being run. then check you value there by using the Watch debug window. it should tell you what value is being passed to the query you are executing. or just msgbox(data.value.tostring) if that looks good, then try putting the date in single quotes ' ' in the query.
User is offlineProfile CardPM
+Quote Post

Paul Washburn

RE: Sql Date - 1900-01-01

6 Jul, 2009 - 09:33 AM
Post #11

D.I.C Head
**

Joined: 2 Oct, 2008
Posts: 65



Thanked: 2 times
My Contributions
QUOTE(bflosabre91 @ 6 Jul, 2009 - 09:14 AM) *

put a breakpoint on the program where the sql query is being run. then check you value there by using the Watch debug window. it should tell you what value is being passed to the query you are executing. or just msgbox(data.value.tostring) if that looks good, then try putting the date in single quotes ' ' in the query.


Thanks I had tried the break already and the dates were coming through fine there.

Your suggestion of putting ' ' around the field seems to have worked.

CODE


Dim addUser As String = "INSERT INTO tblCaseAdmin ( Digit, [Day], Office, CasesReport) VALUES (" & cboDigit.Text & ", '" & cboDate.Value & "' ,'" & cboOffice.Text & "','" & txtCasesReport.Text & "')"



Thanks everyone for the help.
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic

Time is now: 11/8/09 01:23AM
Invision Power Board v2.1.7 © 2009  IPS, Inc.
Licensed to: MediaGroup1

Live VB.NET Help!

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter Fan Us On Facebook

VB.NET Tutorials

Reference Sheets

VB.NET Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month