[02/03] SQL Adapter/ Connection

[Cynoob]

Hey guys, there is something wrong with my Sql connection to my program which I really do not understand what is wrong. I followed the procedures of creating Sql Adapter/ connection from a VB.net 2003 guidebook exactly, then I added a datagrid and fill the form with the dataset. But when I try to run the program, this is what I experience:

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

Additional information: System error.

Can somebody please explain to me what could have went wrong and what can be done to solve the problem?

Please note that the original data was done in MS Access and I used the upsizing wizard to copy all the information to MS SQL Server. When I preview the dataset in my SqlDataAdapter, I have no problem viewing all the data in it

[Tinbeard]

You need to post the code where your getting the exception, otherwise it will just be guess work

[Cynoob]

' Imports Data and SqlClient namespaces...
Imports System.Data
Imports System.Data.SqlClient
 
Public Class frmStaffExp
    Inherits System.Windows.Forms.Form
    Dim SqlConnection1 As SqlConnection = New _
            SqlConnection("Server=(local);Database=pbStaff;User ID=sa;Password=sa;Trusted_Connection=True")
    Dim SqlDataAdapter1 As New SqlDataAdapter
    Dim DsSQLSTAFF1 As DataSet = New DataSet
 
    Private Sub frmStaffExp_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Try
            'Open the database connection...
            SqlConnection1.Open()
            'Now execute the command...
            SqlDataAdapter1.SelectCommand.ExecuteNonQuery()
 
            'Fill the DataSet object with data...
            SqlDataAdapter1.Fill(DsSQLSTAFF1)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            SqlConnection1.Close()
        End Try
 
    End Sub
 
 
End Class

Here is my code.. These is all that I have for the form. Now if I run, a MsgBox prompt me: "login failed for user 'sa'"

[jmcilhinney]

Firstly, you wouldn't call ExecuteNonQuery on the SelectCommand because it IS a query. Remove that line altogether as it's the Fill method that retrieves the data.

Secondly, your adapter has no query and no connection. The fact that you have an SqlDataAdapter doesn't mean that it automatically knows how and where to get data. You have to tell it that by specifying an SQL query AND the connection to use to get the data. This line:

Dim SqlDataAdapter1 As New SqlDataAdapter

should be something like this:

Dim SqlDataAdapter1 As New SqlDataAdapter("SELECT * FROM MyTable", SqlConnection1)

The SQL code tells it what data to get and the connection tells it where to get it from.

[Cynoob]

Hi JMC, I did according to what u posted, but still it has the error Login failed for user 'sa'.

I've searched several other forums and some commented that its the problem of not having a secure Sql connection, some commented not to use sa as the user name as its a bad idea