7 Replies - 10737 Views - Last Post: 22 January 2011 - 12:19 PM Rate Topic: -----

#1 peck  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 23-December 10

Adding New column to the Existing table

Posted 17 January 2011 - 05:59 AM

I wanted to add new column to the existing table in my database table Please help.in the form it has a textbox in which you can enter any name that you want you column to bear. all this will be at runtime
i try but it keep saying (format of the intitialization string does not conform to specification starting at index 0)
below is my code:

Private Sub Create_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Create.Click
Dim ConnString As String = "C:\Users\Peck\Documents\Visual Studio 2010\Projects\LegendaSystem\LegendaSystem\bin\Sample01.mdb"
Dim SqlString As String = "ALTER TABLE School ADD COLUMN" + TextBox1.Text.Trim() + "Text(20)"

Using con As New OleDbConnection(ConnString)
Using cmd As New OleDbCommand(SqlString, con)
con.Open()
cmd.ExecuteNonQuery()
End Using
End Using
End Sub

This post has been edited by peck: 17 January 2011 - 06:02 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Adding New column to the Existing table

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,199
  • Joined: 25-September 09

Re: Adding New column to the Existing table

Posted 17 January 2011 - 06:05 AM

You need to specify an engine that will control your connection

Check out http://www.connectionstrings.com for Access and it will help you create a valid connectionstring.

This post has been edited by CharlieMay: 17 January 2011 - 06:05 AM

Was This Post Helpful? 0
  • +
  • -

#3 peck  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 23-December 10

Re: Adding New column to the Existing table

Posted 17 January 2011 - 06:09 AM

View PostCharlieMay, on 17 January 2011 - 06:05 AM, said:

You need to specify an engine that will control your connection

Check out http://www.connectionstrings.com for Access and it will help you create a valid connectionstring.

thanks for responding, am using Microsoft access
Was This Post Helpful? 0
  • +
  • -

#4 peck  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 23-December 10

Re: Adding New column to the Existing table

Posted 17 January 2011 - 06:32 AM

View Postpeck, on 17 January 2011 - 06:09 AM, said:

View PostCharlieMay, on 17 January 2011 - 06:05 AM, said:

You need to specify an engine that will control your connection

Check out http://www.connectionstrings.com for Access and it will help you create a valid connectionstring.

thanks for responding, am using Microsoft access

now this is what i have done so far,
Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Peck\Documents\Visual Studio 2010\Projects\LegendaSystem\LegendaSystem\bin\Data\Sample01.mdb;Persist Security Info=True"

Dim SqlString As String = "ALTER TABLE Attendant ADD COLUMN" + TextBox1.Text.Trim() + "Text(20)"

Using con As New OleDbConnection(ConnString)
Using cmd As New OleDbCommand(SqlString, con)
con.Open()
cmd.ExecuteNonQuery()
End Using
End Using

but since error, and this is the error message (syntax error in the field definition)
Was This Post Helpful? 0
  • +
  • -

#5 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,199
  • Joined: 25-September 09

Re: Adding New column to the Existing table

Posted 17 January 2011 - 06:53 AM

The syntax is incorrect, here is an example of adding a single column

Quote

Syntax #1

To add a column to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD column_name column-definition;

For example:

ALTER TABLE supplier
ADD supplier_name varchar2(50);

This will add a column called supplier_name to the supplier table.


So I would try:
Dim SqlString As String = "ALTER TABLE Attendant ADD " & TextBox1.Text.Trim() & " Text(20)"


Notice that I removed the word COLUMN and added a space after ADD, I changed + to & for concatenating strings and I added a space before the Text(20) otherwise your string would have looked like
ALTER TABLE Attendant ADDsomecolumnnameText(20)

When it should have looked like
ALTER TABLE Attendant ADD somecolumnname Text(20)

This post has been edited by CharlieMay: 17 January 2011 - 06:54 AM

Was This Post Helpful? 0
  • +
  • -

#6 peck  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 23-December 10

Re: Adding New column to the Existing table

Posted 17 January 2011 - 07:00 AM

View PostCharlieMay, on 17 January 2011 - 06:53 AM, said:

The syntax is incorrect, here is an example of adding a single column

Quote

Syntax #1

To add a column to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD column_name column-definition;

For example:

ALTER TABLE supplier
ADD supplier_name varchar2(50);

This will add a column called supplier_name to the supplier table.


So I would try:
Dim SqlString As String = "ALTER TABLE Attendant ADD " & TextBox1.Text.Trim() & " Text(20)"


Notice that I removed the word COLUMN and added a space after ADD, I changed + to & for concatenating strings and I added a space before the Text(20) otherwise your string would have looked like
ALTER TABLE Attendant ADDsomecolumnnameText(20)

When it should have looked like
ALTER TABLE Attendant ADD somecolumnname Text(20)



WOW I NEVER BELIEVE THIS, SOMETHING THT TOOK ME 2 WEEKS AND YOU UNDER FEW MINS, HONESTLY WHEN U KNOW SOMETHING YOU HAVE THE POWER,,,,,KUDOS TO YOU...I REALLY WANNA SAY A VERY BIG THANK YOU TO YOU MY BROTHER, IT WORKS PERFECTLY
Was This Post Helpful? 0
  • +
  • -

#7 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,199
  • Joined: 25-September 09

Re: Adding New column to the Existing table

Posted 17 January 2011 - 07:21 AM

Peck,
Just remember when you're building strings for these types of things, you can always use MessageBox.Show(stringvariable) to review it and make sure that you have spaces where they need to be. What you display in the messagebox will be how it is sent and MUST meet the syntax. This applies for SQL statements as well, it is too easy to miss that when you're looking at the string with all the concatenations.
Was This Post Helpful? 0
  • +
  • -

#8 peck  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 23-December 10

Re: Adding New column to the Existing table

Posted 22 January 2011 - 12:19 PM

View PostCharlieMay, on 17 January 2011 - 07:21 AM, said:

Peck,
Just remember when you're building strings for these types of things, you can always use MessageBox.Show(stringvariable) to review it and make sure that you have spaces where they need to be. What you display in the messagebox will be how it is sent and MUST meet the syntax. This applies for SQL statements as well, it is too easy to miss that when you're looking at the string with all the concatenations.

hello sir sorry i came back, i need some help , what i want to do is that, if a field already exists in a table, a message box should appear to notify the user that such field already exist. and same thing goes to if you want to delete a field and that field doesn't exist

This post has been edited by peck: 22 January 2011 - 12:20 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1