sqlDataAdapter Error

[Guest]

I have the following code attached to the click event of a button. It fails
on the line "da.Fill (ds, "Results") The error is below the code. Any
suggestions would be greatly appreciated. Thanks.

Dim cmd As SqlCommand = SqlConnection1.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = txtQuery.Text
Dim da As SqlDataAdapter = New SqlDataAdapter
da.SelectCommand = cmd
Dim ds As DataSet = New DataSet
da.Fill(ds, "Results")
dgResults.DataSource = ds
dgResults.DataMember = "Results"

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

Additional information: System error.

 

[W.G. Ryan MVP]

There are a ton of things that can be wrong. First, check that your
connection string is good. YOu may want to open the connection manually
(just for testing) before you call fill to verify that your connection
string is good. So make sure that you have permissions to access the db, the
server name is right etc etc. This is a very likely culprit. Next, I'd
verify that txtQuery.Text will parse.

Also, I'd try catch the .Fill statement (and try catching Connection.Open is
a good idea in many cases since a lot can go wrong with opening a db
connection that's out of your immediate control).
(I'm coding the VB.nET without the ide so I may have a syntax error or two -
but you should get the idea)
Try
da.Fill(ds, "Results")
Catch ex as SqlException
Dim message as New StringBuilder
For Each err as SqlError in ex.Errors
message.Append(err.Message + ControlChars.CrLf)
Next
Debug.Assert(false, message.ToString)
End Try

This will loop through the errors collection and may point you in the right
direction. Let me know if this doesn't fix it.

 

[Guest]

Thanks for the tip. The Try...Catch revealed that the login was failing on
"sa". I changed the server connection to use windows user login and the
problem was resolved.