Help with search button

RPBLEA

Active member
Joined
Apr 26, 2005
Messages
36
Programming Experience
1-3
I'm writing some code in Vb.net I'm using aspx and I have a button the webpage called next and back, How Do I search through the database, I haven't found any code googling and have no clue how to search through records, here is the piece of code where I read through the data. I need help with these buttons Any code appreciated, THank You

This is my search button to search for one field but how Do I move through the data either forward or back?

Dim strSQL As String

strSQL = "Select * From pairs" & _

"WHERE([orderkey] = & String.Parse(txtorderkey.Text)"

 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,322
Location
Sydney, Australia
Programming Experience
10+
There may be a better solution, but if you want to find the next record without knowing what that ID is you can use something like:

"SELECT MIN(orderkey), field1 FROM pairs WHERE orderkey > " & <current orderkey> & " GROUP BY field1"

If you want the previous record just substitute "MAX" for "MIN" and "<" for ">". Note that when using aggregate functions like MAX and MIN, you have to explicitly name each field you want returned in the result set, and all but the aggregated field must appear in the GROUP BY clause.
 

ryager

Member
Joined
May 3, 2005
Messages
8
Location
Florida
Programming Experience
10+
VB or SQL

SQL

Well, if you are attempting to page through the records on the database using SQL perhaps you could try something like.........

select top [items per page]
*
from
pairs
where
[orderkey] >= [Current Position Key Value]

VB

If you want to use a dataset in VB then

Establish your database connection using the System.Data Connection object of your choice.

Obtain the dataset from the database using the new connection.

The dataset object has a Tables Collection, this is used to reference a specific resultset in the dataset. The items in this collection expose another collection of rows which in turn has a collection of columns.
 
Last edited:

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
Move through the records...

try using Me.BindingContex to move through a dataset:

Dataset record counts start with zero, so:

Me.BindingContext(DsInvoices1, "Invoices").Position = 0
moves to the first record.

Me.BindingContext(DsInvoices1, "Invoices").Position = Me.BindingContext(DsInvoices1, "Invoices").Count - 1
moves to the last record.

Me.BindingContext(DsInvoices1, "Invoices").Position += 1
moves foward 1 record.

Me.BindingContext(DsInvoices1, "Invoices").Position -= 1
moves back 1 record.

hope this helps,
 

RPBLEA

Active member
Joined
Apr 26, 2005
Messages
36
Programming Experience
1-3
Jmcilhinney How do you know the current orderkey ?
 

RPBLEA

Active member
Joined
Apr 26, 2005
Messages
36
Programming Experience
1-3
and David how Do I use the Binding With Aspx Web pages it gives me errors when I run that code, with the Whole Binding?
 

RPBLEA

Active member
Joined
Apr 26, 2005
Messages
36
Programming Experience
1-3
Anyone know what's wrong with this code
okay when I want to scroll through the order key txtbox and then i want a bunch of other text fields to be updated along with what was in the order key row, I have this so far, What am I doing wrong? Nothing currently changes, and i'm doing this in asp and would like this to change the first text boxes and the the data in the corresponding text boxes to match with what's in the orderkey text box, any help? thanks?

Dim str As String

str = "SELECT MIN(orderkey), field1 FROM pairs WHERE orderkey > " & CStr(txtorderkey.Text) & " GROUP BY field1"

 

ryager

Member
Joined
May 3, 2005
Messages
8
Location
Florida
Programming Experience
10+
The Order Key

The Current Order Key may start with '0' or null, basicly the smallest possible Key value, and as a page is loaded the new current order key would be the last record in the paged result.
 

RPBLEA

Active member
Joined
Apr 26, 2005
Messages
36
Programming Experience
1-3
I used this to search, Is there an easier way to check if there are nulls to change them to a value in a database cause I used the long if statement way?

CREATE PROCEDURE dbo.cablepair
@cablepair int
AS
select * from pairs
where cablepair = @cablepair
GO


lbltest.Text = ""

Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("strDBConn"))

Try

conn.Open()

Dim cmd As New SqlCommand("cablepair", conn)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@cablepair", txtcablepair.Text)

Dim reader As SqlDataReader

reader = cmd.ExecuteReader()

If reader.HasRows Then

reader.Read()

txtorderkey.Text =
CStr(reader.GetInt32(0))

If (reader.IsDBNull(1)) Then

txtcablepair.Text = CStr("")

Else

txtcablepair.Text = CStr(reader.GetInt32(1))

End If

If (reader.IsDBNull(2)) Then

txtbuilding2.Text = ""

Else

txtbuilding2.Text = reader.GetString(2)

End If

If (reader.IsDBNull(3)) Then

txtroom2.Text = ""

Else

txtroom2.Text = reader.GetString(3)

End If

If (reader.IsDBNull(4)) Then

txtpanel.Text = ""

Else

txtpanel.Text = reader.GetString(4)

End If

If (reader.IsDBNull(5)) Then

txtprefix.Text = CStr("")

Else

txtprefix.Text = CStr(reader.GetInt32(5))

End If

If (reader.IsDBNull(6)) Then

txtextension.Text = ""

Else

txtextension.Text = reader.GetString(6)

End If

If (reader.IsDBNull(7)) Then

txtvgname.Text = ""

Else

txtvgname.Text = reader.GetString(7)

End If

If (reader.IsDBNull(8)) Then

txtport.Text = ""

Else

txtport.Text = reader.GetString(8)

End If

If (reader.IsDBNull(9)) Then

txtdescription.Text = ""

Else

txtdescription.Text = reader.GetString(9)

End If

If (reader.IsDBNull(10)) Then

txtlocation.Text = ""

Else

txtlocation.Text = reader.GetString(10)

End If

If (reader.IsDBNull(11)) Then

txtjackdesi.Text = ""

Else

txtjackdesi.Text = reader.GetString(11)

End If

If (reader.IsDBNull(12)) Then

txtnotes.Text = ""

Else

txtnotes.Text = reader.GetString(12)

End If

If (reader.IsDBNull(13)) Then

txtupdated.Text = ""

Else

txtupdated.Text = reader.GetString(13)

End If

End If

Catch ex As Exception

lblerr.Text = ex.Message

End Try

conn.Close()

loaddatalist3()

 

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
Ok ... i'm doing this instead ...

VB.NET:
While reader.Read()
txtorderkey.Text = [size=2]reader(index) & ""[/size]
 
[size=2]
[/size][size=2]txtcablepair.Text = [/size][size=2]reader(index) & ""[/size]
[size=2][size=2]
[/size][/size][size=2]txtcablepair.Text = [/size][size=2]reader(index) & ""[/size]
[size=2][size=2]
[/size][/size][size=2]txtbuilding2.Text = reader(index) & ""[size=2]
[/size][size=2]etc .....[/size][/size]
[size=2][size=2]End[/size][size=2]While[/size][/size]
[size=2][size=2]
[/size][/size]

Cheers ;)
 

RPBLEA

Active member
Joined
Apr 26, 2005
Messages
36
Programming Experience
1-3
with the statement you provided it gives me some hints to some new coding, but I want to read in the current data, and if it is null then change it to "", not just set all the data to "" right away, Is there a way to do this?
 

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
RPBLEA said:
with the statement you provided it gives me some hints to some new coding, but I want to read in the current data, and if it is null then change it to "", not just set all the data to "" right away, Is there a way to do this?
I guess you've missed some part there ... the code i provided is doing right that you want ... if it finds a value it display value + "" empty string otherwise only empty string and not returns error about DBNull ... Kind regards :)
 

RPBLEA

Active member
Joined
Apr 26, 2005
Messages
36
Programming Experience
1-3
okay yes I see it , Morning time and My brains a little slow, But I still get this error? Data is Null. This method or property cannot be called on Null values,
Which to me doesn't make sense because it should work based on the code!
 

RPBLEA

Active member
Joined
Apr 26, 2005
Messages
36
Programming Experience
1-3
I think when it calls from the database, the reader.GetSTring(index) cannot be null, think this is where the error is coming from
 

RPBLEA

Active member
Joined
Apr 26, 2005
Messages
36
Programming Experience
1-3
can you think if any reason to pass a value to these index's if their null when being read iN?
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
While reader.Read()
If Not reader(0) Is DBNull.Value Then
txtorderkey.Text = reader(0)
Else
txtorderkey.Text = ""
End If
etc .....
EndWhile

Haven't tried this code out, but you could do something along this line.
 

RPBLEA

Active member
Joined
Apr 26, 2005
Messages
36
Programming Experience
1-3
that's what I had before except for the while loop, Thanks anyway! like the input
 
Top Bottom