Urgent Help With a SQL Where statement

Expand Post »

was directed here by another member...great site!

(am a newbie and I recently posted this on MSDN with 0 replies, am hoping someone can help here - you do not need to know VFP, just need help formatted a correct string that ASP will honor),

Current Setup:

Using:
Visual Web Developer Express
SQL Express
IIS 6.0 with .Net 2.0 Installed
Visual Foxpro

I actually have two questions (but am making good progress so will focus on the issue at hand).

1. I setup a Login.aspx / Default.aspx / Register.apsx / Membership.aspx as specified in the examples in MSDN...all is working ok.

2. When I register a new user, I wanted to capture their Customer ID during login to store in their Membership view table. I did this and is working ok....and to make it easy, am storing the value in Comments field.

3. I want to query a Visual Fox free table (DBF) that contains my client's Customer Invoice data...and have the ODBC Connection setup and working properly.

Here's the problem....

I'm having trouble formulating the SELECT string within the asp code to capture the Membership.Comments field.

I need the proper functions to grab the Comments field data and then use it within my select statement.

Presently, the query works if I just hard-code the value i.e.

"SELECT * from [invdata] WHERE cus_no = '1' "

I need to replace the '1' with the comments field data.

Thanks:

On a side issue: it took me days to just to get this all working to where it is...does anyone have a detailed example of how to Login to a site and pass the parms to another page to query the Logged In user's data from a database......a real world example that I have yet to see highlighted anywhere in the Forums or books I've bought.

For example: I am customer ABC123, I log in to my vendor's site and want to see all my orders placed.

Similar Threads

Hi,Need help on sql select statement structure in MS SQL
SQL select statement help in MS Access and FileMaker Pro
Dynamic Query in JSP
c++ SQLServer ODBC errors in C++
cfselect issue trying to get results from a sql select statement in ColdFusion

jeffclarke

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Jan 2007

Permalink

Jan 5th, 2007

Re: Urgent Help With a SQL Where statement

Jeff,

Does Membership.Comments contain only the value corresponding to invdata.cus_no? Or does it contain other verbiage?

If it only contains the customer number, your code could look like this:

MS SQL Syntax (Toggle Plain Text)
 
LOCAL lcCustomerNo 
lcCustomerNo = ALLTRIM(membership.comments) 
"SELECT * from [invdata] WHERE cus_no = lcCustomerNo "
 
LOCAL lcCustomerNo 
lcCustomerNo = ALLTRIM(membership.comments) 
"SELECT * from [invdata] WHERE cus_no = lcCustomerNo "

-Dave Martin

Click to Expand / Collapse Quote originally posted by jeffclarke ...

was directed here by another member...great site!

(am a newbie and I recently posted this on MSDN with 0 replies, am hoping someone can help here - you do not need to know VFP, just need help formatted a correct string that ASP will honor),

Current Setup:

Using:
Visual Web Developer Express
SQL Express
IIS 6.0 with .Net 2.0 Installed
Visual Foxpro

I actually have two questions (but am making good progress so will focus on the issue at hand).

1. I setup a Login.aspx / Default.aspx / Register.apsx / Membership.aspx as specified in the examples in MSDN...all is working ok.

2. When I register a new user, I wanted to capture their Customer ID during login to store in their Membership view table. I did this and is working ok....and to make it easy, am storing the value in Comments field.

3. I want to query a Visual Fox free table (DBF) that contains my client's Customer Invoice data...and have the ODBC Connection setup and working properly.

Here's the problem....

I'm having trouble formulating the SELECT string within the asp code to capture the Membership.Comments field.

I need the proper functions to grab the Comments field data and then use it within my select statement.

Presently, the query works if I just hard-code the value i.e.

"SELECT * from [invdata] WHERE cus_no = '1' "

I need to replace the '1' with the comments field data.

Thanks:

On a side issue: it took me days to just to get this all working to where it is...does anyone have a detailed example of how to Login to a site and pass the parms to another page to query the Logged In user's data from a database......a real world example that I have yet to see highlighted anywhere in the Forums or books I've bought.

For example: I am customer ABC123, I log in to my vendor's site and want to see all my orders placed.

Last edited by dmartin9999; Jan 5th, 2007 at 6:08 pm. Reason: Code tag didn't work like I expected

dmartin9999

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Jan 2007

Permalink

Dec 4th, 2008

Re: Urgent Help With a SQL Where statement

You need a "dynamic" method. Place a textbox on your webform and with it, for example, you can do this on your sql select statement:

MS SQL Syntax (Toggle Plain Text)
<script runat="server">
 
    Protected Sub Button1_Click(ByVal sender AS Object, ByVal e AS System.EventArgs)
 
        Dim cmd AS New SqlCommand
 
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * from [invdata] WHERE cus_no =" & txtMyTextBox.Text
 
        cmd.Connection = conn
 
    End Sub
</script>
<script runat="server">

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        
        Dim cmd As New SqlCommand
        
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * from [invdata] WHERE cus_no =" & txtMyTextBox.Text
        
        cmd.Connection = conn

    End Sub
</script>

-Arturo Rivas
San Rafael, CA

Last edited by arturorivas; Dec 4th, 2008 at 3:35 pm.

arturorivas

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Dec 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: I would like to get the result for a particular query using cursor in transaction

Next Thread in MS SQL Forum Timeline: SELECT name FROM sysobjects

DaniWeb Message

Cancel Changes

User Name
Password