Checking The SELECT Statement For An SqlDataAdapter

Sep 21, 2006

I'm trying to pass a querystring to an SqlDataAdapter object. To check if the query is a valid SELECT statement, I simply use a try-catch. But dispite the try-catch it still accepts valid INSERT statements. However, in the parameterlist of the SqlDataAdapter the required parameter is a Transact SQL SELECT statement or a stored procedure... Am I doing something wrong? Here is my code:try
{
my_conn = conn_open();
da = new SqlDataAdapter(query, my_conn);
da.Fill(result.resultDataset);
my_conn.Dispose();
}
catch (Exception e)
{
result.errMsg = "Database Error: " + e.Message;
result.success = false;
} Kehil

View 1 Replies


ADVERTISEMENT

If Statement Checking For Duplicates???

Jul 2, 2007

I'm wanting to create a if statement that will query a table and check for a duplicate and if there is a duplicate return that uniqueID or if it doesnt find a duplicate continue to add a new record...so here is my question is there a way to run a if statement that will call a function (lets say a function that returns like a bool) and if it finds a duplicate grab that id and store it in a session.....i guess my question is how do i query the database for a record and if found return true if not return false....that is my question....

View 9 Replies View Related

Checking For Null Statement In Sql

Feb 16, 2006

Hi Everyone,
  I have a query
 
select name, address, city from table1
I want to append address +'-' + city in this query . I want to show hyphen only if both address and city are not null. If name is null then I don't want to show hyphen. How can I get around this problem.
Any help will be appreciated.
 
Thanks,
Vinki
 
  

View 3 Replies View Related

Select Statement Within Select Statement Makes My Query Slow....

Sep 3, 2007

Hello... im having a problem with my query optimization....

I have a query that looks like this:


SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)


it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...

View 3 Replies View Related

Checking For @@ERROR After A SELECT Inside A Transaction?

Feb 26, 2006

Is it normal practice to check for @@ERROR  after a SELECT statement that retrieves data from a table OR we should only check for @@ERROR after a DELETE/INSERT/UPDATE type of statement? The SQL statement is inside a transaction.

View 1 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View 5 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Using Conditional Statement In Stored Prcodure To Build Select Statement

Jul 20, 2005

hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View 2 Replies View Related

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement

Oct 29, 2007

Hi guys,
I have the query below (running okay):



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:


Field01 Field02

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

192473 8461760

192474 22810



Because other reasons. I need to modify that query to:



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

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

22810
8461760

And what I need is (without showing any other field):

Field02

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

8461760
22810


Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View 3 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

How To Use Select Statement Inside Insert Statement

Oct 20, 2014

In the below code i want to use select statement for getting customer

address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname

from customer table.Rest of the things will be as it is in the following code.How do i do this?

INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,

[code]....

View 1 Replies View Related

Help With Delete Statement/converting This Select Statement.

Aug 10, 2006

I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID

I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:

SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180

View 1 Replies View Related

Select Statement Problem - Group By Maybe Nested Select?

Sep 17, 2007

Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt 

View 2 Replies View Related

SQL Select Statement To Select The Last Ten Records Posted

Aug 6, 2007

SELECT Top 10    Name, Contact AS DCC, DateAdded AS DateTimeFROM         NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database.  As you know each day someone could add one or two records, how can I write it show the last 10 records entered.

View 2 Replies View Related

Using Select Statement Result In If Statement Please Help

Jul 11, 2007

Hello
How can i say this I would like my if statement to say:  if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement.
<% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>"
ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)">
<SelectParameters>
<asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" />
</SelectParameters>
</asp:SqlDataSource>any help would be appreciated

View 2 Replies View Related

If STATEMENT Within Select Statement Syntax

May 15, 2008

Hi,

I am a newbie to this site and hope someone can help....

I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is:

if(TL_flag= '1', "yes") as [Trial Leave]

it is coming up with an error.... I can use Select case but I should not need to as this should work?

Any ideas?

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

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

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 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

Can I Use SELECT Statement To Select First 100 Record????

Apr 21, 1999

I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?

Thanks,

Sam

View 1 Replies View Related

Select Statement With Count Within Another Select

Aug 23, 2013

I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).

Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.

SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees,
(
SELECT COUNT(gifts.donor_id) AS Count2
FROM gifts
WHERE gifts.donor_id = ed.id_number
) subquery2

[code]....

View 1 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







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