Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-09-18
18
Medium Priority
?
319 Views
Last Modified: 2012-05-10

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
0
Comment
Question by:GlobaLevel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
18 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33710292
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
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33710948
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 33712073
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 10

Author Comment

by:GlobaLevel
ID: 33712117
just one read...
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33712151
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
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33712258
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
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33712290
so its "C:/code_folder/home_page.txt" for the file path...."C:/code_folder/contact_page.txt"
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 2000 total points
ID: 33712303
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
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33712710
is there a way that when the user closes out session(closes the web site) to delete the data in  those txt files?
0
 
LVL 4

Expert Comment

by:timexist
ID: 33714049
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33717432
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
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33717760
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33717835
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
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33717895
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33719090
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
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33719481
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33719715
what are the reasons for the text files
0
 
LVL 10

Author Closing Comment

by:GlobaLevel
ID: 33780437
thnks
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question