Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

SQL Select Statement

Hi,

Is it possible to create a SQL select statement which populates the data into selected texboxes ?

I thought of this code but I know it doesn't work and not sure on how to correct it.

     
Dim cmd As New OleDb.OleDbCommand
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Matthew\SkyDrive\Documents\WORK\main.mdb"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "Select (Inv_ID, Inv_CustomerName) FROM Invoices WHERE ID='" & TextBox4.Text & "' VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "')"
        cmd.ExecuteNonQuery()

Open in new window

0
lincstech
Asked:
lincstech
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
ExecuteNonQuery does not return data. It can be used with statements like insert, update, delete, merge, ...

In your case you first need to fill a dataset. Check http://www.developerfusion.com/thread/52953/adonet-help-search-database-and-populate-a-textbox-with-the-result-for-a-particular-row/
0
 
Carl TawnSystems and Integration DeveloperCommented:
You also appear to be mixing SELECT and INSERT sql queries in one statement. You can't populate textboxes directly from a SQL statement if that is what you are attempting to do.

You need to pull the data back into a container (like a DataTable for example) and then populate your textboxes from that, either manually or using databinding.
0
 
dustockCommented:
You need a data adapter and like Carl and Eric said something to put it in like a datatable or dataset.  Try the code below to get a dataset and to place items in the dataset into text boxes.

        Dim daOle As OleDbDataAdapter
        Dim dsData As DataSet = New DataSet()

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Matthew\SkyDrive\Documents\WORK\main.mdb"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "Select (Inv_ID, Inv_CustomerName) FROM Invoices WHERE ID='" & TextBox4.Text & "' VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "')"
        cmd.CommandType = CommandType.Text
        daOle = New OleDbDataAdapter(cmd)
        daOle.Fill(dsData)

If dsData.Tables(0).Rows.Count > 0 Then
TextBox1.Text = dsData.Tables(0).Rows(0)(0).ToString()
TextBox2.Text = dsData.Tables(0).Rows(0)(1).ToString()
End If

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lincstechAuthor Commented:
I get the following error in the connection string

 
{"Syntax error (comma) in query expression '(Title, FirstName)'."}

Open in new window


     Dim daOle As OleDbDataAdapter
        Dim dsData As DataSet = New DataSet()
        Dim cmd As New OleDb.OleDbCommand

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Matthew\SkyDrive\Documents\WORK\main.mdb"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "Select (Title, FirstName) FROM Contacts WHERE CustomerID='" & TextBox1.Text & "' VALUES('" & TextBox2.Text & "','" & TextBox3.Text & "')"
        cmd.CommandType = CommandType.Text
        daOle = New OleDbDataAdapter(cmd)
        daOle.Fill(dsData)

        If dsData.Tables(0).Rows.Count > 0 Then
            TextBox1.Text = dsData.Tables(0).Rows(0)(0).ToString()
            TextBox2.Text = dsData.Tables(0).Rows(0)(1).ToString()
        End If

Open in new window

0
 
dustockCommented:
Nope that error is from your select statement.  Fix your statement, then try again.
0
 
Carl TawnSystems and Integration DeveloperCommented:
You don't need brackets for the column list in a SELECT statement. But the statement you have still isn't going to work because you are mixing syntax from both a SELECT and an INSERT into the same statement.

Just try your SELECT as:
cmd.CommandText = "Select Title, FirstName FROM Contacts WHERE CustomerID='" & TextBox1.Text & "'"

Open in new window

If CustomerID in your table is a numeric data type then you need to lose the apostrophes as well.
0
 
lincstechAuthor Commented:
I can't see where ?
0
 
lincstechAuthor Commented:
Disregard that last comment
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now