[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VB.Net SQL Insert Help

Posted on 2009-02-14
15
Medium Priority
?
613 Views
Last Modified: 2012-08-13
What is wrong with the simple code below? I know it is connecting because if I purposely misspell the column name, it will throw an exception. But when I type it in correctly, nothing happens. No data is inserted.
Dim conn As New SqlClient.SqlConnection("Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|Database1.mdf;Integrated Security=True;User Instance=True")
 
        Try
            Dim cmd As New SqlCommand 
            Dim params As SqlParameterCollection = cmd.Parameters
   
            cmd.Connection = conn 
            cmd.CommandType = CommandType.Text  
            conn.Open()  
            If conn.State = ConnectionState.Open Then
                cmd.CommandText = "INSERT INTO Test (Test1) VALUES ('Test')"
                cmd.ExecuteNonQuery()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)  
        End Try
        conn.Close() 'Close it

Open in new window

0
Comment
Question by:xterra
  • 8
  • 7
15 Comments
 
LVL 5

Expert Comment

by:Imperdonato
ID: 23643860
Try this:

Please note for VB.Net, this might be syntactically wrong somewhere, as I usually code in C#, but the general idea/ code remains the same.

Also, FYI, a conn.Close() call should always be in a finally block.

If still it doesnot work, try debugging it, and see if it executes the insert statement at all. Also, try running your insert sql in the Sql Management Console.

Hope that helps.

Dim conn As New SqlClient.SqlConnection("Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|Database1.mdf;Integrated Security=True;User Instance=True")
 
        Try
            Dim cmd As New SqlCommand = conn.CreateCommand()
            cmd.CommandType = CommandType.Text  
            cmd.CommandText = "INSERT INTO Test (Test1) VALUES ('Test')"
            conn.Open()  
            If conn.State = ConnectionState.Open Then                
                cmd.ExecuteNonQuery()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)  
        End Try
        conn.Close() 'Close it
 
   

Open in new window

0
 
LVL 1

Author Comment

by:xterra
ID: 23643887
Thanks for the response,
  A couple things.

 Just to make sure, I ran (as you said) the SQL query in the management console.. it says "1 Row Affected", so I know it works. But, alas, when I right click the table (on the solution explorer) and click "Show Table Data" it is still not there! It's not showing anything!

 Also, your code says "Cmd now defined", which is odd, because you *did* define it.
0
 
LVL 5

Expert Comment

by:Imperdonato
ID: 23643919
Sorry, I was being lazy earlier, and didn't run the code.

Please use this one:

Also, what do you mean you ran the insert statement on Management console, but couldn;t see the data? Try running it on Management Console, and do a "Select * from Test" there itself, to see if it is there. It should be.
If you still don't see the data from your "solution explorer" which I presume is the solution explorer in Visual Studio, that would mean you are connecting to a different database there.

Hope that helps.

Dim conn As New SqlClient.SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=Database1;Integrated Security=SSPI")
        Try
            Dim cmd As New SqlClient.SqlCommand
            cmd = conn.CreateCommand()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "INSERT INTO Test (Test1) VALUES ('Test')"
            conn.Open()
            If conn.State = ConnectionState.Open Then
                cmd.ExecuteNonQuery()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            conn.Close()
        End Try

Open in new window

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 1

Author Comment

by:xterra
ID: 23643982
Thanks for being patient. Tried the above code, but no luck. Allow me to be more specific:

I know for a fact I'm connecting to the right Db because if I run a "select" statement (with the code in post #1) , I can print out to the screen whatever data I want from the database.  But, when I run the above code for the insert statement, this is what happens:

The URL for the screen shot is here:
http://img238.imageshack.us/img238/7615/dberrorqu1.jpg

Again, no errors, and I know I'm connecting to the right database, too.

And I know my SQL statement is correct because  of the following screen shot:

http://img136.imageshack.us/img136/3914/dberror2zx6.jpg



Thanks...

0
 
LVL 5

Expert Comment

by:Imperdonato
ID: 23644027
That's weird. I ran the exact same code I posted above and it inserted data in the table just fine. Are you using the exact same code? And do you have Insert permissions on the database/ table? I doubt if that is the case though, but still, please confirm. What happens when you try select/update/ delete on the same table? When you do a select from you code on the same table, do you see results which you just inserted from Management Console?
0
 
LVL 1

Author Comment

by:xterra
ID: 23644075
Well, I deleted the table and did a new one (just incase). This time, when I manually did the insert, it went through. However, get this...I try to do it twice, and it just stops again! This is abso-freaking-lutely odd.

I don't know what else to say. You mentioned insert permissions, but I must have those already if I was able to do it once..

Perhaps in the mean time I should just try to create a new database all together?
0
 
LVL 1

Author Comment

by:xterra
ID: 23644090
Ok, as a test this is what I did  (VS 2005)

File -- New Project--Windows App

Added 'New SQL DB' to project.

I added a new table called "Users" with a column name "user", with a datatype of text.

I right clicked in the solution explorer bar, "New Query". I did a very simple insert:

INSERT INTO Users (user) VALUES ('Robert')

Result:

1 Row Affected.

When I clicked 'Show Table Data' it still shows up as NULL.

I really don't get it.
0
 
LVL 5

Accepted Solution

by:
Imperdonato earned 2000 total points
ID: 23644137
Ok. Follow the steps exactly:
1. Open Microsoft SQL Server Management Console. Run the following script:
USE [master]
GO
/****** Object:  Database [TestDatabase]    Script Date: 02/15/2009 14:33:30 ******/
CREATE DATABASE [TestDatabase] ON  PRIMARY
( NAME = N'TestDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDatabase.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'TestDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDatabase_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 COLLATE Latin1_General_CI_AI
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'TestDatabase', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TestDatabase].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [TestDatabase] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TestDatabase] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TestDatabase] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TestDatabase] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TestDatabase] SET ARITHABORT OFF
GO
ALTER DATABASE [TestDatabase] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TestDatabase] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [TestDatabase] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TestDatabase] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TestDatabase] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TestDatabase] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [TestDatabase] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TestDatabase] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TestDatabase] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TestDatabase] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TestDatabase] SET  ENABLE_BROKER
GO
ALTER DATABASE [TestDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TestDatabase] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TestDatabase] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TestDatabase] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TestDatabase] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TestDatabase] SET  READ_WRITE
GO
ALTER DATABASE [TestDatabase] SET RECOVERY SIMPLE
GO
ALTER DATABASE [TestDatabase] SET  MULTI_USER
GO
ALTER DATABASE [TestDatabase] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [TestDatabase] SET DB_CHAINING OFF


2. Now run this:
USE [TestDatabase]
GO
/****** Object:  Table [dbo].[Test]    Script Date: 02/15/2009 14:33:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Test](
    [Test1] [varchar](50) COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

3. Open Visual Studio. Add a new Console/ Windows app project.

Run the following code:

Dim conn As New SqlClient.SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=TestDatabase;Integrated Security=SSPI")
        Try
            Dim cmd As New SqlClient.SqlCommand
            cmd = conn.CreateCommand()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "INSERT INTO Test (Test1) VALUES ('Test')"
            conn.Open()
            If conn.State = ConnectionState.Open Then
                cmd.ExecuteNonQuery()
            End If
        Catch ex As Exception
            'MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            conn.Close()
        End Try

4. Go to the SQL Server Management Console, Select "TestDatabase", right click on it, and Select 'New Query'; run the following query:

Select * from Test

See if you get results?


If yes, that means in your code earlier, you were not using/ connecting to the same database. Also, what exactly you meant when you said " Added new SQL db to my project?" In your code you are connecting to Database1. Is this the same db?
0
 
LVL 1

Author Comment

by:xterra
ID: 23644217
Hey Imperdonato,
 I appreciate the time you're taking.  I ran the script, and yes I see results.  But the thing is, isn't the database that you created with that long script a "server" database? I.e., what I meant before in 'Adding a SQL DB to my project' is I thought that I could add a database to the project, meaning when I distribute it to clients, each client has their own database file on their system, so that they do no't have to connect remotely to mine.

What is your code doing? Is it creating a database server for remote connections?  Why is that you code can connect to a 'server db' but not a local one?

If you wouldn't mind, in this thread I have screen shots of everything I was doing so you can get a better idea...

http://vbcity.com/forums/topic.asp?tid=159808

Could you perhaps take a look at that? Thanks!
0
 
LVL 5

Expert Comment

by:Imperdonato
ID: 23644261
That code is not connecting to a "server" db. Its just connecting to the db, whose path is defined in the database. And since you are running it on your local machine, it's a local db only, right?
The database create script simply creates a database of name "SampleDatabase" and then a table called "Test".
The VB.Net code then just connects to this db (as it can to any other db, as long it is accessible), and inserts the value. And it can connect to any other db, as long as you keep changing the database path.

The usual way of doing this is to have your connection string defined in the config file, which you can change as and when needed. Also, your installer can create a local database on any client that you install you project to. This will also help with client who have a seperate database server, where you deployment and sql servers would be different.

Your installer should be able to create a database on the sql server the client specify during installation. (Be it local or any other server). If you try doing it from within your project, it would fail if the deployment server won't have a sql server installed, or the name of the sql server instance is different (and not sqlexpress, as hard coded in your connection string)

Hope that helps.

0
 
LVL 1

Author Comment

by:xterra
ID: 23644357
"If you try doing it from within your project, it would fail if the deployment server won't have a sql server installed, or the name of the sql server instance is different (and not sqlexpress, as hard coded in your connection string)"

Ok Thanks!..So essentially if I ever make an application that stores data to a DB the user MUST have sql server installed?
0
 
LVL 5

Expert Comment

by:Imperdonato
ID: 23644371
"So essentially if I ever make an application that stores data to a DB the user MUST have sql server installed? "
No. But the user must be able to provide you access to a (any) SQL Server (so that you are able to create a connection string, to connect to that). Otherwise, where else would you create your Sql database and table? Or where else would you access the table you need data from? A Sql database/ table can not exist without a Sql Server or a Sql Express installed.

0
 
LVL 1

Author Comment

by:xterra
ID: 23644403
Thanks. I think I have some more points I can donate to you I just need to ask a mod to do it since I can't..

Feel free to ignore this comment, as I know you already answered the question but I really am just confused now.

"No. But the user must be able to provide you access to a (any) SQL Server (so that you are able to create a connection string, to connect to that)"

But how would they provide access? I wondered this and Googled my way to this page:

http://msdn.microsoft.com/en-us/library/ms165716(SQL.90).aspx

That page says the user must have SQL Server Express installed...?

I seem to remember not having any issues with Vb6 and ODB.
0
 
LVL 5

Expert Comment

by:Imperdonato
ID: 23644449
But how would they provide access? I wondered this and Googled my way to this page:
To provide you access to that, all you will need is the server address, the Sql server instance name, the userName, password (username and password only if you're not using windows authentication- to construct a connection similar to the one you've written in your code, you wont need this) and the database name. (These are the things you will eventually use in your connection string).

That page says the user must have SQL Server Express installed...?
Yes. Either Sql Server Express edition or any of Sql Servers' full edition. Express edition is free, and if your database size/ users are limited, you can use that.

I seem to remember not having any issues with Vb6 and ODB.
I doubt. With Odb as well, you must have been connecting to some database only? That database has to be installed somewhere. (Or was it some other type of database, not Sql Server- like if it was a flat file where you were storing data, that won't need a similar installation of course? )

The bottomline is: If your application needs to connect to a SQL server database, that database has to be installed somewhere. And if your application gets installed at clients' location (ie, is not a centrally hosted server application), client has to provide you with the required credentials so that your application can find, and in turn connect to the database (or atleast to the Sql server, where you can then go ahead and create the database).
The credentials that they will need to provide would be the same as stated above, things which you'll need to create your connection string. They will also need to have a Sql Server/ Express installed somewhere of course.
0
 
LVL 1

Author Comment

by:xterra
ID: 23646324
That clears things up. Thank you VERY much.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month19 days, 18 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question