insert filename into SQL Table

insert filename into SQL Table

• eyalkz

  Hi Adamus

  Not exacly. I have two pages, the first will allow user to add details containing information a pet's details - Everything exept for a picture. (Table with fields such as Name, age, and FilePath where FilePath should contain the filename of the picture)

  This works as I'm using a Insert from a formview control. After user entered the details and click Insert it will ask if he wants to load a picture of pet. This will link to a second page where user can upload a picture.

  The phisical upload of the file works but I have no idea how to take the name of the file and add this into the sql database already containing the other information inserted from the first page.

  Any ideas

  
  

• frazi

  I'm guessing you're using Update instead of Insert. UpdateCommand="UPDATE [PetTable] SET [ImagePath] = @strFileName">

  Is this what you're trying to do InsertIntoCommand="INSERT INTO [PetTable] (ImagePath)

  Values( @strFileName)">

  or you're missing the WHERE clause...otherwise, you'll update every record in the table with ImagePath=@strFileName

  Just a guess

  Adamus Turner

  
  
  

• smaynard123

  I'm keeping you bussy tonight

  Using SQl Express and VS Express

   

  I have the following declared now but it gives me errors for "the bold" var

  Dim rowsAffected As Integer = 0

  Dim connectionString As String = "$ ConnectionStrings:ConnectionString1"

  Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection

  Dim connectionString1 As String = "Data Source=.\SQLEXPRESS;Catalog=ASPNETDB.MDF;Integrated Security=True;"

  Dim queryString As String = "INSERT INTO [PetTable] SET PetIdTextBox= " & _

  PetIdTextBox.Text & ", " & _

  "UserID=" & UserId.Text & ", '" & _

  "Type=" & Type.Text & ", " & _

  "colour=" & colour.Text & ", " & _

  "Date=" & Date.Value & ", '" & _

  "Location=" & Location.Text & ", '" & _

  "Text=" & Text.Text & ", '" & _

  "Status=" & Status.Text & ", '" & _

  "ImagePath=" & ImagePath.Value & "'"

   

  Error 1 Name 'UserId' is not declared. C:\Documents and Settings\Ewaldt\My Documents\Visual Studio 2005\WebSites\PetfinderProject\MemberPages\AddFound.aspx.vb 58 21 C:\...\PetfinderProject\
  Error 2 'Text' is not a member of 'System.Type'. C:\Documents and Settings\Ewaldt\My Documents\Visual Studio 2005\WebSites\PetfinderProject\MemberPages\AddFound.aspx.vb 59 19 C:\...\PetfinderProject\
  Error 3 Name 'colour' is not declared. C:\Documents and Settings\Ewaldt\My Documents\Visual Studio 2005\WebSites\PetfinderProject\MemberPages\AddFound.aspx.vb 60 21 C:\...\PetfinderProject\
  Error 4 'Value' is not a member of 'Date'. C:\Documents and Settings\Ewaldt\My Documents\Visual Studio 2005\WebSites\PetfinderProject\MemberPages\AddFound.aspx.vb 61 19 C:\...\PetfinderProject\
  Error 5 Name 'Location' is not declared. C:\Documents and Settings\Ewaldt\My Documents\Visual Studio 2005\WebSites\PetfinderProject\MemberPages\AddFound.aspx.vb 62 23 C:\...\PetfinderProject\
  Error 6 'Text' is not a member of 'Text'. C:\Documents and Settings\Ewaldt\My Documents\Visual Studio 2005\WebSites\PetfinderProject\MemberPages\AddFound.aspx.vb 63 19 C:\...\PetfinderProject\
  Error 7 Name 'Status' is not declared. C:\Documents and Settings\Ewaldt\My Documents\Visual Studio 2005\WebSites\PetfinderProject\MemberPages\AddFound.aspx.vb 64 21 C:\...\PetfinderProject\
  Error 8 Name 'ImagePath' is not declared. C:\Documents and Settings\Ewaldt\My Documents\Visual Studio 2005\WebSites\PetfinderProject\MemberPages\AddFound.aspx.vb 65 24 C:\...\PetfinderProject\
  

   

  
  

• diwakar_pal11

  Hi

  This is almost working (I think)

  I added the code just after

  Imports System.Data

   and before

  Partial Class MemberPages_AddFound

  Added the ConnectionString1

  But get 2 errors:

  Call MyInsert() gives       error Syntax error

  And

  Function MyInsert() As Integer       error Statement is not valid in a namespace.

   I changed the below line to INSERT

  Dim queryString As String = "INSERT [PetTable] SET PetIdTextBox= " & _

  
  

• SolTecLLC

  Hi

  The VB code should do something like this:

  First line will load file to my hard drive:

  oFile.PostedFile.SaveAs(strFilePath)

  After this a second line of code should update the DB field ImagePath with the filename:

  UpdateCommand="UPDATE [PetTable] SET [ImagePath] = @strFileName">

  
  

• szembek

  Add it here:

  Protected Sub InsertButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)

  Dim strFileName As String

  Dim strFilePath As String

  Dim strFolder As String

  Call MyInsert()

  -----------------------------------------------------------

  Place the function inside the class

  
  
  

• ihsanakin

  If you're connecting to SQL Server 2005 Express Edition, you'll need to use the following connection string:

  Dim connectionString As String = "Data Source=Server Name Here;Catalog=DBName Here;Integrated Security=True;"

  -->Datasource will look similar to this "TURNERS-WO7FPWF\SQLEXPRESS"

  If you have it installed just open it and copy the full server name. That is the datasource.

  Look also to see if it says "Windows Authentication." If it does, all you need is your database name and your connection string will look exactly like this:

  Dim connectionString As String = "Data Source=TURNERS-WO7FPWF\SQLEXPRESS;Catalog=MyDaterBase;Integrated Security=True;"

  That's it!

  Good luck,

  Adamus

   

  
  
  

• Jigar Lakhani

  Well this is progress...

  First of all, you changed the Update to Insert Into which changes the syntax entirely, but it was what I thought you wanted to do initially. (but add the update syntax to your personal library. I'm sure you'll use it later.)

  I rewriting the Insert Into syntax for you and will post it shortly.

  As a side note, it's much more efficient and practical to create stored procedures and just pass the field values through ado.net instead of hard coding the SQL and it's much more secure.

  At a later time, I can send you the sp_ approach to incorporate into your project.

  Posting again soon,

  Adamus

  
  
  

• Marcas

  Hi Adamus

  I think that will work but just have a problem with my connection string. Will sort that out and check.

  Thanks for all the help. It's worth a lot to me.

  Thanks

  Ewaldt

  
  

• Deurasian

  Ok ewaldt,

  These are the assumptions I'm making:

  1. You're carrying the information from page1 to page2 with either a cookie or through asp.sessions. (The information isn't getting flushed)

  2. The record you're trying to update already exists...(you've committed the record prior to this)

  3. Page2 selects the specific record, and only updates the filename

  4. Last, but not least, the field datatype accepts strings...(had to state the obvious)

  If all assumptions are true, could you reply with the present field value and desired field value Hypothetical examples are acceptable but a practical before and after scenario would be nice. :)

  
  
  

• greg_burns

  You'll have to hard code the connection string using the ado.net as follows:

  Dim connectionString As String = "Data Source=Server Name Here;Catalog=DBName Here;User ID=;Password=;"

  If the connection uses Windows Authentication use this connection string

  Dim connectionString As String = "Data Source=Server Name Here;Catalog=DBName Here;Integrated Security=True;"

  Let me know if I can be of further assistance.

  Adamus Turner

  
  
  

• Aaron Anderson

  Add this Function to the code. Make sure to fill in the connection string. Call this function in the first line of your Procedure before anything else. But before you do this, do you want to Update or Insert Into This will update ALL RECORDS IN THE TABLE!!! Be sure to add a "Where" clause to match the record to something.

  Call MyInsert()

  Function MyInsert() As Integer

  Dim rowsAffected As Integer = 0

  Dim connectionString As String = "$ ConnectionStrings:ConnectionString1"

  Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

  Dim queryString As String = "UPDATE [PetTable] SET  PetIdTextBox= " & _

  PetIdTextBox.Text & ", " & _

  "UserID=" & UserId.Text & ", '" & _

  "Type=" & Type.Text & ", " & _

  "colour=" & colour.Text & ", " & _

  "Date=" & Date.Value & ", " & _

  "Location=" & Location.Text & ", " & _

  "Text=" & Text.Text & ", " & _

  "Status=" & Status.Text & ", " & _

  "ImagePath=" & ImagePath.Value & "'"

  Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand

  dbCommand.CommandText = queryString

  dbCommand.Connection = dbConnection

  dbConnection.Open()

  rowsAffected = dbCommand.ExecuteNonQuery

  Return rowsAffected

  dbConnection.Close()

  End Function

  
  
  

• Fernando Ipar

  Hi Adamus

  I changed all around and only have one page now, both the information and the file details is entered but after the user enters the data and select picture to upload only the picture is uploded and the data is not inserted into the DB.

  Hope this makes more sence as I figured that I could not manage to pass information from page1 to page2 and back. As you can see I'm totally a newbeee at this..

  Thanks for all the help.

  I'm attaching the code below.

  AddFound.aspx code

  <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="AddFound.aspx.vb" Inherits="MemberPages_AddFound" title="Untitled Page" %>

  <asp:Content ID="Content1" ContentPlaceHolderID="LeftLinks" Runat="Server">

  </asp:Content>

  <asp:Content ID="Content2" ContentPlaceHolderID="Centre" Runat="Server">

  <form EncType="multipart/form-data" action="AddFound.aspx">

  PetId:

  <asp:TextBox ID="PetIdTextBox" runat="server" Text='<%# Bind("PetId") %>'></asp:TextBox><br />

  UserId:

  <asp:TextBox ID="UserIdTextBox" runat="server" Text='<%# Bind("UserId") %>'></asp:TextBox><br />

  Type:

  <asp:TextBox ID="TypeTextBox" runat="server" Text='<%# Bind("Type") %>'></asp:TextBox><br />

  colour:

  <asp:TextBox ID="colourTextBox" runat="server" Text='<%# Bind("colour") %>'></asp:TextBox><br />

  Date:

  <asp:TextBox ID="DateTextBox" runat="server" Text='<%# Bind("Date") %>'></asp:TextBox><br />

  Location:

  <asp:TextBox ID="LocationTextBox" runat="server" Text='<%# Bind("Location") %>'></asp:TextBox><br />

  Text:

  <asp:TextBox ID="TextTextBox" runat="server" Text='<%# Bind("Text") %>'></asp:TextBox><br />

  Status:

  <asp:TextBox ID="StatusTextBox" runat="server" Text='<%# Bind("Status") %>'></asp:TextBox><br />

  <asp:Label ID="ImageLabel" runat="server" Text="Picture"></asp:Label>

  <INPUT ID="oFile" type="file" runat="server"><br />

  <br />

  <asp:Button ID="InsertButton" runat="server" CausesValidation="True"

  Text="Upload" OnClick="InsertButton_Click" CommandName="Insert" ></asp:Button>

  <asp:Button ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"

  Text="Cancel"></asp:Button>

  <br />

  <br />

  <asp:Panel ID="frmConfirmation" Visible="False" Runat="server">

  <asp:Label id="lblUploadResult" Runat="server"/>

  </asp:Panel>

  &nbsp;

  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"

  ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" DeleteCommand="DELETE FROM [PetTable] WHERE [PetId] = @original_PetId AND [UserId] = @original_UserId AND [Type] = @original_Type AND [colour] = @original_colour AND [Date] = @original_Date AND [Location] = @original_Location AND [Text] = @original_Text AND [Status] = @original_Status AND [ImagePath] = @original_ImagePath"

  InsertCommand="INSERT INTO [PetTable] ([PetId], [UserId], [Type], [colour], [Date], [Location], [Text], [Status], [ImagePath]) VALUES (@PetId, @UserId, @Type, @colour, @Date, @Location, @Text, @Status, @ImagePath)"

  OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [PetTable]"

  UpdateCommand="UPDATE [PetTable] SET [UserId] = @UserId, [Type] = @Type, [colour] = @colour, [Date] = @Date, [Location] = @Location, [Text] = @Text, [Status] = @Status, [ImagePath] = @ImagePath WHERE [PetId] = @original_PetId AND [UserId] = @original_UserId AND [Type] = @original_Type AND [colour] = @original_colour AND [Date] = @original_Date AND [Location] = @original_Location AND [Text] = @original_Text AND [Status] = @original_Status AND [ImagePath] = @original_ImagePath">

  <InsertParameters>

  <asp:Parameter Name="PetId" Type="String" />

  <asp:Parameter Name="UserId" Type="String" />

  <asp:Parameter Name="Type" Type="String" />

  <asp:Parameter Name="colour" Type="String" />

  <asp:Parameter Name="Date" Type="DateTime" />

  <asp:Parameter Name="Location" Type="String" />

  <asp:Parameter Name="Text" Type="String" />

  <asp:Parameter Name="Status" Type="String" />

  <asp:Parameter Name="ImagePath" Type="String" />

  </InsertParameters>

  </asp:SqlDataSource>

  &nbsp;&nbsp;<br />

  <br />

  <br />

  &nbsp; &nbsp;&nbsp;

  </form>

  </asp:Content>

  <asp:Content ID="Content3" ContentPlaceHolderID="Rightlogin" Runat="Server">

  </asp:Content>

  *****************************************************************************

  AddFound.aspx.vb Code

  Imports System.IO

  Imports System

  Imports System.Data

  Partial Class MemberPages_AddFound

  Inherits System.Web.UI.Page

  Protected Sub InsertButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)

  Dim strFileName As String

  Dim strFilePath As String

  Dim strFolder As String

  strFolder = "/Photos/"

  'Get the name of the file that is posted.

  strFileName = oFile.PostedFile.FileName

  strFileName = Path.GetFileName(strFileName)

  'Create the directory if it does not exist.

  If (Not Directory.Exists(strFolder)) Then

  Directory.CreateDirectory(strFolder)

  End If

  'Save the uploaded file to the server.

  strFilePath = strFolder & strFileName

  If File.Exists(strFilePath) Then

  lblUploadResult.Text = strFileName & " already exists on the server!"

  Else

  oFile.PostedFile.SaveAs(strFilePath)

  lblUploadResult.Text = strFileName & " has been successfully uploaded."

  End If

  'Display the result of the upload.

  frmConfirmation.Visible = True

  End Sub

  End Class

  
  

• JoelSSIS

  Hi

  I used the below:

  Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")

  Looking at your code it's obviously incorrect!!!

  Need to change it to below as I'm also going to try using Form authentication later:

  Dim connectionString As String = "Data Source=Server Name Here;Catalog=DBName Here;User ID=;Password=;"

  And that's how you start learning SQL I suppose. ha ha.

  Thanks again!!

  
  
• insert filename into SQL Table