0 
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.

Improve this question
3
  • 3
    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 Commented Apr 19, 2016 at 8:21
  • 2
    First, I strongly recommend that you use Option Strict On. Then, use SQL parameters for the values in the query. Commented Apr 19, 2016 at 8:23
  • "Everything works fine?" That is confusing as that code cannot possibly work Commented Apr 19, 2016 at 8:37

4 Answers 4

Reset to default
1

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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?
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 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.
1

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

Or

 ",#" & DateTimePicker1.Value.Date & "#)"
Improve this answer

Comments

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.

Improve this answer

Comments

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)

Improve this answer

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.