Beginner: Insert, Update, Delete & Search records from/to sql database

[sathiya]

Hi Everyone,
First of all, thanx Mojio for providing me the right code information on connecting sql server database.

As I'm a beginner in ASP.NET, using vb as language. I have created few text boxes on the form as an input to the application. I hav managed to insert the records to the sql, but not managed to search & update the records from sql.

My Code's on Insert Button:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim strConn As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\staff.mdf;Integrated Security=True;User Instance=True"
Dim cmd As New SqlCommand("INSERT INTO Staff (NRIC, Name, Address, TelNo)VALUES('" & txtIC.Text & "','" & txtName.Text & "','" & txtAddress.Text & "','" & txtTelNo.Text & "')", New SqlConnection(strConn))
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
End Sub

My Query:
I'm trying to update the records thru the textboxes created. The scenario is, the user will key in his student ID in the enter student ID textbox and click on the search button. Then All the details about him such as name, telNO will be displayed accordingly in other textboxes created.How can I retrieve the data from sql back into the textboxes created and from there i edit and update the changes back to sql. Please advise 10q.

Regards,
Sathiya

[campkev]

here is one way to get the info and display it on the screen. See if this works, if it does, try to "reverse" it to get it to update. Let me know if you need help.

Dim dr As SqlClient.SqlDataReader
        Dim cmd As New SqlClient.SqlCommand("Select * from Students where studentid = " & txtStudentID.txt, New SqlClient.SqlConnection(strConn))
        cmd.Connection.Open()
        dr = cmd.ExecuteReader()
        cmd.Connection.Close()

        txtName.Txt = dr("Name")
        txtTelNo.Txt = dr("TelNo")

[tgreer]

sathiya: please use code tags when posting code.

[campkev]

code tags look like this

[code]
[/code]

[sathiya]

Thanx 4 ur info campkev. I hav tried the codes, it's still showing the msg "Invalid attempt to MetaData when reader is closed" under the InvalidOperationException was unhandled by user code dialog box. It was pointing the
txtName.Text = dr("Name")
txtAddress.Text = dr("Address")

I hav tried to write the above code like below after that:
cmd.Connection.Open() n
dr = cmd.ExecuteReader()
txtName.Text = dr("Name")
txtAddress.Text = dr("Address")
cmd.Connection.Close()

This time it's showing the msg "Invalid attempt to read when no data is present" under the InvalidOperationException was unhandled by user code dialog box by pointing the
txtName.Text = dr("Name")
txtAddress.Text = dr("Address")

Please advice.

[campkev]

what does your sql query look like?

[sathiya]

what does your sql query look like?

Dim cmd As New SqlClient.SqlCommand("Select * from Staff where NRIC = '" & txtSearch.Text & "'", New SqlClient.SqlConnection(strConn))

The query should look like this when it runs:

Select * from Staff where NRIC = '840225-145035'

and I have a record sitting on the db. Thank you