Thanks for the suggestions, but for whatever reason, I still can't fill the combos. After poojavb's suggestion, the code I have is:

'load hotel dropdown
Dim cmd As OleDbCommand = New OleDbCommand("SELECT Hotel FROM Hotels ORDER BY Hotel", con)
rdr = cmd.ExecuteReader
While rdr.Read
End While

'load restaurant dropdown
cmd = New OleDbCommand("SELECT Rest_Name FROM Restaurants ORDER BY Rest_Name", con)
rdr = cmd.ExecuteReader
While rdr.Read
End While

I don't see any reason why this shouldn't work. I've double checked to make sure I don't inadvertantly clear the combos elsewhere in the code, but I'm okay there. I honestly don't know what else to try. Is there anything else I can try?

Hi all,
I have a form on my program, and I have two comboboxes that I want to fill with data from columns in my database. I've tried three or four different ways that I've found searching around the web, but none of them seem to be working. They don't produce errors, but they don't load the combos either. For clarity, I've tried to bind them to the database, I've tried using datasets, and tried datatables. The code I've been working with is below. Can anyone offer a suggestion to get this working for me? Thanks!


'load hotel dropdown
Dim cmd As OleDbCommand = New OleDbCommand("SELECT Hotel FROM Hotels ORDER BY Hotel", con)
Dim dt As New DataTable()
Dim da As New OleDbDataAdapter()
da.SelectCommand = cmd

Dim r As DataRow
For Each r In dt.Rows

'load restaurant dropdown
cmd = New OleDbCommand("SELECT Rest_Name FROM Restaurants ORDER BY Rest_Name", con)
Dim dt1 As New DataTable()
Dim da1 As New OleDbDataAdapter()
da1.SelectCommand = cmd

Dim r1 As DataRow
For Each r1 In dt.Rows

Moving where I added the parameters fixed the error. Thanks!

the data type of the Business_Phone field is Text in Access, which I want since I'm going to save the number pre-formatted (i.e. "(123) 456-7890").

I'm entering in the following data:
Company = Test
Business Phone = (123) 456-7890

and getting the following insert string:
INSERT INTO [Customer] (Company, Business_Phone, Home_Phone, Mobile_Phone, Fax) VALUES (@CMPNY, @BUSPH, @HOMPH, @MBLPH, @FAXNM)

Which doesn't make sense, since it should only add the fields to the string if there is data entered. Is there something wrong with the way I'm checking if there is text entered in the textboxes?

In my program, I have a section to insert information about a customer into an Access database. I get the error below at the .ExecuteNonQuery.

Message: System.Data.OleDb.OleDbException (0x80004005): Field 'Customer.Business_Phone' cannot be a zero-length string.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

The problem I'm having, is that the Business_Phone does have a value in it. I added the msgbox to be sure, and it displays "9876543210", just like I entered. The database field is set to not allow zero length strings, because I want this to be a required field. What I don't understand is how it's getting that @BUSPH is a zero length string, when I set it to the value of the textbox, which is the same value that I print to the msgbox so I know that there is a value there. Also, I have a check before I add the field to the insert string to make sure I am not adding null values, so in theory it shouldn't be included in the input string if it is zero length, right? My code section is below. Any ideas what could be causing this error?

Dim custstr As String = "INSERT INTO [Customer] ("
Dim valuestr As String = ") VALUES ("

If txt_company.Text.ToString <> vbNullString Then
    custstr += "Company"
    valuestr += "@CMPNY"
End If
If txt_lastname.Text.ToString <> vbNullString Then
    custstr += ", Last_Name"
    valuestr += ", @LSTNM"
End If ...

Check the properties of First_Name and your other Text fields. Does it allow zero-length strings? You may need to replace the '' values with NULL.

Yes, I set them so they allow zero-length strings. The values I'm inserting are taken from text fields in my vb program, so the only way to set those values to null (that I can see) is a really long if/else statement, which I'd like to avoid if I can.

Thanks for the quick responses.

Nutster - I actually pulled the whole insert statement from Access, because I was having this problem before when I was using VALUES(). The Insert -> Select statement ran successfully in Access 2010, so I figured it should work in VB.

AndreRet - I removed the brackets from the field name and removed Last_Name, and now the error just moved to point at First_Name. The value of custstr is below. I'm thinking it has something to do with the null values. I've set up Access so that it should accept a 0 length string, but I'm thinking there must be some other option I missed to allow a null field.

INSERT INTO [Customer] (Company, First_Name, Business_Phone, Home_Phone, Mobile_Phone, Fax, Email) SELECT 'test', '', '1234657890', '', '', '', ''

Hi all,
In the program I'm working on, I am making a form that submits data to an Access 2007 database. I can connect to the database fine and run a select query, but when I try an insert query I get an error: "statement contains the following unknown field name: 'Last_Name' (see details below). This error occurs at my ExecuteReader command. The field is present in my table, and I've double checked for extra spaces and the like. Any ideas what could be causing this error?

The relevent code is:

Dim custstr As String = "INSERT INTO [Customer] ([Company], [Last_Name], [First_Name], [Business_Phone]," & _  
    " [Home_Phone], [Mobile_Phone], [Fax], [Email]) SELECT '" & txt_company.Text.ToString & _
    "', '" & txt_lastname.Text.ToString & "', '" & txt_firstname.Text.ToString & "', '" & _
    txt_busphone.Text.ToString & "', '" & txt_homephone.Text.ToString & "', '" & _
    txt_mobilephone.Text.ToString & "', '" & txt_fax.Text.ToString & "', '" & txt_email.Text.ToString & "'"

MsgBox("Company: " & txt_company.Text & " Last_Name: " & txt_lastname.Text)

cmd = New OleDbCommand(custstr, con)
rdr = cmd.ExecuteReader()  'error points here
If Not (rdr.Read()) Then
    MsgBox("Could not insert record into database, please try again or contact system admin.\n\n",
          MsgBoxStyle.OkOnly, "Error:\n\n")
End If

Error message and Customer table:

The sample case you have given is correct, yes. Only for the output I want
view_1, view
view_4, view
instead. Being the name and the taggable_type because I plan on doing this with multiple types of objects.

I'm not sure what you mean by data script, but the table structures are below. It may be important to note that I am querying a database that is in the backend of an application, so I have no power to change any of the structure or data contained within, all I can do is query the data.

The views table structure that is relavent to this query:
id (serial number), name (char varying(255))

Note: A view represents an object. All objects have a similar table structure.

Taggings table structure that is relavent:
taggable_id (int), taggable_type(char varying(255))

Notes: the taggable id relates to the id field in the view table.
A "tagging" relates an object id to the tag id that it is tagged with when it is published. For example, a View with confidential information would have a confidential tag attached to it. The view's id and the confidential tag's id are both be linked together in the Taggings table.
The taggable type field will specify if the object tagged is a View, Workbook, or Datasource, which are all valid Tableau objects.

select as object, t.taggable_type as Type
FROM views v left outer join taggings t on ( = t.taggable_id
and t.taggable_type = 'View')
where t.taggable_id is null

This is a good idea, however this solution returns 76 less records than expected. I can't figure out why I can't get the correct records to be returned.

I am developing an sql query to use to grab information from a postgres db. The scenario is as follows: the database I am grabbing information is the backend postgres database of an application (Tableau). My objective is to pull all of the workbooks, views, and datasources (considered objects) that are untagged in the database. I am using 4 tables - taggings (contains all tagged objects related to the object by the objects id), views (contains all views and their ids), workbooks (contains all workbooks and their ids), and datasources (contains all datasources and their ids).

I have already successfully created queries to pull all of the tagged objects from the database (they are the inner select's in the not exists statements), but getting the untagged objects is proving more difficult. The code I currently have works successfully for the datasources, but the workbooks section returns one extra record than it should and the views section returns 19 extra records than it should. I am using not exists to get all of the objects that are not tagged, and union to combine the separate queries for views, datasources, and workbooks. I cannot spot the error, so please let me know if you can.

I am testing the query using pgAdmin.

SELECT distinct as Object, 
t.taggable_type as Type
FROM workbooks w, taggings t
WHERE t.taggable_type = 'Workbook'
(SELECT distinct
FROM taggings
WHERE taggings.taggable_type = 'Workbook' 
AND = taggings.taggable_id)


SELECT distinct as Object, 
t.taggable_type ...