Solved

SQL Select Statement

Posted on 2014-03-20
8
323 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 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