Solved

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

Posted on 2010-09-18
18
309 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
  • 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 500 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
 
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 500 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now