CODEWITHSUNDEEP
sqlsql-servervb.net
Abram
Abram

Reputation: 3

Insert date into SQL Server database through vb.net

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.

Upvotes: 0

Views: 13570

Answers (4)

Jignesh Ravte
Jignesh Ravte

Reputation: 66

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)

Upvotes: 0

Andrew Morton
Andrew Morton

Reputation: 25066

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.

Upvotes: 1

sujith karivelil
sujith karivelil

Reputation: 29036

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.

Upvotes: 0

Abdul Rasheed
Abdul Rasheed

Reputation: 6729

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

Or

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

Upvotes: 1

Related Questions