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

If (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 Sub

Answer 1

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 like

Protected 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

Answer 2

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

Answer 3

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

Answer 4

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