Value of type 'String' cannot be converted to 'System.Data.SqlClient.SqlConnection'

[takwirira]

Im am getting the error above Value of type 'String' cannot be converted to 'System.Data.SqlClient.SqlConnection' when I try to execute the code to populate a GridView on the website with the query shown below. Any ideas why ?

Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click

LoadData()

End Sub

Private Function LoadData() As DataView

Dim ds As DataSet = New DataSet
Dim cmdSelect As New SqlCommand
Dim da As SqlDataAdapter = New SqlDataAdapter
cmdSelect.CommandText = "Select Name from Runners WHERE Name LIKE 'T%'"

cmdSelect.Connection = [COLOR="Red"]"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\runnersdb.mdb"[/COLOR] - this bit is underlined as the problem
da.SelectCommand = cmdSelect
da.Fill(ds)

LoadData = ds.Tables(0).DefaultView

GridView1.DataSource = LoadData()
GridView1.DataBind()
End Function


End Class

 

[JohnH]

It wants a SqlConnection object, so you have to create one and give it.
cmdSelect.Connection = New SqlConnection("the connection string")

 

[takwirira]

GridView not populating results

The error below is what i am getting now when i execute the code to search for all names beginning with T to populate the grid. Where could I be going wrong

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

 

[JohnH]

Isn't "mdb" files Access files? I very much doubt the SQL ADO.Net classes will work for that at all. Try the OleDB ones.

 

[takwirira]

Oledb

mdb files are Acess files yes, any idea where I can find some sample code for these ?

the code below works fine for my vb applications but will not work in asp.net ffor the web page

Imports System.Data
Imports System.Data.OleDb
Public Class Form1

' some code here
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the ' RunnersdbDataSet.Runners' table. You can move, or remove it, as needed.
Me.RunnersTableAdapter.Fill(Me.RunnersdbDataSet.Runners)
' create a connection string
Dim connString As String = "Provider= Microsoft.Jet.OLEDB.4.0;Data Source=C:\runnersdb.mdb"
Dim myConnection As OleDbConnection = New OleDbConnection
myConnection.ConnectionString = connString
' create a data adapter
Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select Name from Runners WHERE Name LIKE 'T%'", myConnection)
' create a new dataset
Dim ds As DataSet = New DataSet
' fill dataset
da.Fill(ds, "Runners")
' write dataset contents to an xml file by calling WriteXml method
' Attach DataSet to DataGrid
DataGridView1.DataSource = ds.Tables(0).DefaultView

End Sub
End Class

 

[JohnH]

Is your ASP.Net user account allowed to access the root of c: drive?
What if you tried to put the db file into the root folder of your web application and used something like this in the conn.string:
"....Data Source=" & server.mappath("runnersdb.mdb")

 

[takwirira]

not a member of _default

I am now getting erro that says RunnersdbTableAdapter and RunnersDbDataset is not a member of _default

Imports System.Data
Imports System.Data.OleDb
Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        'TODO: This line of code loads data into the 'RunnersdbDataSet.Runners' table. You can move, or remove it, as needed.
        Me.RunnersTableAdapter.Fill(Me.RunnersdbDataSet.Runners)
        ' create a connection string
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("runnersdb.mdb")
        Dim myConnection As OleDbConnection = New OleDbConnection
        myConnection.ConnectionString = connString
        ' create a data adapter
        Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select Name from Runners WHERE Name LIKE 'T%'", myConnection)
        ' create a new dataset
        Dim ds As DataSet = New DataSet
        ' fill dataset
        da.Fill(ds, "Runners")
        ' write dataset contents to an xml file by calling WriteXml method
        ' Attach DataSet to DataGrid
        GridView1.DataSource = ds.Tables(0).DefaultView

    End Sub


End Class

 

[takwirira]

error removed by commenting the line that starts with Me.

I ended up with this code here, it does not produce any errors on run time but when i click the search button it does not do anything i.e. populate the gridview box with names starting with T

Imports System.Data
Imports System.Data.OleDb
Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click

'TODO: This line of code loads data into the 'RunnersdbDataSet.Runners' table. You can move, or remove it, as needed.
'Me.RunnersTableAdapter.Fill(Me.RunnersdbDataSet.Runners)
' create a connection string
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("runnersdb.mdb")
Dim myConnection As OleDbConnection = New OleDbConnection
myConnection.ConnectionString = connString
' create a data adapter
Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select Name from Runners WHERE Name LIKE 'T%'", myConnection)
' create a new dataset
Dim ds As DataSet = New DataSet
' fill dataset
da.Fill(ds, "Runners")
' write dataset contents to an xml file by calling WriteXml method
' Attach DataSet to DataGrid
'GridView1.DataSource = ds.Tables(0).DefaultView

End Sub

 

[JohnH]

It looks like you have commented out the code that links the display control with the datasource. When binding to ASP.Net controls in code you also have to use the DataBind method.

 

[takwirira]

Sorry for late reply. This is the new code that works perfectly, well almost. The only problem I have left is when I do a search I cant search for anything else i.e. i'd have to close down and start again. Is there a way to close the connection and refresh when I search again?

Imports System.Data
Imports System.Data.OleDb
Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click

' create a connection string
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("runnersdb.mdb")
Dim myConnection As OleDbConnection = New OleDbConnection
myConnection.ConnectionString = connString
' create a data adapter
'Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select * from Runners", myConnection)
Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select * from Runners WHERE Name LIKE '%" & txtName.Text & "%'", myConnection)

' create a new dataset
Dim ds As DataSet = New DataSet
' fill dataset
'ds.Clear()
ds.Reset()

da.Fill(ds, "Runners")
' Attach DataSet to DataGrid

GridView1.DataSourceID() = ""
GridView1.DataSource = ds.Tables(0).DefaultView
'myConnection.Close()


End Sub

End Class