Member Avatar for 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

  • 3 Contributors
  • 6 Replies
  • 455 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by sathiya

Dani AI

Generated

Quick, practical summary for (and follow-up to /): the two exceptions you saw usually come from (a) closing the connection/reader before you read the results, and (b) trying to read columns when no row was returned. Fix both by keeping the connection open while you read, always calling Read() (or checking HasRows) and using parameterized commands and Using blocks so things are closed for you.

Common checklist

  • Do not call Connection.Close() until after you finish using the reader. In 's snippet the connection was closed right after ExecuteReader() which closes the reader.
  • Always call If rdr.Read() Then ... (or If rdr.HasRows Then rdr.Read()) before accessing rdr("ColumnName").
  • Use parameters to avoid SQL injection and to avoid string quoting issues.
  • When using AttachDbFilename check which .mdf copy your app is talking to (App_Data vs the copy in bin\Debug); Visual Studio can overwrite the file at runtime.

Example: search (safe pattern)

Using conn As New SqlConnection(connStr)
  Using cmd As New SqlCommand("SELECT Name, Address, TelNo FROM Staff WHERE NRIC = @NRIC", conn)
    cmd.Parameters.Add("@NRIC", SqlDbType.VarChar, 50).Value = txtSearch.Text.Trim()
    conn.Open()
    Using rdr As SqlDataReader = cmd.ExecuteReader()
      If rdr.Read() Then
        txtName.Text = rdr("Name").ToString()
        txtAddress.Text = rdr("Address").ToString()
        txtTelNo.Text = rdr("TelNo").ToString()
      Else
        ' no record found — clear fields or inform user
      End If
    End Using
  End Using
End Using

Example: update (safe pattern)

Using conn As New SqlConnection(connStr)
  Using cmd As New SqlCommand("UPDATE Staff SET Name=@Name, Address=@Address, TelNo=@TelNo WHERE NRIC=@NRIC", conn)
    cmd.Parameters.Add("@Name", SqlDbType.VarChar,100).Value = txtName.Text.Trim()
    cmd.Parameters.Add("@Address", SqlDbType.VarChar,200).Value = txtAddress.Text.Trim()
    cmd.Parameters.Add("@TelNo", SqlDbType.VarChar,20).Value = txtTelNo.Text.Trim()
    cmd.Parameters.Add("@NRIC", SqlDbType.VarChar,50).Value = txtSearch.Text.Trim()
    conn.Open()
    Dim affected As Integer = cmd.ExecuteNonQuery()
    ' check affected > 0 to confirm update
  End Using
End Using

Extra tips: move the connection string to web.config, add Try/Catch around DB calls, and test the exact NRIC value in SQL Management Studio to confirm the row exists. This will eliminate both exceptions and make your insert/search/update flow robust.

Recommended Answers

All 6 Replies

Member Avatar for 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")
Member Avatar for tgreer

sathiya: please use code tags when posting code.

Member Avatar for campkev

code tags look like this

[code]
[/code]
Edited by TrustyTony because: fixed formatting
Member Avatar for 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.

Member Avatar for campkev

what does your sql query look like?

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.