• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

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
0
vbnewbie01
Asked:
vbnewbie01
  • 3
1 Solution
 
Type25Commented:
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
0
 
Type25Commented:
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.
0
 
Type25Commented:
Actually, this is a lot better:


sub InsertProjectImage(byVal intProjectID as integer)
     

          dim intExtensionID as integer
         
          Dim strSQL 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(strSQL, 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)
         
         
          strSQL = "INSERT INTO tblImage " _
          & "(fldImageExtensionID, fldImageName, fldImageDescription)" _
          & "VALUES (" & intExtensionID & ", '" & strImageName & "', '" & strImageDescription & "')"
     
         
         mycommand = New OleDbCommand(strSQL2, strConnection)
         
          myCommand.ExecuteNonQuery()
         
          'retrieve the last imageID
          myCommand.CommandText = "SELECT @@IDENTITY"
          dim intImageID as integer = myCommand.ExecuteScalar()
         


          strSQL = "INSERT INTO tblProjectImageLink " _
          & "(fldProjectID, fldImageID)" _
          & "VALUES (" & intProjectID & ", " & intImageID & ")"
     
       
          mycommand = New OleDbCommand(strSQL3, strConnection)
         

          myCommand.ExecuteNonQuery()
          strConnection.Close()
         
         



end sub
0
 
vbnewbie01Author Commented:
thanks, type.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now