I have a single variable to be utilitized in my SQL Where clause...
Select *
FROM projects
Where project_id = @id
How do I use this c# variable in the datasource?
string TableID;
TableID = "192.AB";
I know this is simple, but I figured I'd ask. I don't want to do a work around , like a hidden textbox with the value assigned, and then link the datasource to the tb control, seems extremely hackish.
I need to be able to, at run time, set the location of a DataSource in an SSIS package.
I've searched and searched and beat my head against the wall till I'm bloody but I can't figure this out.
Can anyone please point me in the right direction?
I would like to create a stored procedure that imports an Excel file toa sql server table. I need to be able to pass the path to thespreadsheet to the stored procedure like so:CREATE PROCEDURE dbo.ImportSpreadsheet(@Path nvarchar(120))ASSELECT * INTO AuditFROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data Source = ' + @Path + ';Extended Properties=Excel 8.0')...Audit$I will call the stored procedure from within my .NET application andpass it the path as @Path. The path will be chosen by the user at runtime and will be something similar toC:SpreadsheetsAudits.xls.I can make this run in the Query Analyzer:SELECT * INTO AuditFROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data Source = ''C:SpreadsheetsAudits.xlsAudit_TC.xls'';Extend edProperties=Excel 8.0')...Audit$I could run the SQL directly from my app, but I like to use storedprocs whenever I can for the speed and flexibility. I can change thestored proc and not have to recompile/reinstall the app.Any help would be greatly appreciated.
View 1 Replies View RelatedAll:
I am trying to code the following SQL into an OLEDB data source but it is not allowing me to do so because I think the variables are nested in multiple SQL statements. I have seen other posts that suggest using a variable to store the SQL but I am not sure how it will work.
I would also like to mention that the OLEDB source executes from within a For Each loop that is actually passing the values for the variables, which was one of the reasons I got stumped on how I could have a variable store the SQL.
Here is the SQL:
select b.ProgramID, b.ProductCode, b.BuyerID, b.Vendor,sum(a.Ordered) As Qty_Pruchased
From SXE..POLine a INNER JOIN
(SELECT VIR_Program.ProgramID, VIR_ActiveSKU.ProductCode, VIR_ActiveSKU.BuyerID, Vendor
FROM VIR_Program INNER JOIN
VIR_ActiveSKU ON VIR_Program.ProgramID = VIR_ActiveSKU.ProgramID
INNER JOIN Vendor ON VIR_Program.VendorID = Vendor.VendorID
WHERE ProgramFreq=?) b
ON a.ProductCode = b.ProductCode
WHERE a.TransDate >=? AND
a.TransDate ?
Group By b.ProgramID, b.ProductCode, b.BuyerID, b.Vendor
Thanks!
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
Dan
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.
Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)
It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.
Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!
Here is the code of the function in the book:
/*
** fn_FindReports.sql
**
** This multi-statement table-valued user-defined
** function takes an EmplyeeID number as its parameter
** and provides information about all employees who
** report to that person.
*/
USE ClassNorthwind
GO
/*
** As a multi-statement table-valued user-defined
** function it starts with the function name,
** input parameter definition and defines the output
** table.
*/
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
AS
BEGIN
DECLARE @RowsAdded int
-- Initialize @reports with direct reports of the given employee
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES
WHERE ReportsTo = @InEmployeeID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
-- Mark all employee records whose direct reports are going to be
-- found in this iteration
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees marked 1
INSERT @reports
SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
FROM employees e, @reports r
WHERE e.ReportsTo = r.EmployeeID
AND r.processed = 1
SET @RowsAdded = @@rowcount
-- Mark all employee records whose direct reports have been
-- found in this iteration
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
RETURN -- Provides the value of @reports as the result
END
GO
Hi
View 7 Replies View RelatedI keep getting this debug error, see my code below, I have gone thru it time and time agian and do not see where the problem is. I have checked and have no NULL values that I'm trying to write back.
~~~~~~~~~~~
Error:
System.NullReferenceException was unhandled by user code Message="Object variable or With block variable not set." Source="Microsoft.VisualBasic"
~~~~~~~~~~~~
My Code
Dim DBConn As SqlConnection
Dim DBAdd As New SqlCommand
Dim strConnect As String = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString
DBConn = New SqlConnection(strConnect)
DBAdd.CommandText = "INSERT INTO D12_MIS (" _
& "CSJ, EST_DATE, RECORD_LOCK_FLAG, EST_CREATE_BY_NAME, EST_REVIEW_BY_NAME, m2_1, m2_2_date, m2_3_date, m2_4_date, m2_5, m3_1a, m3_1b, m3_2a, m3_2b, m3_3a, m3_3b" _
& ") values (" _
& "'" & Replace(vbCSJ.Text, "'", "''") _
& "', " _
& "'" & Replace(tmp1Date, "'", "''") _
& "', " _
& "'" & Replace(tmpRecordLock, "'", "''") _
& "', " _
& "'" & Replace(CheckedCreator, "'", "''") _
& "', " _
& "'" & Replace(CheckedReviewer, "'", "''") _
& "', " _
& "'" & Replace(vb2_1, "'", "''") _
& "', " _
& "'" & Replace(tmp2Date, "'", "''") _
& "', " _
& "'" & Replace(tmp3Date, "'", "''") _
& "', " _
& "'" & Replace(tmp4Date, "'", "''") _
& "', " _
& "'" & Replace(vb2_5, "'", "''") _
& "', " _
& "'" & Replace(vb3_1a, "'", "''") _
& "', " _
& "'" & Replace(vb3_1b, "'", "''") _
& "', " _
& "'" & Replace(vb3_2a, "'", "''") _
& "', " _
& "'" & Replace(vb3_2b, "'", "''") _
& "', " _
& "'" & Replace(vb3_3a, "'", "''") _
& "', " _
& "'" & Replace(vb3_3b, "'", "''") _
& "')"
DBAdd.Connection = DBConn
DBAdd.Connection.Open()
DBAdd.ExecuteNonQuery()
DBAdd.Connection.Close()
CREATE TABLE #T(branchnumber VARCHAR(4000))
insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
I working on a form using an SQL DataSource. I am going to add a where clause. In that where clause a value from a label will be passed from the user.
How do I pass to the HTML Source the label value from the where clause. The value can change from user to user.
Sample below:
SELECT [KeyRolesID], [KeyRolesDesc] FROM [KeyRoles] WHERE ([JobCodeFamily] = @JobCodeFamily).
I want to replace the @JobCodeFamily which is currently coded with a value from the Sql DataSource in the design form with a label value entered by the user.
Hi, guys
I used an exported wcf service as a web service, try to use it as the reporting datasource,
[OperationContract]
DataSet GetTestReportData();
the app.config in the service host:
<configuration>
<appSettings>
<add key="BaseReportingAddress" value="http://localhost:8999/ReportHandlerService"/>
</appSettings>
<system.serviceModel>
<services>
<service name="Reporting_Handler.ReportHandlerService">
<endpoint address="http://localhost:8999/ReportHandlerService"
binding="basicHttpBinding"
behaviorConfiguration="ReportingServiceBehavior"
contract="Reporting_Handler.IReportingHandler"/>
</service>
</services>
<behaviors>
<behavior name="ReportingServiceBehavior">
<metadataPublishing
enableGetWsdl="true"
enableMetadataExchange="true"
enableHelpPage="false">
</metadataPublishing>
</behavior>
</behaviors>
I set the datasource type as xml and the connection string to http://localhost:8999/ReportHandlerService, the query string for dataset is
<Query>
<SoapAction>
http://tempuri.org/IReportingHandler/GetTestString
</SoapAction>
<Method Namespace="http://tempuri.org/IReportingHandler/"
Name="GetTestString">
</Method>
</Query>
but it doesn't work, I tried use a pure asp.net web service and it works. can anyone help me to set the connecting string for datasource and query for dataset when it using wcf as the web service? Thanks in advance.
bruce
Hi All,
I have a question about the datasource that we create on the Report Manager. What is it used for? In the below connection string which is given in the "Connection String" text box of a datasource link:
"Data Source=ProdServer;Initial Catalog=Northwind"
What is this datasource used for? Is it used only to use the ReportServer database or even our queries incorporated in the RDL files will be run against this datasource? I think the latter should not happen because while creating RDL files also, we provide a connection string to run the stored procedures against that datasource.
Please let me know about the same.
Thanks a lot and let me know if the question is not clear.
Manoj Deshpande.
Hello,
I run the DTS to copy data from Progress to SQL Server. How can match/convert the date variables to check.
The field p-date as format 'mm-dd-year' . It has the value of 10/09/2001.
The field s-date as varchar format. It has the value 2001-10-09.
How can use the where condition ( Select ...... WHERE p-date = s-date.)
Thanks
When trying to upsize an access database to sql server using the upsize wizard, I get the following error:
"Object variable or With block variable not set."
Any assistance is greatly appreciated.
Hello!
I'm using SQL Server 2000.
I have a variable which contains the name of another variable scoped in my stored procedure. I need to get the value of that other variable, namely:
DECLARE @operation VARCHAR(3)
DECLARE @parameterValue VARCHAR(50)
SELECT @operation='DIS'
CREATE table #myTable(value VARCHAR(20))
INSERT into #myTable values('@operation')
SELECT top 1 @parameterValue = value from #myTable
-- Now @parameterValue is assigned the string '@operation'
-- Here I need some way to retrieve the value of the @operation variable I declared before (in fact
-- another process writes into the table I retrieved the value from), in this case 'DIS'
DROP TABLE #myTable
I've tried several ways, but didn't succeed yet!
Please tell me there's a way to solve my problem!!!
Thank you very much in advance!
I have an SSIS package that creates a csv file based on a execute sql task.
The sql is incredibly simple select singlecolumn from table.
In the properties of the execute sql task I specify the result set as "full result set" when I run it I get the error that: Error:
The type of the value being assigned to variable "User::CSVoutput" differs from the current variable type.
Variables may not change type during execution. Variable types are strict, except for variables of type Object.
If I change the resultset to single row then I only get the first row from the DB (same if I choose none), If I choose XML then I get the error that the result is not xml. What resultset type do I need to choose in order to get all the rows into the CSV? The variable I am populating is of type string and is User::CSVoutput
Can someone help me with this? For the life of me, I cannot figure out why I recieve this error: Procedure or function usp_ContactInfo_Update has too many arguments specified.Here is my code: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand="usp_ContactInfo_Select" SelectCommandType="StoredProcedure" UpdateCommand="usp_ContactInfo_Update"UpdateCommandType="StoredProcedure"><UpdateParameters><asp:Parameter Name="Division" Type="String" /><asp:Parameter Name="Address" Type="String" /><asp:Parameter Name="City" Type="String" /><asp:Parameter Name="State" Type="String" /><asp:Parameter Name="Zip" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="TollFree" Type="String" /><asp:Parameter Name="Fax" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Res" Type="Boolean" /><asp:Parameter Name="Res_Rec" Type="Boolean" /><asp:Parameter Name="Com" Type="Boolean" /><asp:Parameter Name="Com_Rec" Type="Boolean" /><asp:Parameter Name="Temp" Type="Boolean" /><asp:Parameter Name="Temp_Rec" Type="Boolean" /><asp:Parameter Name="Port" Type="Boolean" /><asp:Parameter Name="Land" Type="Boolean" /><asp:Parameter Name="Drop" Type="Boolean" /></UpdateParameters><SelectParameters><asp:ProfileParameter Name="DivID" PropertyName="Division" Type="String" /></SelectParameters></asp:SqlDataSource>Here is my Stored Proc:ALTER PROCEDURE dbo.usp_ContactInfo_Update @Division varchar(1),@Address varchar(50),@City varchar(50),@State varchar(2),@Zip varchar(10),@Phone varchar(20),@TollFree varchar(20),@Fax varchar(20),@Email varchar(50),@Res bit,@Res_Rec bit,@Com bit,@Com_Rec bit,@Temp bit,@Temp_Rec bit,@Port bit,@Land bit,@Drop bit/*(@parameter1 int = 5,@parameter2 datatype OUTPUT)*/AS/* SET NOCOUNT ON */ UPDATE tblDivisionSET Division = @Division, Address = @Address, City = @City, State = @State, Zip = @Zip, Phone = @Phone, TollFree = @TollFree, Fax = @Fax, Email = @Email,Residential = @Res, Residential_Recycling = @Res_Rec, Commercial = @Com, Commercial_Recycling = @Com_Rec, Temp = @Temp, Temp_Recycling = @Temp_Rec, Portable_Restrooms = @Port, Landscaping = @Land, Drop_Off_Center = @DropWHERE (Division = @Division) RETURNAny Help is greatly appreciated!
View 2 Replies View RelatedI have this web store that I have been creating. When a customer goes to check out, he has to log in, then he is redirected to a page where he can view/add/or edit shipping and billing address. I have based all the sql statements on the profile username, adding records and retrieving them works just fine. When I go to change something in the info it uses an sql statement that updates based on "Where AccountUserName = @AccountUserName", I have @AccountUserName set to Profile("Username"). Keep in mind this works fine for adding new or bring up current records. I even put in code in the updated event for the sql data source to post a msgbox telling me how many rows were affected, it says 1 even though I dont see any change in the data. What am I doing wrong here, it's driving me nuts, its just a very simple update.
View 2 Replies View RelatedMy god.All I want to do is post the contents of a web form to a database sql express 2005 or access using C#. Why can I find nothing for this very simple process online?Can you tell I am totally frustrated? So lets say i have a few text fields and I want to click submit and have that entered into a table. I use C# for code behind. So I can do basic C# programming and I can to webforms with visual web developer 2005 dragging and dropping for textboxs and a button to the aspx page. But then the mystery begins for me. How the hell do I simple post that form data to the table. I understand connection string basics. I aslo can design and create tables. But tying this all together is becoming a problem. I don't want to use gridview formview detailview or any of that canned UI stuff. I also don't understand VB and when I see VB examples they only cloud my already cloud ASP.NET world. Please help by posting examples, and maybe some links or books to add to my newbish collection. Thanks,Frank
View 3 Replies View RelatedLets say I have a Sqldatasource that uses the following SelectCommand="SELECT category,name FROM table". How do I get the value on category from my datasource in code behind if I know that my selectcommand always will return one row? Can I write something like datasource.items["category"].Value? Thanks for your help!
View 1 Replies View RelatedHi there,
I'm using a Repeater at the moment which is bound to a SQLDataSource. I expect much load on that Website, should I choose another DataSource? Which other DataSource is better if it's about Performance?
I read some stuff about the SQLAdapter and a DataSet.. is that better in performance? Why is it better?
What about LinQ?
Thanks a lot for any clarification.
I am using a SQLDatasource to populate a dataview as illustrated below. I run into a problem when I search for a "LastName" that includes an (') Apostrophe ( e.g. O'Reilly) . Searchin for the whole name there is no problem, but when I search for simply O, or O' I get errors.
I am not sure ...when to address names with an apostrophe...
1) On insertion to the database (using a "Replace(LastName, "'", "''") 2) or after they have been entered and they are to be searched for.
If scenario 1, can anyone provide the best way to do this...If scenario 2, how would that be worked into the SQL Datasource code below....
I have tried several variations with the times SQL Datasource to no avail....
I would appreciate any help !
Thanks !
<asp:SqlDataSource ID="SqlDataSource1" runat="server"ConnectionString="<%$ ConnectionStrings:ClinicTest2ConnectionString1 %>"
SelectCommand="SELECT [PatientID], [Accession], [FirstName], [LastName], [Address1], [City], [strddlPatientState], [ZIP], [DOB] FROM [ClinicalPatient] WHERE [LastName] LIKE @LastName ORDER BY [LastName],[FirstName]ASC">
<SelectParameters>
<asp:QueryStringParameter Name="LastName" QueryStringField="NameSearch_Result" />
</SelectParameters>
</asp:SqlDataSource>
Hi
When I try to configure a SQL datasource and I use a Microsoft SQL Server datasource I get a blank drop down list for server name. My aspnetdb is available in SQL Server 2005 and I can log into it through management studio, and the service is started. I am also using Vista Ultimate.
Is there any other configuration I need to do for Visual Studio 2008 to see the SQL Server instance?
Thanks
Kwis
hi... i am very new to SQL server.. previously was using MySQL...
now i am trying to connect my project to SQL Server..
but i wasnt able to...
i keep getting errors
System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at System.Data.Odbc.OdbcConnection.Open() at icms.DB.q(String mySTR) in C:icmsDB.vb:line 30
below is my webconfig
i am not very sure about the value for the user.. because if it is MySQL i can get it from my control centre.. but what about SQL server ? where should i get my value ?
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="db" value="icms" />
<add key="db_user" value="sqladmin" />
<add key="db_server" value="server" />
<add key="db_pwd" value="*****" />
<add key="session_timeout" value="600" />
</appSettings>
<system.web>
<compilation defaultLanguage="vb" debug="true" />
<trace enabled="true"/>
<customErrors mode="RemoteOnly" />
</system.web>
</configuration>
======================
Dim myCMD As New SqlCommand
Public Sub New()
Dim db_server = AppSettings("db_server")
Dim db = AppSettings("db")
Dim db_user = AppSettings("db_user")
Dim db_pwd = AppSettings("db_pwd")
Dim DBConnection As String = "DRIVER={SQL Server};" & _ '<==== is my driver correct?
"SERVER=" & db_server & ";" & _
"DATABASE=" & db & ";" & _
"UID=" & db_user & ";" & _
"PASSWORD=" & db_pwd & ";" & _
"OPTION=3;"
myDB.ConnectionString = DBConnection
myCMD.Connection = myDB
End Sub
'Public Function q(ByVal mySTR As String) As OdbcDataReader 'SQL Query
Public Function q(ByVal myStr As String) As SqlDataReader
myCMD.CommandText = myStr
Try
myDB.Open()
q = myCMD.ExecuteReader(Data.CommandBehavior.CloseConnection)
Catch ex As Exception
Err(ex.ToString)
End Try
End Function
Public Sub c(ByVal mySTR As String)
' COMMAND ONLY
Try
myCMD.Connection.Open()
myCMD.CommandText = mySTR
myCMD.ExecuteNonQuery()
myCMD.Connection.Close()
Catch ex As Exception
Err(ex.ToString)
End Try
End Sub
If I add this to my web config ConnectionStrings section:<add name="SQLPubs" connectionString="myServer99;uid=MyUId;pwd=MyPWD;database=MyDB" providerName="System.Data.SqlClient" />Everything displays correctly, referring to this section in the SQLDataSource ControlHowever, if I add the connection string, exactly as it's listed in the web.config, and INSTEAD of the using that entry, enter it implicitly in the Connectionstring property of the DataSource Control, along with adding the System.Data.SQLClient to the ProviderName property of the DataSource control (which I understood was possible), I get this error:Unable to find the requested .Net Framework Data Provider. It may not be installedWhat am I missing?here's my tag:<asp:SQLDataSource ID="DS1" Runat="Server" SelectCommand = "SELECT top 50 {FieldList}From {TableName}" ConnectionString="myServer99;uid=MyUId;pwd=MyPWD;database=MyDB" ProviderName="System.Data.SQLClient"></asp:SQLDataSource>
View 2 Replies View Relatedim using this datasource to extract values from a database.i simply want to extract the values for ratingsum and ratingcount to populate variables so as to be checked within a code loop.how do i do this? in classis asp would be something like <%=rsRecords(RatingSum)%><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" ProviderName="<%$ ConnectionStrings:MyConnectionString.ProviderName %>" SelectCommand="SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE Itemid=100"> </asp:SqlDataSource>
View 1 Replies View RelatedI have a simple form that accepts a zip code and a city when the submit button is hit this is triggered...
Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Dim Zip, City As String
Zip = txtZip.Text
City = txtCity.Text
grdZipCodes.DataSourceID = "ZipSearch"
ZipSearch.SelectParameters("ZipCode").DefaultValue = Zip
ZipSearch.SelectParameters("City").DefaultValue = City
grdZipCodes.DataBind()
End Sub
This is supposed to populate a gridview with filtered data. Instead I get nothing, even though in query analyzer if I dont provide a city or a state all results are returned. Is there a reason my gridview is not populating.
this is what my SQL Datasource is :
<asp:SqlDataSource ID="ZipSearch" runat="server" ConnectionString="<%$ ConnectionStrings:something%>" SelectCommand="ZipCodesSearch_s" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter Name="ZipCode" Type="string" Direction=input />
<asp:Parameter Name="City" Type="string" Direction=input />
</SelectParameters>
</asp:SqlDataSource>
In the following example...
EXEC sp_addlinkedserver
@server = 'Shipping',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:ShippingShipping BackEnd.mdb'
...can I specify the UNC path of the Access DB instead?
Hi,
New to databases so need some help. While trying to set up data source in control panel, I find that there are no drivers for *mdf files. Just *.dbo and *.xls and others. Can someone tell me what to do. I can't get analysis services to let me access my database, which are *mdf files a friend sent me.
What do I need to do...Can someone help me.
Regards,
Milfredo.
I am trying to do incremental extract using Datasource reader.
I need to send a variable in where condition to extract the data base on the max date from other table.
To implement this i have created two variables
1)to get the max date from the table.
2)to store the sql query and send the mad date from other variable to extract the data.
i have give this sql variable in data flow expression.but its not working.
can any one tell me where i am going wrong.
Hi All,
I am using SSRS 2005 (Report Designer), and in 1 of the Report, I have a Report Parameter for example say IMask (Dropdown List), which I am populating by values from the following XML file (Imask.xml)
<?xml version="1.0" standalone="yes"?>
<REPORTCONFIG>
<INVALIDMASK>
<INDEX>0</INDEX>
<IMASK>00000000</IMASK>
<DESC>Custom Created</DESC>
</INVALIDMASK>
<INVALIDMASK>
<INDEX>1</INDEX>
<IMASK>FFFFFFFF</IMASK>
<DESC>Custom Created1</DESC>
</INVALIDMASK>
</REPORTCONFIG>
I have created a XML DataSet whose DataSource is of €śType : XML€śand get the values from the above mentioned XML file by using following XML query :
REPORTCONFIG {}/ INVALIDMASK
The Result of the above XML query is:
INDEX | IMASK | DESC
--------------------------------------------------------------
0 00000000 Custom Created
1 FFFFFFFF Custom Created1
Now, the report Parameter (IMask) which is a DDL has
€śvalue field€?= IMASK and €ślabel field€?= DESC
So far so good, but what I really want :
1) in case of label field is something like this:
€ślabel field€?= IMASK : DESC (for eg; 00000000 : Custom Created 1)
2) If no nodes(<INVALIDMASK>) are present in the XML file, I get no results when the above XML query is fired. So, Is there any way of getting the count of rows returned by XML query.
And in case of no rows I want my Report Parameter (IMask) to have
€śvalue field€?= NONE and €ślabel field€?= NONE
Regards,
abhi_viking