webservice-转换odbc访问连接到SQL Server(webservice- converting odbc access connections to sql server)

 我正在尝试将我的软件从odbc连接更新到我的新sql server。 新的sql连接默认为旧数据库（参见下面的代码）。 我甚至试图保持sql的odbc连接并指向我的新数据库，但webservice返回一个布尔值，而不是soap。 sql server是为较新的webservice设置和工作的。 我错过了什么？ 
 
 这是旧代码： 
 
<WebMethod()> Public Function LookupItem(ByVal strUPC As String) As DataSet
    Try
        Dim connUPC As New Odbc.OdbcConnection
        Dim ConnectionString As String
        Dim strSQL As String
        '//get upc info            
        strSQL = "SELECT Inv_Qty.*,PLU.*,PLU.PLU_NUM, PLU.DEPT_NUM From PLU INNER JOIN Inv_Qty ON PLU.PLU_NUM=Inv_Qty.PLU_NUM WHERE ((PLU.PLU_NUM) = '" & strUPC & "');"
        ConnectionString = "DSN=mainODBC"
        connUPC.ConnectionString = ConnectionString
        Dim cmdUPC As New Odbc.OdbcCommand(strSQL, connUPC)
        Dim dsUPC As DataSet = New DataSet
        Dim daUPC As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(cmdUPC)
        connUPC.Open()
        daUPC.Fill(dsUPC, "TC")
        connUPC.Close()
        connUPC = Nothing
        Return dsUPC
    Catch ex As Exception

    End Try
End Function
 
 我试图改变连接： 
 
<WebMethod()> Public Function LookupItem(ByVal strUPC As String) As DataSet
    Try
        Dim connUPC As New Data.SqlClient.SqlConnection
        Dim ConnectionString As String
        Dim strSQL As String
         '//get upc info            
        strSQL = "SELECT Inv_Qty.*,PLU.*,PLU.PLU_NUM, PLU.DEPT_NUM From PLU INNER JOIN Inv_Qty ON PLU.PLU_NUM=Inv_Qty.PLU_NUM WHERE ((PLU.PLU_NUM) = '" & strUPC & "');"
         ConnectionString = "Data Source=localhost\db;Initial Catalog=DB_SQL;Persist Security Info=True;User ID=myID;Password=myPass"
        connUPC.ConnectionString = ConnectionString
         Dim cmdUPC As New Data.SqlClient.SqlCommand(strSQL, connUPC)
        Dim dsUPC As DataSet = New DataSet
        Dim daUPC As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter(cmdUPC)
         connUPC.Open()
        daUPC.Fill(dsUPC, "TC")
        connUPC.Close()
        connUPC = Nothing
        Return dsUPC
    Catch ex As Exception

    End Try
End Function

I'm trying to update my software from odbc connections to my new sql server. The new sql connections are defaulting to the old database (see below code). I've even tried to keep the odbc connections for sql and point to my new db, but the webservice is returning a boolean, not the soap. The sql server is setup for and working on a newer webservice. What am I missing?
 
Here is the old code:
 
<WebMethod()> Public Function LookupItem(ByVal strUPC As String) As DataSet
    Try
        Dim connUPC As New Odbc.OdbcConnection
        Dim ConnectionString As String
        Dim strSQL As String
        '//get upc info            
        strSQL = "SELECT Inv_Qty.*,PLU.*,PLU.PLU_NUM, PLU.DEPT_NUM From PLU INNER JOIN Inv_Qty ON PLU.PLU_NUM=Inv_Qty.PLU_NUM WHERE ((PLU.PLU_NUM) = '" & strUPC & "');"
        ConnectionString = "DSN=mainODBC"
        connUPC.ConnectionString = ConnectionString
        Dim cmdUPC As New Odbc.OdbcCommand(strSQL, connUPC)
        Dim dsUPC As DataSet = New DataSet
        Dim daUPC As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(cmdUPC)
        connUPC.Open()
        daUPC.Fill(dsUPC, "TC")
        connUPC.Close()
        connUPC = Nothing
        Return dsUPC
    Catch ex As Exception

    End Try
End Function
 
I've tried to just change the connections:
 
<WebMethod()> Public Function LookupItem(ByVal strUPC As String) As DataSet
    Try
        Dim connUPC As New Data.SqlClient.SqlConnection
        Dim ConnectionString As String
        Dim strSQL As String
         '//get upc info            
        strSQL = "SELECT Inv_Qty.*,PLU.*,PLU.PLU_NUM, PLU.DEPT_NUM From PLU INNER JOIN Inv_Qty ON PLU.PLU_NUM=Inv_Qty.PLU_NUM WHERE ((PLU.PLU_NUM) = '" & strUPC & "');"
         ConnectionString = "Data Source=localhost\db;Initial Catalog=DB_SQL;Persist Security Info=True;User ID=myID;Password=myPass"
        connUPC.ConnectionString = ConnectionString
         Dim cmdUPC As New Data.SqlClient.SqlCommand(strSQL, connUPC)
        Dim dsUPC As DataSet = New DataSet
        Dim daUPC As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter(cmdUPC)
         connUPC.Open()
        daUPC.Fill(dsUPC, "TC")
        connUPC.Close()
        connUPC = Nothing
        Return dsUPC
    Catch ex As Exception

    End Try
End Function

原文：https://stackoverflow.com/questions/15765044