SQLDataSource Messages

[MichaelWClark]

Hello I have a Gridview that is updating and inserting information into my tables. I am using the following query to insert:

Dim InsStr = _
                 "IF NOT EXISTS" & _
                 "(SELECT [PRODUCT_NAME] FROM [PRODUCT] WHERE [PRODUCT_NAME] = '" + ProdName + "'" & _
                 " UNION " & _
                 " SELECT [PRODUCT_MODEL] FROM [PRODUCT] WHERE [PRODUCT_MODEL] = '" + ProdModel + "')" & _
                 "INSERT INTO [PRODUCT] " & _
                 "([PRODUCT_CATAGORY], [PRODUCT_NAME],[PRODUCT_DESCRIPTION]," & _
                 "[PRODUCT_MODEL] , [PRODUCT_MANUFACTURER])" & _
                 "VALUES ( " & _
                 "'" + ProdCatagory + "'," & _
                 "'" + ProdName + "'," & _
                 "'" + ProdDescription + "'," & _
                 "'" + ProdModel + "'," & _
                 "'" + ProdManufacturer + "')"

It won't insert if the name or model number matches any existing rows, so it works.. the issue is I have no way of letting the user know that his/her row didn't update/insert because of a duplicate model/product name. In my SQL Studio I run the command with a duplicate name or model and the result will be:

Command(s) completed successfully.

If it inserts it will be:

1 Row Affected

How can i check these conditions pragmatically with my asp code?

Thanks! :)

[rohand]

Do one thing, before inserting/updating row in your table, first check whether the name/model exist in database. You can check this by executing Select Query. so if it returns any rows that means duplicate name/model. If it's not returning any rows that means no duplication and then fire your insert query. So your code may look like below:

SqlCommand cmd = new SqlCommand("your select query goes here",your connection)
int cnt = Convert.ToInt32(cmd.ExecuteSclar())
if (cnt > 0)
{
show validation message that duplicate model/name
}
else
{
your insert/update query code goes here
}

try it and let us know.

Hello I have a Gridview that is updating and inserting information into my tables. I am using the following query to insert:

Dim InsStr = _
                 "IF NOT EXISTS" & _
                 "(SELECT [PRODUCT_NAME] FROM [PRODUCT] WHERE [PRODUCT_NAME] = '" + ProdName + "'" & _
                 " UNION " & _
                 " SELECT [PRODUCT_MODEL] FROM [PRODUCT] WHERE [PRODUCT_MODEL] = '" + ProdModel + "')" & _
                 "INSERT INTO [PRODUCT] " & _
                 "([PRODUCT_CATAGORY], [PRODUCT_NAME],[PRODUCT_DESCRIPTION]," & _
                 "[PRODUCT_MODEL] , [PRODUCT_MANUFACTURER])" & _
                 "VALUES ( " & _
                 "'" + ProdCatagory + "'," & _
                 "'" + ProdName + "'," & _
                 "'" + ProdDescription + "'," & _
                 "'" + ProdModel + "'," & _
                 "'" + ProdManufacturer + "')"

It won't insert if the name or model number matches any existing rows, so it works.. the issue is I have no way of letting the user know that his/her row didn't update/insert because of a duplicate model/product name. In my SQL Studio I run the command with a duplicate name or model and the result will be:

Command(s) completed successfully.

If it inserts it will be:

1 Row Affected

How can i check these conditions pragmatically with my asp code?

Thanks! :)

[MichaelWClark]

Worked Perfectly using the following code:

Private Function CheckDuplicate(ByVal Name As String, ByVal Model As String, ByVal ProdID As Integer) As Boolean
        Try
            Dim strSelect As String
            strSelect = "SELECT [PRODUCT_NAME] FROM [PRODUCT] WHERE [PRODUCT_NAME] ='" + Name + "' AND [PRODUCT_ID] !=" + ProdID.ToString + _
            " union SELECT [PRODUCT_MODEL] FROM [PRODUCT] WHERE [PRODUCT_MODEL] = '" + Model + "' AND [PRODUCT_ID] !=" + ProdID.ToString
        
            
            Dim SQLConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
            SQLConn.Open()
            Dim Q = strSelect
            Dim SqlCom As New SqlCommand(strSelect, SQLConn)
            Dim RowCnt = SqlCom.ExecuteScalar()
            SQLConn.Close()
            SQLConn.Dispose()
            If RowCnt = Nothing Then
                Return True
            Else
                Return False
            End If            
        Catch ex As Exception
            CType(pnlProductError.FindControl("pnlError"), Panel).Visible = True
            CType(pnlProductError.FindControl("lblError"), Label).Text = ex.ToString
        End Try
    End Function

I knew I could run another query and check that way but I was hoping to limit my lines of code and my # of SQL Transactions...but..for now this will work, it does what its intended too. Thanks again! :)

[rohand]

cool :-)