retrieve fields from database

[dinster]

Hi all,

Am knew to all the aspx stuff. Currently am creating an intranet and am tryin to search for hospno and then fill the firstname and surname into webpage form fields.

Am not entirely sure how to go bout this....

i've started off.....

Sub GetHospitalNoButtonClick(Source as Object, E as EventArgs)

Dim sql As String
sql = "SELECT * FROM PMI_PATIENTS WHERE [TRUST_NUM] = '" & HospitalNo & "'"

dim conn as New OleDbConnection(application("dblocation")) 'create a new connection to database
dim cmd as New OleDbCommand(sql,conn) ' create a new command to send to database




end sub

my question is am i on the right track? and how do i just get the firstname and surname from the database and poppulate it on the webpage?

an example would be really helpful

many thankx

 

[jbenson001]

It depends on if your query is returning multiple rows or not. If it is, then you would want to use a repeater, datalist, gridview... etc. If you are just showing 2 fields and there is only one row, then you can display the text in a textbox or lable..etc.

Your SQL code will have to return a dataset, then pull the values from that. Also, use stored procedures with parameters.

 

[dinster]

thankyou for replying....

The hospitalno is unique so it will only return one record.

Could you show me quick same or link somewhere this has been done?

cheers

 

[jbenson001]

There are many examples in this forum and on-line

 

[dinster]

this is what i got so far....

dim conn as New OleDbConnection(application("dblocation")) 'create a new connection to database
dim cmd as New OleDbCommand(sql,conn) ' create a new command to send to database
dim adapt as New OleDbDataAdapter(cmd) ' create a new adapater to process command
dim dspeople as New system.data.DataSet() ' create a new dataset to store data


Dim dtpeople As DataTable = dspeople.Tables.Add("Mydata") ' create a table to store data in dataset
Dim reccount As Integer

Try ' attempt to open database
conn.Open()
Catch ' cannot open
lblinfo.text = "*** ERROR : The database cannot be opened ***"
exit sub
End Try

Try 'attempt to read Users table
adapt.Fill(dspeople,"Mydata")
reccount = dtpeople.Rows.Count
Catch 'cannot read
lblinfo.text = "*** ERROR : The users table cannot be opened ***"
conn.close()
exit sub
End Try

conn.Close()

if reccount = 0 then ' no user exists in User table with that login id
lblinfo.text = "This user login id is not recognised by the system."
exit sub

else ' password matches so assign session variables

Surname.Text = dspeople.Tables("Mydata").rows(0)("Surname")
Firstname.Text = dspeople.Tables("Mydata").rows(0)("Forenames")


end if



end sub


but doesnt seem to be working?

 

[jbenson001]

but doesnt seem to be working?

What does that mean?