Click here to Skip to main content
15,939,243 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i am trying to insert date in SQL table with date datatype column from text box formatted to "DD/MM/YYYY"

1. if the date is 01/11/2018 it shows in sql table as 2018-01-11

2. if date is 16/11/2018 it gives error conversion failed when converting date and /or time from character string.

What I have tried:

<
VB
pre>Private Sub ButTest_Click(sender As Object, e As EventArgs) Handles ButTest.Click
        Dim ErrorMsg As String

        Try

            Call DBCnConnection()

            Dim Cmd As New SqlClient.SqlCommand

            Cmd.Connection = DBCnCommon

            Cmd.CommandText = " Insert Into TblTest(Entno,Posdate)" &
                "Values('" & 1 & "','" & TbxPostDate.Text & "')"

            Cmd.ExecuteNonQuery()
            DBCnClose()
        Catch ex As Exception
            ErrorMsg = ex.Message
            System.IO.File.AppendAllText("d:\Log\Log.text", ex.ToString & vbNewLine & vbNewLine)
            MsgBox(ErrorMsg)
        End Try

        DBCnClose()

    End Sub
Posted
Updated 16-Nov-18 2:02am

Not like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

So use DateTime.TryParse to convert your textbox to an actual DateTime object - reporting any problem back to the user - and then pass the DateTime to your DB directly via a query parameter.
Dim postDate As DateTime

If Not DateTime.TryParse(TbxPostDate, postDate) Then
    ... report problem to user
    Return
End If

Using con As New SqlConnection(strConnect)
    con.Open()
    Using com As New SqlCommand("INSERT INTO myTable (myColumn1, myColumn2) VALUES (@C1, @C2)", con)
        com.Parameters.AddWithValue("@C1", myValueForColumn1)
        com.Parameters.AddWithValue("@C2", myValueForColumn2)
        com.ExecuteNonQuery()
    End Using
End Using
 
Share this answer
 
SQL Injection was identified over 20 years ago, why do we see code which is susceptible every day still?

The best thing to do would be to read the values into properly typed variables and validate that they are within the range that you want.

Then use Parameterized Queries to add these values to your SQL Command. The bonus to this is that when you add the values this way, ADO will know what types of values so it can format the command correctly.
C#
// you will need to transpose to VB

DateTime Posdate = (DateTime)TbxPostDate.Text; // I would validate this prior

Cmd.CommandText = "INSERT TblTest(Entno,Posdate) VALUES (1, @Posdate)";
Cmd.Paramaters.AddWithValue("@Posdate", Posdate);
 
Share this answer
 
Comments
Richard Deeming 16-Nov-18 9:09am    
Wikipedia claims it was first mentioned in an article from December 1998, so it's still a couple of weeks short of 20 years. :)
MadMyche 16-Nov-18 11:24am    
I will agree that the article was published in a magazine a tad shy of 20 years ago...

I cannot recall any time that I discovered a problem in an MS product, researched it, consulted and conversed with an MS division group about, followed up with an article about it, and; had it published within a few months
Richard Deeming 16-Nov-18 11:28am    
SQLi isn't a problem in an MS product; it affects every DBMS, and every language used to access them.

It probably still took a while to write and publish the article. But I don't think it would have to be run past Microsoft first. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900