Database will connect but no data can be inserted

Question

So I am using a Local Database (.mdf) in Visual Studio 2015. I had a hard time connecting to the database at first but now it is connecting as far as I can tell. I am using INSERT to add data to the database and I also have a variable telling me if any rows were affected once the user clicks the 'save' button but every time it says successful and I check the database and there is nothing there.

I have searched Google a bunch of times about this answer and everyone is saying to use SQL Server Management Studio to create the database and give the database a logical name. I did do that, but when I try to add that database to visual studio it says it gives me an error code 40; and i even detached the database from the server and tried adding just the database and it says that it does not have the correct permissions for it. So I do not know what to do!!

Here is what I got so far and hopefully I can get some help!

Lets start off with the database:

Database name: dbo.WebLogins

[Id]                 INT            IDENTITY  (1,1) NOT NULL,
[Nickname]           VARCHAR (50)   NULL,
[WebsiteURL]         VARCHAR (200)  NULL,
[WebsiteUsername]    VARCHAR (50)   NULL,
[WebsitePassword]    VARCHAR (50)   NULL,
[WebsiteEmail]       VARCHAR (50)   NULL,
[WebsiteAbout]       VARCHAR (200)  NULL,
[WebsiteRating]      INT            NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)

Now I have some textboxes and a trackbar and two buttons:

txtNickname
txtURL
txtUsername
txtEmail
txtPassword
txtPasswordAgain (This just verifies the first password box, doesn't go in db)
txtAbout
tbRating
btnSave
btnCancel

This is my App.Config file:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFileEntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
   <add name="PassLock.Properties.Settings.dbMainConnectionString" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\dbMain.mdf;Initial Catalog=dbMain;Integrated Security=True" providerName="System.Data.SqlClient" />
// NOTE: I ADDED INITIAL CATALOG=dbMain.mdf
  </connectionStrings>
  <startup>
   <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <entityFramework>
   <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
   <parameters>
    <parameter value="mssqllocaldb"/>
  </parameters>
 </defaultConnectionFactory>
<providers>
 <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>
</configuration>

Now the code for the application:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.Runtime.InteropServices;

namespace PassLock
{
     public partial class frmAddSite : Form
     {
          string connString = ConfigurationManager.ConnectionStrings["dbMainConnectionString"].ConnectionString;

          public frmAddSite()
          {
               InitializeComponent();
          }

          private void btnSave_Click(object sender, EventArgs e)
          {
               txtNickname.Enabled = false;
               txtPassword.Enabled = false;
               txtUrl.Enabled = false;
               txtUsername.Enabled = false;
               txtEmail.Enabled = false;
               txtAbout.Enabled = false;
               tbRating.Enabled = false;
               cbShowPassword.Enabled = false;
               btnSave.Enabled = false;
               btnCancel.Enabled = true;

               using (SqlConnection con = new SqlConnection(connString))
               {
                    SqlCommand cmd = new SqlCommand("INSERT INTO WebLogins(Nickname, WebsiteURL, WebsiteUsername, WebsitePassword, WebsiteEmail, WebsiteAbout, WebsiteRating) VALUES (@nickname, @websiteURL, @websiteUsername, @websitePassword, @websiteEmail, @websiteRating)", con);
                     cmd.CommandType = CommandType.Text;
                     cmd.Connection = con;

                     cmd.Parameters.AddWithValue("@nickname", txtNickname.Text);
                     cmd.Parameters.AddWithValue("@websiteURL", txtURL.Text);
                     cmd.Parameters.AddWithValue("@websiteUsername", txtUsername.Text);
                     cmd.Parameters.AddWithValue("@websitePassword", txtPassword.Text);
                     cmd.Parameters.AddWithValue("@websiteEmail", txtEmail.Text);
                     cmd.Parameters.AddWithValue("@websiteAbout", txtAbout.Text);

                     int rating = 0;
                     if (int.TryParse(tbRating.Text, out rating))
                     {
                          cmd.Parameters.Add("@websiteRating", SqlDbType.Int).Value = rating;
                     }
                     else
                     {
                          cmd.Parameters.Add("@websiteRating", SqlDbType.Int).Value = DBNull.Value;
                     }

                     cmd.ExecuteNonQuery();

                     int iAffectedRecords = cmd.ExecuteNonQuery();
                     if (iAffectedRecords > 0)
                     {
                          MessageBox.Show("successful");
                     }
                     else
                     {
                          MessageBox.Show("Unsuccessful");
                     }
               }
         }
           private void btnCancel_Click(object sender, EventArgs e)
           {
                SqlConnection con = new SqlConnection(connString);

                if (con.State == ConnectionState.Open)
                {
                     con.Close();
                     this.Close();
                }
                else
                {
                      this.Close();
                }
          }

          private void frmAddSite_Load(object sender, EventArgs e)
          {
               SqlConnection con = new SqlConnection(connString);
               con.Open();
          }
     }
}

Hopefully everything was copied right, the braces should be right, I am not recieving any errors in visual studio so everything looks good. I have the following in my Settings.Settings file:

Name Type Scope Value dbMainConnectionString (ConnectionString) Application Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\dbMain.mdf;Initial Catalog=dbMain;Integrated Security=True

I also have a DataSet that is called dbMainDataSet.xsd that has the following properties:

Build Action:             None
Copy to:                  Do not copy
Custom Tool:              MSDataSetGenerator
Custom Tool Namespace:   
File Name:                dbMainDataSet.xsd
Full Path:                C:\Users\Andy\Documents\Visual Studio 2015\Projects\PassLock\PassLock\dbMainDataSet.xsd

And my database (dbMain.mdf) has the following properties:

Build Action:             None
Copy to:                  Copy always
Custom Tool:              
Custom Tool Namespace:    
Full Name:                dbMain.mdf
Full Path:                C:\Users\Andy\Documents\Visual Studio 2015\Projects\PassLock\PassLock\dbMain.mdf

I don't know if this matters or not but when I go into the Advanced Properties this is what I get:

Advanced:
     MultipleActiveResult     False
     Network Library
     Packet Size              8000
     Transaction Binding      Implicit Unbind
     Type System Version      Latest
Connection Resiliency
     ConnectRetryCount        1
     ConnectRetryInterval     10
Context
     Application Name         .NET SqlClient Data Provider
     Workstation ID
Initialization
     ApplicationIntent        ReadWrite
     Asynchronous Process     False
     Connect Timeout          15
     Current Language   
Pooling
     Enlist                   True
     Load Balance Timeout     0
     Max Pool Size            100
     Min Pool Size            0
     Pooling                  True 
Replication
     Replication              False
Security
     Encrypt                  False
     Integrated Security      True
     Password
     Persist Security Info    False
     TrustServerCertificate   False
     User ID
Source
     AttachDbFilename         C:\Users\Andy\Documents\Visual Studio 2015\Projects\PassLock\PassLock\dbMain.mdf
     Context Connection       False
     Data Source              (LocalDB)\MSSQLLocalDB
     Failover Partner
     Initial Catalog
     MultiSubnetFailover      False
     User Instance            False

And under Modify Connection it is:

Data Source: Microsoft SQL Server Database File (SqlClient)
Database File Name (new or existing): Visual Studio 2015\Projects\PassLock\PassLock\dbMain.mdf
Log On to the server: Use Windows Authentication

When I test the connection it is successful

In the packages.config file:

<?xml version="1.0" encoding="utf-8"?>
<packages>
     <package id="EntityFramework" version="6.1.2-beta1" targetFramework="net45" />
</packages>

And last but not least the Program.cs file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace PassLock
{
     static class Program
     {
          [STAThread]
          static void Main()
          {
               Application.EnableVisualStyles();
               Application.SetCompatibleTextRenderingDefault(false);
               Application.Run(new frmMain());
          }
     }
}

I am also using a theme on my application, but the textboxes are the normal textboxes that come with visual studio.

I am not sure if you needed all this information but I have seen a bunch of posts where people say they need more information so I gave you it all and hopefully someone can help!

How your connString looks? Are you able to connect with same credentials in SSMS? — Rahul Singh
– Rahul Singh, Commented Jan 17, 2015 at 3:47
First, get rid of that try/catch/throw/finally and replace it by putting the connection into a using block. — John Saunders
– John Saunders, Commented Jan 17, 2015 at 5:33
Second, you didn't get an error, you got an exception. Please post the full exception, including stack trace and any inner exceptions. — John Saunders
– John Saunders, Commented Jan 17, 2015 at 5:34
I just did the code again and ran it, and rather than grabbing the connection string from the app.config I grabbed it from the settings.setting file, and it seemed to connect because I didn't get any problems. I entered information into the textboxes and when I checked the database there was nothing added. Not sure what I am doing wrong here. I don't know why I didn't have a connection problem this time, I am so lost! — Andy
– Andy, Commented Jan 17, 2015 at 16:57

Shell · Accepted Answer · 2015-01-20 04:49:21Z

1

you can also store your credential information in local drive instead of database. But, make sure the information must be encrypted otherwise anybody can hack your credential information. you can split the information into multiple part and store it in different file. how it will be more secure and there is less possibility to hack your system.

In your exception it seems authentication issue. please provide more detail about the exception.

EDITED: You were saying that the application can't connect the sql server. But, the error is saying you are assigning invalid type parameter value. As per your error message the problem may be in your last parameter. you are assigning string value in integer datatype parameter. you should convert the value into integer datatype. if the textbox is empty or null then the parameter values should be DBNull.Value.

You can do like this.

int rating = 0;
if (int.TryParse(tbRating.Text, out rating))
    cmd.Parameters.AddWithValue("@websiteRating", rating);
else
    cmd.Parameters.AddWithValue("@websiteRating", DbNull.Value);

or

int rating = 0;
if (int.TryParse(tbRating.Text, out rating))
    cmd.Parameters.Add("@websiteRating", SqlDbType.Int).Value = rating;
else
    cmd.Parameters.Add("@websiteRating", SqlDbType.Int).Value = DBNull.Value;

int.TryParse method will try to convert string variable into integer datatype. if the value is empty or having non-numeric character then it will fail to convert then the condition will return false.

int iAffectedRecords = cmd.ExecuteNonQuery();
if (iAffectedRecords > 0)
{
     //Record successfully saved.
}

If you get value more than 0 in iAffectedRecords variable than it means the record is inserted or updated in the database.

edited Jan 20, 2015 at 4:49

answered Jan 17, 2015 at 5:28

Shell

6,86911 gold badges43 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Andy Over a year ago

I think that what you are saying would be the best thing to do in my case, I can't get the database to work! Is there a good tutorial that you know of? I am going to search for it and see what I can find, just to see what my options are.

Shell Over a year ago

@Andy I have edited my answer please take a look. You were saying that application could not connect the server. but, the error is displaying you are assigning incorrect parameter type value.

Andy Over a year ago

you are the best! No error happend during it but I checked the database and there isn't any data in there. Will data be added to the database if I am using debugging? Also, when the user clicks the save button I have all of the textboxes and buttons beside cancel set to enable=false; is there any way to make sure that the database saved the data and if it did enable the textboxes again or show a message or something? I wonder if my stored procedure is messed up and making it not work now, ah never ends eh!

Shell Over a year ago

@Andy return the value from ExecuteNonQuery() method. If the record is inserted or updated then it will return the number of affected rows. check my updated answer.

Andy Over a year ago

I did exactly what you said to do, and it always says it was successful and then I check the database and there is still no data in there! Could you please check the source I added to my updated answer and see if there is something wrong...I am so confused and frustrated! And thank you for all of your help :) I really appreciate it

terary · Accepted Answer · 2015-01-17 03:29:42Z

0

Try using a regular connection string with your credentials. Make sure the credentials work. Get your code working the stick that string in App.config

answered Jan 17, 2015 at 3:29

terary

1,13815 silver badges31 bronze badges

1 Comment

Andy Over a year ago

With a local service based database (i think that is what they are called) it is a .mdf file, I did not set up any credentials. If I use a different type of database in VS would it store it on the users computer?

<add name="PassLock.Properties.Settings.dbMainConnectionString" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\dbMain.mdf; Integrated Security=True" providerName="System.Data.SqlClient" />

Does this look like it will work or is there something I need to change? Also, is there something else in the code that needs to be put in place?

Collectives™ on Stack Overflow

Database will connect but no data can be inserted

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related