How Do I Reuse A SqlDataAdapter

Apr 25, 2007

It's a pretty basic question but I haven't been able to find any examples out there.  I dimmed a dataadapter and would like to reuse later in my code (line 3 in the code below).  What is the correct syntax to do this?

 
Dim da As New SqlDataAdapter("SELECT * FROM myTable", conn)
da.Fill(myDataTable)
da.______  ("SELECT * FROM myTable2", conn)
da.Fill(myDataTable2) 

View 2 Replies


ADVERTISEMENT

Log Reuse Help

Apr 30, 2007

Dear Folks,
I'm getting the error with application like this..............

"the transaction log for database "mydatabase" is full.....to find out why space in the log can not be reused, see the log_reuse_wait_desc column in sys databases. "

I've checked with the column, there are totally 3 options.(I've checked with all other databases)
1)checkpoint,
2)log_backup,
3)nothing

what can i choose for this error to be suppressed?
actually there was log_backup option.

please guide me in this regard.........

thanks in advace.

Vinod

View 1 Replies View Related

Can We Reuse SqlParameter's?

Jul 10, 2004

Hi,

I am getting the following error:

The SqlParameter with ParameterName '@pk' is already contained by another SqlParameterCollection

If I could work out what code to post I would, but I can say that I am managing my Sql data in my code by caching small arrays of SqlParameter objects as pulled from the database. If I need to update the DB I change the cached SqlParameter and re-insert it. If I perform a SELECT I check the cache first to see if I already have the SqlParameter.
However, currently, I am experiencing the above error when performing a select, then later an update, followed by another update.
Would I be correct in saying that a SqlParameter object can only be used once for a database operation and should be discarded? Would I be correct if I said that the SqlCommand object should be discarded? I am barking up the wrong tree entirely?

Distressed in DBLand,
Matt.

View 2 Replies View Related

How Can I / Should I Reuse SqlCacheDependency? Thanks

May 1, 2006

I can create a SqlCacheDependency, and link it to a cached item in httpcontext cache. When something change, it will remove the cached item from the cache. I think I have to redo the process when that happens - prepare sql command, create SqlCacheDependency and insert the item into cache. Now I only need a notification from my SQL when something changes in one of my table, I don;t need read anything from db, and I think I should find a way to not recreate the SqlCacheDependency object everytime?
any suggestion?

View 2 Replies View Related

Reuse Deleted Primary Key Id

Dec 15, 2007

Hello,          Can I reuse the deleted primary key id? I'm using SQLServer2005. cheers,imperialx   

View 6 Replies View Related

Reuse Calculated Values

Nov 14, 2006

What is the best code pratice to use do the following code,

SELECT
fo.no as LNum,
fo.name as LName,
sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END) as In1,
sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END) as In2,
sum(In1+In2)/10 as inDec,
from fo group by fo.no,fo.name order by fo.name

instead of

SELECT
fo.no as LNum,
fo.name as LName,
sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END) as In1,
sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END) as In2,
((sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END))+sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END)))/10 as inDec,
from fo group by fo.no,fo.name order by fo.name

I cant use functions and procedures. Is there any better and cleaner way to code this, reusing the calculated values?

View 1 Replies View Related

Stored Procedure Reuse

Jul 23, 2005

I wonder if there is a solution for this in SQL 2000 (or do I have towait for SQL 2005)?I am currently in the middle of developing a 'Yahoo' style portal whichwill be rolled out in about 20 or so countries. I have set up in SQLServer one database per country. All the portals have the samefunctionality - but show different data.Is it possible to have a single database which holds storedproceedures, functions and views and have the individual countrydatabases use these?Note: I want to avoid using EXEC sp_executesql.I look forward to some good news on this! Thank you in advance.Dadou.

View 2 Replies View Related

Cannot Reuse Database Name After Deleting Old One

Apr 19, 2008



Hi. I'm starting to try out LINQ to SQL, and so I'm using SQL Express (on Vista) to experiment. My problem is that once I create and delete a database, I can never use the name again. If, after deleting the old database, I try to create another one with the same name (say Acct1), I get "Create failed for Database 'Acct1'. An exception occurred while executing a Transact-SQL statement or batch. The logical file name "Acct1" is already in use. Choose a different name. Error: 1828"

I am trying to create the database in SQL Server Management Studio Express. That database name does not appear in the list of databases: there is only AdventureWorks and the system databases. The .MDF and .LDF files have been deleted. Not just sent to the recycle bin, but permanently deleted.


I have already used up Acct1, Acct2, and Acct3, just to try out different scenarios. Each time, I delete the old database before trying to create a new one with the same name, but I am forced to always supply a new, different name. I have checked the directory with hidden files and system files showing to be sure there is no old file lurking there somewhere.


Is there a way to delete these old "logical file names"? I can't even find any reference to their existence except for the message that says they are already in use.

View 11 Replies View Related

Transact SQL :: How To Reuse Subquery

Jul 29, 2015

with c1 As (
select 1 As '1' , 2  As '2' , 3 As '3'

, c2 As (
select 4  As '4', 5  As '5',  6 As '6'
union all
select 1 , 2 ,3 from c1  -- >>>>>  select from c1 here 
) select * from c2 
  union all
  select * from c1 -- >>>>>> and select from c1 here 

According to the query above , I try to reuse the subquery by put  the subquery into 'with cte'  name (c1)  then i select this  2 times .

if I do this way , how many time this subquery (c1) execute ?
if 1 time then this is the right way to reuse this subquery .
if 2 times  , it is not then what should i do to reuse this subquery ?

View 3 Replies View Related

Reuse Of Conversation Handles

Nov 26, 2007



I have read the articles posted online concerning different dialog reuse strategies. Most of them create a new table in the sender to hold dialog ids. I was wondering what is wrong, if anything, with the following approach:




Code Block
declare @dlg uniqueidentifier
select top 1 @dlg = conversation_handle from sys.conversation_endpoints where state IN ('CO')
if @dlg is null
begin
begin dialog conversation @dlg
from service [tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender]
to service '//TyMetrix360Audit/DataWriter','386DDD04-7E55-466A-BE83-37EFC20910B9'
on contract [//TyMetrix360Audit/Contract] with encryption = off;
end
;send on conversation @dlg
message type [//TyMetrix360Audit/Message] (@msg)





Here I simply select a conversation handle directly from the sys.conversation_endpoints table. Can anyone see any issues with this approach?

Thanks in advance....

View 6 Replies View Related

Reuse Of Field Aliases

Jan 3, 2007

I have been working with SQL for quiet a while but think this perhaps is a very basic question that has always escaped me:

At my work I was exposed to both, MS SQL Server 2000 and Sybase Adaptive Server Anywhere/Sybase SQL Anywhere.

Under Sybase I was able to use aliases in other calculations and filters but i have never been able to do the same with SQL.

Example:
In Sybase I can write this:

Select Price * Units as Cost Cost * SalesTax as TotalTaxFrom Invoice Where TotalTax > 3.5
However if i want to do this in MS SQL 2000 i have to go trough


Select
Price * Units as Cost
Price * Units * SalesTax as TotalTax
From Invoice
Where (Price * Units * SalesTax) > 3.5

In the long run this is costing me a lot of code redundancy, not to mention a debugging nightmare. Is there a way to replicate this alias usage in MS SQL Server?

View 3 Replies View Related

Reuse Of Report Elements

Jun 5, 2007

I am working on a project developing a fairly large number of reports with a team of developers. Many of these reports have common elements and code, such as common headers with user-selectable colors. Additionally, many of the common parts of the reports are at mockup stage currently, and many features will have to be added to the reports as time goes on.



We're attempting to create a generalized framework that will minimize the duplication of effort as we develop these reports, and as we go back and modify or fix them later.

What is the best way to approach this?



My first attempt was to create a report template and base all the reports off of the same template. That was fine for the first pass, but as we need to make changes later, they will not be propagated to the already existing reports.



My second attempt was to have each componant of that template reference a subreport, so that changes to the actual report template will be minimized as we go forward. This works great for minimizeing work, but it appears that you lose many features with the use of subreports, and there seems to be a pretty serious performance impact as well. I have posted about one such issue here: Pagination



If anyone has pointers about how to go about this, and where I should start, they would be greatly apreciated!

View 2 Replies View Related

Sqldataadapter

Sep 30, 2007

hi
it looks like my thread has been deleted but I keep on asking.
When trying to connect to a table i a SQL2005 database in a vb.Net webapplication I get the following error message from the wizard:
"The wizard detected the following errors when configuring the data adapter "SqlDataAdapter1".

"Check" Generated SELECT Statement
"Check" Generated Mappings
"Warning" Generated INSERT Statement
There were errors configuring the data adapter
"Warning" Generated UPDATE Statement
There were errors configuring the data adapter
"Warning" Generated DELETE Statement
There were errors configuring the data adapter"

The same connection works fine in a vb6 application. I have seen other threads concerning this in this forum but I can't find any answers. Any ideas anyone?
ciao chris

View 2 Replies View Related

Reuse Deleted Identity In Table Again

Jun 19, 2014

I have a table in which there is a column of type identity(1,1),in the same table i have bulk data, and i have deleted a row from my table, so now i want to reuse that same Id again, how to do that.

View 3 Replies View Related

Reuse Numbers In Identity Column

Mar 4, 2008

Hello,

I have an ID column which is an Identity column, and it counts from 1 upwards.

If I have 10 records, I have ID's number 1 to 10.
If I then add another record, it will give the ID number 11.

Imagine I then delete ID number 9
Is it possible to get the next record I create to take number 9 instead of 12 ?

View 5 Replies View Related

How To Reuse Functions In Script Tasks ?

Feb 7, 2006

Hello

I have some common functions that i use in several script tasks. How du i store a function globaly so that i can use it from different projects and still only have to edit it one place ?

View 4 Replies View Related

ReUse Common Surrogate Key Pipeline

Jun 12, 2007

I have several stage to star (i.e. moving data from a staging table through the key lookups into a fact table) ETL transformations in a single SSIS package. Each fact table has a different set of measures but the identical foreign key set, e.g. ConsultantKey, SubsidiaryKey, ContestKey, ContestParamKey and MonthKey.



Currently I have to replicate the key lookup (Surrogate Key Pipeline, or SKP) for each data flow. If I could cache each dimension one time in the package and reuse it for each stage to fact it would be much more efficient.



Is there a way for me to reuse a common data flow?

View 6 Replies View Related

Inherits SqlDataAdapter

Aug 16, 2006

Hi guys. Does anybody have any clue about how to create a class which inherits from SqlDataAdapter? I tried but looks like SqlDataAdapter is not inheritable. I would like to extend it with some custom method such as a method which fills a Dataset (used with a DataGrid) with the only few records to display per page (say 20) against the thousand records it might instead contain otherwise.Any workarounds? Many thanks in advance for your help.

View 1 Replies View Related

SqlDataAdapter && SqlParameter

Mar 26, 2007

Hi I am writing an app in flash which needs to hook up to MS SQL via asp.I need the code below to pass the var (ptodaysDate) to the sql statement. I can hard code it in and it works great but I really need to pass the var from flash.Pulling my hair out here, not much left to go.Any help greatly appreciated.----------------------------------------------    [WebMethod]    public Schedule[] getSchedule(int ptodaysDate)    {                SqlDataAdapter adpt =            new SqlDataAdapter("SELECT scheduleID, roomName, eventType,unitName,groupName,staffName,staffName2,theDate,theEnd FROM tb_schedule Where theDate >= @rtodaysDate", connString);        SqlParameter rtodaysDate = new SqlParameter("@rtodaysDate", ptodaysDate);               DataSet ds = new DataSet();        ArrayList al = new ArrayList();        adpt.Fill(ds);        foreach (DataRow row in ds.Tables[0].Rows)        {            Schedule obj = new Schedule();            obj.scheduleID = (int)row["scheduleID"];            obj.roomName = (string)row["roomName"];            obj.eventType = (string)row["eventType"];            obj.unitName = (string)row["unitName"];             obj.groupName = (string)row["groupName"];             obj.staffName = (string)row["staffName"];            obj.staffName2 = (string)row["staffName2"];            obj.theDate = (string)row["theDate"];            obj.theEnd = (string)row["theEnd"];            al.Add(obj);        }        Schedule[] outArray = (Schedule[])al.ToArray(typeof(Schedule));        return outArray;    }    public class Schedule    {        public int scheduleID;        public string roomName;        public string eventType;        public string unitName;        public string groupName;        public string staffName;        public string staffName2;        public string theDate;        public string theEnd;           }

View 2 Replies View Related

Problems With SqlDataAdapter

Aug 15, 2007

I'm working on a project to dynamically create PDFs with content from an SQL server. My current approach is to get the data I need into a DataSet using SqlDataAdapter, write the DataSet to a stream as XML, transform the XML into FO and then output a pdf using FOP. For some reason I get the following exception "System.NullReferenceException - Object reference not set to an instance of an object" when I try to set the SelectCommand property of my data adapter. Code for the project follows:Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load        Dim con As SqlConnection        Dim cmd As SqlCommand        Dim IDlist As String        Dim keyidary As Array        Dim query As String        Dim tempxml As Stream        query = "*****query is valid and very long, so it's not included here*****"        IDlist = Request.Form("chosenIDs")        keyidary = Split(IDlist, ",")        Dim makequery As New StringBuilder(query)        For Each ID As String In keyidary            makequery.Append(" OR (KeyID = '" & ID & "')")        Next        query = makequery.ToString()        'Response.Write(query)         When uncommented this prints the query just fine        'Response.End()        Try            con = New SqlConnection            con.ConnectionString = "****Connection String is valid****"            cmd = New SqlCommand            cmd.CommandText = query            cmd.CommandType = CommandType.Text            cmd.Connection = con            Dim adpt As SqlDataAdapter            adpt.SelectCommand = cmd            'Response.Write(query)          When these are above or between the previous 2 statements, query is printed,            'Response.End()                     otherwise I get the error described above.            Dim profiles As New DataSet            adpt.Fill(profiles)            profiles.WriteXml(tempxml)            Dim step1 As XslTransform = New XslTransform            Dim step2 As XslTransform = New XslTransform            step1.Load(Server.MapPath("TransAttmpt1.xslt"))            step2.Load(Server.MapPath("formatXML.xsl"))            Dim xml, pdf As String            xml = "profiles.xml"            pdf = "Profiles.pdf"            Dim temp2xml As New XPathDocument(tempxml)            Response.Write(query)            Response.End()            Dim midstream As Stream            Dim finalxml As StreamWriter = New StreamWriter(xml)            step1.Transform(temp2xml, Nothing, midstream, Nothing)            Dim xpathdoc2 As XPathDocument = New XPathDocument(midstream)            step2.Transform(xpathdoc2, Nothing, finalxml, Nothing)            GeneratePDF(xml, pdf)        'There's a lot more but it doesn't seem relevant now.... I'm somewhat at a loss on how to proceed and any help is very greatly appreciated.Thanks! 

View 2 Replies View Related

SqlDataAdapter Update

Oct 1, 2007

Hi all,
I have datatable having around 50 rows and 3 columns ID, Name and ExpVal, which is an expression columns,where the values can be any SQL functions Like REPLICATE(), SOUNDEX ( 'value' ) Or REVERSE ( 'value' ).....
i want to insert each row in that datatable like
INSERT INTO TAB1 ( ID, Name, ExpVal) VALUES (1, 'some name', SOUNDEX ( 'some name' ) )
so that the ExpVal will have value of the function ie inserted row look like
ID  Name            ExpVal1   some name    S500 <--- Result of SOUNDEX ( 'some name' )
I'm using sqldatadapter to insert these values to the database
string sql = "INSERT INTO TAB1 (ID, Name, ExpVal)VALUES (@ID, @Name, @ExpVal) ";SqlDataAdapter sqlAdptr = new SqlDataAdapter();SqlCommand sqlCmd = new SqlCommand(sql, con);sqlCmd.parameters.Add("@ID", SqlDbType.Int, 0, "ID");sqlCmd.parameters.Add("@Name", SqlDbType.NVarChar, 200, "Name");sqlCmd.parameters.Add("@ExpVal", SqlDbType.VarChar, 100, "ExpVal");sqlAdptr.InsertCommand = sqlCmd;sqlAdptr.Update(dataTable);
This works fine, but the problem is, now the TAB1 contains
ID  Name            ExpVal1   some name   'SOUNDEX ( 'some name' )' instead of S500
Thatis the sql funtion is passed by SqlDataAdapter to database as a string and it is not executing while row is inserted to the table.Please provide what changes i have to make if i want SOUNDEX ( 'some name' ) to executed while data insertion take place
Thanks in advance

View 1 Replies View Related

SqlDataAdapter And DataSet

Jun 3, 2008

I'm getting a strange error with my dataadapter.  Here's my code:  using (SqlConnection conn = new SqlConnection(
ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
SqlDataAdapter da = new SqlDataAdapter("sprocCUSTOM", conn);
da.SelectCommand.Parameters.AddWithValue("@eventID", EventID);

conn.Open(); //open connection
DataSet ds = new DataSet();
da.Fill(ds);
gvAttendees.DataSource = ds;
gvAttendees.DataBind();
}This is the error I'm getting: Incorrect syntax near 'sprocCUSTOM'.But the error is highlighted at the line that reads "da.Fill(ds);"There's nothing wrong with the stored procedure either.  It exists and works fine. 

View 2 Replies View Related

SqlDataAdapter.Update

Feb 6, 2004

I am using SqlDataAdapter.Update(DataSet) to insert records into multiple tables in one call, but for some reason only first table was inserted.


Dim _cnn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim _sql As String = "Select a,b FROM Table1;Select d, c From Table2;"
Dim _da As New SqlDataAdapter(_sql, _cnn)
Dim _sqlCmdBdr As New SqlCommandBuilder(_da)
Dim _ds As New DataSet
_da.Fill(_ds)
Dim _newrow1 As DataRow = _ds.Tables(0).NewRow
_newrow1("a") = "NewA"
_newrow1("b") = "NewB"
_ds.Tables(0).Rows.Add(_newrow1)

Dim _newrow2 As DataRow = _ds.Tables(1).NewRow
_newrow2("d") = "NewD"
_newrow2("c") = "NewC"
_ds.Tables(1).Rows.Add(_newrow2)

_da.Update(_ds)


new record inserted only into Table1, no new rows in Table2

Please advise!
Thanks

View 1 Replies View Related

SQLDataAdapter Error

Jul 6, 2007

Hi,



I am porting an ASP.NET/C# application from Microsoft Access to SQL Server 2005. In my code I have a method that uses the DataAdapter class to open a table, read some values out of it which are stored, open a second table, read some additional data from it storing the data in the same structure as used before, then updating the first table with the combined data.



When I execute the dataAdapter.Update() method I get the following error:



"Dynamic SQL generation for the Update Command is not supported against a Select Command that does not return any key column information."



I am uncertain what this error is about. This code worked fine against Microsoft Access so it must be something different in how SQL Server works.



Can anyone tell me what this error is complaining about?



Many thanks!



Bob

View 1 Replies View Related

Reuse Existing Connection String For TableAdapter?

Jan 25, 2008

I've added a typed DataSet and dragged a table across from the server explorer. When I click configure on the table adapter, then click previous back to the "Choose Your Data Connection" dialog. The only option is the new connection that was just created when I added the sql server to the server explorer. Is there anyway to reuse my existing web.config connection string? My goal is to have a single connection string in my web.config.
 Thanks.
 -David

View 3 Replies View Related

Connection Manager Reuse For Oracle And SQL Databases

May 2, 2008

I'm trying to implement a configurable way of executing a group of SQL statements using either an Oracle or SQL database as the source for the data.

I'm currently building a connection string with a Script Task and then assigning it to a package variable so that it can be used by a connection manager but this only works with a SQL OLEDB connection.


I'm holding all the connection details in a database table, hence the need for the scripting but would it be possible to extend the task to reuse a single connection manager by changing it's properties?

View 7 Replies View Related

The Client Was Unable To Reuse A Session With SPID XX

May 11, 2007

Hello,



We have a recurring problem with one of our MS SQL Servers (MS SQL Server 2005 SP 1).

The SQL Server serves as a database server for a Web application with approximately 1500 users, the server is dedicated for the DataBase server.



The scenario is that the database servers cpu peaks at 100% and stays there until restart of the SQL Server service.



The error message from the applicatoion log:



Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 18056



The client was unable to reuse a session with SPID 94, which had been reset for conection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.



SPID varies.



Anybody that knows anything why this error occurs?



Regards

John

View 25 Replies View Related

Using Stored Procedures With A SQLDataAdapter

Mar 16, 2007

With MUCH help from this site and its users, I now know how to link a SqlCommand to a stored procedure:
 What I don't know how to do, if it is possible, is to use a stored procedure with a SQLDataAdapter...
Here is the code as I am using, anyone have an idea on how to do this the right way?
' Define data objects
Dim conn As SqlConnection
Dim dataSet As New DataSet
Dim adapter As SqlDataAdapter
If ViewState("FooterDataSet") Is Nothing Then

' Read the connection string from Web.config
Dim connectionString As String = _
ConfigurationManager.ConnectionStrings( _
"Greensheet").ConnectionString
' Initialize connection
conn = New SqlConnection(connectionString)
' Create adapter
adapter = New SqlDataAdapter("SELECT * " & _





"FROM Table " & _





"WHERE (ID= " & intID & ")", conn)
' Fill the DataSet
adapter.Fill(dataSet, "Footer")
' Store the DataSet in view state
ViewState("FooterDataSet") = dataSet
Else

dataSet = ViewState("FooterDataSet")
End If
' Prepare the sort expression using the gridSortDirection and
' gridSortExpression properties
Dim sortExpression As String
If gridSortDirection = SortDirection.Ascending Then

sortExpression = gridSortExpression & " ASC"
Else

sortExpression = gridSortExpression & " DESC"
End If
' Sort the data
dataSet.Tables("Footer").DefaultView.Sort = sortExpression
' Bind the grid to the DataSet
footerGrid.DataSource = _
dataSet.Tables("Footer").DefaultView
footerGrid.DataBind()

View 1 Replies View Related

A Problem About Inserting A New Row With SqlDataAdapter

Jul 13, 2007

Since my select command involves multiple tables, I manually write the insert command, proplating the SqlParameters as follows:
string insCmdText =                "INSERT INTO itcac_alan.COURSE " +                 " ([COURSE_ID], [COURSE_NAME], [COURSE_NO], [BEGIN_DATE], [END_DATE], [CREATER]) " +                " VALUES(@COURSE_ID, @COURSE_NAME, @COURSE_NO, @BEGIN_DATE, @END_DATE, @CREATER)";
            SqlCommand insCmd = new SqlCommand(insCmdText, conn);            insCmd.Parameters.Add("@COURSE_ID", SqlDbType.VarChar);            insCmd.Parameters.Add("@COURSE_NAME", SqlDbType.VarChar);            insCmd.Parameters.Add("@COURSE_NO", SqlDbType.VarChar);            insCmd.Parameters.Add("@BEGIN_DATE", SqlDbType.DateTime);            insCmd.Parameters.Add("@END_DATE", SqlDbType.DateTime);            insCmd.Parameters.Add("@CREATER", SqlDbType.VarChar);
            da.InsertCommand = insCmd;  // da is a data adapter
 
However, as I use the preceding adapter to update the underlying database which in fact is based on a disconnected DataRow, an exception is thrown:
      The parameterized query '(@COURSE_ID varchar(8000),@COURSE_NAME varchar(8000),@COURSE_NO ' expects the parameter '@COURSE_ID', which was not supplied.
The code I update the database is as follows:
      DataRow newRow = sourceTable.NewRow();  //sourceTable is a DataTable whose data rows are populated with da (SqlDataAdpater) in the preceding code
      // Populating the newRow
      Hashtable newValues = new Hashtable();
      .....................
            foreach (DictionaryEntry entry in newValues)            {                // entry.Key is identical to a column name in the newRow                newRow[(string)entry.Key] =                    (entry.Value == null ? DBNull.Value : entry.Value);            }
            sourceTable.Rows.Add(newRow);
            adapter.SelectCommand.Connection.Open();
            adapter.Update(sourceTable);  // Exception is thrown here
            adapter.SelectCommand.Connection.Close();
 
Please give me some directions to debug. It seems that the values of Columns withn DataRow cannot be fetched into the corresponding adapter.InsertCommand.Parameters by the adpater. (As I use SqlCommandBuilder to automatically generate insert command, the code above wroks rightly.)
Thanks a million.
Ricky.

View 2 Replies View Related

Updatecommand Not Working With SqlDataAdapter

Aug 14, 2007

I know there are loads of posts on this, but this just will not update. I have tried various forms of the code all to no avail.  And now I'm ready to throw my PC out the window........
 What am I doing wrong? 
cheers Mike
Dim da As SqlDataAdapterDim dSetOrp As DataSet
oCn = New SqlConnection(db.m_ConnectionString)
oCn.Open()da = New SqlDataAdapter("Select * from contact WHERE conid = " & lngConId, oCn)
dSetOrp = New DataSet
da.Fill(dSetOrp, "locTable")  ' Fill the DataSet.
dSetOrp.Tables(0).Rows(0)("ConSttDate") = dtEffDateDim myBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)
myBuilder.GetUpdateCommand()
da.UpdateCommand = myBuilder.GetUpdateCommand()
 lRows = da.Update(dSetOrp, "locTable")

View 2 Replies View Related

Help With SqlDataAdapter.Update URGENT

Feb 7, 2008

 MyCommand.Parameters.Add(new SqlParameter("@ConsultantName",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Calls",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@PPC",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Mth",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@DaysInMonth",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Coach",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Center",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@ProductValue",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@ObjectiveValue",SqlDbType.VarChar));
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.CommandTimeout = 360;
try
{
SqlDataAdapter saveCenterCoaches = new SqlDataAdapter(MyCommand);
saveCenterCoaches.InsertCommand = MyCommand;

DataSet updateSet = finalSet.GetChanges(DataRowState.Added);
saveCenterCoaches.Update(updateSet.Tables[0]);
}
catch(Exception ex)
{
throw ex;
}
 Iam getting "Procedure expects parameter @ConsultantName, which was not supplied."
I have consultantname and other parameters built in my datatable.
Is it the correct way of doing?
Can someone help.It is urgent.

View 5 Replies View Related

Update Database Using The SqlDataAdapter

Feb 20, 2008

 Hi, I was looking at how to update a database using SqlDataAdapter and I stumbled upon this code snippet from (http://www.java2s.com/Code/CSharp/Database-ADO.net/UpdatedatabaseusingtheSqlDataAdapter.htm):using System;
using System.Data;
using System.Data.SqlClient;

class Class1{
static void Main(string[] args){
SqlConnection thisConnection = new SqlConnection("server=(local)\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT ID, FirstName FROM Employee", thisConnection);

SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

DataSet thisDataSet = new DataSet();

thisAdapter.Fill(thisDataSet, "Employee");

Console.WriteLine("name before change: {0}", thisDataSet.Tables["Employee"].Rows[9]["FirstName"]);

thisDataSet.Tables["Employee"].Rows[1]["FirstName"] = "Inc";

thisAdapter.Update(thisDataSet, "Employee");

Console.WriteLine("name after change: {0}", thisDataSet.Tables["Employee"].Rows[9]["FirstName"]);


}
}I was just wondering, without iteration, how did the line  "thisDataSet.Tables["Employee"].Rows[1]["FirstName"] = "Inc";" managed to know which row it is to update? I tried it on my own application and it worked fine.Any help to assist me in understanding this would be appreciated. Thanks   

View 3 Replies View Related

Accessing Queries In SqlDataAdapter

Apr 28, 2006

Hello Dears;
I have an SqlDataAdapter which contains may queries. One query contians 3 query parameters as follows:
SELECT     SUM(Amount) AS TotalFROM         BoxesWHERE     EntryDate BETWEEN @date1 AND @date2 AND Area=@Area
the query cannot be ran unless the queries parameters were provided. I have to access this query in code to add the parameters from controls. Is it possible to access it  or use sqlDataSource instead?
Thanks alot

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved