Inserting new values in database Asp .Net

I have a code for inserting values in ASP.net using vb. I’m having problem with my code says login failed, cannot open database.

Dim struser, strpass, stremail As String
            struser = TextBox1.Text
            strpass = TextBox2.Text
            stremail = TextBox4.Text
            'declaring sql connection. 

            Dim thisConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("DatabaseConnection").ConnectionString)

            'Create Command object
            Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand()


            Try
                ' Open Connection
                thisConnection.Open()

                Dim strcommand As String
                strcommand = "Insert into Account (Username,Password, Email) values ('" + struser + "','" + strpass + "','" + stremail + "')"

                Dim sqlcomm As New SqlCommand(strcommand, thisConnection)
                Dim o As String = sqlcomm.ExecuteNonQuery()


            Catch ex As SqlException
                ' Display error
                MsgBox(ex.ToString())
            Finally
                ' Close Connection
                MsgBox("Success")
                thisConnection.Close()


            End Try

connection string:

<add name="DatabaseConnection" connectionString="Data Source=.SQLEXPRESS;Initial Catalog=o2database.mdf;Integrated Security=SSPI" providerName="System.Data.SqlClient"/>

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

1) Initial catalog must be name of the schema you are accessing

2) You may use ‘Server Explorer’ & try to just connect to the database
from there. Once succeeded just copy the connection string from
properties & replace your current connection string.

Method 2

I think your Initial Catalog is wrong. your pointing at a file you should use here the database-name. I guess o2database.

if this is not the case – you are using SSPI to login – maybe your user does not have the permission to do so.

another thing is that your web-application is not configured in the iis to pass on your domain-user credentials – so it cannot work using SSPI to login.

Method 3

your code is right, the problem is with your sql server configuration, you cannot access sql server with integrated security, so, you need to configure it to work fine, take a look at this post:

http://support.microsoft.com/kb/914277

if you’re in IIS, you should able the remote access on sql server too.

Look how to access using SSI:

http://msdn.microsoft.com/en-us/library/aa984236(v=vs.71).aspx

http://msdn.microsoft.com/pt-br/library/bsz5788z.aspx

Method 4

Warning : You are giving rise to SQL Injection in your code.

Sample Stored Procedure

Create Proc ProcedureName
@UserName Varchar(50),
@Password Varchar(50),
@Email Varchar(50)
As
SET NOCOUNT ON
SET XACT_ABORT ON

Begin Try
    Begin Tran
        Insert into Account (Username,Password, Email)
        Values(@UserName, @Password, @Email)
    Commit Tran 
End Try

Begin Catch
    Rollback Tran
End Catch

Sample code in C Sharp

private void InsertRecord()
{
    String struser = string.Empty, strpass = string.Empty, stremail = string.Empty;
    using (SqlConnection con = new SqlConnection("Your Connection String"))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = con;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "Your Stored Procedure name";
            SqlParameter[] param = new SqlParameter[3];
            param[0].Direction = System.Data.ParameterDirection.Input;
            param[0].ParameterName = "UserName";
            param[0].Value = struser;
            cmd.Parameters.Add(param[0]);

            param[1].Direction = System.Data.ParameterDirection.Input;
            param[1].ParameterName = "Password";
            param[1].Value = strpass;
            cmd.Parameters.Add(param[1]);

            param[2].Direction = System.Data.ParameterDirection.Input;
            param[2].ParameterName = "Email";
            param[2].Value = stremail;
            cmd.Parameters.Add(param[2]);

            cmd.ExecuteNonQuery();
        }
    }
}

Sample Code in VB.Net

Private Sub InsertRecord()
    Dim struser As [String] = String.Empty, strpass As [String] = String.Empty, stremail As [String] = String.Empty
    Using con As New SqlConnection("Your Connection String")
        Using cmd As New SqlCommand()
            cmd.Connection = con
            cmd.CommandType = System.Data.CommandType.StoredProcedure
            cmd.CommandText = "Your Stored Procedure name"
            Dim param As SqlParameter() = New SqlParameter(2) {}
            param(0).Direction = System.Data.ParameterDirection.Input
            param(0).ParameterName = "UserName"
            param(0).Value = struser
            cmd.Parameters.Add(param(0))

            param(1).Direction = System.Data.ParameterDirection.Input
            param(1).ParameterName = "Password"
            param(1).Value = strpass
            cmd.Parameters.Add(param(1))

            param(2).Direction = System.Data.ParameterDirection.Input
            param(2).ParameterName = "Email"
            param(2).Value = stremail
            cmd.Parameters.Add(param(2))

            cmd.ExecuteNonQuery()
        End Using
    End Using
End Sub

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0