I moved from using data adapters to tableadapters. This is my sql query that is included in my data adapter
sql = "SELECT f.Date, CPT, CPTModifier, CPTModifier2, Description, Fee, Tax, Balance, [SPatient Number]
FROM Financial f
INNER JOIN Demographics d ON d.[Patient Number]=f.[SPatient Number] WHERE (f.[SPatient Number]= (" & intPID & "))"
conn.ConnectionString = My.Settings.EbtblsConnectionString
da = New SqlDataAdapter(sql, conn)
intPID is an input box that a user types a number in and compares it to SPatient Number on the Financial table. The query has been working fine.
However, when I go into designer view and hit the table adapter's "add query" property, in vs2008. I put that query in, and it gives me an error saying that intPID is not a valid column. I am aware it's not a valid column..because it isn't a column, it's a variable in the windows form. How do I put a variable in the sql string for table adapters?
Hi, I have problem vith implementing Transaction with TableAdapters. My code is: try { using (bookTableAdapter adapter = new bookTableAdapter()) { transaction = TableAdapterHelper.BeginTransaction(adapter); ID=(Guid) adapter.bookInsertNew(bookOrgId, bookISBN, bookName, Convert.ToInt32(bookYear), publisherId, languageId, orgLanguageId, bookTranslation, Convert.ToInt32(bookColors), Convert.ToInt32(bookPages), bookFormat, Convert.ToDouble(bookWidth), Convert.ToDouble(bookHeight), Convert.ToDouble(bookWeight), Convert.ToDouble(bookThickness), Convert.ToInt32(bookPrize), bookResumeText, bookResumeHtml, bookPicture_s, bookPicture_m, bookPicture_l, Convert.ToInt32(bookStatusId));
} using (bookAuthorTableAdapter adapter1 = new bookAuthorTableAdapter()) { TableAdapterHelper.SetTransaction(Adapter1, transaction);
for (int i = 0; i <= leng1; i++) { adapter1.Insert(author[i],ID,1);
} } transaction.Commit(); } catch { transaction.Rollback(); throw; }finally { transaction.Dispose(); } First Insert is ok, but trow exception on second try to insert in database. Exceprion is : Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.The statement has been terminated.Source Error:
Line 1839: } Line 1840: try { Line 1841: int returnValue = this.Adapter.InsertCommand.ExecuteNonQuery(); Line 1842: return returnValue; Line 1843: } I also set Connect Timeout=200 in Web.config but nothing change :( Please help :( :( :(
So I just created a very nice dataset class with 30+ tables and relationships for a very large project. Each table has 3-8 associated queries besides the usual Fill/Get and then there will be many more in the BLL that utilize this DAL. I then created a BaseDAL class and other DAL classes that inherit from the BaseDAL(has basic sql query access if needed). What I want to know is if the way I am doing this is correct and if this is a good idea for a database of this size. I've never used TableAdapters before and I don't know if I should be using them at all? I don't know how they handle connections and if one user will have more than one connection open because of using them or will they all share an open connection? Can someone please direct me. Thank you! Also, if you notice in the top of the UserDAL code I have listed below, I declare and initialize the usersTableAdapter and usersDeptTableAdapter. Should the call to create the object be moved into each function?Public Class Users : Inherits BaseDAL Private usersDS As OEE_USERSTableAdapter = New OEE_USERSTableAdapter()Private usersDeptDS As OEE_USER_DEPTSTableAdapter = New OEE_USER_DEPTSTableAdapter()Private retcode As Integer = 0
#Region "USER" ' Returns employee ID if user is in system, otherwise returns 0 Public Function ValidateUser(ByVal username As String) As String 'Dim usersDS As OEE_USERSTableAdapter = New OEE_USERSTableAdapter()Dim dtUser As New OEE_USERSDataTable dtUser = usersDS.GetUser(username) If dtUser.Rows.Count = 1 ThenReturn dtUser.Rows(0)("EMPLID").ToString Else Return "0" End IfEnd Function
'Add User Public Function InsertUser(ByVal emplid As String, ByVal username As String) As String Dim insertedEmplid As String = "" emplid = usersDS.AddUser(emplid, username)Return insertedEmplid End Function Public Function FlagAsInactive(ByVal emplid As String) As Boolean retcode = usersDS.FlagAsInactive(emplid)Return retcode <> 0 End Function 'Sets inactive flag to null for user (Reactivates them) Public Function ActivateUser(ByVal emplid As String) As Boolean retcode = usersDS.ActivateUser(emplid) Return True End Function 'Returns list of all usersPublic Function GetUsers() As DataTable Return usersDS.GetUsers End Function #End Region
Hello. I have been using Table Adapters in VS2005 for the last several projects and love them. However, when I am connecting to a 2005 SQL Server database, I have problems connecting. That is, I cannot create new stored procedures with the TableAdapter Config Wizard. For instance, I try to create a new query. I use the wizard to create my SQL statement and continue through until then end of the wizard, when it asks me to "finish". At this point, I get an error -- This only happens when trying to connect to a 2005 table. Any ideas?
Hello everyone... I have created a report that pulls data from 5 different tables. I have created a tableadapter for this. I have a sql stored procedure with left joins that I am trying to use but keep getting key violations and not null value violations when I try to use it. I have tried setting the NULLValue to NOT throw exception but that has not helped. I have also tried to writing a query for each table. I can add each query to my tableadapter but I can use them all at the same time for some reason. Can this be done? When I go to my report and try to add fields to it from the tableadapter, I only see the results of one query. I am only trying to SELECT. I am not doing any updates or deletes to the tables on the SQL Server. Any advice would be greatly appreciated.
I've been working with TableAdapters, DAL, and BLL for a few months now. At this point, most of my new queries are too customized with multiple tables to fit into any of my existing TableAdapter schema's. For instance, if I want to execute an aggregate function with group by's and counts that includes three separate tables, how would I go about doing this with a strongly typed DAL using TableAdapters. My workaround to this point is to just create a whole new TableAdapter for that one query.Is there anyway to extend the DAL in a strongly typed dataset to create these "read-only" aggregate queries, or should I continue to create new TableAdapters for each one of these queries? What's the proper way to handle such customized SQL statements that don't fit any of my existing TableAdapters?
hello Could you please help me with this problem? I have a stored procedure like this: ALTER PROCEDURE dbo.UniqueChannelName ( @UserName nvarchar(50), @ChannelName nvarchar(50) ) AS return 5;
Then inside of my dataset, I added a new query(dataset1.QueriesTableAdapter) to handle above mentioned stored procedure. Properties window is showing that return type of this adapter is of type int32 as we expected to be. now I want to use it inside of my code: DataSet1TableAdapters.QueriesTableAdapter b = new DataSet1TableAdapters.QueriesTableAdapter(); int i; i=Convert.ToInt32( b.UniqueChannelName("Ahmad", "test")); as you may guess, the return type of b.UniqueChannelName("Ahmad", "test") is object and needs to be type-casted before assigning it's value to i; but even after explicit type casting, the value of i is always set to 0, not 5. could you please show me the way? many thanks in advance
The test sub below operates on a SQL Server Table with an xml-type field ("xml"). The purpose of the sub is to learn about storing and retrieving a whole xml document as a single field in a SQL Server table row.When the code saves to the xml field, it somehow automagically strips the xml.document.declaration (<?xml...>). So when it reads the xml field back and tries to create an xmldocument from it, it halts at the xmldocument.load.I order to get the save/retrieve from the xmlfield to work, I add the <?xml declaration to the string when I read it back in from the xml field (this is in the code below).At that point the quickwatch on the string I'm attempting to load into the xmldocument is this:-----------------------------------------------------<?xml version="1.0" encoding="utf-16" ?><Control type="TypeA"><Value1><SubVal1A>Units</SubVal1A><SubVal1Btype="TypeA">Type</SubVal1B></Value1><Value2><SubVal2A>Over</SubVal2A><SubVal2B>Load</SubVal2B></Value2></Control>-----------------------------------------------------The original xml document string is this:-----------------------------------------------------<?xml version="1.0" encoding="utf-16" ?><Control type="TypeA"> <Value1> <SubVal1A>Units</SubVal1A> <SubVal1B type="TypeA">Type</SubVal1B> </Value1> <Value2> <SubVal2A>Over</SubVal2A> <SubVal2B>Load</SubVal2B> </Value2></Control>-----------------------------------------------------which seems to have all the same characters as the quickwatch result above, but clearly is formatted differently because of the indenting.THE FIRST QUESTION: Is there a simpler way to do this whole thing using more appropriate methods that don't require adding the xml.document.declaration back in after reading the .xml field, or don't require using the memorystream to convert the .xml field in order to load it back to the XML document.THE SECOND QUESTION: Why does the original document open in the browser with "utf-16", but when I write the second document back to disk with "utf-16" it won't open...I have to change it to "utf-8" to open the second document in the browser.Here's the test sub'============================================ Public Sub XMLDSTest() '=========================================== Dim ColumnType As String = "XML" '=========================================== '----------Set up dataset, datatable, xmldocument Dim wrkDS As New DSet1() Dim wrkTable As New DSet1.Table1DataTable Dim wrkAdapter As New DSet1TableAdapters.Table1TableAdapter Dim wrkXDoc As New XmlDocument wrkXDoc.Load(SitePath & "App_XML" & "XMLFile.xml") Dim str1 = wrkXDoc.OuterXml Dim wrkRow As DSet1.Table1Row wrkRow = wrkTable.NewRow '=======WRITE to SQL Server============== '------ build new row With wrkRow Dim wrkG As Guid = System.Guid.NewGuid TestKey = wrkG.ToString .RecordKey = TestKey .xml = wrkXDoc.OuterXml '<<< maps to SQL Server xml-type field End With '----- add row to table and update to disk wrkTable.Rows.Add(wrkRow) wrkAdapter.Update(wrkTable) wrkTable.AcceptChanges() '----- clear table wrkTable.Clear() '=======READ From SQL Server ============== '----refill table, read row, wrkAdapter.FillBy(wrkTable, TestKey) Dim wrkRow2 As DSet1.Table1Row = _ wrkTable.Select("RecordKey = '" & TestKey & "'")(0) '===== WRITE TO New .xml FILE =========================== Dim wrkS1 As New StringBuilder Select Case ColumnType Case "XML" '---if xml build xml declaration: '---add this to xml from sql table => <?xml version="1.0" encoding="utf-16" ?> wrkS1.Append("<?xml version=" & Chr(34) & "1.0" & Chr(34)) wrkS1.Append(" encoding=" & Chr(34) & "utf-16" & Chr(34) & " ?>") wrkS1.Append(wrkRow2.xml) End Select Dim wrkBytes As Byte() = (New UnicodeEncoding).GetBytes(wrkS1.ToString) Dim wrkXDoc2 As New XmlDocument Dim wrkStream As New MemoryStream(wrkBytes) wrkXDoc2.Load(wrkStream) '=========================================== '---- this just shows that the file actually was touched Dim wrkN2 As XmlNode = wrkXDoc2.CreateNode(XmlNodeType.Text, "ss", "TestNode2") wrkN2 = wrkXDoc2.SelectSingleNode("//Value1/SubVal1B") wrkN2.Attributes("type").Value = "This was from the xml field" '---------------- '------ update the encoding....otherwise the file won't open in the browser with utf-16 Dim wrkN1 As XmlNode = wrkXDoc2.CreateNode(XmlNodeType.Element, "ss", "TestNode") wrkN1 = wrkXDoc2.FirstChild wrkN1.InnerText = Replace(wrkN1.InnerText, "utf-16", "utf-8") '------------Now write the file back as an .xml file Dim wrkFilePath As String = SitePath & "App_XML" & "XMLFile2.xml" Dim wrkXW As XmlWriter = XmlWriter.Create(wrkFilePath) wrkXDoc2.WriteContentTo(wrkXW) wrkXW.Close() End Sub===============================
