I am trying to add a row to my database and although it does not give me an
error message, it is not adding it to my database. I am using C# and SQL
2005 Express editions.
When I look into my Database Explorer I do not see the row added. What are
the steps that have to be taken to create a database and then add rows to
it? Do I have to do anything in the Solution Explorer? I am using a Window
Console application as I do not need a form as I am trying to access the
database directly without a dataset. Here is my code below.
TIA
Roy
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
namespace testing
{
class Class1
{
// private System.Data.DataSet dataSet;
[STAThread]
static void Main(string[] args)
{
int lnSOBN = 900;
int lnBN1 = 188;
string strConnection = @."Data
Source=. \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\Database1.mdf;Integrate
d
Security=True;Connect Timeout=30;User Instance=True";
SqlConnection conn = new SqlConnection(strConnection);
string strInsert = "INSERT INTO tblDatabase1 (SOBN, BN1) VALUES (@.par0,
@.par1) ";
SqlCommand cmd = new SqlCommand(strInsert, conn);
SqlParameter parameter1 = new SqlParameter("@.par0", SqlDbType.Int, 32);
SqlParameter parameter2 = new SqlParameter("@.par1", SqlDbType.Int, 32);
parameter1.Value = lnSOBN;
parameter2.Value = lnBN1;
cmd.Parameters.Add(parameter1);
cmd.Parameters.Add(parameter2);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
}
TIA
RoyHi Roy,
> I am trying to add a row to my database and although it does not give me
> an error message, it is not adding it to my database. I am using C# and
> SQL 2005 Express editions.
> When I look into my Database Explorer I do not see the row added.
After you insert the record, do you refresh the query that displays your
table results? From the Database Explorer, you locate the table in your
database, right click on it and select Show Table Data. When this opens up,
it shows a static view of the data, any records added to the table while
this view is open will not be reflected unless you re-run the underlying
query. You can do this by pressing the bold, red exclaimation mark in the
toolbar, or pressing CTRL+R.
Regards,
Jeff Papiez
jeff . papiez @. microsoft . com
SQL Server 2005 Upgrade Advisor Team
http://blogs.msdn.com/jpapiez
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.|||Hi Jeff,
I have tried that but it does not work? Do I have to do anything with the
Solution Explorer. It's pretty frustrating because I have tried so many
things and nothing seems to be working.
Any suggestions.
Thanks
Roy
"Jeff Papiez [MSFT]" <jpapiez@.online.microsoft.com> wrote in message
news:O9AeJ0T6FHA.1276@.TK2MSFTNGP09.phx.gbl...
> Hi Roy,
>
> After you insert the record, do you refresh the query that displays your
> table results? From the Database Explorer, you locate the table in your
> database, right click on it and select Show Table Data. When this opens
> up, it shows a static view of the data, any records added to the table
> while this view is open will not be reflected unless you re-run the
> underlying query. You can do this by pressing the bold, red exclaimation
> mark in the toolbar, or pressing CTRL+R.
>
> --
> Regards,
> Jeff Papiez
> jeff . papiez @. microsoft . com
> SQL Server 2005 Upgrade Advisor Team
> http://blogs.msdn.com/jpapiez
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>|||Hi Roy,
No you shouldn't have to do anything with Solution Explorer, it should "just
work". If you write a query to select all results from your table, using the
same connection string, and write it out to the console, do you still see no
results? If no error is raised after executing the insert, the data is being
sent somewhere successfully.
I can feel your frustration... this has happened to me before where I was
expecting the data to show up in one database, but it was going to
another... oops :)
Jeff.
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:xA3ef.5204$wc.488038@.wagner.videotron.net...
> Hi Jeff,
> I have tried that but it does not work? Do I have to do anything with the
> Solution Explorer. It's pretty frustrating because I have tried so many
> things and nothing seems to be working.
> Any suggestions.
> Thanks
> Roy
> "Jeff Papiez [MSFT]" <jpapiez@.online.microsoft.com> wrote in message
> news:O9AeJ0T6FHA.1276@.TK2MSFTNGP09.phx.gbl...
>|||Hi Jeff,
I just want to make sure of something in the strConnection string that I
have.
Data Source = .\SQLEXPRESS is this not the name of the server?
AttachDbFilename = |DataDirectory|Database1.mdf what directory or database
does this point to.
Can I use something like AttachDbFilename = C:\MyDB\Database1.mdf instead?
string strConnection = @."Data
Source=. \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\Database1.mdf;Integrate
d
Security=True;Connect Timeout=30;User Instance=True";
How do I write a query to the console? I don't know how?
Thanks
Roy
"Jeff Papiez [MSFT]" <jpapiez@.online.microsoft.com> wrote in message
news:urY$3KU6FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi Roy,
> No you shouldn't have to do anything with Solution Explorer, it should
> "just work". If you write a query to select all results from your table,
> using the same connection string, and write it out to the console, do you
> still see no results? If no error is raised after executing the insert,
> the data is being sent somewhere successfully.
> I can feel your frustration... this has happened to me before where I was
> expecting the data to show up in one database, but it was going to
> another... oops :)
> Jeff.
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:xA3ef.5204$wc.488038@.wagner.videotron.net...
>|||Hi Jeff,
I just noticed something. You might be right about it being written
somewhere else. When I go look into the directory where I have my
Database1.mdf I also see a sub-directory called bin\Debug where there is
another copy of my Database1.mdf which seems to change the date modified
when I run my program. On the other hand the other one does not change time.
How do I open or see the one in the bin\Debug directory?
Roy
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:ZO2ef.5200$wc.484253@.wagner.videotron.net...
> Hi,
> I am trying to add a row to my database and although it does not give me
> an error message, it is not adding it to my database. I am using C# and
> SQL 2005 Express editions.
> When I look into my Database Explorer I do not see the row added. What are
> the steps that have to be taken to create a database and then add rows to
> it? Do I have to do anything in the Solution Explorer? I am using a Window
> Console application as I do not need a form as I am trying to access the
> database directly without a dataset. Here is my code below.
> TIA
> Roy
> using System;
> using System.Data;
> using System.Data.Common;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.Text;
>
> namespace testing
> {
> class Class1
> {
> // private System.Data.DataSet dataSet;
> [STAThread]
>
> static void Main(string[] args)
> {
> int lnSOBN = 900;
> int lnBN1 = 188;
>
> string strConnection = @."Data
> Source=. \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\Database1.mdf;Integra
ted
> Security=True;Connect Timeout=30;User Instance=True";
> SqlConnection conn = new SqlConnection(strConnection);
> string strInsert = "INSERT INTO tblDatabase1 (SOBN, BN1) VALUES (@.par0,
> @.par1) ";
> SqlCommand cmd = new SqlCommand(strInsert, conn);
> SqlParameter parameter1 = new SqlParameter("@.par0", SqlDbType.Int, 32);
> SqlParameter parameter2 = new SqlParameter("@.par1", SqlDbType.Int, 32);
> parameter1.Value = lnSOBN;
> parameter2.Value = lnBN1;
> cmd.Parameters.Add(parameter1);
> cmd.Parameters.Add(parameter2);
> cmd.Connection.Open();
> cmd.ExecuteNonQuery();
> cmd.Connection.Close();
> }
> }
> }
> TIA
> Roy
>|||Finally it worked.:) I just changed my connection on the Database Explorer
to the other location on bin\Debug and it wrote the row. Funny though when I
run it a couple of times it just rewrites over the same record over and over
again without append any other records. So I only have 1 record at a time.
BTW why is it that VS creates 2 copies of my database?
Thanks
Roy
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:ZO2ef.5200$wc.484253@.wagner.videotron.net...
> Hi,
> I am trying to add a row to my database and although it does not give me
> an error message, it is not adding it to my database. I am using C# and
> SQL 2005 Express editions.
> When I look into my Database Explorer I do not see the row added. What are
> the steps that have to be taken to create a database and then add rows to
> it? Do I have to do anything in the Solution Explorer? I am using a Window
> Console application as I do not need a form as I am trying to access the
> database directly without a dataset. Here is my code below.
> TIA
> Roy
> using System;
> using System.Data;
> using System.Data.Common;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.Text;
>
> namespace testing
> {
> class Class1
> {
> // private System.Data.DataSet dataSet;
> [STAThread]
>
> static void Main(string[] args)
> {
> int lnSOBN = 900;
> int lnBN1 = 188;
>
> string strConnection = @."Data
> Source=. \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\Database1.mdf;Integra
ted
> Security=True;Connect Timeout=30;User Instance=True";
> SqlConnection conn = new SqlConnection(strConnection);
> string strInsert = "INSERT INTO tblDatabase1 (SOBN, BN1) VALUES (@.par0,
> @.par1) ";
> SqlCommand cmd = new SqlCommand(strInsert, conn);
> SqlParameter parameter1 = new SqlParameter("@.par0", SqlDbType.Int, 32);
> SqlParameter parameter2 = new SqlParameter("@.par1", SqlDbType.Int, 32);
> parameter1.Value = lnSOBN;
> parameter2.Value = lnBN1;
> cmd.Parameters.Add(parameter1);
> cmd.Parameters.Add(parameter2);
> cmd.Connection.Open();
> cmd.ExecuteNonQuery();
> cmd.Connection.Close();
> }
> }
> }
> TIA
> Roy
>|||Hi Jeff,
Resolved everything as I created a new solution and database and everything
now is ok. Somehow, it must have created a 2nd database and that is probably
why I had all the problems.
Thanks again
Roy
"Jeff Papiez [MSFT]" <jpapiez@.online.microsoft.com> wrote in message
news:urY$3KU6FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi Roy,
> No you shouldn't have to do anything with Solution Explorer, it should
> "just work". If you write a query to select all results from your table,
> using the same connection string, and write it out to the console, do you
> still see no results? If no error is raised after executing the insert,
> the data is being sent somewhere successfully.
> I can feel your frustration... this has happened to me before where I was
> expecting the data to show up in one database, but it was going to
> another... oops :)
> Jeff.
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:xA3ef.5204$wc.488038@.wagner.videotron.net...
>|||Hi Roy,
> Finally it worked.:) I just changed my connection on the Database Explorer
> to the other location on bin\Debug and it wrote the row. Funny though when
> I run it a couple of times it just rewrites over the same record over and
> over again without append any other records. So I only have 1 record at a
> time.
> BTW why is it that VS creates 2 copies of my database?
I'm glad you've gotten it working, finally :)
With SQL Express we support this new feature where you can automatically
attach a database, as noted in the connection string you are using. When you
add a database to a project (Project -> Add New Item -> SQL Database) the
default option is to copy the database on every build to the output
directory, in your case the bin\debug directory. In addition, a new database
connection is added to the Database Explorer which points to the database in
your project directory. Anything you add to the database through the Data
Explorer will be added to the database file in your project folder. When you
build your project, a copy of the database overwrites the database files in
your build directory (bin\debug).
Are you
yet? I am! :)If this isn't the behavior you're after there are a few things you can do.
1. Add a new data connection. In the Database file name (new or existing)
text box, give the path to the new file as a path on your filesystem that's
outside the project, something like "C:\MSSQL\TestDB". You'll be prompted to
create the new database file. After it's done you can copy the connection
string from the properties grid for the new connection.
2. Create a new database on the server using sqlcmd. Assuming your Express
instance is SQLEXPRESS:
Start->Run: cmd.exe
In the console type:
sqlcmd -E -S.\SQLEXPRESS
1> create database TESTDB
2> go
The connection string to this database will look like the following:
Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Integrated
Security=True;User Instance=True
3. Select the database in the Solution Explorer, press F4 to bring up the
Properties window.
Set the "Copy to Output Directory" = Do not copy
Then you can change your connection back to the path of your project
folder. Now, all changes you make to your database will be in the same file.
Regards,
Jeff Papiez
jeff . papiez @. microsoft . com
SQL Server 2005 Upgrade Advisor Team
http://blogs.msdn.com/jpapiez
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.|||Thanks Jeff,
I am a little bit
, but there must be a good reason for it to make acopy which I presently do not fully understand as I am a newby.
Roy
"Jeff Papiez [MSFT]" <jpapiez@.online.microsoft.com> wrote in message
news:uBoKBXV6FHA.252@.TK2MSFTNGP15.phx.gbl...
> Hi Roy,
>
> I'm glad you've gotten it working, finally :)
> With SQL Express we support this new feature where you can automatically
> attach a database, as noted in the connection string you are using. When
> you add a database to a project (Project -> Add New Item -> SQL Database)
> the default option is to copy the database on every build to the output
> directory, in your case the bin\debug directory. In addition, a new
> database connection is added to the Database Explorer which points to the
> database in your project directory. Anything you add to the database
> through the Data Explorer will be added to the database file in your
> project folder. When you build your project, a copy of the database
> overwrites the database files in your build directory (bin\debug).
> Are you
yet? I am! :)> If this isn't the behavior you're after there are a few things you can do.
> 1. Add a new data connection. In the Database file name (new or existing)
> text box, give the path to the new file as a path on your filesystem
> that's outside the project, something like "C:\MSSQL\TestDB". You'll be
> prompted to create the new database file. After it's done you can copy the
> connection string from the properties grid for the new connection.
> 2. Create a new database on the server using sqlcmd. Assuming your Express
> instance is SQLEXPRESS:
> Start->Run: cmd.exe
> In the console type:
> sqlcmd -E -S.\SQLEXPRESS
> 1> create database TESTDB
> 2> go
> The connection string to this database will look like the following:
> Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Integrated
> Security=True;User Instance=True
> 3. Select the database in the Solution Explorer, press F4 to bring up the
> Properties window.
> Set the "Copy to Output Directory" = Do not copy
> Then you can change your connection back to the path of your project
> folder. Now, all changes you make to your database will be in the same
> file.
>
> --
> Regards,
> Jeff Papiez
> jeff . papiez @. microsoft . com
> SQL Server 2005 Upgrade Advisor Team
> http://blogs.msdn.com/jpapiez
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>
