I added a column to a table, but, I can't see it.
-
I ran the code below that adds a column to an SQL table. I refreshed Data Sources, but, the new column does not appear. I refreshed Server Explorer and the new column does not appear. I tried running the code again and I got: "Column names in each table must be unique. Column name 'CaseNbr' in table 'Patient' is specified more than once." so the column did actually get added, but, I can't see it. Imports System.Data.SqlClient Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim sqlConnection As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Patient.mdf;Integrated Security=True;User Instance=True") Dim str As String = "ALTER TABLE Patient ADD CaseNbr Varchar(50)" Dim command As New System.Data.SqlClient.SqlCommand(str, sqlConnection) sqlConnection.Open() command.ExecuteNonQuery() sqlConnection.Close() Close() End Sub End Class
-
I ran the code below that adds a column to an SQL table. I refreshed Data Sources, but, the new column does not appear. I refreshed Server Explorer and the new column does not appear. I tried running the code again and I got: "Column names in each table must be unique. Column name 'CaseNbr' in table 'Patient' is specified more than once." so the column did actually get added, but, I can't see it. Imports System.Data.SqlClient Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim sqlConnection As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Patient.mdf;Integrated Security=True;User Instance=True") Dim str As String = "ALTER TABLE Patient ADD CaseNbr Varchar(50)" Dim command As New System.Data.SqlClient.SqlCommand(str, sqlConnection) sqlConnection.Open() command.ExecuteNonQuery() sqlConnection.Close() Close() End Sub End Class
I Think Ur Code is Correct if u have any confusion then please review this Code thanks Dim cn As New SqlClient.SqlConnection cn.ConnectionString = "Data Source=ssi-SIQBAL;Initial Catalog=Dummy;integrated Security=SSPI" cn.Open() Dim cmd As New SqlClient.SqlCommand Dim str As String str = "ALTER TABLE TBL1 ADD HOME varchar(500)" cmd.Connection = cn cmd.CommandText = str cmd.ExecuteNonQuery()
-
I ran the code below that adds a column to an SQL table. I refreshed Data Sources, but, the new column does not appear. I refreshed Server Explorer and the new column does not appear. I tried running the code again and I got: "Column names in each table must be unique. Column name 'CaseNbr' in table 'Patient' is specified more than once." so the column did actually get added, but, I can't see it. Imports System.Data.SqlClient Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim sqlConnection As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Patient.mdf;Integrated Security=True;User Instance=True") Dim str As String = "ALTER TABLE Patient ADD CaseNbr Varchar(50)" Dim command As New System.Data.SqlClient.SqlCommand(str, sqlConnection) sqlConnection.Open() command.ExecuteNonQuery() sqlConnection.Close() Close() End Sub End Class
Is the MDF file listed in your project in Solution Explorer? The problem usually occurs because the .NDF file is being copied to your Release or Debug folders along with your app's .EXE. The Server Explorer is looking at one version of the database (in your project folder) and your code is looking at the other copy of your database in the bin\Release or bin\Debug folder. Wanna see? Just open your project folder and search all the subfolders for any files with the extension MDF.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
Is the MDF file listed in your project in Solution Explorer? The problem usually occurs because the .NDF file is being copied to your Release or Debug folders along with your app's .EXE. The Server Explorer is looking at one version of the database (in your project folder) and your code is looking at the other copy of your database in the bin\Release or bin\Debug folder. Wanna see? Just open your project folder and search all the subfolders for any files with the extension MDF.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
That was the problem. Another question: Why is it that in some projects, the .mdf file is in bin\Release and in other projects, the .mdf file is in the bin\Debug folder?
It'll be in either depending on which build configuration you use when you hit, well, Build.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
It'll be in either depending on which build configuration you use when you hit, well, Build.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007OK, I deleted the Patient.mdf in Server Explorer. Then I opened a New Connection and browsed to the .mdf file under the output directory (\bin\debug). Now I can see the new column in Server Explorer. But, how do I get it to appear in Data Sources of Solution Explorer?
-
OK, I deleted the Patient.mdf in Server Explorer. Then I opened a New Connection and browsed to the .mdf file under the output directory (\bin\debug). Now I can see the new column in Server Explorer. But, how do I get it to appear in Data Sources of Solution Explorer?
You don't want to do that. You just have to understand that the Server Explorer will look at the copy of the database in the Project folder and your running code will look at the copy in the bin\ folders. Why? Because when you build the project, by default, the database in the project folder will be copied to the bin\ folders every time you build the project. This will overwrite the bin\ folder copy, and any changes your code makes to it. If this is a permanent change you want to make to the project folder copy, either use the SQL Server Management Studio to make the change, or the Server Explorer, or make the change with your code, then copy the bin\folder version up to the project folder.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007