[2008] quick question

[Christopher_Arm]

My mind has blanked on using a SqlDataReader to read a field in a table( the field contains a table name) and then store that field in a string variable and
then use that string variable in an INSERT Query. Anybody ? I can't believe this went out of my head particularly when I used this x months ago

[Besoup]

you'll want to use SQLClient and .ExecuteScalar if it's only 1 value being returned.

[mendhak]

Do it all in the stored procedure if all you need is a single value from another table in the database.

[Christopher_Arm]

ok this is what I have...

Dim sqlComm As New SqlCommand("SELECT Category,Type,TableLocation from dbo.DocumentSettings", objConn)
        Dim dr As SqlDataReader = sqlComm.ExecuteReader()

        While dr.Read()
            Dim CategoryString As String = CStr(dr("Category"))
            Dim Typestring As String = CStr(dr("Type"))
            Dim TableString As String = CStr(dr("TableLocation"))
        End While

        objCommand.Connection = objConn
        objCommand.CommandText = "INSERT INTO" & "'" & TableString & "'"
     & "(Image,FileName, Category, Type) " & _
        "VALUES(@Image,@Path,@Category,@Type)"

The datareader does work however....
what is wrong with this as I want to pick the tablestring value so whatever table is reflected in the variable gets picked up ?

[riteshjain1982]

did you open connection ?

[Christopher_Arm]

The idea is to make it so that the INSERT picks up the values any table that is listed as a fieldname from the table that is being read. And of course I am not using stored procedures on this one unfortunately. In this case it does do to use execute scalar because I am picking up three values category, type, and table location.

[Christopher_Arm]

did you open connection ?

the connection is open. Yes from previous code although this isn't reflected here..sorry about that.

[riteshjain1982]

few queries...
1>are you able to read data from Reader?
2>no need of single quote in objCommand.CommandText = "INSERT INTO" & TableString

3>Declare variable outside While

Dim CategoryString As String 
Dim Typestring As String
Dim TableString As String
 While dr.Read()
             CategoryString   = CStr(dr("Category"))
            Typestring   = CStr(dr("Type"))
            TableString  = CStr(dr("TableLocation"))
        End While

or Insert INSERT Statement in while loop.

[Christopher_Arm]

few queries...
1>are you able to read data from Reader?
2>no need of single quote in objCommand.CommandText = "INSERT INTO" & TableString

3>Declare variable outside While

Dim CategoryString As String 
Dim Typestring As String
Dim TableString As String
 While dr.Read()
             CategoryString   = CStr(dr("Category"))
            Typestring   = CStr(dr("Type"))
            TableString  = CStr(dr("TableLocation"))
        End While

or Insert INSERT Statement in while loop.

It picks up the values in the datareader no problem but now I get the following error after it steps into my INSERT QUERY below that.invaild operation exception was handled.
There is already an open DataReader associated with this Command which must be closed first.

        Dim sqlComm As New SqlCommand("SELECT Category,Type,TableLocation from dbo.DocumentSettings", objConn)
        Dim dr As SqlDataReader = sqlComm.ExecuteReader()

        dr.Read()
        Dim CategoryString As String = CStr(dr("Category"))
        Dim Typestring As String = CStr(dr("Type"))
        Dim TableString As String = CStr(dr("TableLocation"))


        objCommand.Connection = objConn
        objCommand.CommandText = "INSERT INTO" & "'" & TableString & "'" & _
      "(Image,FileName, Category, Type) " & _
        "VALUES(@Image,@Path,@Category,@Type)"

        For a = 0 To imageList.Count - 1
            For b = 0 To fileCategoryList.Count - 1
                For c = 0 To categoryList.Count - 1
                    For d = 0 To typeList.Count - 1
                        objCommand.Parameters.Clear()
                        objCommand.Parameters.AddWithValue("@Image", imageList.Item(a))
                        objCommand.Parameters.AddWithValue("@Path", fileCategoryList.Item(b).ToString)
                        objCommand.Parameters.AddWithValue("@Category", categoryList.Item(c).ToString)
                        objCommand.Parameters.AddWithValue("@Type", typeList.Item(d).ToString)

                        objCommand.ExecuteNonQuery()
                    Next
                Next
            Next
        Next

now the question becomes where to put the dr.close that won't cause an error ?

[riteshjain1982]

create new connection object for

objCommand.Connection = objConn

as it's already used by dr

[Christopher_Arm]

the full code guys looks like this:

 Sub Main()
        Dim document As New XmlDocument()


        Dim theStream As New MemoryStream
       ' Dim Q = App_Path()
        document.Load(App_Path() + "\Config.xml")
        Dim serverName As String
        Dim databaseName As String
        Dim userName As String = "howie"
        Dim password As String = "mandel"
        serverName = document.SelectSingleNode("/configuration/settings/config.server").InnerXml

        databaseName = document.SelectSingleNode("/configuration/settings/config.db").InnerXml
 
        Dim root As String = App_Path()
        Dim root2 = document.SelectSingleNode("/configuration/settings")
        root2 = (root2.Item("path"))
        Dim root3 = root2.InnerText
        Dim filePathList() As String = Directory.GetFiles(root3)

        'Dim tb = document.SelectSingleNode("/configuration/settings/config.location").Inner




        'Dim db = document.SelectSingleNode("/configuration/settings")
        'db = (db.Item("config.db"))
        'Dim dbase = db.InnerText

       
        Dim pathList As New ArrayList
        For Each node As XmlNode In document.SelectNodes("/configuration/settings/path")
            pathList.Add(node.InnerXml)
        Next

        Dim categoryList As New ArrayList
        For Each node As XmlNode In document.SelectNodes("/configuration/settings/category")
            categoryList.Add(node.InnerXml)
        Next

        Dim typeList As New ArrayList
        For Each node As XmlNode In document.SelectNodes("/configuration/settings/type")
            typeList.Add(node.InnerXml)
        Next

        Dim databaseLoc As New ArrayList
        For Each Node As XmlNode In document.SelectNodes("/configuration/settings/config.location")
            databaseLoc.Add(Node.InnerXml)
        Next

        'Dim tableLoc As New ArrayList
        'For Each Node As XmlNode In document.SelectNodes("/configuration/settings/tablelocation")
        '    tableLoc.Add(Node.InnerXml)
        'Next


        Dim connString As String = String.Concat("Server=", serverName, ";Database=", databaseName, ";uid=", userName, ";Password=", password)


        Dim objConn As New SqlConnection(connString)
        Dim objCommand As New SqlCommand
        objCommand.Connection = objConn


        Dim fileCategoryList As New ArrayList
        Dim f As String = ""
        For Each f In filePathList
            Dim filename As String = Path.GetFileName(f)
            fileCategoryList.Add(filename)

        Next

        Dim imageList As New ArrayList
        For Each f In filePathList
            Dim fi As New System.IO.FileInfo(f)
            Dim stream As System.IO.FileStream = fi.OpenRead()
            Dim byteArray(stream.Length) As Byte

            stream.Read(byteArray, 0, byteArray.Length)
            stream.Close()
            imageList.Add(byteArray)
        Next


        objConn.Open()
        objCommand.Connection = objConn
        objCommand.CommandText = "INSERT INTO dbo.DocumentSettings" & _
        "(Category, Type, TableLocation) " & _
        "VALUES(@Category,@Type,@DatabaseLocation)"



        ' For s = 0 To imageList.Count - 1
        'For w = 0 To fileCategoryList.Count - 1
        For x = 0 To categoryList.Count - 1

            For j = 0 To typeList.Count - 1
                For k = 0 To databaseLoc.Count - 1

                    objCommand.Parameters.Clear()
                    'objCommand.Parameters.AddWithValue("@Image", imageList.Item(s))
                    'objCommand.Parameters.AddWithValue("@Path", fileCategoryList.Item(w).ToString)
                    objCommand.Parameters.AddWithValue("@Category", categoryList.Item(x).ToString)
                    objCommand.Parameters.AddWithValue("@Type", typeList.Item(j).ToString)
                    objCommand.Parameters.AddWithValue("@DatabaseLocation", databaseLoc.Item(k).ToString)
                    objCommand.ExecuteNonQuery()
                Next
            Next
        Next
        'Next
        ' Next

        'reads from the destination table name from the config database and stores it in a string
        'for the insert QUERY to be used later on 

        'the datareader will read the category, type and table name and then the select will retreive the values'
        'from the table and store them into variables. Then the INSERT  will use the string values in 
        '**reads the table***'


        Dim sqlComm As New SqlCommand("SELECT Category,Type,TableLocation from dbo.DocumentSettings", objConn)
        Dim dr As SqlDataReader = sqlComm.ExecuteReader()

        dr.Read()
        Dim CategoryString As String = CStr(dr("Category"))
        Dim Typestring As String = CStr(dr("Type"))
        Dim TableString As String = CStr(dr("TableLocation"))


        objCommand.Connection = objConn
        objCommand.CommandText = "INSERT INTO" & "'" & TableString & "'" & _
      "(Image,FileName, Category, Type) " & _
        "VALUES(@Image,@Path,@Category,@Type)"

        For a = 0 To imageList.Count - 1
            For b = 0 To fileCategoryList.Count - 1
                For c = 0 To categoryList.Count - 1
                    For d = 0 To typeList.Count - 1
                        objCommand.Parameters.Clear()
                        objCommand.Parameters.AddWithValue("@Image", imageList.Item(a))
                        objCommand.Parameters.AddWithValue("@Path", fileCategoryList.Item(b).ToString)
                        objCommand.Parameters.AddWithValue("@Category", categoryList.Item(c).ToString)
                        objCommand.Parameters.AddWithValue("@Type", typeList.Item(d).ToString)

                        objCommand.ExecuteNonQuery()
                    Next
                Next
            Next
        Next
        Dim watcher As FileSystemWatcher = New FileSystemWatcher
        watcher.Path = root3
        watcher.NotifyFilter = NotifyFilters.LastAccess
        watcher.NotifyFilter = NotifyFilters.LastWrite
        watcher.NotifyFilter = NotifyFilters.FileName
        watcher.NotifyFilter = NotifyFilters.CreationTime

        watcher.EnableRaisingEvents = True



        Dim fi2 As FileInfo = New FileInfo(root)

        Dim DateAccessed As String = fi2.LastWriteTime
        Dim objComm As New SqlCommand
        Dim accessedby As String = fi2.LastAccessTime

        objComm.Connection = objConn
        objComm.CommandText = "INSERT INTO dbo.Audit" & _
               "(FileName,Category, Type , FileWrite_Accessed ,Accessed_Date)" & _
               "VALUES(@FileName,@Category,@Type,@DateAccessed,@Accessed_By)"
        For t = 0 To fileCategoryList.Count - 1
            For x = 0 To categoryList.Count - 1
                For j = 0 To typeList.Count - 1

                    objComm.Parameters.Clear()
                    objComm.Parameters.AddWithValue("@FileName", fileCategoryList.Item(t).ToString)
                    objComm.Parameters.AddWithValue("@Category", categoryList.Item(x).ToString)
                    objComm.Parameters.AddWithValue("@Type", typeList.Item(j).ToString)
                    objComm.Parameters.AddWithValue("@DateAccessed", DateAccessed.ToString)
                    objComm.Parameters.AddWithValue("@Accessed_By", accessedby.ToString)
                    objComm.ExecuteNonQuery()
                Next
            Next
        Next









        objConn.Close()
    End Sub

[Christopher_Arm]

create new connection object for

objCommand.Connection = objConn

as it's already used by dr

I don't understand. And even so place that where ?

[riteshjain1982]

create new connection object for Insert like

 Dim objConnInsert As New SqlConnection(connString) 'and assign this to 
objCommand.Connection = objConnInsert

[Christopher_Arm]

create new connection object for Insert like

 Dim objConnInsert As New SqlConnection(connString) 'and assign this to 
objCommand.Connection = objConnInsert

Ok I'll try this now.

[riteshjain1982]

also dont forget to Exclude single Quote from Insert 'TableName' (see psot #8 point 2)

[Christopher_Arm]

I am getting an sql exception unhandled error incorrect syntax near dbo.blahblah (my tablename). the breakpoint stops at the executenonquery statement here ***:

        Dim sqlComm As New SqlCommand("SELECT Category,Type,TableLocation from dbo.DocumentSettings", objConn)
        Dim dr As SqlDataReader = sqlComm.ExecuteReader()

        dr.Read()
        Dim CategoryString As String = CStr(dr("Category"))
        Dim Typestring As String = CStr(dr("Type"))
        Dim TableString As String = CStr(dr("TableLocation"))

        Dim objConnInsert As New SqlConnection(connString) 'and assign this to

        objCommand.Connection = objConnInsert
        objConnInsert.Open()
        objCommand.CommandText = "INSERT INTO" & "'" & TableString & "'" & _
      "(Image,FileName, Category, Type) " & _
        "VALUES(@Image,@Path,@Category,@Type)"

        For a = 0 To imageList.Count - 1
            For b = 0 To fileCategoryList.Count - 1
                For c = 0 To categoryList.Count - 1
                    For d = 0 To typeList.Count - 1
                        objCommand.Parameters.Clear()
                        objCommand.Parameters.AddWithValue("@Image", imageList.Item(a))
                        objCommand.Parameters.AddWithValue("@Path", fileCategoryList.Item(b).ToString)
                        objCommand.Parameters.AddWithValue("@Category", categoryList.Item(c).ToString)
                        objCommand.Parameters.AddWithValue("@Type", typeList.Item(d).ToString)

                     ***   objCommand.ExecuteNonQuery()
                    Next
                Next
            Next
        Next

[riteshjain1982]

seems you forgot post #15

[riteshjain1982]

objCommand.CommandText = "INSERT INTO" &  TableString &   _
      "(Image,FileName, Category, Type) " & _
        "VALUES(@Image,@Path,@Category,@Type)"

[Christopher_Arm]

I had to put a space before tablestring otherwise that would have generated an error because INTOdbo.blahblah generated that error.But I took care of that


I am getting another error around the exceutenonquery again but one further down .
There is already an open DataReader associated with this Command which must be closed first.

 Dim sqlComm As New SqlCommand("SELECT Category,Type,TableLocation from dbo.DocumentSettings", objConn)
        Dim dr As SqlDataReader = sqlComm.ExecuteReader()

        dr.Read()
        Dim CategoryString As String = CStr(dr("Category"))
        Dim Typestring As String = CStr(dr("Type"))
        Dim TableString As String = CStr(dr("TableLocation"))

        Dim objConnInsert As New SqlConnection(connString) 'and assign this to

        objCommand.Connection = objConnInsert
        objConnInsert.Open()
        objCommand.CommandText = "INSERT INTO" & " " & TableString & _
      "(Image,FileName, Category, Type) " & _
        "VALUES(@Image,@Path,@Category,@Type)"

        For a = 0 To imageList.Count - 1
            For b = 0 To fileCategoryList.Count - 1
                For c = 0 To categoryList.Count - 1
                    For d = 0 To typeList.Count - 1
                        objCommand.Parameters.Clear()
                        objCommand.Parameters.AddWithValue("@Image", imageList.Item(a))
                        objCommand.Parameters.AddWithValue("@Path", fileCategoryList.Item(b).ToString)
                        objCommand.Parameters.AddWithValue("@Category", categoryList.Item(c).ToString)
                        objCommand.Parameters.AddWithValue("@Type", typeList.Item(d).ToString)

                        objCommand.ExecuteNonQuery()
                    Next
                Next
            Next
        Next
        Dim watcher As FileSystemWatcher = New FileSystemWatcher
        watcher.Path = root3
        watcher.NotifyFilter = NotifyFilters.LastAccess
        watcher.NotifyFilter = NotifyFilters.LastWrite
        watcher.NotifyFilter = NotifyFilters.FileName
        watcher.NotifyFilter = NotifyFilters.CreationTime

        watcher.EnableRaisingEvents = True



        Dim fi2 As FileInfo = New FileInfo(root)

        Dim DateAccessed As String = fi2.LastWriteTime
        Dim objComm As New SqlCommand
        Dim accessedby As String = fi2.LastAccessTime

        objComm.Connection = objConn
        objComm.CommandText = "INSERT INTO dbo.Audit" & _
               "(FileName,Category, Type , FileWrite_Accessed ,Accessed_Date)" & _
               "VALUES(@FileName,@Category,@Type,@DateAccessed,@Accessed_By)"
        For t = 0 To fileCategoryList.Count - 1
            For x = 0 To categoryList.Count - 1
                For j = 0 To typeList.Count - 1

                    objComm.Parameters.Clear()
                    objComm.Parameters.AddWithValue("@FileName", fileCategoryList.Item(t).ToString)
                    objComm.Parameters.AddWithValue("@Category", categoryList.Item(x).ToString)
                    objComm.Parameters.AddWithValue("@Type", typeList.Item(j).ToString)
                    objComm.Parameters.AddWithValue("@DateAccessed", DateAccessed.ToString)
                    objComm.Parameters.AddWithValue("@Accessed_By", accessedby.ToString)
                    ***objComm.ExecuteNonQuery()
                Next
            Next
        Next

wait a minute do I have to create another connection object ?

[riteshjain1982]

the thing is that you can not use same connection object that is used by Reader unless it is closed..........so may be you should create another common connection object for all Insert statement

[Christopher_Arm]

the thing is that you can not use same connection object that is used by Reader unless it is closed..........so may be you should create another common connection object for all Insert statement

so then what would you suggest for now ? I need the same connection string as you can see because the string is being built dynamically taking its components with the exception of the user id and password from an xml file. It is reading the nodes to build the string and that would be universal in terms of accessing the main table in the database called DocumentSettings.

where would I close my datareader logically ? and from there could I declare a connection object using the same connection string for the INSERT into audit and would that work ?

[riteshjain1982]

from you code look like you are using only 1st row return by SQL query so may be you can close at

   dr.Read()
        Dim CategoryString As String = CStr(dr("Category"))
        Dim Typestring As String = CStr(dr("Type"))
        Dim TableString As String = CStr(dr("TableLocation"))
        dr.Close()

and use same connection object objConn for insert.

[Christopher_Arm]

putting the dr.close here works and it runs without me having put a objconn insert as before..

But now the issue becomes asking whether there will be multiple categories and types to be stored as the datareader is reading then I would use the arraylist logic I have above and store each value in them and them call upon them in my parameters.

[riteshjain1982]

better fill all the Record return by SELECT into datatable instead of using datareader and than loop through datatable record for performing INSERT,by that way you dont have to worry about niether connection nor arraylistand other stuff.

[Christopher_Arm]

how is that done exactly out of curiousity ? can you show me an example of that by any chance ?

[riteshjain1982]

something like this

 Dim dtDocSetting As New DataTable("DocumentSetting")
        Dim sqlAdap As New SqlDataAdapter("SELECT Category,Type,TableLocation from dbo.DocumentSettings", objConn)
        sqlAdap.Fill(dtDocSetting)
        Dim dr As DataRow
        For Each dr In dtDocSetting.Rows
            Dim CategoryString As String = CStr(dr("Category"))
            Dim Typestring As String = CStr(dr("Type"))
            Dim TableString As String = CStr(dr("TableLocation"))

         'From here onward check for code as per your requirement,i just put as sample.            
 objCommand.CommandText = "INSERT INTO" & " " & TableString & _
                        "(Image,FileName, Category, Type) " & _
                          "VALUES(@Image,@Path,@Category,@Type)"

            For A = 0 To ImageList.Count - 1
                For b = 0 To fileCategoryList.Count - 1
                    For c = 0 To categoryList.Count - 1
                        For d = 0 To typeList.Count - 1
                            objCommand.Parameters.Clear()
                            objCommand.Parameters.AddWithValue("@Image", ImageList.Item(A))
                            objCommand.Parameters.AddWithValue("@Path", fileCategoryList.Item(b).ToString)
                            objCommand.Parameters.AddWithValue("@Category", categoryList.Item(c).ToString)
                            objCommand.Parameters.AddWithValue("@Type", typeList.Item(d).ToString)

                            objCommand.ExecuteNonQuery()
                        Next
                    Next
                Next
            Next
        Next