Inserting new values in database Asp .Net

Question

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"/>

score 2 · Answer 1 · answered Mar 06 '12 at 17:31

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.

answered Mar 06 '12 at 17:31

Gambrinus

2,140
16
26

I've changed the Catalog to o2database and the Integrated Security=false. Same error occurred – Alvin Pulido Mar 06 '12 at 17:38
Have you got the success even a single time to connect to database? I meant, is it every time issue or not ? – Pankaj Mar 06 '12 at 17:46
@PankajGarg not yet.whenever I click the button the error will display – Alvin Pulido Mar 06 '12 at 17:58

score 2 · Accepted Answer · answered Mar 06 '12 at 18:12

2

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.

answered Mar 06 '12 at 18:12

Zo Has

12,599
22
87
149

+1. Can you explain further where I can see the connection string in server Explorer properties ? – Pankaj Mar 07 '12 at 03:28
@Pankaj just go to server explorer->Connect to database->(Select your datasource SQL Server etc)->Type in server name (YourMachineName\SQLServerInstance)->Select database from Connect to database dropdown->Click Test Connection->If it says Succeeded->Click Advanced...->On the popup window, Copy all text before the OK button (its the connection string). This would definitely work if you succeeded with the connection. (also, it is easier to set & check parameters of connection string from here) Damien. – Zo Has Mar 07 '12 at 04:52

score 1 · Answer 3 · answered Mar 06 '12 at 17:35

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

Pankaj · Answer 4 · 2012-03-07T03:53:33.037

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

Inserting new values in database Asp .Net

4 Answers4

Sample Stored Procedure

Sample code in C Sharp

Sample Code in VB.Net

Linked