mrinal mrinal - 6 months ago 112
Vb.net Question

to insert only date in dd/mm/yyyy format from textbox.error while inserting dates in vb.net

' Code For Insert query to insert data.'

Try
Dim Query As String
Query = "Insert * into test"
Dim cmd As SqlCommand
Query = "Insert into test(name,date,d1,d2,d3)values(@name,Getdate(),Convert(varchar,@d1,103),Convert(varchar,@d2,103),Convert(varchar,@d3,103))"
'Convert Function used to insert date in dd/MM/yyyy format'
cmd = New SqlCommand(Query, con)
con.Open()
cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = TextBox1.Text
cmd.Parameters.Add("@d1", SqlDbType.DateTime).Value = TextBox2.Text
cmd.Parameters.Add("@d2", SqlDbType.DateTime).Value = TextBox3.Text
cmd.Parameters.Add("@d3", SqlDbType.DateTime).Value = TextBox4.Text

cmd.ExecuteNonQuery()
con.Close()
lblvalid.Visible = True
lblvalid.Text = "Record is inserted successfully"

Catch ex As Exception
lblvalid.Text = (ex.Message)

End Try


'while inserting value in datetime.I want to insert in only date form'

Answer

If you are inserting date into ms-sql server then apply anyone of tow concept.

The basic solution is that you have to provide date into either mm/DD/YYYY format or in YYYY-MM-DD date format into ms-sql query.

So, before passing date to query convert your date into either mm/DD/YYYY format or in YYYY-MM-DD format.

To convert date from custom format to m/D/yyyy you can use following function.

Imports System.Globalization

Public Function GetMDYFrmCustDt(ByVal CustDate As String) As String
    Dim tmp As Date = DateTime.ParseExact(CustDate, "dd/MM/yyyy", DateTimeFormatInfo.CurrentInfo)
    GetMDYFrmCustDt = GetMDYFrmSysDt(tmp)
End Function

Public Function GetMDYFrmSysDt(ByVal SysDate As Date) As String
    Try
        GetMDYFrmSysDt = SysDate.ToString("MM/dd/yyyy")
    Catch ex As Exception
        GetMDYFrmSysDt = SysDate.ToString("MM-dd-yyyy")
    End Try
End Function

So, you need to use following code

Try
    Dim Query As String
    Query = "Insert * into test"
    Dim cmd As SqlCommand
    Query = "Insert into test(name,date,d1,d2,d3)values(@name,Getdate(),@d1,@d2,@d3)"
'Convert Function used to insert date in dd/MM/yyyy format'
    cmd = New SqlCommand(Query, con)
    con.Open()
    cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = TextBox1.Text
    cmd.Parameters.Add("@d1", SqlDbType.DateTime).Value = GetMDYFrmCustDt(TextBox2.Text)
    cmd.Parameters.Add("@d2", SqlDbType.DateTime).Value = GetMDYFrmCustDt(TextBox3.Text)
    cmd.Parameters.Add("@d3", SqlDbType.DateTime).Value = GetMDYFrmCustDt(TextBox4.Text)

   cmd.ExecuteNonQuery()
   con.Close()
   lblvalid.Visible = True
    lblvalid.Text = "Record is inserted successfully"

Catch ex As Exception
    lblvalid.Text = (ex.Message)

End Try
Comments