SQL Data Adapter

[mike86]

Hi, all. I've created a SQLDataAdapter so that I could output the data on the forms with specified labels. I've binned the data on the text and tag at the properties. Below is my code:

Private Sub GetStudyDetails(ByVal studyid As Double)

Dim strQuery As String
strQuery = "SELECT ReasonForStudy, LabType, SystolicPressure, DiastolicPressure, HeartRateBPM, Status, EchoStudyType FROM Study_Rpt WHERE (StudyId = @Param2)"

SqlDataAdapter3.SelectCommand.CommandText = strQuery
SqlDataAdapter3.SelectCommand.Parameters.Add("@Param2", SqlDbType.Float).Value = studyid
DsStudy1.Clear()
SqlDataAdapter3.Fill(DsStudy1)

End Sub

After debugging the program, it shows an error msg:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

Could somebody help?

 

[ss7thirty]

This could be a number of things. But the first thing I have to ask, and this may sound stupid, but I am assuming you cut and pasted the code.

SqlDataAdapter3.SelectCommand.Parameters.Add("@Par am2", SqlDbType.Float).Value = studyid

The space in the @Param2 could be causing the error because it is looking for that parameter and cannot find it when executing the statement.

That would be my first guess.

Also you should try using trys to help display more information about the error:

Try
'you code
Catch Ex As SqlClient.SqlException
  Msgbox(ex.message & vbcrlf & ex.stacktrace) 'and whatever other useful things you want to add
Finally
'used to execute code whether or not there is an exception
End Try

The other thing that I would have to guess is the way you are adding the parameter and setting the value at the same time. It is possible that it is working but I would separate it just to make the code more readable using the SQLParameter Object.

Like so:

        'The value of your parameter will be stored on this object
        Dim yourobject As New Object
        Dim p As New SqlClient.SqlParameter("@param2", yourobject)
        'using your table adapter
        With youradapater
            .SelectCommand.Parameters.Add(p)
            .Fill(yourdataset)
        End With

 

[cjard]

This could be a number of things. But the first thing I have to ask, and this may sound stupid, but I am assuming you cut and pasted the code.

SqlDataAdapter3.SelectCommand.Parameters.Add("@Par am2", SqlDbType.Float).Value = studyid

That's the forum software inserting a space, because he didnt use code tags..
I put no spaces in the following string:

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

Also you should try using trys to help display more information about the error:

Try
'you code
Catch Ex As SqlClient.SqlException
  Msgbox(ex.message & vbcrlf & ex.stacktrace) 'and whatever other useful things you want to add
Finally
'used to execute code whether or not there is an exception
End Try

Good call.. I'd use the exception helper in debug mode though, and use "Copy Exception Detail" feature

The other thing that I would have to guess is the way you are adding the parameter and setting the value at the same time. It is possible that it is working but I would separate it just to make the code more readable using the SQLParameter Object.

Doesnt normally cause any problems...