Web Development

Ask something
Discussion Locked

.NET SQLConnection

0 helpful
16 replies

by pnakiet

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello friends,

I'm programming with VB.NET and SQL SERVER 2000.
I use SQLDataAdapter to connect to SQLServer in project WINDOWS APPLICATION (these objects created with wizard)---> the SQLConnection connect successfully.
But when I use the same objects to connect to SQLServer in project CLASS LIBRARY ---> the SQLConnection connect FAILED.
I use .NET Framework 1.1 in OS Windows2000 Advance Server.

Thanks,

16 replies

by awimsatt

Need more info... Can you post a code snippet? Do you get any more info besides connect failed?

by saaedb

I hope you are aware, in the project class lib, you have to go through the wizard all over again, and make the connection object again from scratch.
If you are not able to make the connection object using the wizard, here is a sample code that you can use to connect to a sql server from your .net application to the sql server.

You need to import the required NameSpace classes at the top of your code.

imports System.Data.SqlClient
imports System.Data

Then you need to Declare the objects any where in your class.

Dim objConn as SqlConnection
Dim objDA as SqlDataAdaptor

once you have the object defined, you need to initiate it and provide it the connection string.

objConn = new SqlConnection()

objConn.ConnectionString = "Data Source=YOur_Sql_ServerName_OR_IP;User ID=username;password=password;Initial Catalog=Your_Database_Name"

Now you can open the connection

objConn.Open()

this is all it takes to open a connection to a database.

Here is a sample example

///////////////////////////////////////////
Imports System.Data
Imports System.Data.SqlClient

Public Class SQL_TEST

Private objCon As SqlConnection
Dim sqlConString As String

sqlConString = "Data Source=YOur_Sql_ServerName_OR_IP;User ID=username;password=password;Initial Catalog=Your_Database_Name"

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)

'Put user code to initialize the page here
If Not Page.IsPostBack Then
objCon = New SqlConnection(sqlConString)
objCon.Open()
Console.WriteLine("Connection Made :)")
End If

End Sub

End Class

//////////////////////////////////////////////////

Hope it helps :-)

by pnakiet

This is my a specific class, please look and help me for the problem, thanks,
------------------------------------

Imports System.Data.SqlClient
Imports System.Data

Public Class clsClass1

Friend WithEvents pSQLConn As System.Data.SqlClient.SqlConnection

Friend WithEvents sdaDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
Friend WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
Friend WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
Friend WithEvents dtsDataset1 As myProjectDLL.dtsDataset1

Private Sub InitializeComponent()
Me.sdaDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
Me.dtsDataset1 = New myProjectDLL.dtsDataset1
'
'sdaDataAdapter1
'
Me.sdaDataAdapter1.DeleteCommand = Me.SqlDeleteCommand1
Me.sdaDataAdapter1.InsertCommand = Me.SqlInsertCommand1
Me.sdaDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.sdaDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "dbo_sp_storeprocedureGet", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("msChucvu", "msChucvu"), New System.Data.Common.DataColumnMapping("tenChucvu", "tenChucvu")})})
Me.sdaDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "dbo.[sp_storeprocedureGet]"
Me.SqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand1.Connection = Me.pSQLConn
Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@parameter1", System.Data.SqlDbType.VarChar, 12))
'
'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "dbo.[spu_StoreProcedureAdd]"
Me.SqlInsertCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlInsertCommand1.Connection = Me.pSQLConn
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@parameter2", System.Data.SqlDbType.VarChar, 30, "field2"))
'
'SqlUpdateCommand1
'
Me.SqlUpdateCommand1.CommandText = "dbo.[spu_StoreProcedureEdit]"
Me.SqlUpdateCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlUpdateCommand1.Connection = Me.pSQLConn
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@parameter1", System.Data.SqlDbType.VarChar, 12, "field1"))
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@parameter2", System.Data.SqlDbType.VarChar, 30, "field2"))
'
'SqlDeleteCommand1
'
Me.SqlDeleteCommand1.CommandText = "dbo.[spu_StoreProcedureDel]"
Me.SqlDeleteCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlDeleteCommand1.Connection = Me.pSQLConn
Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@parameter1", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "field1", System.Data.DataRowVersion.Original, Nothing))

End Sub

Public Sub New(Optional ByVal SQLConn As System.Data.SqlClient.SqlConnection = Nothing)

InitializeComponent()
If Not (SQLConn Is Nothing) Then
Me.pSQLConn = New System.Data.SqlClient.SqlConnection
Me.pSQLConn = SQLConn
End If
End Sub

Public Property SQLConn() As System.Data.SqlClient.SqlConnection
Get
Me.pSQLConn = Nothing
Me.pSQLConn = New System.Data.SqlClient.SqlConnection
SQLConn = Me.pSQLConn

End Get
Set(ByVal Value As System.Data.SqlClient.SqlConnection)
Me.pSQLConn = Value
End Set
End Property

Public Function getData(ByVal strParameter1 As String _
, ByRef strLoi As String) As System.Data.DataTable
Try
SqlSelectCommand1.Parameters("@parameter1").Value = strParameter1

'--------------- Test Only, removed after test
Dim a As String
a = pSQLConn.ConnectionString
'--------------- End Test Only, removed after test

sdaDataAdapter1.Fill(dtsDataset1.dbo_sp_storeprocedureGet)
getData = dtsDataset1.dbo_sp_storeprocedureGet
Catch dtException As System.Data.DataException
strLoi = dtException.ToString
End Try
End Function

Public Function updateData(ByVal setData As System.Data.DataTable, ByRef strLoi As String) As String
Dim lDataTable As System.Data.DataTable
Try
lDataTable = setData
updateData = sdaDataAdapter1.Update(lDataTable).ToString
Catch dtException As System.Data.DataException
strLoi = dtException.ToString
End Try
End Function

Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub

End Class
---------------------------------------------

The pSQLConn is sent from .EXE project to .DLL project by setting "NEW THE CLASS" or calling "SQLConn" procedure.
pSQLConn is open from .EXE before sending to .DLL. pSQLConn. ConnectionString is following code (in EXE):

pSQLConn = New System.Data.SqlClient.SqlConnection
pSQLConn.ConnectionString = "password=PASSWORD1;packet size=4096;user id=USER1;data source=""SRV1\SRV1" & _
";persist security info=False;initial catalog=DATABASE1"
pSQLConn.Open()

by pnakiet

This is the error message show me when I try to open datatable:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Fill: SelectCommand.Connection property has not been initialized.

by saaedb

In the end of the code where you are doing
pSQLCon.Open().

Since you are getting the error on this line, we need to know exactly what is the issue.

put that in a try catch block, catch the exception and print the stack out on the page, and traverse it and get to the cause of the error. here is how you can do that

try

pSQLCon.Open()

catch e as exception

console.writeline(e.stacktrace)
console.writeline(e.source)
console.writeline(e.message)

end try

let me know what you get, and the methods of the Exception class that i used there might be a bit off, so make sure they are spelled out right.

by pnakiet

This is the error message show me when I try to open datatable.
The error doesn't cause where I open the connection (in .EXE project) but this cause when I call "FILL" method in "Public Function getData(ByVal strParameter1 As String _
, ByRef strLoi As String) As System.Data.DataTable" in the code followed up.

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Fill: SelectCommand.Connection property has not been initialized.

by saaedb

The error that you are showing me according to that, there is some issue here
Me.SqlSelectCommand1.CommandText = "dbo.[sp_storeprocedureGet]"

do you have any stored procedure inthere with that name ? if not, then get rid of this and put your sql command thre like

Me.SqlSelectCommand1.CommandText = "Select * From Your_Table_Name"

by pnakiet

Hello friend,
I had already gotten the procedure to execute and I already tested this in Query Analyzer. It run OK.
And I copied this code back to Form (in .EXE project) that I use Wizard to create it, it also run OK.
Have any ways to solve this problem? Did you have any project with .DLL that run OK? Please tell me how you create this!

by saaedb

I am not following you, you started of with the error that you are not able to open the connection to the database, then you said you are having issues with the Command object.

And now you are having some problems with the dll ? what is that suppose to mean ?.

Secondly, all the wizard generated code looks ugly :) i am very much a do it your self all the way type of programmer, so if you clean out all the extra code from your paste, ans just show me what i need to look at, and define your problem to the point, it would help me alot more to better understand and daignose your problem.

by rrv

check able-consulting.com and find connection string for SQL server. This site has sample code for connecting to any database available using VB and VB.NET [both versions]

Good luck
RV

by pnakiet

Hello RVLion,
You have been right to tell my problem is not from .DLL but my Connection.

In my Server Explorer, my .NET program can connect to Server SQL Server 2000, but my connection created in code can not connect to Server.

following you, What should I do to solve this problem?

PNAKiet

by rrv

Hi., If your normal code works and the code in class did not work - i can see only one problem with the scope of your connection object. In any version of VB when you convert normal module or form code into class code it will become a private member. So in the properties of your class change it to public.

Good Luck
RV

by pnakiet

Hello RVLion
I think my Class scope is not the problem because the Connection from my .EXE project sent to .DLL project as parameter of "NEW" object procedure, not related to scope.
I see that my Connect could not connect even in .EXE project either.

These projects created in VB.NET Architect 2002, it can connect in .EXE OK (Server SQLServer 7.0), but since I converted them to VB.NET Architect 2003, it can not connect to Server (SQLServer 2000). But in Server Explorer, the connection connect successfully.

PNAKiet

This discussion is closed to new replies.