[RESOLVED] [02/03] URGENT : Strange Problem with SqlParameter in Update Query

[Coool]

Hi all,
I have faced one problem in SqlParamter passing.

I have one table named tbl_Member in SQL Server 2000

Mem_Id---> Int - Primary Key
Mem_Income-->Int
Mem_Note --> Varchar(50)

Try
            Dim sqlConn As New SqlConnection("Server=Aceteam;Database=Parth-Temp;UID=sa;PWD=*sAraM!wAt")
            Dim strQuery As String = ""
            strQuery = "UPDATE tbl_Member SET " + _
                            " Mem_Id = @ID ," + _
                            " Mem_Income = @Income ," + _
                            " Mem_Note = @Note " + _
                            " WHERE Mem_Id = " + Convert.ToInt32(e.Item.Cells(0).Text)
            Dim sqlCmd As New SqlCommand
            sqlCmd.Parameters.Add("@ID", SqlDbType.Int)
            sqlCmd.Parameters.Add("@Income", SqlDbType.Int)
            sqlCmd.Parameters.Add("@Note", SqlDbType.VarChar)
 
            sqlCmd.Parameters("@ID").Value = Convert.ToInt32(DirectCast(e.Item.Cells(1).Controls(0), TextBox).Text)
            sqlCmd.Parameters("@Income").Value = Convert.ToInt32(DirectCast(e.Item.Cells(2).Controls(0), TextBox).Text)
            sqlCmd.Parameters("@Note").Value = DirectCast(e.Item.Cells(1).Controls(0), TextBox).Text
 
            sqlCmd.CommandType = CommandType.Text
            sqlCmd.CommandText = strQuery
            sqlCmd.Connection = sqlConn
 
            sqlConn.Open()
            sqlCmd.ExecuteNonQuery()
            sqlConn.Close()
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try

The error it shows is ...
Cast from string "UPDATE tbl_Member SET Mem_Id = " to type 'Double' is not valid.

PLZ HELP URGENT

[jmcilhinney]

It's because you're using the "+" operator, which is addition. Addition means concatenation when both operands are strings but if one is a number then it means numerical addition and the non-numeric operand will be implicitly converted to a number. That conversion is failing in your case, as you'd expect. The "proper" string concatenation operator is "&". That will implicitly convert numbers to strings.

Also, you're using parameters for three values and then you go and use a literal value for the fourth. You should be using a parameter for Mem_Id too.

[Coool]

Thanks u jmcilhinney very much.

[Coool]

Oh.. I struct with one more problem.. My Update Query doesn't work .

I can see affected no of columns with No UPDATION..!!!

Here is my Code.

Try
            Dim sqlConn As New SqlConnection("Server=Aceteam;Database=Parth-Temp;UID=sa;PWD=*sAraM!wAt")
 
            Dim sqlCmd As New SqlCommand
            sqlCmd.CommandType = CommandType.Text
 
            Dim strQuery As String = ""
            strQuery = "UPDATE tbl_Member SET " & _
                                  " Mem_Income = @iIncome ," & _
                                  " Mem_Note = @iNote " & _
                                  " WHERE Mem_Id = @iCurrentId"
 
            sqlCmd.Parameters.Add("@iIncome", SqlDbType.Int)
            sqlCmd.Parameters.Add("@iNote", SqlDbType.VarChar)
            sqlCmd.Parameters.Add("@iCurrentId", SqlDbType.Int)
 
            sqlCmd.Parameters("@iIncome").Value = Convert.ToInt32(CType(e.Item.Cells(2).Controls(1), TextBox).Text)
            sqlCmd.Parameters("@iNote").Value = DirectCast(e.Item.Cells(3).Controls(1), DropDownList).SelectedValue
            sqlCmd.Parameters("@iCurrentId").Value = Convert.ToInt32(CType(e.Item.Cells(1).Controls(1), Label).Text)
 
 
            sqlCmd.CommandText = strQuery
            sqlCmd.Connection = sqlConn
 
            'sqlCmd = New SqlCommand("Update tbl_Member SET Mem_Note = 'Test Note' WHERE Mem_Id=1", sqlConn)
            sqlConn.Open()
 
            Dim i As Integer = sqlCmd.ExecuteNonQuery()
            Response.Write(i.ToString)
            Response.Write(sqlCmd.CommandText)
 
            sqlConn.Close()
        Catch ex As Exception
            Response.Write(ex.ToString)
        End Try
 
        dgrMember.EditItemIndex = -1
 
        Try
            Dim sqlConn As New SqlConnection("Server=Aceteam;Database=Parth-Temp;UID=sa;PWD=*sAraM!wAt")
            Dim sqlCmd As New SqlCommand("Select * from tbl_Member", sqlConn)
            Dim sqlDA As New SqlDataAdapter(sqlCmd)
            Dim objDS As New DataSet
            sqlConn.Open()
            sqlDA.Fill(objDS)
            dgrMember.DataSource = objDS.Tables(0)
            dgrMember.DataBind()
        Catch ex As Exception
            Response.Write(ex.ToString)
        End Try

[Coool]

Oh I am sorry , I didn't make use of IsPostBack.. This is the worst day of ASP for me.