locked
can some please tell me what is wrong with my code? RRS feed

  • 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

    Wednesday, 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 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

    • 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 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

    • 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