Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Finding a value that is not NULL

Status
Not open for further replies.
asafb

asafb

Programmer
Jun 17, 2003
80
US
Dear tek tips gurus,

I have a page here which displays a random record each time. However, I only want it to display the record if it meets this criteria: Specialtag = 1 (Specialtag is a columm in my database). I only want to show a special product, not a normal product, this is why. So look at my code below and see what I can do! Thanks (Basically, i want to display a random record of just the special product)

<%@ Page Language="vb" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">


Sub Page_Load(sender As Object, e As EventArgs)
Dim lowestnumber, highestnumber, randomnumber, altrandomnumber
Dim ConnectionString as String = "server=x;user id=x;pwd=x;database=x"
RANDOMIZE
LowestNumber = 4
HighestNumber = 39
RandomNumber = Int((HighestNumber-LowestNumber+1)*Rnd+LowestNumber)
Dim CommandText As String = "SELECT * FROM products WHERE idproduct = " & randomnumber
Dim myConnection As New SqlConnection(ConnectionString)
Dim objdr as SQLDataReader

Dim myCommand As New SqlCommand(CommandText, myConnection)
myConnection.open()
objdr=myCommand.ExecuteReader(system.data.CommandBehavior.CloseConnection)
objdr.read()

if objdr("specialtag") = "0"
(OR should it be WHILE ???)
'(i'm trying to say if it is NOT a special product, randomize again until you find the criteria of specialtag = 1!!)
randomize?
end if


'the code below puts teh data on display!

image1.imageurl = objdr("smallImageURL")
description.text = objdr("description")
listprice.text = "$" & objdr("listprice")
price.text = objdr("price")
descriptionlong.text = objdr("descriptionLong")
hyperlink1.navigateurl = "estore/scripts/prodview.asp?idproduct=" & objdr("idproduct")
hyperlink1.text = objdr("description")
objdr.close()
myConnection.close()
End Sub


</script>
<html>
<head>
<link href="icw.css" type="text/css" rel="stylesheet" />
</head>
<body>
<table>
<tbody>
<tr>
<td valign="top">
<span class="icwsmall">
<p align="center">
<asp:Image id="Image1" runat="server"></asp:Image>
<br>
<asp:HyperLink id="hyperlink1" Runat="server"></asp:HyperLink>
<p align="center">
<img border="0" src="images/ssale.jpg" width="193" height="27"><br>
</a>
<br />
<asp:Label ID="description" Runat="server"></asp:Label></a></font>
<br />
<asp:Label ID="special" Runat="server"></asp:Label><br>
List Price: <asp:Label ID="listprice" Runat="server"></asp:Label>
<br />
Our Price: <asp:Label ID="price" Runat="server"></asp:Label>
<br />
<asp:Label ID="descriptionlong" Runat="server"></asp:Label>


</td>
<td>

</td>


<tbody>
</tbody>
</tbody>
</table>
</body>
</html>
 
Sort by date Sort by votes
How about changing the SQL to:

"SELECT *
FROM products
WHERE Specialtag = 1
AND idproduct = " & randomnumber
 
Upvote 0 Downvote
'IsDbNull takes an int for and argument so I believe you would need to know the
'position of specialtag, I could be all wet, better check my VB
Code: 
While objdr.Read
	If objdr.IsDBNull(position of specialtag)
		'do nothing
	Else
		If Convert.ToInt32(objdr.GetValue((position of specialtag))) = 1 Then
			Exit While 'we found a 1
		End If
	End If
End While
Marty
 
Upvote 0 Downvote
Hi marty i get syntax error says "position" not declared ort something
 
Upvote 0 Downvote
asafb,
By position I meant the index of the column in the row coming back from the database. Sorr about that. It is zero based so where in the row is specialtag? That number would go there.
Marty
 
Upvote 0 Downvote
Without knowing what you are trying to do, I think JohnE66 has a better solution.
Marty
 
Upvote 0 Downvote
Hello Marty. Let me explain. I revised it now slightly.

I want the datareader to retrieve this command
SELECT * from products where specialtag = 1 and idproduct = " & randomnumber.

Now, i want this: If the randomnumber retrieves no record, then randomize again until the randomnumber that is retrieved produces a match.

i.e.:

select * from products where specialtag = 1 and idproduct = 39. If this retrieves no data, then lets get a different number (random) until we have one that DOES retrieve data. This is waht i'm trying to do. Does this make sense? Here is the revised code:

<%@ Page Language="vb" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

Sub Page_Load(sender As Object, e As EventArgs)
Dim lowestnumber, highestnumber, randomnumber, altrandomnumber
Dim ConnectionString as String = "server=x;user id=x;pwd=x;database=x"
RANDOMIZE
LowestNumber = 4
HighestNumber = 39
RandomNumber = Int((HighestNumber-LowestNumber+1)*Rnd+LowestNumber)
Dim CommandText As String = "SELECT * FROM products WHERE specialtag = '1' and idproduct = " & randomnumber
Dim myConnection As New SqlConnection(ConnectionString)
Dim objdr as SQLDataReader

Dim myCommand As New SqlCommand(CommandText, myConnection)
myConnection.open()
objdr=myCommand.ExecuteReader()
objdr.read

(THE BELOW should SAYS: IF IT DOESN'T RETRIEVE A ROW (I.E. NO DATA), THEN I WANT IT TO GENERATE A NEW RANDOMNUMBER AND KEEP GENERATING UNTIL WE HAVE DATA!)

do while not objdr.hasrows

This is the code i need guys.By the way, when i try to do exit do, it stays stuck there!

loop
[[ the code below puts the values into display ]]
image1.imageurl = objdr("smallImageURL")
description.text = objdr("description")
listprice.text = "$" & objdr("listprice")
price.text = objdr("price")
descriptionlong.text = objdr("descriptionLong")
hyperlink1.navigateurl = "estore/scripts/prodview.asp?idproduct=" & objdr("idproduct")
hyperlink1.text = objdr("description")
response.write("not null,1")
response.write(randomnumber)
response.write ("has rows")

objdr.close()
myConnection.close()
End Sub


</script>

(the rest of the code is not needed for my help it just puts the values in there)

What do you think MArty? Thanks so much for you rhelp!!!! :)
 
Upvote 0 Downvote
I think I understand,
John's query modified, better check the sqlserver syntax for between
Code: 
"SELECT * 
FROM products 
WHERE specialtag = 1
AND (idproduct BETWEEN 4 and 39)
This way you make one DB hit.
Code: 
While objdr.Read
    If Convert.ToInt32(objdr.GetValue(("idproduct"))) = GetRandom Then
            Exit While 'we found our random number
    End If
End While
Here you can generate a random number, you should modify it so it take the high and low as parms
Code: 
Public Shared Function GetRandom As Integer
	Dim LowestNumber As Integer
	Dim HighestNumber As Integer
    LowestNumber = 4
    HighestNumber = 39
    Return Int((HighestNumber-LowestNumber+1)*Rnd+LowestNumber)
End Function
hth,
Marty
 
Upvote 0 Downvote
asafb I forgot RANDOMIZE in the function
Code: 
Public Shared Function GetRandom As Integer
    Dim LowestNumber As Integer
    Dim HighestNumber As Integer
    LowestNumber = 4
    HighestNumber = 39
	RANDOMIZE
    Return Int((HighestNumber-LowestNumber+1)*Rnd+LowestNumber)
End Function
Marty
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

AhmedAzizBarbary
  • Locked
  • Question
How to pass list of checkboxes not checked on page model razor page?
Replies
0
Views
424
AhmedAzizBarbary
AhmedAzizBarbary
Makumbi256
  • Locked
  • Question
How to make days vertical and grid view editable using asp.net vb
Replies
0
Views
274
Makumbi256
Makumbi256
weberm
  • Locked
  • Question
How do I make a page with multiple AJAX custom controls?
Replies
2
Views
354
weberm
weberm
a6green
  • Locked
  • Question
How to configure AT&T / Avaya Definity G3 incoming ISDN trunk
Replies
2
Views
384
a6green
a6green
AhmedAzizBarbary
  • Locked
  • Question
How to read data response body after success login razor page ?
Replies
0
Views
391
AhmedAzizBarbary
AhmedAzizBarbary

Part and Inventory Search

Sponsor

Back
Top