asked on

accessing database multiple times within the same procedure

first off, there's no problem with the code below ... it works fine.

i normally program using .net with sql server, but have just taken on a small .net access project.

my question is, is it possible to access the database multiple times within the same procedure ... i mean without having to create multiple variables for the same thing (see below).

sub InsertProjectImage(byVal intProjectID as integer)


            dim intExtensionID as integer

            Dim strSQL1 as string = "SELECT fldExtensionID " _
            & "FROM tblExtension WHERE fldExtensionName='" & strImageExtension & "'"


            dim strConnection As Oledb.OledbConnection = New Oledb.OledbConnection(ConfigurationSettings.Appsettings("vidalityDB"))
            Dim myCommand As OleDbCommand = New OleDbCommand(strSQL1, strConnection)

            strConnection.Open()

            dim myDataReader as OleDbDataReader = myCommand.ExecuteReader()

            While myDataReader.Read()
                  intExtensionID = myDataReader("fldExtensionID")
            end while


            myDataReader.Close()
            strConnection.Close()








            dim strImageName as string = trim(txtImageName.text)
            dim strImageDescription as string = trim(txtImageDescription.text)


            Dim strSQL2 As String = "INSERT INTO tblImage " _
            & "(fldImageExtensionID, fldImageName, fldImageDescription)" _
            & "VALUES (" & intExtensionID & ", '" & strImageName & "', '" & strImageDescription & "')"


            dim strConnection2 As Oledb.OledbConnection = New Oledb.OledbConnection(ConfigurationSettings.Appsettings("vidalityDB"))
            Dim myCommand2 As OleDbCommand = New OleDbCommand(strSQL2, strConnection2)

            strConnection2.Open()
            myCommand2.ExecuteNonQuery()

            'retrieve the last imageID
            myCommand2.CommandText = "SELECT @@IDENTITY"
            dim intImageID as integer = myCommand2.ExecuteScalar()

            strConnection2.Close()








            Dim strSQL3 As String = "INSERT INTO tblProjectImageLink " _
            & "(fldProjectID, fldImageID)" _
            & "VALUES (" & intProjectID & ", " & intImageID & ")"


            dim strConnection3 As Oledb.OledbConnection = New Oledb.OledbConnection(ConfigurationSettings.Appsettings("vidalityDB"))
            Dim myCommand3 As OleDbCommand = New OleDbCommand(strSQL3, strConnection3)

            strConnection3.Open()
            myCommand3.ExecuteNonQuery()
            strConnection3.Close()



end sub

Type25

sub InsertProjectImage(byVal intProjectID as integer)

dim intExtensionID as integer

Dim strSQL1 as string = "SELECT fldExtensionID " _
& "FROM tblExtension WHERE fldExtensionName='" & strImageExtension & "'"

dim strConnection As Oledb.OledbConnection = New Oledb.OledbConnection(ConfigurationSettings.Appsettings("vidalityDB"))
Dim myCommand As OleDbCommand = New OleDbCommand(strSQL1, strConnection)

strConnection.Open()

dim myDataReader as OleDbDataReader = myCommand.ExecuteReader()

While myDataReader.Read()
intExtensionID = myDataReader("fldExtensionID")
end while

myDataReader.Close()

dim strImageName as string = trim(txtImageName.text)
dim strImageDescription as string = trim(txtImageDescription.text)

Dim strSQL2 As String = "INSERT INTO tblImage " _
& "(fldImageExtensionID, fldImageName, fldImageDescription)" _
& "VALUES (" & intExtensionID & ", '" & strImageName & "', '" & strImageDescription & "')"

myCommand2 As OleDbCommand = New OleDbCommand(strSQL2, strConnection1)

myCommand2.ExecuteNonQuery()

'retrieve the last imageID
myCommand2.CommandText = "SELECT @@IDENTITY"
dim intImageID as integer = myCommand2.ExecuteScalar()

strConnection2.Close()

Dim strSQL3 As String = "INSERT INTO tblProjectImageLink " _
& "(fldProjectID, fldImageID)" _
& "VALUES (" & intProjectID & ", " & intImageID & ")"

myCommand3 As OleDbCommand = New OleDbCommand(strSQL3, strConnection1)

strConnection3.Open()
myCommand3.ExecuteNonQuery()
strConnection1.Close()

end sub

Type25

That might be slightly wrong - kinda rushed that.

But basically, you can use the same connection object for each command object.

Also you don't need to create three command objects, just keep reinitalize the first one.

ASKER CERTIFIED SOLUTION

Type25

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbnewbie01

ASKER

thanks, type.