none
[Microsoft] [ODBC Driver Manager] Data source name not found and no default driver specified. RRS feed

  • Question

  • I am developing a VB.NET 2008 software package that, up until this point, has worked fine. Then, for no reason it just started throwing this message when I started the debugger.

    If I compile it and drop the files over to a folder on my desktop the program works fine, (this makes the debugging process slow). BUT if I drop the files in ANY other folder I get this same error.

    I have checked my file permissions, compiled both x86 and x64 versions checked my connection string uninstalled and re-installed VS2008 and SQL Server 2008 Express and even upgraded to VS2010.

    I have turned Windows Updates off, so an update is not the culprit.

    Any help would be very appreciated. Thanks.

    I am using:

    Windows 7 x64
    SQL Server 2008 Express R2
    VS2008 Professional
    ADODB For My Connection


    • Moved by edhickey Thursday, March 31, 2011 3:04 PM (From:.NET 3.0/3.5 Windows Workflow Foundation)
    Thursday, March 31, 2011 1:51 PM

Answers

  •   Public Function adoQuery(ByVal sqlQuery As String) As DataSet
        Dim SQLConnection As New System.Data.SqlClient.SqlConnection(connectionString)
    
        Try
          SQLConnection.Open()
    
          Dim SQLString As String = sqlQuery
          Dim SQLCommand As New SqlClient.SqlCommand(SQLString, SQLConnection)
          Dim SQLAdapter As New SqlDataAdapter
    
          SQLAdapter.SelectCommand = SQLCommand
    
          Dim SQLDataset As New DataSet
    
          SQLAdapter.Fill(SQLDataset)
    
          SQLConnection.Close()
          SQLConnection.Dispose()
    
          adoQuery = SQLDataset
        Catch ex As Exception
          MessageBox.Show(ex.Message)
        End Try
      End Function
    

    This will return a dataset that you can loop through, no need to specify the table.

    To Use:

    Dim rs as DataSet
    
    rs = adoQuery("select * from tblBlah")
    
    For i = 0 to rs.Tables(0).Rows.Count - 1
       blah = rs.Tables(0).Rows(i).Item("blahItem").ToString()
    Next
    

    • Marked as answer by Runtest Friday, April 15, 2011 3:02 PM
    Friday, April 15, 2011 3:02 PM

All replies

  • Are you using an ODBC DSN to connect? I can see where switching the Platform option from x86 to AnyCPU or x64 could cause this problem.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, March 31, 2011 7:36 PM
  • You see, I am not sure. Here is my connection string.

    Provider=SQLOLEDB;Data Source=GS-SERVER\SQLEXPRESS;Network Library=DBMSSOCN;Initial Catalog=dbGrimmIS;User Id=sa;Password=password

    Thursday, March 31, 2011 8:37 PM
  • That's the old SQL Server OLEDB Provider for SQL Server 7 or 2000. With .NET the native SQL Server library (System.Data.SqlClient) is recommended, however; you're still using Classic ADO (ADODB) and should at least use the latest OLEDB Provider for 2008. Below is a connection string example:

    Provider=SQLNCLI10;Server=myServerName\theInstanceName;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, April 1, 2011 12:06 AM
  • Hey, thanks for the help but I am still getting the same error.

    [Microsoft] [ODBC Driver Manager] Data source name not found and no default driver specified.

    The weird thing is, I can take all the files in the debug folder and move them to the folder on the desktop and the software works. This is making debugging quite slow as you can imagine.

    I have tried the steps of uninstalling all instances of the SQL Native Connection and removing SQL server. I have also uninstalled Visual Studio and installed a new version. I am running Visual Studio as administrator.



    Oh, on a side note I did include a popup to feed me back my connection string, it is being set and read.
    Friday, April 1, 2011 12:42 PM
  • Can you post your code and identify the line where the exception occurs?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, April 1, 2011 3:56 PM
  • It is actually the second it tries to perform a query.

    Dim rt as ADODB.Recordset

    rt = query("select * from tblTable")

    The query is just a custom recordset return function.

     

    Still getting the error, but just in visual studio while trying to debug. If I compile it and run it, it works.

    Friday, April 1, 2011 4:56 PM
  • Well if it works the first time then it sounds like the ADO connection is not being properly closed and other objects closed and/or destroyed. Can't see code so I'm unable to tell for sure.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, April 1, 2011 6:39 PM
  • I will reply with as much as I can, it is copyrighted and I can only give a relevant snippet. Thank you for all the help. Will reply in a bit.
    Friday, April 1, 2011 7:16 PM
  •         rs = query("select * from tblSettings where uniqueid = 1")

            panelColor = Color.FromArgb(checknull(rs("panelColors").Value))

    Fails there.

        Function query(ByVal strsql As String, Optional ByVal ignore As String = "", Optional ByVal conn As String = "") As ADODB.Recordset
            On Error GoTo Error2
            Dim rs As ADODB.Recordset
            Dim objConnection As ADODB.Connection
            rs = New ADODB.Recordset
            objConnection = New ADODB.Connection
            If conn = "" Then
                objConnection.ConnectionString = ConnStr
            Else
                objConnection.ConnectionString = conn
            End If
            objConnection.Open()
            rs.Open(strsql, objConnection, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
            query = rs
            Exit Function
    Error2:
            If ignore <> "" Then
                query = query(ignore)
                Exit Function
            End If
            MsgBox(Err.Description, , "Information System")
        End Function

    Friday, April 8, 2011 1:47 PM
  • Can't tell from the code where you are closing your connection, but I would definitely recommend closing the connection when your done accessing the database and only opening it when you need to connect to the database again. In other words, don't persist your connections.

    Also, at some point you will want to convert your ADO code to ADO.NET and use the native .NET System.Data.SqlClient library instead.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, April 8, 2011 2:36 PM
  • I agree 100%, but for the moment the entire project (around 100k lines) is ADO. Once I finish the project, I will begin to convert it over. For the time being however I am still getting error. I also see a host of other people having the same issue with no resolve.

    If I have to convert over to SqlClient I will but I need some guidance on that. I just can not understand, with no changes to the source or any type of updates, why this will now not run on any machine. Every single computer gives the same error.

    I use the ADODB library because I do not have to configure each machine individually, I can just drop a generic connection string. I also do not want to have to create a connection for each and every query, with this I can just.

    Dim something as ADODB.Recordset

    something = query("query string")

     

    How would I accomplish the same feet with System.Data.SqlClient?

    Friday, April 8, 2011 2:50 PM
  • That's why I used the phrase "at some point", meaning "in the future", once you have learned ADO.NET.

    So did you change your code so that the connection is closed after the query and then re-opened the next time you query the database?


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, April 8, 2011 3:05 PM
  •  Yeah it still bombed with the same error.
    Friday, April 8, 2011 4:35 PM
  • Yeah it still bombed with the same error. How about this? What would my connection string look like for an sqlexpress server? Again, thanks for the help!

     

        Public Function adoQuery(ByVal qString As String) As SqlDataReader
            Dim connectionString As String = ConnStr
            Dim queryString As String = qString

            Using connection As New SqlConnection(connectionString)
                Dim command As SqlCommand = connection.CreateCommand()
                command.CommandText = queryString
                connection.Open()
                Dim dataReader As SqlDataReader = command.ExecuteReader()

                adoQuery = dataReader

                'If I close it can I still access it?
                'dataReader.Close()
            End Using


    End Function
    Friday, April 8, 2011 4:37 PM
  • That code won't quite work because you need to have an open connection for the DataReader and you can't assign it directly to a Recordset. The below example uses Classic ADO, SQL OLEDB and a disconnected Recordset. I will post an equivalent example that uses ADO and the SQL Server library.

        Dim ConnectionString As String
        ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=Northwind;Trusted_Connection=yes"
    
        Dim ADORecordset As New ADODB.Recordset
        Dim ADOConnection As New ADODB.Connection
    
        Try
    
          ADOConnection.CursorLocation = ADODB.CursorLocationEnum.adUseClient
          ADOConnection.Open(ConnectionString)
          ADORecordset.Open("SELECT * FROM Customers", ADOConnection, ADODB.CursorTypeEnum.adOpenStatic)
          ADORecordset.ActiveConnection = Nothing
          ADOConnection.Close()
    
        Catch ex As Exception
          MessageBox.Show(ex.Message)
        End Try
    
        '... do your stuff with Recordset
        '...
    
        ADORecordset.Close()
        ADORecordset = Nothing
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Alan_chenModerator Tuesday, April 12, 2011 7:19 AM
    • Unmarked as answer by Runtest Friday, April 15, 2011 12:50 PM
    Friday, April 8, 2011 6:39 PM
  • The below example uses ADO.NET and the native System.Data.SqlClient library with a DataSet/DataTable:

        Dim ConnectionString As String
        ConnectionString = "Data Source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Northwind;"
        Dim SQLConnection As New System.Data.SqlClient.SqlConnection(ConnectionString)
    
        Try
    
          SQLConnection.Open()
    
          Dim SQLString As String = "SELECT * FROM Customers"
          Dim SQLCommand As New SqlClient.SqlCommand(SQLString, SQLConnection)
          Dim SQLAdapter As New SqlDataAdapter
          SQLAdapter.SelectCommand = SQLCommand
          Dim SQLDataset As New DataSet
          SQLAdapter.Fill(SQLDataset, "Customers")
          Dim SQLDataTable As DataTable = SQLDataset.Tables("Customers")
          SQLConnection.Close()
          SQLConnection.Dispose()
    
          '... do your stuff with DataTable
          '...
    
          SQLDataset.Dispose()
          SQLDataTable.Dispose()
    
        Catch ex As Exception
          MessageBox.Show(ex.Message)
        End Try
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, April 8, 2011 6:58 PM
  • Alright, I setup a query function like this.

      Public Function adoQuery(ByVal sqlQuery As String, ByVal table As String, Optional ByVal ignore As String = "") As DataSet
        Dim SQLConnection As New System.Data.SqlClient.SqlConnection(connectionString)
    
        Try
          SQLConnection.Open()
    
          Dim SQLString As String = sqlQuery
          Dim SQLCommand As New SqlClient.SqlCommand(SQLString, SQLConnection)
          Dim SQLAdapter As New SqlDataAdapter
          SQLAdapter.SelectCommand = SQLCommand
          Dim SQLDataset As New DataSet
          SQLAdapter.Fill(SQLDataset, table)
          Dim SQLDataTable As DataTable = SQLDataset.Tables(table)
          SQLConnection.Close()
          SQLConnection.Dispose()
    
          adoQuery = SQLDataset
    
        Catch ex As Exception
          MessageBox.Show(ex.Message)
        End Try
      End Function
    

    And did this.

        Dim rs As New DataSet
        If TextBox1.Text.Trim = "" Then
          rs = adoQuery("select * from tblCustomers", "tblCustomers")
        Else
          rs = adoQuery("select top " & Val(TextBox1.Text) & " * from tblCustomers", "tblCustomers")
        End If
    
    
        ListView1.Items.Clear()
    
        For i = 0 To rs.Tables("tblCustomers").Rows.Count - 1
          ListView1.Items.Add(rs.Tables("tblCustomers").Rows(i).Item("uniqueid").ToString())
          ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(rs.Tables("tblCustomers").Rows(i).Item("lname").ToString())
          ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(rs.Tables("tblCustomers").Rows(i).Item("fname").ToString())
        Next
    

    But how do I get away from having to specify the table, what if I used a combined query?

    This still does not answer what the problem is with the ado but I have resigned to figuring this out and posing a function for everyone one else experiencing the same problem to use to maybe make migration a little easier on them.

    Friday, April 15, 2011 12:52 PM
  •   Public Function adoQuery(ByVal sqlQuery As String) As DataSet
        Dim SQLConnection As New System.Data.SqlClient.SqlConnection(connectionString)
    
        Try
          SQLConnection.Open()
    
          Dim SQLString As String = sqlQuery
          Dim SQLCommand As New SqlClient.SqlCommand(SQLString, SQLConnection)
          Dim SQLAdapter As New SqlDataAdapter
    
          SQLAdapter.SelectCommand = SQLCommand
    
          Dim SQLDataset As New DataSet
    
          SQLAdapter.Fill(SQLDataset)
    
          SQLConnection.Close()
          SQLConnection.Dispose()
    
          adoQuery = SQLDataset
        Catch ex As Exception
          MessageBox.Show(ex.Message)
        End Try
      End Function
    

    This will return a dataset that you can loop through, no need to specify the table.

    To Use:

    Dim rs as DataSet
    
    rs = adoQuery("select * from tblBlah")
    
    For i = 0 to rs.Tables(0).Rows.Count - 1
       blah = rs.Tables(0).Rows(i).Item("blahItem").ToString()
    Next
    

    • Marked as answer by Runtest Friday, April 15, 2011 3:02 PM
    Friday, April 15, 2011 3:02 PM
  • Thanks for all the help man. You got me going in the right direction, though I would still love a solution to the other issue as I am sure that not everyone is in a position to rewrite their software.
    Friday, April 15, 2011 3:03 PM
  • Not sure what to tell you at this point. The connection string posted in my ADO example (originally marked as answer) works fine for me so I would suspect there is something in your connection string that is incorrect or it's a platform issue. If you are using a local version of SQL Express then it should be identical, except for the database name.

    You could also try switching the Platform option to x86 (Build...Configuration Manager) and force the app to run 32-bit to see if it makes any difference.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, April 15, 2011 4:26 PM
  • Yeah I tried switching between platforms, no luck. Something interesting happened though, I converted the first couple of forms to ADO.NET and the rest of the applications started working. I have no clue how that happened. The best I can tell is something was not loading during the initial first parts of the application. When I ran through the debugger my ADO query function would stop half way and say, "There is no code to continue." or something like that.

    Once I converted the main form and the first one that pops up over to ADO.NET the rest of the application also started working, albeit is still ADO.

    Weird. Once again thanks for the help.


    Friday, April 15, 2011 4:37 PM
  • Maybe I could get some help with this. I am trying to update a record if it exist, add if it does not exist using the query function I posed above. It is now working.

        Dim rs As DataSet
        Dim newRS As DataRow
    
        rs = adoQuery("Select * from tblContact where uniqueid =0")
    
        If rs.Tables(0).Rows.Count = 0 Then
          newRS = rs.Tables(0).NewRow()
        Else
          newRS = rs.Tables(0).Rows(0)
        End If
    
        newRS("status") = recCTaStatus.Text
        newRS("Catagory") = recCTaCatagory.Text
        newRS("SubCatagory") = recCTaSubCatagory.Text
        newRS("Employee") = recCTaEmployee.Text
        newRS("DateTime") = recCTaDateTime.Text
        newRS("nextExpectedDate") = recCTaNextExpectedDate.Text
        newRS("premeetingnotes") = recCTaPreMeetingNotes.Text
        newRS("contactDescription") = recCTaContactDescription.Text
        newRS("CustomerID") = lblCurrentCustomer.Tag
        newRS("nextaction") = recCTaNextAction.Text & " "
        newRS("job") = recCTaJob.Text
        newRS("calanderitem") = Val(recCTaCalanderItem.Text)
        newRS("totalhrsworked") = Val(recCTaTotalHrsWorked.Text)
        newRS("BillableHrs") = Val(recCTaBillableHrs.Text)
        newRS("OpenClosed") = 0
    
        If recCTaOpenClosedè1.Checked = True Then newRS("OpenClosed") = 1
        newRS("ClosedDate") = recCTaClosedDate.Text
    
        PublicSQLDataAdapter.Update(rs)
    
    The public data adapter is just in a module and is declared as public. I did not want to specify the data adapter every single time I wanted to do this in the application.

    Friday, April 15, 2011 4:39 PM