Question Sql connection problem

[Haseo]

Hi i'm new in VB2008 and i have a problem about SQL connection..

this is my module code
Imports System.Data.SqlClient


Module Module1


Public sqlcnn As New SqlConnection
Public sqlcom As New SqlCommand
Public sqldr As SqlDataAdapter
Public strsql As String
Public sqlda As New SqlDataAdapter


Sub connect()
If sqlcnn.State = ConnectionState.Open Then
sqlcnn.Close()
sqlcnn.ConnectionString = "Data Source=.\sqlexpress; integrated security = true; attachdbfilename,=|datadirectory|\SQLSAMPLE.mdf"
sqlcnn.Open()
End If


End Sub


End Module


MY FORM CODE

Public Class Form1


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Module1.connect()
End Sub



Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
strsql = "insert into sample (fname, lname, address) values ('" _
& txtfname.Text & "','" _
& txtlname.Text & "','" _
& txtadd.Text & "')"
Dim sqlcom As New SqlClient.SqlCommand
sqlcom.CommandText = strsql
sqlcom.Connection = sqlcnn
sqlcom.ExecuteNonQuery() <<<<< this is my problem "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed."}
MsgBox("SAVE")


End Sub
End Class




Thanks.......

 

[jmcilhinney]

DO NOT create your connection object separately to your command object. You should be create your connection object where you create your command. There is no benefit to creating a single connection object in a module. ADO.NET is designed specifically to NOT be used like that. Create the connection and the command together, open the connection, execute the command, close the connection. That is the proper way to use ADO.NET.

 

[Haseo]

OK i will try it... thanks

 

[Haseo]

DO NOT create your connection object separately to your command object. You should be create your connection object where you create your command. There is no benefit to creating a single connection object in a module. ADO.NET is designed specifically to NOT be used like that. Create the connection and the command together, open the connection, execute the command, close the connection. That is the proper way to use ADO.NET.

MR. Moderator

can you give me sample code for sql connection????

 

[jmcilhinney]

You've already got example code. You posted it yourself. You simply need to create the connection immediately before you create the command. Once the command has been executed and the connection closed, you simply discard both.

If you need further examples for some reason then there will be plenty on the web already. There's no real need for me to spend time duplicating what's already out there. If you've looked at other examples and are still having issues then please explain what those issues are because it would be a waste of time for me to simply tell you what you already know.

 

[Herman]

Here's the simplest example:

' Executes a given query over a given connection string, and returns the number of rows affected.
Private Function ExecuteNonQuery(ByVal strConnectionString As String, ByVal strQuery As String) As Integer
    Using SqlConn As New SqlConnection(strConnectionString)
        Using SqlComm As New SqlCommand(strQuery, SqlConn)
            SqlComm.Connection.Open()
            ExecuteNonQuery = SqlComm.ExecuteNonQuery()
        End Using
    End Using
End Function