Inserting the current datetime in vb.net(DateTime to String Conversion)

Question

I'm suppose to enter datatime to the database by passing this query

Dim regDate As DateTime = DateTime.Now
Dim strDate As String = regDate.ToString("yyyyMMddHHmmss")

I pass the "strDate" to the query,data type of my database table is datetime

objcon.DoExecute("INSERT INTO DistributorF VALUES('" & txtDisId.Text & "',
     '" & txtDisNm.Text & "','" & txtDisAdd.Text & "',
     '" & txtDisTele.Text & "','" & txtDisEmail.Text & "','" & regDate & "')")"

but it's getting error saying that

conversion failed when converting date and/or time from character string.

Help me to solve this problem

If the field expects a datetime you should pass a datetime. Could you show the failing code? And tell us what database system are you using. — Steve, Jun 19 '14 at 06:45
'Dim strDate As String = regDate.ToString("yyyy-MM-dd HH:mm:ss") ?' — HengChin, Jun 19 '14 at 06:46
@Steve this is the code " Dim regDate As DateTime = DateTime.Now" i'm passing regDate to my database field.I'm using sql server 2008 r2 — thilim9, Jun 19 '14 at 06:48
@NagarajS ya it's work fine.but it's not committed to the database.getting error that saying "conversion failed when converting data and/or time from character string."that is the problem i have — thilim9, Jun 19 '14 at 06:57
@Randz90, how you pass the value to database? The conversion might failed at program level but not database level — HengChin, Jun 19 '14 at 07:00
@NagarajS "Dim regDate As DateTime = DateTime.Now Dim strDate As String = regDate.ToString("yyyyMMddHHmmss")" i'm passing strDate to the db. — thilim9, Jun 19 '14 at 07:04
@chinz this is my sql string "objcon.DoExecute("INSERT INTO DistributorF VALUES('" & txtDisId.Text & "','" & txtDisNm.Text & "','" & txtDisAdd.Text & "','" & txtDisTele.Text & "','" & txtDisEmail.Text & "','" & regDate & "')")" — thilim9, Jun 19 '14 at 07:06
did you change **yyyyMMddHHmmss** to **yyyy-MM-dd HH:mm:ss** ? — HengChin, Jun 19 '14 at 07:07
you should have changed it and pass in the **strDate** in your query — HengChin, Jun 19 '14 at 07:13
@chinz I want to validate NIC number text field in my vb form.It should contain 9 numbers[0-9] and one letter(letter should be "v")how do i validate such a thing? — thilim9, Jun 19 '14 at 07:19
what you're looking for is something called regular expression (regex). Go search around. — HengChin, Jun 19 '14 at 07:21
The real problem here is the DoExecute method that doesn't accept parameters. Without a radical change in that method there is no way to propose a valid (and definitive) answer to your recurring problems (It seems a lot of yours question revolves around this point) — Steve, Jun 19 '14 at 08:50

score 2 · Answer 1 · answered Jun 19 '14 at 07:10

2

 Dim regDate As DateTime = DateTime.Now
 Dim strDate As String = regDate.ToString("yyyy-MM-dd HH:mm:ss")

 objcon.DoExecute("INSERT INTO DistributorF VALUES('" & txtDisId.Text & "',
 '" & txtDisNm.Text & "','" & txtDisAdd.Text & "',
 '" & txtDisTele.Text & "','" & txtDisEmail.Text & "','" & strDate & "')")"

answered Jun 19 '14 at 07:10

HengChin

583
5
16

2

Glad to help, but like Negaraj said, you should have go for paremeterized query in the future to avoid SQL injection. refer to http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i – HengChin Jun 19 '14 at 07:18
2

SQL Injection is a very big big problem here and everywhere. We should never try to use workarounds to a parameterized query. By the way the problem with this query is just postponed, what happens if, in one or more of the textboxes above, the user types a single quote? – Steve Jun 19 '14 at 08:36
@Steve I guess everyone know about the SQL Injection problem(if they knows SQL injection). Perhaps we couldn't stop people how they code, but suggest, until they know more and further advance they coding skill? – HengChin Jun 19 '14 at 08:42
Suggesting what the community consensus tell us it is a bad way to do things? In this precise case I didn't downvote your answer because the real problem is the DoExecute method that doesn't seem to accept parameters but nevertheless you should really emphasize the problem in your answer. – Steve Jun 19 '14 at 08:47

score 0 · Answer 2 · answered Jun 19 '14 at 07:12

0

you have to pass strDate in query.Always use paremeterized query to avoid SQL injection

objcon.DoExecute("INSERT INTO DistributorF VALUES('" & txtDisId.Text & "',
     '" & txtDisNm.Text & "','" & txtDisAdd.Text & "',
     '" & txtDisTele.Text & "','" & txtDisEmail.Text & "','" & strDate & "')")"

answered Jun 19 '14 at 07:12

Nagaraj S

13,316
6
32
53

1

SQL Injection is a very big big problem here and everywhere. We should never try to use workarounds to a parameterized query. By the way the problem with this query is just postponed, what happens if, in one or more of the textboxes above, the user types a single quote? – Steve Jun 19 '14 at 08:38
@steve I don't know how to use parameter for DoExecute – Nagaraj S Jun 19 '14 at 08:57
Yes I know. That's the reason I didn't downvote your and @chinz answer. That method is fundamentally flawed, however you could explain the problem in your answer – Steve Jun 19 '14 at 09:16

score 0 · Answer 3 · answered Jun 19 '14 at 12:37

May this works for you, at least it works for me on an acces db

Try    
  Dim cn As New OleDbConnection("your conection string here")
        If cn.State = ConnectionState.Open Then
            cn.Close()
        End If
        cn.Open()
  Dim sSQL As String = "insert into UserInfo(Date) values(@d1)"
  Dim cmd As OleDbCommand = New OleDbCommand(sSQL, cn)

  Dim date As OleDbParameter = New OleDbParameter("@d1", OleDbType.Date, 15)
  date.Value = DateTimePicker1.Text.ToString()
  cmd.Parameters.Add(date)

  If cmd.ExecuteNonQuery() Then
                cn.Close()
                MsgBox("successfully... ", MsgBoxStyle.Information, "Record Saved")
  Else
                MsgBox("failed... ", MsgBoxStyle.Critical, "Registration failed")
                Return
  End If

  Catch ex As Exception
        MessageBox.Show(ex.Message.ToString(), "Data Error")
        Exit Sub
  End Try

i hope this helps you,

regards Tom

Inserting the current datetime in vb.net(DateTime to String Conversion)

3 Answers3