Answered by:
can some please tell me what is wrong with my code?

Question
-
User1298215938 posted
Hi, Im trying to create a application that will check data before it in entered into sql .
CODE:
Protected Sub SUBMIT_Click(sender As Object, e As EventArgs) Handles Enter.Click
Dim conn As String = "Data Source=connection string"
Dim connection As SqlConnection = New SqlConnection(conn)
Dim cmd1 As New SqlCommand("SELECT * FROM Warehousedata (Name),(DM),(Glight),(Rlight),(Act),(date) VALUES = (@Name), (@DM), (@GLight), (@Rlight), (@Act),(@date)", connection)
connection.Open()
cmd1.Parameters.AddWithValue("@Name", Name.Text)
cmd1.Parameters.AddWithValue("@DM", dm.Text)
cmd1.Parameters.AddWithValue("@Green", Glight.Text)
cmd1.Parameters.AddWithValue("@Red", Rlight.Text)
cmd1.Parameters.AddWithValue("@Act", Act.Text)
cmd1.Parameters.AddWithValue("@date", [date].SelectedDate)
Dim reader0 As SqlDataReader = cmd1.ExecuteReaderIf (reader0.HasRows) Then
results.Text = "DATA ALREADY IN SQL"Else
connection.Close()
connection.Open()
Dim cmd As New SqlCommand("INSERT INTO Warehousedata (Name),(DM),(Glight),(Rlight),(Act),(date) VALUES = (@Name), (@DM), (@GLight), (@Rlight), (@Act),(@date)", connection)
cmd.Parameters.AddWithValue("@Name", Name.Text)
cmd.Parameters.AddWithValue("@DM", dm.Text)
cmd.Parameters.AddWithValue("@Green", Glight.Text)
cmd.Parameters.AddWithValue("@Red", Rlight.Text)
cmd.Parameters.AddWithValue("@Act", Act.Text)
cmd.Parameters.AddWithValue("@date", [date].SelectedDate)
End If
End SubWednesday, August 26, 2020 10:22 PM
Answers
-
User-943250815 posted
First, on webforms we use Validators, to check values before PostBack, you can get more info about at
https://www.codemag.com/Article/0307101/Validating-Data-On-Web-Forms
https://www.tutorialspoint.com/asp.net/asp.net_validators.htm
In addition you can do your own validation on your submit button before go to SqlCommand, something likeProtected Sub SUBMIT_Click(sender As Object, e As EventArgs) Handles Enter.Click if Name.Text = "Joe" 3 then ' Do your interaction with Database else results.Text = "Name is invalid!" end if
End sub- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, August 27, 2020 12:22 AM -
User-1330468790 posted
Hi MOHIIMRAN,
I am afraid that you have constructed a wrong SQL query for both select and insert.
SqlConnection and SqlCommand are supposed to be used for Sql Server database while the Sql Server does not support those Select and Insert statement syntax.
You could refer to the following links for more details: SELECT (Transact-SQL) and INSERT (Transact-SQL)
When it comes to an implementation, you have to set up a rule that how you identify the duplicated values.
For example, if the rule is that a data row is not duplicated when the data of this is not exactly the same as any row in the database.
The SELECT and INSERT statement should be as follow:
'SELECT Statement SELECT * FROM Warehousedata WHERE Name = @Name AND DM=@DM AND GLight = @GLight AND Rlight=@Rlight Act= @Act AND date = @date 'INSERT Statement
INSERT INTO Warehousedata (Name,DM,Glight,Rlight,Act,date) VALUES (@Name, @DM, @GLight, @Rlight, @Act,@date)If you have type constraints on each column, you should do validation on the inputs of the page as @jzero suggested.
Any further question, feel free to tell me.
Hope this can help you.
Best regards,
Sean
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, August 27, 2020 2:14 AM
All replies
-
User-943250815 posted
First, on webforms we use Validators, to check values before PostBack, you can get more info about at
https://www.codemag.com/Article/0307101/Validating-Data-On-Web-Forms
https://www.tutorialspoint.com/asp.net/asp.net_validators.htm
In addition you can do your own validation on your submit button before go to SqlCommand, something likeProtected Sub SUBMIT_Click(sender As Object, e As EventArgs) Handles Enter.Click if Name.Text = "Joe" 3 then ' Do your interaction with Database else results.Text = "Name is invalid!" end if
End sub- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, August 27, 2020 12:22 AM -
User-1330468790 posted
Hi MOHIIMRAN,
I am afraid that you have constructed a wrong SQL query for both select and insert.
SqlConnection and SqlCommand are supposed to be used for Sql Server database while the Sql Server does not support those Select and Insert statement syntax.
You could refer to the following links for more details: SELECT (Transact-SQL) and INSERT (Transact-SQL)
When it comes to an implementation, you have to set up a rule that how you identify the duplicated values.
For example, if the rule is that a data row is not duplicated when the data of this is not exactly the same as any row in the database.
The SELECT and INSERT statement should be as follow:
'SELECT Statement SELECT * FROM Warehousedata WHERE Name = @Name AND DM=@DM AND GLight = @GLight AND Rlight=@Rlight Act= @Act AND date = @date 'INSERT Statement
INSERT INTO Warehousedata (Name,DM,Glight,Rlight,Act,date) VALUES (@Name, @DM, @GLight, @Rlight, @Act,@date)If you have type constraints on each column, you should do validation on the inputs of the page as @jzero suggested.
Any further question, feel free to tell me.
Hope this can help you.
Best regards,
Sean
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, August 27, 2020 2:14 AM -
User1298215938 posted
Hi Sean I added a constraints on each column in my SQL table change a error page into a label box instead?
Thanks
Thursday, August 27, 2020 5:02 PM -
User-1330468790 posted
Hi MOHIIMRAN,
Do you mean that you already had a contraints on SQL database?
However, it is not enough. What we are talking about is to validate the input from user so that we could only process those user inputs which are fulfill the specific data format.
change a error page into a label box instead?I am not very sure what this question mean. Do you mean you want to render the error using an error page instead of a label?
Or you want to use a pop up to tell users that they input a wrong type of data?
Best regards,
sean
Friday, August 28, 2020 8:00 AM