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


ADVERTISEMENT

SqlDataAdapter / DataSet And SQL Server Side Trigger / Constraints

Apr 23, 2007

When updating data using .NET 2.0 SqlDataAdapter and a strong typed dataset, it seems that constraints (foreign key) and trigger are disabled. I'm using a stored procedure to delete rows and calling it from SSMS causes the trigger to fire. Calling it via the SqlDataAdapter not.



Can someone confirm this?



At least here, my triggers don't fire.



How can I enable, that triggers in SQL Server do fire?



Are there differences in this behaviour when using batch updates or not?



TIA,



Hannoman

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

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

SQL Server 2008 :: Populate One Dataset In SSRS Based On Results From Another Dataset Within Same Project?

May 26, 2015

I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.

View 0 Replies View Related

Integration Services :: Perform Lookup On Large Dataset Based On A Small Dataset

Oct 1, 2015

I have a small number of rows in a dataset, Table 1.  There is a CLOB on a large dataset, Table 2.  They join on a PK.  I would like to retrieve this CLOB and add it to the data flow for Table1.  In short I want to emulate the following:

Table 1:  Small table without CLOB, 10 rows. 
Table 2: Large table with CLOB, 10,000,000 rows

select CLOB
from table2
where pk = (select pk from table1)

I want this to return the CLOBs for the small number of rows in Table 1.  The PK is indexed obviously so it should be a fast look up.

Table 1 and Table 2 live on different Oracle databases.  How do I perform this operation efficiently in SSIS?  It seems the Lookup and Merge Join wont do this.

View 2 Replies View Related

Reporting Services :: Populate One Dataset In SSRS Based On Results From Another Dataset Within Same Project?

May 27, 2015

I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.

I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.

View 3 Replies View Related

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

Configuring SQLdataadapter With Nested Query.

Aug 14, 2007

 
How to configure sqldatadapter with query like
 "select name ,id from tlb1 where id in (select id from tlb2 where dept=@dept)"
   Is the nested subquery is not allowed while  configuring sqldaadapter?
  Swati
 
 

View 1 Replies View Related

Updating DataTable In DataBase Using SqlDataAdapter

Sep 4, 2007

Hi GuysI am facing a problem while updating a DataTable to database using sqldataadapter.In my application I am fetching a dataTable from Database and filling values into textboxes on the UI.User has given facility to change or add new texboxes (new row) on the fly .(Textboxes on the UI are like in a row(tr) having two textboxes in each row.)I am again then converting a new (empty) datatable from scrap and filling its rows with the value of textboxes on submit button event.The datatable which I have created has the same schema as the database table.Now what I want here is that changed value should be reflected to the already existed rows in database and only new rows should be  inserted.I am using a SQLDataAdapter having two sqlcommands , one for update with update procedure name and parameter mapping and another for insert with parameter mapping.But SqlDataAdapter is inserting fresh new rows all the time in the database table not updating the older one.Please help me in the matterThanks & RegardsVishal Sharma 

View 2 Replies View Related

Setting Field To Integer From SqlDataAdapter

Apr 1, 2008

How do i get the result 'puzzle' from the SQL and assign it to Integer and use it to compare with Request.Form ? 
 Protected Sub Page_Load(ByVal sender As Object, _
                      ByVal e As System.EventArgs) Handles Me.Load
 
            Dim connString As String = _           ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString
 
           Dim strsql As String = _
           "Select CustomerID,CustomerNo,dbo.LA_DEC(Passkey) as passcode,dbo.LA_DEC(PuzzleKey) As puzzle, PuzzleFlag, NickName from Customers Where CustomerNo = '" & myAccount & "'"
            Using myConnection As New SqlConnection(connString)                  Dim myCommand As New SqlCommand(strsql, myConnection)
 
                  myConnection.Open()
                   Dim PuzzleDataSet As New DataSet
                  Dim PuzzleAdapter As New SqlDataAdapter(myCommand)                                    //                              <----    Coding here
 
 
 
                   myConnection.Close()
         End Using
 
End Sub

View 7 Replies View Related

SqlDataAdapter Binding Empty Data

Mar 31, 2004

I have a block of code that does a Fill() with a DataAdapter, which seems to throw an exception if no values are returned. Then, when I try to bind the DataSet that gets filled, an error occurs, it doesn't even try to bind the column headers or anything.

This process seems to work fine when some rows are found, but it is not always guaranteed that the row count will always be > 0. The nice thing about using the Fill() function, is that it creates all my column headers automatically, so I was just wondering if there's a way to do a Fill() then a subsequent DataBind() to a DataGrid which would automatically format the columns even when no rows are found?

Here's a bit of the source.. if the last line finds no rows, it throws an exception ( I assume this is normal):

strSQL = "SELECT * FROM Prods";
cm.CommandText = strSQL;
da = new SqlDataAdapter( cm );
da.Fill(shelf);

Anyone got any ideas on how to fix this problem?


Brent

View 3 Replies View Related

Sqldataadapter Problem Using Union Query

Feb 28, 2005

hello i have this query but the data adapter wont accept it

i get this error

syntax error or access violation

if i hardcode the @person parameter in query analyzier it works fine.

ex: @person = 33 hardcoded

but it doesnt work in data adapter either 33 or @person

can someone please help me out




SELECT m.title, m.movieID, a.personID, 'Actor', p.firstName + ' ' + p.lastName
FROM movieactors a, people p, movies m
WHERE a.personID = p.personID
AND a.movieID = m.movieID
and a.personid = @person
UNION
(SELECT m.title, m.movieID, a.personID, 'Director', p.firstName + ' ' + p.lastName
FROM moviedirectors a, people p, movies m
WHERE a.personID = p.personID
AND a.movieID = m.movieID
and a.personid = @person
UNION
(SELECT m.title, m.movieID, a.personID, 'Writer', p.firstName + ' ' + p.lastName
FROM moviewriters a, people p, movies m
WHERE a.personID = p.personID
AND a.movieID = m.movieID
and a.personid = @person
UNION
(SELECT m.title, m.movieID, a.personID, 'Cinematographer', p.firstName + ' ' + p.lastName
FROM moviecinematographers a, people p, movies m
WHERE a.personID = p.personID
AND a.movieID = m.movieID
and a.personid = @person
UNION
(SELECT m.title, m.movieID, a.personID, 'Producer', p.firstName + ' ' + p.lastName
FROM movieproducers a, people p, movies m
WHERE a.personID = p.personID
AND a.movieID = m.movieID
and a.personid = @person))));

View 2 Replies View Related

How Can I Use SQL Reporting Services To Get A Dynamic Dataset From Another Web Service As My Reports Dataset?

May 21, 2007

I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking

View 2 Replies View Related

Deleting Using SqlDataAdapter Via A Data Access Layer

Feb 20, 2008

I've a management module (managing Products) currently being displayed on the aspx page using ObjectDataSource and GridView control.The datasource is taken from a class residing in my Data Access layer with custom methods such as getProducts() and deleteProduct(int productID)I'm currently using SqlDataAdapter as well as Datasets to manipulate the data and have no big problems so far.However, my issue is this, each time i delete a product using the deleteProduct method, I would need to refill the dataset to fill the dataset before i can proceed to delete the product. But since I already filled the dataset using the getProducts() method, is it possible to just use that dataset again so that I dont have to make it refill again? I need to know this cos my data might be alot in the future and refilling the dataset might slow down the performance. 1 public int deleteCompany(Object companyId)
2 {
3 SqlCommand deleteCommand = new SqlCommand("DELETE FROM pg_Company WHERE CompanyId = @companyId", getSqlConnection());
4
5 SqlParameter p1 = new SqlParameter("@companyId", SqlDbType.UniqueIdentifier);
6 p1.Value = (Guid)companyId;
7 p1.Direction = ParameterDirection.Input;
8
9 deleteCommand.Parameters.Add(p1);
10 dataAdapter.DeleteCommand = deleteCommand;
11
12 companyDS = getCompanies(); // <--- I need to refill this before I can delete, I would be iterating an empty ds.
13
14 try
15 {
16 foreach (DataRow row in companyDS.Tables["pg_Company"].Select(@"companyId = '" + companyId + "'"))
17 {
18 row.Delete();
19 }
20 return dataAdapter.Update(companyDS.Tables["pg_Company"]);
21 }
22 catch
23 {
24 return 0;
25 }
26 finally { }
27 }
I thank you in advance for any help here.

View 3 Replies View Related

Help On Using SqlDataAdapter InsertCommand Targeting LocalSqlServer Connection

Apr 29, 2008

 I am trying to use a sqldataadapter to log a record to a table with a stored procedure call. I am getting the following error: "System.NullReferenceException: Object reference not set to an instance of an object.
at ContactUs.LogEmail() in c:inetpubwwwrootMDWelcomeContactUs.aspx.vb:line 55"Here is my code: Protected Sub LogEmail()
'Trace.IsEnabled = True
Dim strConnect As String = ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString
Dim strCommandText As String = "dbo.EmailsLogged_Insert"
Dim objConnect As New SqlConnection(strConnect)
Try
objConnect.Open()
Dim objDataAdapter As New SqlDataAdapter(strCommandText, objConnect)
objDataAdapter.InsertCommand.CommandText = strCommandText'this is line 55 in my source where error is thrown
objDataAdapter.InsertCommand.CommandType = CommandType.StoredProcedure
With objDataAdapter.InsertCommand.Parameters
.Add("@emailaddress", SqlDbType.VarChar, 255).Value = UsersEmail.Text
.Add("@subject", SqlDbType.VarChar, 255).Value = Subject.Text
.Add("@company", SqlDbType.VarChar, 255).Value = Company.Text
.Add("@phone", SqlDbType.VarChar, 255).Value = Phone.Text
.Add("@emailbody", SqlDbType.VarChar, 255).Value = Body.Text

End With
objDataAdapter.InsertCommand.ExecuteNonQuery()
objConnect.Close() 'then close the connection
Catch SQLError As SqlException
Response.Write("<H1>SQL Exception:</H1>")
Response.Write("<h2>" & SQLError.Procedure & "</h2><br />")
Response.Write("<h2>" & SQLError.Message & "</h2><br />")
Dim lineNum As Integer
Dim errMsgArray() As String = Split(SQLError.StackTrace, vbCrLf, , Microsoft.VisualBasic.CompareMethod.Text)
Do While lineNum <= errMsgArray.Length - 1
If Trim$(errMsgArray(lineNum)) <> "" Then
Response.Write("<br>" & errMsgArray(lineNum))
End If
lineNum = lineNum + 1
Loop
Catch objError As Exception
Dim oMsg As String
'display error details
oMsg = "<b>* Error while accessing data</b>.<br />" _
& objError.Message & "<br />" & objError.Source

Trace.Write(oMsg)
Response.Write(oMsg)
Response.End()
End Try
End Sub
 I think the connection is OK (debugging shows that objConnect.State is open) but I am unclear why this is incorrect code here. 

View 2 Replies View Related

What Happened To Nice SqlDataAdapter.Fill Method In ASP.net 2.0

Dec 26, 2005

What happened to nice SqlDataAdapter.Fill method in ASP.net 2.0 + How can i now access and traverse trhough table.
ex : sqlDataAdapter.Fill(dataSetname.tblsname);
            dataSet11.rows[][]

View 5 Replies View Related

Listing Datasets In Report (dataset Name, Dataset's Commands)

Oct 12, 2007



Is there any way to display this information in the report?

Thanks

View 3 Replies View Related

Duplicate Last Record When Using SqlDataAdapter.Update For Insert Command

Jun 24, 2007

I'm getting duplicate records for the last record in the datatable. No matter how much or how little my datatable contains row records, it always duplicate the last one for some reason. Is there something wrong with my code below? EXAMID pulling from another stored procedure, which is outputed back to a variable.
---Data Access Layer---- If dt.Rows.Count > 0 Then
'INSERT EXAM ROSTERInsertComm = New SqlCommandsqladapter = New SqlDataAdapterInsertComm = New SqlClient.SqlCommand("ExamOfficers_AddOfficerSpecificExamRoster", conndb)InsertComm.CommandType = CommandType.StoredProcedure
sqladapter.InsertCommand = InsertCommInsertComm.Parameters.Add("@examid", SqlDbType.Int)InsertComm.Parameters("@examid").Value = examidInsertComm.Parameters.Add("@officerid", SqlDbType.Int, 12, "Officer_UID")InsertComm.Parameters.Add("@reimburse", SqlDbType.Bit, 12, "ReimburseToDb")InsertComm.Parameters.Add("@posttest", SqlDbType.Int, 12, "Post_Test")InsertComm.Parameters.Add("@pqcdate", SqlDbType.DateTime, 12, "pqc_date")InsertComm.Parameters.Add("@pqcscore", SqlDbType.Int, 12, "pqc_score")
conndb.Open()
sqladapter.UpdateBatchSize = 100InsertComm.UpdatedRowSource = UpdateRowSource.Nonesqladapter.Update(dt)
InsertComm.ExecuteNonQuery()InsertComm.Dispose()
End If
----Stored Procedure----
ALTER PROCEDURE [dbo].[ExamOfficers_AddOfficerSpecificExamRoster]
@ExamID as int,@OfficerID as int,@reimburse as bit=NULL,@posttest as int=NULL,@pqcdate as datetime=NULL,@pqcscore as int=NULL
ASBEGIN SET NOCOUNT ON;
Insert Into Exam_Officers(EXAM_UID,Officer_UID,reimburse,post_test,pqc_date,pqc_score)values(@ExamID,@OfficerID,@reimburse,@posttest,@pqcdate,@pqcscore)
END

View 1 Replies View Related

DeadLock Occur While Execute SqlDataAdapter.Fill Method

Mar 25, 2008

Hi,
I am using  following code to fill my dataset. when multiple user connect to the web-site it will give me following error:
Error Message: Transaction (Process ID 98) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I am using stored procedure to select records from database.
DataSet ds = new DataSet();SqlConnection mc = OpenSqlConnection();
try
{SqlCommand cmd = new SqlCommand(strCmd, mc);cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;if (parameters != null)
{foreach (KeyValuePair<string, object> p in parameters)
{
cmd.Parameters.AddWithValue(p.Key, p.Value);
}

}SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
}catch (Exception ex)
{
}
finally
{
mc.Close();
}
return ds;
 
Appreciate your help,
prashant

View 1 Replies View Related

Update A Table With SqlDataAdapter...does It Work With Sql Text DataType ?

Dec 15, 2003

I tryed to update tables part of my MSDE database, using the SqlDataAdapter.Update() method. It worked fine untill I tryed to update a table that has a Column with the Text SQL DataType. It didn't work. The error was :

"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

Is there a way to do it ?
Thanks,
Jeff

View 4 Replies View Related







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