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.
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?
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
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.
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; }
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)
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!
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
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
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?
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()
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.
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")
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.
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"]);
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
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
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.
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.
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.
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
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
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
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
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);
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))));
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
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.
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.
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[][]
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
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;
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."
