Having trouble adding a column using commandbuilder

Gilanor

New member
Joined
Sep 18, 2006
Messages
2
Programming Experience
1-3
Hi,

I've just started learning VB.NET and ADO.NET (yesterday afternoon) and have rather thrown myself in at the deep end. I've been able to battle through most problems so far but this one has me a little stuck.

I'm trying to read in some Strings from another class and check which of them are already column names in a table on my database. The ones that aren't I would like to add to the dataset and then update the database.

To do this I was under the impression that I could use a command builder that would write the SQL for me and then calling DataAdapter.Update would make it change the update command to whatever is appropriate.

I would very much appreciate it if someone could tell me what silly thing it is i've done this time. I'm sure it will be obvious!

Cheers
David

p.s. I would also like any constructive criticism of coding style since the only other OO language I use is Java.

VB.NET:
Dim ds As New DataSet, da As OleDb.OleDbDataAdapter
Dim modelList As New Collections.ArrayList
Dim q As Integer, modelName As String, exists As Boolean
Dim cb As OleDb.OleDbCommandBuilder
 
modelList = AddModel.modelList
 
da = New OleDb.OleDbDataAdapter(Constants.SQL_STRING + Constants.MODEL_LIST_STRING, dbCon)
 
 
 

Try [INDENT]dbCon.Open()[/INDENT][INDENT]da.Fill(ds, Constants.MODEL_LIST_STRING)[/INDENT][INDENT]cb = New OleDb.OleDbCommandBuilder(da)[/INDENT][INDENT]For Each modelName In modelList [INDENT]exists = False [INDENT]For q = 0 To (ds.Tables(Constants.MODEL_LIST_STRING).Columns.Count - 1) [INDENT]If (ds.Tables(Constants.MODEL_LIST_STRING).Columns(q).ColumnName = modelName) Then[/INDENT][INDENT]exists = True[/INDENT][INDENT]Exit For[/INDENT]End If 
 

[/INDENT]Next q 
 

If exists = False Then [INDENT]ds.Tables(Constants.MODEL_LIST_STRING).Columns.Add(modelName, System.Type.GetType("System.Boolean"))[/INDENT][INDENT]ds.Tables(Constants.MODEL_LIST_STRING).Rows(0).Item(modelName) = True[/INDENT]

[INDENT]da.Update(ds, Constants.MODEL_LIST_STRING)[/INDENT]End If 
 

[/INDENT]Next 
 

[/INDENT]Catch ex As Exception 

 
Finally
dbCon.Close()
End Try
 
Last edited by a moderator:
I'm confused. Youre reading in strings and checking which are existing columns. If any strings are not existing columns you want to issue a DDL statement like:

ALTER TABLE tblWhatever ADD COLUMN whatColumn


I'm jsut tryiong to ascertain whether youre confused over the difference between columns and rows
 
Yes thats right, I got it working without using the command builder but i was wondering if I could save myself having to hand write the query
 
I may be able to answer this in future; i havent looked a the command builder at the moment, but it may form a more elegant solution to the requirement I was given for my program to "be able to search the entire database based on text a user enters in any part of any visible field in any form of the program"

Fazzle wazzle gnash gnash about that one I can tell you..
 
Back
Top