6 Replies - 905 Views - Last Post: 27 April 2016 - 09:20 AM

#1 guinness1up  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 16-February 16

Problem with Aspx SELECT FROM WHERE

Posted 27 April 2016 - 08:13 AM

Hello, I am stuck for a couple days now on this. What I have is a data file that is read in from a access file. The program goes through and asks for name, address....and what product that want to buy. Once chosen it should go to the sub which does a SELECT FROM WHERE to get the price. When I run it the way it is now I get a "Command text was not set for the command object." error in VB 2013.
Help would be greatly appreciated.
   <%@ Import Namespace="System.Data.OleDb" %>
<%@Page Language="VB" Debug="true" %>


<script  runat="server">
Sub calculate1(sender As Object, e As EventArgs)
    Dim dataconnector, strSQL, datacommand, dataread ,code, prodPrice, subTotal, item1
    item1 = productSelect.SelectedItem.Text
      dataconnector = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("data1.mdb"))
      dataconnector.Open()
    <%--subtotal = "SELECT price FROM products"--%>
      prodPrice = "SELECT price FROM products WHERE ID ="& productSelect.SelectedItem.value
      code = "SELECT  FROM products WHERE id "
      datacommand = New OleDbCommand(subtotal, dataconnector)
      dataread = datacommand.ExecuteReader()
      customers.DataSource = dataread
      customers.DataBind()
      dataread.Close()
      dataconnector.Close()
    nameLBL.Text=name.Text
    addressLBL.Text=address.Text
  cityLBL.Text=   city.Text
   stateLBL.Text= stateSelect.SelectedItem.text
   ziplbl.text= zip.Text
  quantityLBL.Text=  "Quantity: " + qty.Text
    lbl8.Text= (prodPrice)
End Sub
</script>

<!DOCTYPE html>
<html>
<body>

<form runat="server">

    <asp:DataList ID="customers" runat="server">

<HeaderTemplate>

</HeaderTemplate>

<ItemTemplate>
<tr>
<%--<td><%#Container.DataItem("ID")%></td>--%>
<%--<td><%#Container.DataItem("codenum")%></td>--%>
<td><%#Container.DataItem("price")%></td>
<%--<td><%#Container.DataItem("qty")%></td>--%>
</tr>
</ItemTemplate>

<FooterTemplate>
</table>
</FooterTemplate>

</asp:DataList>
Name:
<asp:TextBox id="name" runat="server" /> </br >
Address:
    <asp:TextBox id="address" runat="server" /></br >
City:
    <asp:TextBox id="city" runat="server" /></br >
State:
    <asp:DropDownList id="stateSelect"  runat="server" SelectionMode="Multiple" >
        <asp:ListItem value="-1" text="Select"></asp:ListItem>
  <asp:ListItem value="1" text="AL"></asp:ListItem>
  <asp:ListItem value="2" text="AK"></asp:ListItem>
  <asp:ListItem value="3" text="AZ"></asp:ListItem>
</asp:DropDownList></br >
    Zip code
      <asp:TextBox id="zip" runat="server" /></br >
    Product:
    <asp:DropDownList id="productSelect"  runat="server" SelectionMode="Multiple" >
  <asp:ListItem value="1" text="111111"></asp:ListItem>
  <asp:ListItem value="2" text="222222"></asp:ListItem>
  <asp:ListItem value="3" text="333333"></asp:ListItem>
  <asp:ListItem value="4" text="444444"></asp:ListItem>
</asp:DropDownList>
     Quantity:
    <asp:TextBox id="qty" runat="server" /> </br >
   
    <asp:Button onclick="calculate1" Text="Submit" runat="server" />
<p><asp:Label id="nameLBL" runat="server" /></p>
    <p><asp:Label id="addressLBL" runat="server" /></p>
    <p><asp:Label id="cityLBL" runat="server" /></p>
    <p></p><asp:Label id="stateLBL" runat="server" /></p>
    <p><asp:Label id="zipLBL" runat="server" /></p>
    <p><asp:Label id="prodSelectLBL" runat="server" /></p>
    <p><asp:Label id="quantityLBL" runat="server" /></p>
    <p><asp:Label id="lbl8" runat="server" /></p>
</form>

</body>
</html>



Is This A Good Question/Topic? 0
  • +

Replies To: Problem with Aspx SELECT FROM WHERE

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13400
  • View blog
  • Posts: 53,477
  • Joined: 12-June 08

Re: Problem with Aspx SELECT FROM WHERE

Posted 27 April 2016 - 08:16 AM

In theory "subtotal" should be your SQL query. You have it commented out.

14	      datacommand = New OleDbCommand(subtotal, dataconnector)

Was This Post Helpful? 0
  • +
  • -

#3 guinness1up  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 16-February 16

Re: Problem with Aspx SELECT FROM WHERE

Posted 27 April 2016 - 08:24 AM

View Postmodi123_1, on 27 April 2016 - 08:16 AM, said:

In theory "subtotal" should be your SQL query. You have it commented out.

14	      datacommand = New OleDbCommand(subtotal, dataconnector)

very good eye... Thank you... Took care of that problem.... Other problems still remain "Ill be Back"
Thank you again
Was This Post Helpful? 0
  • +
  • -

#4 guinness1up  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 16-February 16

Re: Problem with Aspx SELECT FROM WHERE

Posted 27 April 2016 - 08:48 AM

View Postmodi123_1, on 27 April 2016 - 08:16 AM, said:

In theory "subtotal" should be your SQL query. You have it commented out.

14	      datacommand = New OleDbCommand(subtotal, dataconnector)

Here is the problem that I had before I made that mistake.
the value of that (number 1)product is 25. I want to set that to a variable so i can multiply this by quantity.
here is what i have as of now
<script  runat="server">
Sub calculate1(sender As Object, e As EventArgs)
    Dim dataconnector, strSQL, datacommand, dataread ,code, prodPrice, subTotal, item1
    item1 = productSelect.SelectedItem.Text
      dataconnector = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("data1.mdb"))
      dataconnector.Open()
      prodPrice = "SELECT price FROM products WHERE ID ="& productSelect.SelectedItem.value
      datacommand = New OleDbCommand(prodPrice, dataconnector)
      dataread = datacommand.ExecuteReader()
      customers.DataSource = dataread
      customers.DataBind()
      dataread.Close()
      dataconnector.Close()
    nameLBL.Text=name.Text
    addressLBL.Text=address.Text
  cityLBL.Text=   city.Text
   stateLBL.Text= stateSelect.SelectedItem.text
   ziplbl.text= zip.Text
  quantityLBL.Text=  "Quantity: " + qty.Text
    lbl8.Text= prodPrice
End Sub
</script>




but... when I have lbl8.text = prodPrice it just prints it out as a string
as shown below
bob
123 street
towncity
AL
12345
Quantity: 2
SELECT price FROM products WHERE ID =1

I do know that it is getting the data in properly because further down in the code I have a line that states
<td><%#Container.DataItem("price")%></td>
and it prints the price out , but I want this data in my sub

Thanks again to any help
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13400
  • View blog
  • Posts: 53,477
  • Joined: 12-June 08

Re: Problem with Aspx SELECT FROM WHERE

Posted 27 April 2016 - 09:00 AM

Why are you setting the textbox value to a SQL select string?
20	    lbl8.Text= prodPrice


This is why you should have explicit turned on and actually declare your variables as they should be.

You should be using parameters, or recognize that comparing a column value to a string the string needs tick marks around it. 'like this'.
07	      prodPrice = "SELECT price FROM products WHERE ID ="& productSelect.SelectedItem.value

Was This Post Helpful? 0
  • +
  • -

#6 guinness1up  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 16-February 16

Re: Problem with Aspx SELECT FROM WHERE

Posted 27 April 2016 - 09:15 AM

View Postmodi123_1, on 27 April 2016 - 09:00 AM, said:

Why are you setting the textbox value to a SQL select string?
20	    lbl8.Text= prodPrice


This is why you should have explicit turned on and actually declare your variables as they should be.

You should be using parameters, or recognize that comparing a column value to a string the string needs tick marks around it. 'like this'.
07	      prodPrice = "SELECT price FROM products WHERE ID ="& productSelect.SelectedItem.value

I apologize, I do understand what you are saying, but I am unclear as to how to fix it.
When I try and use the string as a integer I get this type of error
Conversion from string "SELECT price FROM products WHERE" to type 'Integer' is not valid.
.... the errors vary depending on what I do, But this is the idea. I am not sure how to use the
string as a integer in order to do the math required
Which is where My biggest problem at this point lies... I think anyway.

I really do appreciate your help... this has been driving me crazy, im just going around in circles.
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13400
  • View blog
  • Posts: 53,477
  • Joined: 12-June 08

Re: Problem with Aspx SELECT FROM WHERE

Posted 27 April 2016 - 09:20 AM

I have no idea what you are referring to.

Also - you really don't need to quote the post above yours. Just use the 'reply'.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1