Edit code Help

Question

chanthung 0 Junior Poster in Training

16 Years Ago

Hello friends, hope you guys are dong well.
problem again and really looking up to my expert friends here.
I'm new and trying to write Edit code in VB.NET. the problem is
Select * From Application Where Sl_No='" + txteditno.Text + "'", con)
is not displaying and record although it works if i remove the WHERE part in my select statement....plz examinemy code...thanks

Private Sub Formedit_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        strc = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=J:\Alumni\Alumni\Alumni\aadb.mdb"
        con = New OleDbConnection(strc)
        txteditno.Text = n
        'da = New OleDbDataAdapter("Select * From Application Where Sl_No='" + txteditno.Text + "'", con)

       
        'ds = New DataSet()
        'da.Fill(ds, "Application")
        cmd1 = New OleDbCommand("Select * From Application ", con)
        ds = New DataSet()


        dt = ds.Tables("Application")
        txtslno.DataBindings.Add("Text", dt, "Sl_No")
        txtname.DataBindings.Add("Text", dt, "Name")
        txtdob.DataBindings.Add("Text", dt, "Date_of_birth")
        txtgender.DataBindings.Add("Text", dt, "Gender")
        txtdegree.DataBindings.Add("Text", dt, "Degree_obtained")
        txtenroll.DataBindings.Add("Text", dt, "Year_of_Enrollment")
        txtpass.DataBindings.Add("Text", dt, "Year_of_passing")
        txtpadd.DataBindings.Add("Text", dt, "Permanent_Address")
        txtpin.DataBindings.Add("Text", dt, "PIN_Code")
        txtphone.DataBindings.Add("Text", dt, "Phone_No")
        txtmobile.DataBindings.Add("Text", dt, "Mobile_No")
        txtemail.DataBindings.Add("Text", dt, "Email_Id")

        con.Close()
        con = Nothing
    End Sub

.net:vb.net microsoft:sql-server

4 Contributors
11 Replies
186 Views
1 Year Discussion Span
Latest Post 14 Years Ago Latest Post by nicolle john

Dani AI

Generated 6 Months Ago

A few quick, practical fixes that address the errors you’re seeing (and build on the tips from and ).

The root causes here are (1) you bind controls before any table has been filled (so the DataSource is Nothing) and (2) Sl_No is an AutoNumber (numeric) yet your WHERE clause was treating it like text. Rather than concatenating strings, use a parameterized query, fill a DataTable, check that it contains rows, then bind. Also clear previous bindings before re-binding.

Example pattern to SELECT and bind safely:

Dim id As Integer
If Not Integer.TryParse(txteditno.Text, id) Then
    MessageBox.Show("Enter a valid Sl_No.")
    Return
End If

Dim connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\Alumni\Alumni\Alumni\aadb.mdb"
Dim sql = "SELECT * FROM [Application] WHERE [Sl_No] = ?"

Using cn As New OleDb.OleDbConnection(connStr)
    Using da As New OleDb.OleDbDataAdapter(sql, cn)
        da.SelectCommand.Parameters.AddWithValue("?", id)
        Dim tbl As New DataTable()
        da.Fill(tbl)
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("No record found.")
            Return
        End If

        Dim bs As New BindingSource() With {.DataSource = tbl}
        txtslno.DataBindings.Clear() : txtname.DataBindings.Clear()
        txtslno.DataBindings.Add("Text", bs, "Sl_No")
        txtname.DataBindings.Add("Text", bs, "Name")
        ' bind other controls the same way
    End Using
End Using

For UPDATE use a parameterized command (match parameter order for OleDb) and update by Sl_No (not Mobile_No unless that’s intended). Example:

Dim upd = "UPDATE [Application] SET [Name]=?, [Date_of_birth]=? WHERE [Sl_No]=?"
Using cn As New OleDb.OleDbConnection(connStr)
    Using cmd As New OleDb.OleDbCommand(upd, cn)
        cmd.Parameters.AddWithValue("?", txtname.Text)
        cmd.Parameters.AddWithValue("?", txtdob.Text)
        cmd.Parameters.AddWithValue("?", CInt(txtslno.Text))
        cn.Open()
        cmd.ExecuteNonQuery()
    End Using
End Using

Troubleshooting checklist: confirm tbl IsNot Nothing and tbl.Columns.Contains("Sl_No") before binding; clear old bindings so they don’t stack; don’t quote numeric Autonumber values; use the ACE provider if your DB is .accdb; and prefer parameters over concatenation to avoid type/format problems. This approach resolves the ArgumentNullException and the “Cannot bind … Sl_No” error.

All 11 Replies

finito 46 Nearly a Posting Virtuoso

16 Years Ago

you want to make that part into OleDbCommand not adapter

pritesh2010 30 Posting Whiz in Training

16 Years Ago

you this line is wrong that's whi it's not working
''da = New OleDbDataAdapter("Select * From Application Where Sl_No='" + txteditno.Text + "'", con)
in VB.net we are not concatenation string with '+' sign we are use '& '.
so instead of this write like this

da = New OleDbDataAdapter("Select * From [Application] Where [Sl_No] ='" & trim(txteditno.Text) & " '",con)

pritesh2010 30 Posting Whiz in Training

16 Years Ago

ya finto is right. write this in command not in the ADpter.

Reply to this topic

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.

chanthung 0 Junior Poster in Training · Answer 1 · 2010-05-24T10:35:51+00:00

Thanks Pritesh2010 and Finito

Ok, so I take the value of txteditno.text from and InputBox and tried running the program, taking you suggestion of using OLEDBCommand and using & instead of + but the problem still persist…I get the following error.
ArguementNullException was Unhandled
Value cannot be null.
Parameter name: dataSource

My database is MSAccess and Sl_No is Autonumber...m still trying to get it right but really will appreciate help

chanthung 0 Junior Poster in Training · Answer 2 · 2010-05-24T11:51:52+00:00

My code to modify record after displaying the records using a code.

Private Sub btnmodify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnmodify.Click
        con = New OleDbConnection(strc)
        con.Open()
        str = "UPDATE Application SET Name='" & Trim(txtname.Text) & "',Date_of_birth='" & Trim(txtdob.Text) & "',Gender='" & Trim(txtgender.Text) & "',Degree_obtained='" & Trim(txtdegree.Text) & "',Year_of_Enrollment='" & Trim(txtenroll.Text) & "',Year_of_passing='" & Trim(txtpass.Text) & "',Permanent_Address='" & Trim(txtpadd.Text) & "',PIN_Code='" & Trim(Val(txtpin.Text)) & "',Phone_No='" & Trim(Val(txtphone.Text)) & "',Mobile_No='" & Trim(Val(txtmobile.Text)) & "',Email_Id='" & Trim(txtemail.Text) & "' WHERE Mobile_No ='" + n + "'"
        cmd1 = New OleDbCommand(str, con)
        cmd1.ExecuteNonQuery()
        con.Close()
        con = Nothing
    End Sub

This is the CODE to display the records after searching the database by its Serial No. which is entered through txteditno.text.

Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click
        strc = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=J:\Alumni\Alumni\Alumni\aadb.mdb"
        con = New OleDbConnection(strc)
        con.Open()
        cmd1 = New OleDbCommand("Select * From [Application] Where [Sl_No]='" & Val(txteditno.Text) & "'", con)
        ds = New DataSet("Application")
        dt = New DataTable
        Dim dv As DataView
        'dt = ds.Tables("Application")
        dv = New DataView(ds.Tables("Application"))

        txtslno.DataBindings.Add("Text", dv, "Sl_No")
        txtname.DataBindings.Add("Text", dv, "Name")
        txtdob.DataBindings.Add("Text", dv, "Date_of_birth")
        txtgender.DataBindings.Add("Text", dv, "Gender")
        txtdegree.DataBindings.Add("Text", dv, "Degree_obtained")
        txtenroll.DataBindings.Add("Text", dv, "Year_of_Enrollment")
        txtpass.DataBindings.Add("Text", dv, "Year_of_passing")
        txtpadd.DataBindings.Add("Text", dv, "Permanent_Address")
        txtpin.DataBindings.Add("Text", dv, "PIN_Code")
        txtphone.DataBindings.Add("Text", dv, "Phone_No")
        txtmobile.DataBindings.Add("Text", dv, "Mobile_No")
        txtemail.DataBindings.Add("Text", dv, "Email_Id")

        con.Close()
        con = Nothing

This code is not working and shows ERROR:
Cannot bind to the property or column Sl_No on the DataSource.
Parameter name: dataMember

finito 46 Nearly a Posting Virtuoso · Answer 3 · 2010-05-24T12:13:46+00:00

This means one of the values you are calling is NULL.

SELECT WHEN CASE DOB IS NULL THEN '19750608' ELSE DOB END FROM Application

.

This is a sample code where you are checking if column DOB is NULL, if it is NULL then you give it a standard value in this case '19750608' in yyyyMMdd format or however you want to.

chanthung 0 Junior Poster in Training · Answer 4 · 2010-05-24T15:08:06+00:00

Actually have dont input validation before the SQL qry.

If (txtname.Text = "") Then
            MessageBox.Show("Name field cannot be blank, Please enter the name.", "Error", MessageBoxButtons.OK)
            txtname.Focus()
            Exit Sub
        Else
            If (IsNumeric(txtname.Text)) Then
                MessageBox.Show("Please enter a valid name. eg: Jack, Bryan", "Error", MessageBoxButtons.OK)
                txtname.Focus()
                Exit Sub
            End If
        End If

finito 46 Nearly a Posting Virtuoso · Answer 5 · 2010-05-24T17:56:23+00:00

Cannot bind to the property or column Sl_No on the DataSource.
Parameter name: dataMember

Which Line?

chanthung 0 Junior Poster in Training · Answer 6 · 2010-05-25T09:53:04+00:00

chanthung 0 Junior Poster in Training

16 Years Ago

Line 12 of btnSEARCH Code

finito 46 Nearly a Posting Virtuoso · Answer 7 · 2010-05-25T10:20:00+00:00

Ok I see the error.

txtslno.DataBindings.Add("Text", [B]ds[/B], "[B]Application[/B].Sl_No")

if this doesn't work try adding [] to Application.

nicolle john 0 Newbie Poster · Answer 8 · 2012-03-03T15:25:24+00:00

nicolle john 0 Newbie Poster

14 Years Ago

what if i want to make it in adapter??how can i do that sir?

Edited 14 Years Ago by nicolle john because: wrong grammar

Edit code Help

Dani AI

Recommended Answers Collapse Answers

All 11 Replies

Recommended Answers