Solved

SQL Select Statement

Posted on 2014-03-20
8
324 Views
Last Modified: 2014-04-06
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
Comment
Question by:lincstech
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39942051
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39942055
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
 
LVL 9

Accepted Solution

by:
dustock earned 500 total points
ID: 39942146
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:lincstech
ID: 39942271
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
 
LVL 9

Expert Comment

by:dustock
ID: 39942285
Nope that error is from your select statement.  Fix your statement, then try again.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39942344
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
 

Author Comment

by:lincstech
ID: 39942362
I can't see where ?
0
 

Author Comment

by:lincstech
ID: 39942365
Disregard that last comment
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question