Help with search button

[RPBLEA]

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]

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]

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.

 

[DavidT_macktool]

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]

Jmcilhinney How do you know the current orderkey ?

 

[RPBLEA]

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?

 

[DavidT_macktool]

sorry, no asp help here

Sorry, didn't see the asp part. Look here for some help.

http://www.startvbdotnet.com/aspsite/ado/databinding.aspx

 

[jmcilhinney]

Presumably the current orderkey value is String.Parse(txtorderkey.Text).

 

[RPBLEA]

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]

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]

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]

Ok ... i'm doing this instead ...

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]

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]

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]

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!