1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

ODBC Error 3146 on rs.update

Discussion in 'MS Access' started by sharon7, Feb 23, 2012.

  1. sharon7

    sharon7 New Member

    Joined:
    Feb 23, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Hi,
    Using MS Access, I have a form that is based on a table called Motion_Imagery. In the VBA of this code I want to take two fields on the form and add the data in those two fields to the last record that was added to table Online_Books. When I run my code I get the error when it gets to rs.update, here is my code:

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("dbo_Filestream_Files", dbOpenDynaset, dbSeeChanges)

    rs.MoveLast

    rs.Edit
    rs![Prefix_CTRL_NBR] = Me.Prefix_CTRL_NBR
    rs![CTRL_ID] = Me.CTRL_NBR
    rs.Update


    Can you tell me what the problem is? Is this possible to do?
    Thanks,
    Sharon
     
  2. sharon7

    sharon7 New Member

    Joined:
    Feb 23, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Here is my code followed by my C# code. The error I'm getting is ODBC 3146 on the rs.Update

    MS ACCESS:
    Code:
    Public Sub Load_Files_Click()
       Dim strSQL As String
       Dim intCntr As Integer
       Dim db As Database
       Dim rs As Recordset
       Dim FileStream
       
       On Error GoTo Err_Prefix_Ctrl_Nbr
       
       FileStream = Shell("C:\Development\GEOINT\GEOINT\bin\Release\GEOINT.EXE",
    1)
       
       Set db = CurrentDb()
       Set rs = db.OpenRecordset("dbo_Filestream_Files", dbOpenDynaset,
    dbSeeChanges)
         
       rs.MoveLast
           
       rs.Edit
       rs![Prefix_CTRL_NBR] = Me.Prefix_CTRL_NBR
       rs![CTRL_ID] = Me.CTRL_NBR
       rs.Update
       
    Exit_Prefix_CTRL_NBR:
       rs.Close
       Set rs = Nothing
       Exit Sub
       
    Err_Prefix_Ctrl_Nbr:
           If Err = 3146 Then
               MsgBox "Error Number: " & Err & " - " & Err.Description
               Resume Exit_Prefix_CTRL_NBR
           End If
           
    End Sub
    
    C#:
    Code:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.IO;
    using System.Runtime.InteropServices;
    using Microsoft.Win32.SafeHandles;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    
    
    namespace GEOINT
    {
        public partial class frmForm : Form
        {
            public frmForm()
            {
                InitializeComponent();
            }
            //Insert using Filestream, file into SQL Server Table
            private void btnInsert_Click(object sender, EventArgs e)
            {
                OpenFileDialog openFileDlg = new OpenFileDialog();
                openFileDlg.InitialDirectory = Directory.GetCurrentDirectory();
                if (openFileDlg.ShowDialog() == DialogResult.OK)
                {
                    FileInfo fi = new FileInfo(openFileDlg.FileName);
                    FileStream fs = new FileStream(fi.FullName, FileMode.Open, FileAccess.Read);
                    BinaryReader rdr = new BinaryReader(fs);
                    byte[] fileData = rdr.ReadBytes((int)fs.Length);
                    rdr.Close();
                    fs.Close();
    
                    // Row_Guid_Col_ID is the key for the document
                    // fData is the actual picture broken down into binary format
                    // fName is the name of the document
    
                    string cs = "";
    
                    if (comboBox1.SelectedIndex == 0)
                    {
                        cs = @"server=JITC-PC\GEOINT;database=DEV_GEOINT;integrated security=SSPI";
                    } 
                    else if (comboBox1.SelectedIndex == 1)
                    {    
                      cs = @"server=JITC-PC\GEOINT;database=DEV_GEOINT;integrated security=SSPI";
                    }
                    using (SqlConnection con = new SqlConnection(cs))
                    {
                        con.Open();
                        string sql = "INSERT INTO Filestream_Files (Row_Guid_Col_ID, fData, fName) VALUES (default, @fData, @fName)";
                        SqlCommand cmd = new SqlCommand(sql, con);
                        cmd.Parameters.Add("@fData", SqlDbType.Image, fileData.Length).Value = fileData;
                        cmd.Parameters.Add("@fName", SqlDbType.NVarChar).Value = fi.Name;
                        cmd.ExecuteNonQuery();
    
                        con.Close();
                    }
                    MessageBox.Show(fi.FullName, "Selected Document/Picture Inserted!", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            private void btnExit_Click(object sender, EventArgs e)
            {
                this.Close();
            }
    
            private void frmForm_Load(object sender, EventArgs e)
            {
            }
     
    Last edited by a moderator: Mar 1, 2012

Share This Page