I need to select all the records in a table, loop through them one by one, calculating some new field data, and then write the new data back to the same table. Here is the basic structure of what I've come up with:
<CODE>
SqlCmd = SqlConn.CreateCommand
SqlStatement = "SELECT ProductID, Name FROM tblProducts"
SqlCmd.CommandText = SqlStatement
SqlRdr = SqlCmd.ExecuteReader
If SqlRdr.HasRows Then
While SqlRdr.Read
If SqlRdr.FieldCount > 0 Then
...
SqlWriteCmd = SqlConn.CreateCommand
SqlStatement = "UPDATE tblProducts SET Name = '" & NewName & "' WHERE ProductID = " & CStr(ProductID)
SqlWriteCmd.CommandText = SqlStatement
SqlWriteCmd.ExecuteNonQuery()
SqlWriteCmd = Nothing
End If
End While
End If
SqlRdr.Close()
SqlCmd = Nothing
</CODE>
I get an error that tells me to close out the Reader before trying to execute the write query. But I can't close it out for the loop to work properly. So I assume that there must be another way to do this simple task, but I'm so new to all of this that I need some help! Thanks!
I am new to database programming. What I want to do is have a database on a clients PC to use as data storage. This program will connect to a program running on our server (via TCP) which will store this information in another database. The clients database will only be accessed by my program - so I think that I don't need to register it with SQLEXPRESS. The servers database will probably need to be so another program can access it.
The problem I was having was writing and reading to a database that I created inside Visual Studio 2005. As I see it, there could be three issues - reading, writing, or setup of the database. To find out this issue I downloaded the Northwind database. The code below returns column 0 row 0 of the Customers table.
Code Snippet
NORTHWNDDataSet northwindDataSet = new NORTHWNDDataSet();
NORTHWNDDataSetTableAdapters.CustomersTableAdapter customersTableAdapter = new NORTHWNDDataSetTableAdapters.CustomersTableAdapter();
This code returns "ALFKI". Which is what is in col 0 row 0 (This can be checked by right clicking on the Customers table in NorthWNDdataset.xsd and selecting preview data).
Next is writing. This is where I have the issues. The method below is one way that I tryed.
I'm trying to read a byte array of an image datatype from sql server, and then to put this in another field in the database. I get a byte array, but somehow the image doesn't get into the db well with the sql parameters. Does anyone have an idea how to tackle this problem?
Halo, I am a bit new to this Please can someone help me, I would like to write a file(Any type) to a SQL database like a attached document(s) for the current record and be able to detatch the document when needed. I use VB.NET for a ASP.NET app. I basicly would like to attach documents to a piece of equipment may it be any kind and if the user views the equipment he will be able to detatch the documents for that piece of equipment and open it with the correct software. PLEASE HELP!!!!!!!
It looks like its not possible to both read and write the same variable from a script using the conventional Me.Variables.<variable> syntax.
I can only assign a variable as Readonly or ReadWrite and not both. If I assign it ReadOnly I can only access it in the PreExecute subroutine. If I assign it ReadWrite I can only access it in the PostExecute subroutine (in fact doesn't this just make it WriteOnly in fact?). So I can only either read in or read out a variable using this syntax, noth both. Is this right?
So the read and write a variable to a script, the VariableDispenser approach is the only option to use. Is this right? and is it documented somewhere that this is how to use variables in scripts. Thanks.
Hi, I have a data structure called 'Quote' which contains a number of different variables and controls ranging from text boxes, check boxes and radio buttons, i need to be able to read and write this from a database.
First I think a description of my overall project is needed:
Project Description I have been given a brief that basically says: i have to create a programmed solution in VB to solve a problem. This problem can be anything we like, and I personally have chosen to create a program that manages quotes for building Log Cabins (this is very contrived and far from anything someone would do in the real world).
My solution will allow a generic user to create a quote (using a form with controls such as text boxes, check boxes, radio buttons) , and then save this to file. These users may then wish to load/edit this quote at a later date, from another form.
Whilst completing this project, i'll only have up to about 5 records (quotes) within the system, so i dont need the ability to store hundreds of records. And each record will be relatively short, with only about 10-15 data items within the data structure.
Also the Admin (or business owner in this case) need to be able to view all saved quotes in a presentable format, and edit them if needs be, from within this same program.
This solution does not need to be absolutely perfect and 100% efficiently coded, or have all the bells and whistles a real-world program would have. This is for an A level computing project by the way.
So basically, i need to be able to read from the database (to populate a Data Grid (i imagine this is best way?)) and so Admin can access any quote and edit it (editing is not vital, but viewing/printing is. Maybe i should stop at just viewing any quote?). Also i need generic users to be able to fill in the Edit Quote form and then save this data into the database.
And is a data structure really required for me to use a database?
I've never used databases in VB before (but have used them elsewhere, mainly Access) and so am completely new to this. Any help will be much appreciated. Thanks
I'm doing a test package which reads a flat file, makes an adjustment using the derived column task and writes to the same flat file. But, the read locks the flat file, so the write can't access it. Any ideas for a resolution?
We have a package that is using a ForEach loop container to access files on a network drive. For some reason I am getting a message that the ForEach enumerator is empty and did not find any files that matched the pattern. For the pattern I left the default *.* for testing purposes. I have specified the file folder as \remoteserverfilesharesubfolder and also as \remoteserverc$filesharesubfolder and have gotten the same message. However when I map a network drive and set the file folder to the network drive it finds the files. Is this a permissions issue?
After I finish processing the file I want to move it to a new directory. Once this is deployed in production, the package will not be running under a domain account and probably won't have access to the network folder. Is there any way to specifiy in the connection manager itself that it should use a specific account to access the folder?
-- drop proc SP_Trio_Popul_Stg_Tbls1 CREATE PROC [dbo].[SP_Trio_Popul_Stg_Tbls1] @tableName varchar(20) AS -- alter PROC [dbo].[SP_Trio_Popul_Stg_Tbls1] @tableName varchar(20) AS
declare @sql varchar (20)
set @sql = 'INSERT INTO dbo.Name_Pharse_Stg_Tbl1 (nm_last) SELECT nm_name FROM dbo.' + quotename(@tableName)
print @sql exec (@sql)
---------------------------------------------------------------------- exec SP_Trio_Popul_Stg_Tbls1 '00485' ---------------------------------------------------------------------- INSERT INTO dbo.Name Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'Name'.
im trying to run this SP but im getting an error. Can you help me to fix it?
Hi I need to read a very big table more than 60,000 record but it is giving the following problem: But if it is small table there is no problem. Same problem also views.
Server Error in '/POBuilds' Application.
Runtime Error Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
<!-- Web.Config Configuration File -->
<configuration> <system.web> <customErrors mode="Off"/> </system.web> </configuration>Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.
Hi,I'm using a 3rd-party app's back end which stores SQL statements in atable, so I have no choice but to use dynamic SQL to call them (unlesssomeone else knows a workaround...)Problem is, I can't get the statement to run properly, and I can't seewhy. If I execute even a hard-coded variation likeDECLARE @sql nvarchar(MAX)SET @sql ='SELECT foo FROM foostable'sp_executesql @sqlI get: Incorrect syntax near 'sp_executesql'.If I runsp_executesql 'SELECT foo FROM foostable'I get: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.which I understand, as it's omitting the N converter--so if I runsp_executesql N'SELECT foo FROM foostable'it's fine. I don't understand why the first version fails. Is it somesort of implicit conversion downgrading @sql? Every variation of CASTand CONVERT I use has no effect.This is SQL Server 2005 SP2. Thanks in advance.
I have written several scripts to pull in nested info to the analyzer window. How do I get this data to write to the new table I have created in the database? here is the current script:
select Hierarchy_List.Hierarchy_Label as Hierarchy_Name, Hierarchy_List.hierarchy_ID as Hierarchy_ID, Hierarchy_List.Parent_ID as Parent_ID, frequency_item.manufacturer as Motor_Make, frequency_item.model as Frame from hierarchy_list full outer join Frequency_item ON HIERARCHY_LIST.HIERARCHY_ID = frequency_item.HIERARCHY_ID where parent_id in (select hierarchy_id from hierarchy_list where parent_id in (select hierarchy_id from hierarchy_list where parent_id in (select hierarchy_id from hierarchy_list where parent_id in (select hierarchy_id from hierarchy_list where parent_id=0) and parent_id<>0) and parent_id<>0) and parent_id<>0) and parent_id<>0 and frequency_item.description = 'motor'
I need to move this data to the VAER.Al_Machines and the column names are the same. I can move data via DTS, but it won't work on this because the nested info. Is there a script addendum I can add to this to execute both the search and the transfer in one job so I can automate it? Thanks for any help.
i have declared an attribute with datatype char(20) in sql server 7. and i tried to write words into the table. when i read them for comparison, if the word is less than 20 characters, i have include whitespaces to make it exactly 20 characters to match. why is this? and how can i solve this problem?
I have a data flow task that performs an "upsert" by directing successful rows from a Lookup to an OLE DB Command that updates rows and unsuccessful rows (Lookup error output) to an OLE DB Destination for insertion.
The problem is that execution hangs when both tasks update/insert into the same table (execution is still hung after 20 minutes). Modifying the OLE DB Destination to insert into a different table succeeds (execution completese within 2 minutes). Replacing the OLE DB Destination with a Row Count transformation also works.
Could this be due to a table-locking issue? Any suggestions?
I have a series of tasks in a Sequence Container. One of them is a Data Flow task, and inside that task is a Row Count transformation that counts the number of rows I add to a table. The Row Count transformation was added to record the number of rows written to a table in a log table.
When I try to retrieve the resulting variable (RowCount) in the Data Flow task, I get the default value (0). When I try to retrieve it in a subsequent task, I get the value 1. When I try to consume it in the OnPostExecute Event Handler of the original Data Flow task, I get a value of NULL.
1) When is the appropriate time to call the variable assigned to a Row Count transformation so it can be written to a log table?
2) Is there a way during debugging to see the set value of the RowCount variable?
I am using Microsoft SQL Server Integration Services Designer Version 9.00.2047.00 in Visual Studio 2005 Version 8.0.50727.42 (RTM.050727-4200)
I'm developing an application in VB 2005 Express using SQL 2005 Express. I need to put a timestamp into my table each time I create a row...
The following is a snippet...
Dim DDate As [SqlDateTime] = Now()
Dim TheQuery As String = "INSERT INTO Groups (PC_Name_Stamp, OperatorNo, Group_Type, Date_Time) VALUES ('Development', '2', 'Test',' " & DDate & " ')"
Which won't work as I am attempting to concatinate a SqlDateTime into a string.
My best guess is that I need to somehow to use a DEFAULT value in the table that persists so each time a row is created the datetime it was created is saved with the row, rather than being re-calculated each time the table is opened. There are probably several other ways of doing it and this may not be the easiest.
I'm not a programmer, just an Engineer, so I can only read Help for 5 minutes at a time.
I want to (and succeeded) read information from one table in my database ([services] in this case) and display them in a table with an additional column with a textbox so that I can enter volumes in it. So far so good, this works fine. But I now want to store all the volumes together with the ID's in a seperate table ([service_volume]) by clicking the Submit button. Does anyone know how to achieve this? <%@ Page Language="VB" %> <%@ Import Namespace="System.Data.SqlClient" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
Hi ... I need to read rows from a large source file , check if the data selected already exists in the destination and if it does, upate the destination , else insert a new row . Now i could use a sp, but that means i have to call it for each row in the text file ... any ideeas on what kind of package can be created /
First of all let me say that ASP.NET a new programming environment for me so please forgive my ignorance. Can someone please tell me how to write data to a SQL table that is a Binary data type? I have a stored procedure on the SQL server that I am calling to insert data into a table. I build a parameter list and set the values. It worked just fine before I added a binary field to the SQL table. My problem is that I don't know how to set the Binay data type to pass it to the stored procedure. Here is part of the code: GetCMD = Myconnection.CreateCommand GetCMD.CommandType = CommandType.StoredProcedure GetCMD.CommandText = "SCHEMANAME.InsertLineItem" GetCMD.Parameters.Add("HEADER_ID", SqlDbType.VarChar, 150) GetCMD.Parameters("HEADER_ID").Value = "some value" GetCMD.Parameters.Add("@OPTIONS", SqlDbType.Binary) GetCMD.Parameters("@OPTIONS").Value = HOW DO I SET THIS VALUE???? rowsaffected = GetCMD.ExecuteNonQuery() I assume serialization but have not figured out how. Anyone's help is greatly appreciated!!
I am working on building a template/design pattern for a DTS to SSIS upgrade project.
During our ETL processing, if we encounter a record that cannot be inserted into a destination table, we'd like to be able to write the entire record out to a common error/reject table. The obvious problem is that every SSIS package that is using this template will of course be dealing with varying table schemas.
I was thinking that if there were a way that I could transform the error record/buffer row into XML, I could then achieve my goal of having a common table to receive errors/rejects.
Has anyone done something like this, or have suggestions on how we might accomplish?
I am trying to think of a way to read a control table, build the SQL statement for each line, and then execute them all, without using a cursor.
To make it simple... control table would look like this:
CREATE TABLE [dbo].[Control_Table]( [Server_Name] [varchar](50) NOT NULL, [Database_Name] [varchar](255) NOT NULL, CONSTRAINT [PK_Control_Table] PRIMARY KEY CLUSTERED
[Code] ....
So if we then load:
insert into zt_Planning_Models_Plant_Include_Control_Table values ('r2d2','planing1'), ('r2d2','planing7'), ('deathstar','planing3')
Then you would build a SQL script that would end up looking like the following (note all the columns are the same):
insert into master_models Select * from r2d2.planning1.dbo.models insert into master_models select * from r2d2.planning7.dbo.models insert into master_models Select * from deathstar.planning3.dbo.models
Hi, I have a problem when updating tables in a SQL Server 2000. I am able to make a select form tables but I can't insert data. I got this error : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. SQLExpress database file auto-creation error:
I've a table with more columns and 1 identifier. I need to write this table when a modified row is detecting respect to the columns not to the identifier.
So I've created a temporary table to put the potential rows to write on the real table, but I want to detect the modified rows. I've thought to use the checksum function, but I don't know how to use it and if it could be useful in this scenario.
Moreover, in the temporary table I've collected daily the rows to write: the first day a row could have a value respect to his columns, the next day a different value and the next one the same value respect to the first day.
Is there a way I can write a query to dynamically select a database table. That is, instead of having a variable for a certain column like customerId which would be €œcustomerID = @customerID€? I want to use the variable to select the table. I assume I may have to use a stored procedure but I am unclear as to how this would be coded.
I am developing an application in vb.net 2005 using SQL Server 2000. In this I have two tables SessionMaster and SessionChild. Fields of session master - SessionMastId, Start_Date, End_Date, Session_Type, Fields of session child - SessionChildId, SessionMastId, UserName, Comment. SessionMastId and SessionChildId are primary keys of respective tables and also they are auto increment fields. Please how to write trigger to insert record into both tables at a time.
