asp.net - SQL issue with multi query...


I am having issue with my code where I want to run multiple queries, each to its won varaible..then based on value to write to an existing textfile....

...especially this line:Using command As New SqlCommand(home_page, contact_page, product_trial,overview,support_center,user_forum,uninstall

......code...

Dim sqlConnection As String

        Using sqlConn As New SqlConnection

            sqlConnection = "Data Source=xx.xx.xx.xx;Initial Catalog=DWET;User Id=;Password=;"
            sqlConn.ConnectionString = sqlConnection

            Dim home_page As String = "SELECT home_page FROM PRODUCTS WHERE comp_name = " & varComp_name & " AND EMAIL = " & varEMAIL
            Dim contact_page As String = "SELECT contact_page FROM PRODUCTS WHERE comp_name = " & varComp_name & " AND EMAIL = " & varEMAIL          
            Dim product_trial As String = "SELECT product_trial FROM PRODUCTS WHERE comp_name = " & varComp_name & " AND EMAIL = " & varEMAIL
            Dim overview As String = "SELECT overview FROM PRODUCTS WHERE comp_name = " & varComp_name & " AND EMAIL = " & varEMAIL
            Dim support_center As String = "SELECT support_center FROM PRODUCTS WHERE comp_name = " & varComp_name & " AND EMAIL = " & varEMAIL
            Dim user_forum As String = "SELECT user_forum FROM PRODUCTS WHERE comp_name = " & varComp_name & " AND EMAIL = " & varEMAIL
            Dim uninstall_utility As String = "SELECT uninstall_utility FROM PRODUCTS WHERE comp_name = " & varComp_name & " AND EMAIL = " & varEMAIL
            Dim license_center As String = "SELECT license_center FROM PRODUCTS WHERE comp_name = " & varComp_name & " AND EMAIL = " & varEMAIL
            Dim session as string = "SELECT license_center FROM PRODUCTS WHERE comp_name = " & varComp_name & " AND EMAIL = " & varEMAIL
            ' Dim dataReader As SqlDataReader
            sqlConn.Open()
            Using command As New SqlCommand(home_page, contact_page, product_trial,overview,support_center,user_forum,uninstall_utility,license_center,session,sqlConn)
                dataReader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
                Do While dataReader.Read()
                    If dataReader.GetValue(0).ToString() = "static" Then
                  Dim oFile as system.io.file
                  Dim oWrite as system.io.streamwriter
                  oWrite = oFileExist("C:\home.txt")
                  owrite.writeline(home_page)

                  Dim oFile as system.io.file
                  Dim oWrite as system.io.streamwriter
                  oWrite = oFileExist("C:\contact.txt")
                  owrite.writeline(contact_page)

                  ...etc....

                 
                     
                    End If              
     
                   
                Loop
                dataReader.Close()
                sqlConn.Close()
            End Using
        End Using
LVL 10
GlobaLevelProgrammerAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
How many rows are you expecting to see? You have a loop but if you want to assign to variables then it has to be just the one read

Assign the results to a datareader variable

dr = dataReader.Read()

Now you can access individual fields

dr("databasefield")    eg   dr("home_page")

so check the return value of that then create tyour file

put it in a method, would be easier
eg

CreateMyFile (dr("home_page"))

then in your procedure CreateMyFile

private sub CreateMyFile(byval sText as string)

    .... put your open file code in here so you do not duplicate code




What are all the values you are going to check against and what does it map to (filename)

0
 
rockiroadsCommented:
let me get this straight, you want to check one table with the same where clause
why dont you just select all fields in one sql

Dim mystuff As String = "SELECT home_page, contact_page, product_trial, overview, support_center, user_forum, uninstall_utility, license_center, session FROM PRODUCTS WHERE comp_name = " & varComp_name & " AND EMAIL = " & varEMAIL

then use this query, data reader can then check each field and assign variable accordingly
0
 
GlobaLevelProgrammerAuthor Commented:
How would this look in datareader... As I need to capture the data in each column in a variable and I don't know what that data is....
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
GlobaLevelProgrammerAuthor Commented:
just one read...
0
 
rockiroadsCommented:
ok, so no loop required

then do as I say, have u got a map of what value and field equates to what file? list that here
0
 
GlobaLevelProgrammerAuthor Commented:
well theres a lot...about twenty...
but the good thing is that if you see above the varaible matches the field which also maches the name of the file...so for example:

variable:             File:                      field on DB:
home_page        home_page         Home_page
contact_page     contact_page       contact_page
product_trial       product_trial        product_trial
overview            overview              overview                    ...etc....as you can see in my example above...


as falls as values....they are all txt....can be a page long....any kind of characters....does this help?
0
 
GlobaLevelProgrammerAuthor Commented:
so its "C:/code_folder/home_page.txt" for the file path...."C:/code_folder/contact_page.txt"
0
 
rockiroadsConnect With a Mentor Commented:
Ok, here is a simple method that takes a string and creates a file of that name under C:\ and adds that content in that file

    Private Sub CreateFile(ByVal sTag As String)
        Dim swRite As StreamWriter

        Try
            swRite = New StreamWriter("C:\\" & sTag & ".txt")
            swRite.WriteLine(sTag)
            swRite.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub



Now all you need to do is call it. You can iterate thru reader fields like this

        Dim i As Integer
        For i = 0 To reader.FieldCount - 1
            CreateFile(reader.GetValue(i))
        Next


Right I gotta dash be back later this evening

0
 
GlobaLevelProgrammerAuthor Commented:
is there a way that when the user closes out session(closes the web site) to delete the data in  those txt files?
0
 
timexistCommented:
I think all these queries should be put in the database.

in the stored procedure, you set up the look up table,

You can use the stored procedure to return the target file name. update the file with vb code.


0
 
rockiroadsCommented:
You can use the unload method to delete the files. But first lets address the problem you raised in your question. Did that last solution help? You would need to change it to use the right path that you want.
0
 
GlobaLevelProgrammerAuthor Commented:
it worked!  Really cool!

--

so the unload to delete ALL data in the .txt file:
would this work or append empty string to the exisitng file at the end of last line wiht data..??

Public Sub Unload()
'DELETE ALL DATA IN THIS FILE ONCE THE SESSION AND/OR WEB PAGE IS CLOSED...
Dim swRite As StreamWriter
Dim noData as string
noData = ""
dim i as integer
dim stag as string

stag = "file"


for i = 1 to .eof
        Try
            swRite = New StreamWriter("C:\\" & sTag & ".txt")
            swRite.WriteLine(noData)            
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Next
swRite.Close()
End Sub
0
 
rockiroadsCommented:
We need to code the removal

If its all under one folder you could clear up all those files in that directory otherwise we have to remember the files we created then deleted
0
 
GlobaLevelProgrammerAuthor Commented:
yes, all the files all in one directory under the Solution explorer:

name_of_project>app_data >txt_files


just thought of something once the asp.net site is build and published and live for users to user..

will a user be able to see other users data in this txt file during runtime?

0
 
rockiroadsCommented:
One thing I am unsure off is where the files are going to be created. If on the server, which I assume it to be the case then they shouldnt be able to. If locally then yes.
0
 
GlobaLevelProgrammerAuthor Commented:
right now the files are located inside the asp.net project .....the asp.net project/web site will be located on a server with both the IIS and the ms sql db....i cant have shared access to these files...between users...I'm down to two options:

1) create the .txt files
-load data from sql db
-the pages will take the data from the .txt files and load into page
-.txt file deleted on unload...

2) create a global varaible that can be used between all pages at runtime


no idea how to do number 2(global varaible)..that would be the best and would be less taxing performance wise..as I dont want a hundred users creating files hundreds of files on my server...
0
 
rockiroadsCommented:
what are the reasons for the text files
0
 
GlobaLevelProgrammerAuthor Commented:
thnks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.