Printable View
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:eek2:
you'll want to use SQLClient and .ExecuteScalar if it's only 1 value being returned.
Do it all in the stored procedure if all you need is a single value from another table in the database.
ok this is what I have...
The datareader does work however....Code: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)"
what is wrong with this as I want to pick the tablestring value so whatever table is reflected in the variable gets picked up ?
did you open connection ?
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.
the connection is open. Yes from previous code although this isn't reflected here..sorry about that.Quote:
Originally Posted by 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
or Insert INSERT Statement in while loop.Code: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
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.Quote:
Originally Posted by riteshjain1982
There is already an open DataReader associated with this Command which must be closed first.
now the question becomes where to put the dr.close that won't cause an error ?Code:
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
create new connection object foras it's already used by drCode:objCommand.Connection = objConn
the full code guys looks like this:
Code: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
I don't understand. And even so place that where ?Quote:
Originally Posted by riteshjain1982
create new connection object for Insert like
Code:Dim objConnInsert As New SqlConnection(connString) 'and assign this to
objCommand.Connection = objConnInsert
Quote:
Originally Posted by riteshjain1982
Ok I'll try this now.
also dont forget to Exclude single Quote from Insert 'TableName' (see psot #8 point 2)
I am getting an sql exception unhandled error incorrect syntax near dbo.blahblah (my tablename). the breakpoint stops at the executenonquery statement here ***:
Code:
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
seems you forgot post #15 :D
Code:objCommand.CommandText = "INSERT INTO" & TableString & _
"(Image,FileName, Category, Type) " & _
"VALUES(@Image,@Path,@Category,@Type)"
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.
wait a minute do I have to create another connection object ?Code: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
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.Quote:
Originally Posted by riteshjain1982
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 ?
from you code look like you are using only 1st row return by SQL query so may be you can close at
and use same connection object objConn for insert.Code: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()
putting the dr.close here works and it runs without me having put a objconn insert as before..:D
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.
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.
how is that done exactly out of curiousity ? can you show me an example of that by any chance ?
something like this
Code: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