regarding database data inserting problem

Question

2.00/5 (1 vote)

See more:

My Problem is that when I run the application from localhost, It insert the data to remote sql database successfully. But when insert the data from the live site, it does not insert the data into SQL server what i have to do plz help
i am using following connection string(star is used for security purpose)
<add name="DefaultConnection" connectionstring="Server=mssql2008.********.co.in;Database=***_dbs;Uid=******_db_user;Password=*************" providername="System.Data.SqlClient">
and code is

VB

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If FileUpload1.PostedFile IsNot Nothing Then
            Try
                Dim FileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
                'Save files to disk 
                FileUpload1.SaveAs(Server.MapPath("~/Uploads/" & FileName))

                Dim result As Boolean = addDataUsingAdepter(lastid + 1, TextBox1.Text, "~/Uploads/" + FileName, TextBox2.Text, DropDownList1.Text)
                If (result = True) Then
                    Label9.ForeColor = Drawing.Color.Green
                    Label9.Text = "Product Added Successfully"
                    TextBox1.Text = ""
                    TextBox2.Text = ""
                    DropDownList1.SelectedIndex = 0
                Else
                    Label9.ForeColor = Drawing.Color.Red
                    Label9.Text = "Sorry An Error Occured"
                End If
            Catch ex As Exception
            End Try
        End If
    End Sub

function is......

VB

Public Shared Function addDataUsingAdepter(id As Integer, Name As String, photoFilePath As String, Description As String, type As String) As Boolean
        Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
        Dim conn As SqlConnection
        Dim adptr As SqlDataAdapter
        Dim dt As String = Now.Day.ToString + "/" + Now.Month.ToString + "/" + Now.Year.ToString + " " + Now.Hour.ToString + ":" + Now.Minute.ToString + ":" + Now.Second.ToString

        Dim query As String = "insert into Products values('" & id & "','" & Name & "','" & photoFilePath & "','" & Description & "','" & dt & "','" & type & "')"
        Try
            conn = New SqlConnection(ConnectionString)
            conn.Open()
            adptr = New SqlDataAdapter
            adptr.InsertCommand = New SqlCommand(query, conn)
            adptr.InsertCommand.ExecuteNonQuery()
            Return True
            conn.Close()
        Catch ex As Exception

            MsgBox(ex.Message)
            Return False
        End Try

    End Function

Posted 8-May-15 7:09am

bhagawan sahay

Updated 8-May-15 7:33am

PIEBALDconsult

v3

Add a Solution

Comments

PIEBALDconsult 8-May-15 13:36pm

First off, do not use string concatenation to form SQL statements -- use parameterized statements.
That is not a reasonable use of a DataAdapter.
Why do you return before closing the connection?
Also, learn to use ToString on a DateTime.

ZurdoDev 8-May-15 14:50pm

Give the error. I'm sure it explains exactly why it is not working.

bhagawan sahay 9-May-15 0:14am

you were right here is the error:
System.UnauthorizedAccessException: Access to the path 'C:\HostingSpaces\vmtindus\vmtindustry.co.in\wwwroot\Uploads\chaff-cutter.jpeg' is denied. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode) at System.Web.HttpPostedFile.SaveAs(String filename) at System.Web.UI.WebControls.FileUpload.SaveAs(String filename) at VMTAdmin_AddProduct.Button1_Click(Object sender, EventArgs e) in C:\HostingSpaces\vmtindus\vmtindustry.co.in\wwwroot\Admin\AddProduct.aspx.vb:line 24

3 solutions

Solution 1

Start by fixing the SQL Injection[^] vulnerability in your code:

VB.NET

Public Shared Function addDataUsingAdepter(id As Integer, Name As String, photoFilePath As String, Description As String, type As String) As Boolean
    Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
    Dim query As String = "insert into Products values (@id, @Name, @photoFilePath, @Description, @dt, @type)"
    
    Try
        Using conn As New SqlConnection(ConnectionString)
            Using cmd As New SqlCommand(query, conn)
                cmd.Parameters.AddWithValue("@id", id)
                cmd.Parameters.AddWithValue("@Name", Name)
                cmd.Parameters.AddWithValue("@photoFilePath", photoFilePath)
                cmd.Parameters.AddWithValue("@dt", DateTime.Now)
                cmd.Parameters.AddWithValue("@type", type)
                
                conn.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using
        
        Return True
        
    Catch ex As Exception
        MsgBox(ex.ToString())
        Return False
    End Try
End Function

That will almost certainly fix the issue. If not, you'll need to post the details of the error you're getting.

Posted 8-May-15 8:41am

Richard Deeming

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sujee1 · Accepted Answer · 2015-05-09T00:15:00

Solution 2

You have to change your folder 'Uploads' security permissions for the your user to 'Full control' on your server(live).

Just right click on the folder- Properties- Security Tab- Edit- Set Users Access Permissions.

I hope it'll work for you.

Posted 9-May-15 0:15am

Sujee1

ZurdoDev · Accepted Answer · 2015-05-09T01:13:00

Based on the error you provided in comments the problem has nothing to do with SQL. However, you will want to change your SQL as Solution 1 mentions so that you do not have issues with Sql Injection.

And, as Solution 2 mentions, you have a permissions issue. Look at the identity of the application pool in IIS and that user needs permissions to create files in your uploads folder. Or, change the identity to a user that can.