Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Select Statement

Posted on 2014-03-20
8
Medium Priority
?
335 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…
Suggested Courses

972 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