Solved

SQL Select Statement

Posted on 2014-03-20
8
325 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

726 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