Insert date into SQL Server database through vb.net

Question

Dim SALESINSERT As New SqlCommand("INSERT INTO Tbl_Sales (Sale_id, Transaction_No, Customer_id, Item_id, Amount, Date) VALUES(" _
                                 & SalesIdMax + 1 & "," & Transaction_label.Text & "," & 1 & "," & Label4.Text & "," & TextBox1.Text & _
                                 "," & DateTimePicker1.Value.Date & ")", sqlcon)

sqlcon.Open()
SALESINSERT.ExecuteNonQuery()
sqlcon.Close()

SALESINSERT = Nothing

I have this code. Everything works just fine, but the problem is with the date. For some reason it inserts the same date every time: "1/1/1900".

When I debugged the code to see the SQL command text it was fine and the date was fine and I executed it in SQL query and it was perfectly fine.

But in VB it doesn't.

I do not know why it is not working.

Please can I have suggestions to fix it.

Use parameterised queries - this will prevent sql injection and will fix this issue as a side effect. Note that your code will fail as it is if it is run with a date different locale — Matt Wilko, Apr 19 '16 at 08:21
First, I strongly recommend that you use [Option Strict On](https://msdn.microsoft.com/en-us/library/zcd4xwzs.aspx). Then, use SQL parameters for the values in the query. — Andrew Morton, Apr 19 '16 at 08:23
"Everything works fine?" That is confusing as that code cannot possibly work — Matt Wilko, Apr 19 '16 at 08:37

score 1 · Answer 1 · answered Apr 19 '16 at 08:20

1

Use the single quotes for the date value ",'" & DateTimePicker1.Value.Date & "')"

Or

 ",#" & DateTimePicker1.Value.Date & "#)"

answered Apr 19 '16 at 08:20

Abdul Rasheed

6,486
4
32
48

score 1 · Accepted Answer · answered Apr 19 '16 at 09:20

If you always use parameterized queries then you will avoid problems with representing dates as strings.

You can use SQL parameters (I had to guess at the database column data types) for your query like this:

Dim salesinsert As New SqlCommand("INSERT INTO Tbl_Sales ([Sale_id], [Transaction_No], [Customer_id], [Item_id], [Amount], [Date])" &
                                  " VALUES(@SaleId, @TransactionNo, @CustomerId, @ItemId, @Amount, @Date)", sqlcon)

salesinsert.Parameters.Add(New SqlParameter With {.ParameterName = "@SaleId", .SqlDbType = SqlDbType.Int, .Value = SalesIdMax + 1})
salesinsert.Parameters.Add(New SqlParameter With {.ParameterName = "@TransactionNo", .SqlDbType = SqlDbType.NVarChar, .Size = 20, .Value = Transaction_label.Text})
salesinsert.Parameters.Add(New SqlParameter With {.ParameterName = "@CustomerId", .SqlDbType = SqlDbType.Int, .Value = 1})
salesinsert.Parameters.Add(New SqlParameter With {.ParameterName = "@ItemId", .SqlDbType = SqlDbType.NVarChar, .Size = 20, .Value = Label4.Text})
salesinsert.Parameters.Add(New SqlParameter With {.ParameterName = "@Amount", .SqlDbType = SqlDbType.Decimal, .Value = CDec(TextBox1.Text)})
salesinsert.Parameters.Add(New SqlParameter With {.ParameterName = "@Date", .SqlDbType = SqlDbType.DateTime, .Value = DateTimePicker1.Value})

sqlcon.Open()
salesinsert.ExecuteNonQuery()
sqlcon.Close()

salesinsert.Dispose()

I escaped the column names with square brackets - this avoids problems with using SQL reserved keywords as column names. It is easier to always escape the column names.
You should not set SALESINSERT = Nothing - instead, use salesinsert.Dispose() as this cleans up unmanaged resources properly.
You need to change each .SqlDbType (and .Size for strings) to match the datatypes of the database columns. The Decimal values ought to have the .Scale and .Precision defined too.
The controls could do with descriptive names - TextBox1 does not suggest that it will have an amount in it.
The values should be validated before running the query, e.g. can the amount text be converted to a Decimal and is it a sensible value.

well that will do it,but Iam wondering about the way you add the parameters because I just used this way 'SALESINSERT.Parameters.Add("@trans", SqlDbType.BigInt).Value = Transaction_label.Text' and it did not work why is that? — Abram, Apr 19 '16 at 09:58
well that will do it,but Iam wondering about the way you add the parameters because I just used this way `SALESINSERT.Parameters.Add("@trans", SqlDbType.BigInt).Value = Transaction_label.Text` and it did not work why is that? — Abram, Apr 19 '16 at 10:04
@Abram 1) I like using `.Add(New SqlParameter With {....` because it makes it obvious what the parameters are *and it works*. 2) You have a data type mismatch error. You can't assign a string to a number - if you use Option Strict On then the compiler will point that out for you. — Andrew Morton, Apr 19 '16 at 10:13

score 0 · Answer 3 · edited May 23 '17 at 12:16

0

The problem is with the format of the given date. you can escape from this problem by formatting the dateTime input using .ToString(). ie.,

DateTimePicker1.Value.Date.ToString("yyyy-MM-dd HH:mm:ss")

Then comes the real issue of injection; to avoid that you have to use parameterised queries instead for the text only queries.

edited May 23 '17 at 12:16

Community

1
1

answered Apr 19 '16 at 08:22

sujith karivelil

28,671
6
55
88

score 0 · Answer 4 · answered Jul 09 '21 at 12:28

0

use this one Dim SALESINSERT As New SqlCommand("INSERT INTO Tbl_Sales (Sale_id, Transaction_No, Customer_id, Item_id, Amount, Date) VALUES(" _ & SalesIdMax + 1 & "," & Transaction_label.Text & "," & 1 & "," & Label4.Text & "," & TextBox1.Text & _ ",CONVERT(DateTime,'09/07/2021',103))", sqlcon)

answered Jul 09 '21 at 12:28

Jignesh Ravte

66
1
2

This is not a good practice to concatenate values into the sql query. Some characters might cause issues, values might not be passed in the correct format / as the right object type. – Yair Maron Jul 09 '21 at 14:15

Insert date into SQL Server database through vb.net

4 Answers4