SQL Syntax error

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Ado
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Problem with ODP.Net and TNSNames I am using asp.net (Framework 1.1) with Oracle 9i and ODP.Net version 9.2.0.401. I am developing using a local copy of IIS version 5.1. When I use a connection string to connect to Oracle utilising a data source in my TNSNames.ora file initially everything is fine and I can connect without any problem. After about 2 minutes as I move from page to page and requery the database I seem to loose the connection with the error "ORA-12154: TNS:could not resolve service name". I am using the following line in my web.config file to create the connection string:- <add key="Main.ConnectionString" value="data source=dev.ora;User Id=xxxx;Password=xxxx;persist security info=false;Pooling=true;Connection Lifetime=120;Min Pool Size=0;Incr Pool Size=1;Decr Pool Size=1;Max Pool Size=20;"/> Interestingly if I change this to the relevant data source entry within my TNSNames file:- <add key="Main.ConnectionString" value="data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxx)(PORT=xxxx)))(CONNECT_DATA =(SERVICE_NAME = dev.xxx.xxx.xx)) );User Id=xxxx;Password=xxxx;Pooling=true;Connection Lifetime=120;Min Pool Size=0;Incr Pool Size=1;Decr Pool Size=1;Max Pool Size=20;"/> Then I do not have any problems. I would prefer to use TNSNames rather than entering the datasource directly. Has anyone any ideas why I loose the connection? Tag: SQL Syntax error Tag: 125743
  - 2
    - newbie: why does DataSet AutoIncrement advance by 2? I create a DataSet called database and add a table to it as shown below. Why does the AutoIncrement value advance by 2 every time I add a record? How do I get it to advance by 1? DataTable tblProject = database.Tables.Add("TableProject"); DataColumn project_ID = tblProject.Columns.Add("Project_ID", typeof(Int32)); project_ID.AllowDBNull = false; project_ID.Unique = true; project_ID.AutoIncrement = true; project_ID.AutoIncrementSeed = 1; project_ID.AutoIncrementStep = 1; tblProject.PrimaryKey = new DataColumn[] { tblProject.Columns[0] }; //other columns added here... Tag: SQL Syntax error Tag: 125738
  - 3
    - Internal .Net Framework Data Provider error 12 Hi NG, i use the oldedbExecuteReader Method on an Access DB and get a System.InvalidOperationException with the message :"Internal .Net Framework Data Provider error 12" Do anybody now what the framework will tell me ? I cant't find a desciption of the error. The error ocurs sometimes and somitimes not. Thanks Maik Tag: SQL Syntax error Tag: 125737
  - 4
    - .net and DB Data layer : Best Practice I am converting an app. from PHP to .net. I know that you can create an xsd and then have the system generate code to load data into a dataset. I assume that you use this and an a data adapter to talk to the DB. However, is this the "optimal" or recommended way to add, update, etc. information? Or should one create code to accept the data and then pass it to a stored procedure? I do not dislike the adapter way, but I can see that it could introduce problems and the programmer can loose control which may cause errors or data integrity violations. Comments? Thanks Tag: SQL Syntax error Tag: 125728
  - 5
    - Sychronize Key between Database and Dataset on Insert Hi, I'm sure this has been asked many times before, but I haven't had a lot luck finding an answer; not googling the question right. So I apologize before hand. I have a TableAdapter whose insert command uses a store procedure. When I call TableAdapter.Update() passing in the table the database gets updated, but the primary key in of the DataTable doesn't match the one in the database. I've read some responses about selecting the "Refresh Dataset" option under Advanced Options in the Dataset Designer, but I only see that option when using a SQL statement and not a stored procedure. I prefer to ues the stored procedures. Is there an option I can configure on the TableAdapter that I missed? What's the simplient way to handle this? Cheers, Aeden Tag: SQL Syntax error Tag: 125724
  - 6
    - Search button to filter a Dataset Hi all, I hope someone can help with this relatively simple problem. I am building a timesheet application using ASP.NET C# with Visual Studio 2003.As it is only a protoype application, my database has been made in MSDE. I have a 'View Resources' page, the purpose of which is to show all employees, taken from that table in the database. This works fine, up to a point. I have a DataGrid, which is done with a DataSet and a DataAdapter. I now want the user to be able to 'filter' the DataGrid, using a text box for input, and a 'search' button. I have tried different ways of doing this but can't get it to work. I imagine that it is achieved by running some kind of different SQL statement (with a % LIKE % operator) in the btnSearch_Click method, and re-binding the Data Grid after the filter has finished. So for example the user could enter 'John' in the 'First Name' text box and click 'search', and the Data Grid would refresh accordingly with all records LIKE 'John'. My Page_Load code looks like this currently ------------------------------------------------------------------------- this.sqlDataAdapter1.Fill(this.dsResources1); DataGrid1.DataBind(); sqlConnection2.Close(); this.DataGrid1.PageIndexChanged += new System.Web.UI.WebControls.DataGridPageChangedEventHandler(this.GridPageIndexChanged); -------------------------------------------------------------------------------------------- Hope someone can help, I'm sure the btnSearch_Click method is where I need to put the code. Thanks in advance, Al Tag: SQL Syntax error Tag: 125717
  - 7
    - OutOfMemoryException on DataTables Hi, In my application I have text(flat) file as input and I have to generate an XML file. The maximum input text file size can be 900MB and gererated xml may result 2+ GB. Based on the first column value from the text file, the row will be moved to any of those 23 DataTable which are created onfly. For eg. 01;- data for Table 1- 02;- data for Table 2- 03;- data for Table 3- 04;- data for Table 4- 04;- data for Table 4- 04;- data for Table 4- 04;- data for Table 4- 04;- data for Table 4- Based on the child relation, I have to create XML node, after stripping the parent-child relation column. I am using XmlTextWriter to write the xml data, which are fetched from tables based on select with different condition. The server uses 2GB RAM memory When the application process a 500MB file, it throws OutOfMemoryException. In the task manager, the utilised memory when the system throws OutOfMemoryException is 2.15 GB. But in my desktop, for the surprise, the system has 512 MB and when I load several application, the process memory reached 1GB without any OutOfMemoryException issues. Can you one help me in this regard. Thanks in advance With Regards venkatg Tag: SQL Syntax error Tag: 125710
  - 8
    - GridView and reader source Hi, I can give a DataTable to gridview and in one line data are displayed int the gridview using : dgvEmployee.DataSource = myDataTable.; When I use a dataReader instead, do "dgvEmployee.DataSource = myReader;", nothing is displayed in the gridview. Does that mean that GridView dosnt or can not bind to a data reader Thanks for your help Tag: SQL Syntax error Tag: 125708
  - 9
    - DataAdapter + DataRow + Inserting a record ########################################################################### private void btnAdd_Click(object sender, System.EventArgs e) { OleDbConnection con = new OleDbConnection(str); con.Open(); string qry = "select * from t_contact"; OleDbDataAdapter da = new OleDbDataAdapter(qry,con); DataSet ds = new DataSet(); da.Fill(ds,"t_contact"); DataTable dt = new DataTable(); dt = ds.Tables["t_contact"]; DataRow dr ; dr = dt.NewRow(); dr["FirstName"] = txtFirstName.Text; dr["LastName"] = txtLastName.Text; dr["E-Mail"] = txtEMail.Text; dr["PhoneNumber"] = txtPhoneNumber.Text; ds.Tables["t_contact"].Rows.Add(dr); textBox2.Text = ds.GetXml(); da.Update(ds,"t_contact"); } ########################################################################### __________________________________________________________ here e-mail is the primary key.Unable to insert record in the database. __________________________________________________________ The error coming is : System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at Contact_new.Form1.btnAdd_Click(Object sender, EventArgs e) in c:\documents and settings\administrator\my documents\visual studio projects\projects\contact_new\form1.cs:line 234 Tag: SQL Syntax error Tag: 125701
  - 10
    - Typed datasets 3-tier application We are in the middle of a project that requires both a web GUI and web services. We have elected to go with a 3-tier (data, business, presentation) model using typed datasets as the data layer. We are not using either the sql data source or the object data source on the page, just rolling our own, old school style. The customer(s) will are all business partners with reliable and fast connects and there is no expectation of ever having a consumer direct option. We are posting this since we have been unable to find any real examples of this type of implementation and are looking for help in poking / filling the holes. Who knows you may be looking for an example, so here goes..... Data Layer Straight vs2005 generated typed datasets for all updatable data access. For read only reporting and display data access we will use untyped datasets. We have not had much success with data readers so we will shy away from them if possible. You can generate a strongly typed dataset so we won't bore you with the details. Business layer Accepts requests from pres layer or web service layer and interacts with the data layer. Any necessary validation, conditional enforcement, or other business logic is checked / applied. In many cases this is just a pass through if we have done a good job with required fields, getting and setting nulls for value types, and client side validation. In others, especially calls from the web services, there will be a bit of processing going on in the Biz obj. This layer is not creating a business object from the typed dataset, only interacting with the datasets as they pass through. Presentation layer Is "Aware" of the typed datasets, but has no idea where they come from, and is limited to reading data from them and plugging data into them. All data access must go through the biz layer, even if just a pass through or simple lookup. The pages are smart enough to do most of the required field checking and input validation, however any conditional biz logic or other complex enforcement is left to the biz object. We are currently storing the typed datasets in the viewstate. As mentioned before the users all have reliable connectivity. We don't load any data that we are not willing to put on the page so there are no data security issues. All of our updatable datasets are dealing with 1 or 2 rows data so they don't really bloat the viewstate all that badly. We have implemented several pages and so far it seems to be working. The typed dataset is living up to the billing of reducing both design and run time errors due to the increased use of intellisense. The performance so far is great and we are already reaping the benefits of reusable code blocks, both in the data and biz layers. So there it is - shoot it down before it multiplies. We think we are on the right track - but since we built the track - what else would we think? I would be glad to provide some code samples upon request Thanks in advance. Tag: SQL Syntax error Tag: 125697
  - 11
    - Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information Hi, I have data access tier designed in such a way, just by changnging application settings, same lines of code is made to work with both MS SQL Server or MS Access. Code works fine with MS SQL Server. however while executing following code lines on MS Acess ds = new DataSet(); oAdapter.SelectCommand.CommandText = this.SelectCommand; oAdapter.SelectCommand.Connection = oConn; if (idbType == (int) ApplicationConstants.DBType.SQLServer) { oAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand(); oAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand(); oAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand(); } else if (idbType == (int) ApplicationConstants.DBType.Access) { oAdapter.DeleteCommand = oleCommandBuilder.GetDeleteCommand(); oAdapter.InsertCommand = oleCommandBuilder.GetInsertCommand(); oAdapter.UpdateCommand = oleCommandBuilder.GetUpdateCommand(); } return oAdapter.Fill(ds); It throws out the following error System.InvalidOperationException was unhandled Message="Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information." But i have defined primary keys on all tables in Access databasel, just like what i have in MS Sql database. Just to avoid this error i can not use explict Insert, Delete or Update command for Adapter as the same lines of code (above) has to work with 15 diff tables. Any suggestions to this? Thanks & Regards Ganesh Tag: SQL Syntax error Tag: 125691
  - 12
    - Problem in database updation through Adapter in VB.Net.. Hi Friends, I am new to .Net. So I don't know much. I am facing a problem in updating database through ADO.Net I am creating the dataset and there is no problem in the updation and deletion or insertion in the dataset but when I am updating the database through adaptor error occures (Coloured Red). For ref the code follows: Code: Imports System.Data.OleDb Module Module1 Private Const s As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Rabi\Database\DBTest-1.mdb;Persist Security Info=False" Public Con As OleDb.OleDbConnection Public adopt As OleDb.OleDbDataAdapter Public ds As DataSet Public sql As String Dim cmdDel As New OleDb.OleDbCommand, sDelSql As String Dim cmdIns As New OleDb.OleDbCommand, sInsSql As String Dim cmdUpd As New OleDb.OleDbCommand, sUpdSql As String Dim Param As New OleDb.OleDbParameter Public Sub Display(ByRef Table As DataTable) Dim row As DataRow Dim col As DataColumn Dim i, j As Integer For i = 0 To Table.Rows.Count - 1 row = Table.Rows(i) Select Case row.RowState Case DataRowState.Deleted Console.WriteLine("[Deleted]") Case DataRowState.Modified Console.WriteLine("[Modified]") Case DataRowState.Added Console.WriteLine("[Added]") Case DataRowState.Unchanged Console.WriteLine("[Unchanged]") End Select For j = 0 To Table.Columns.Count - 1 If row.RowState <> DataRowState.Deleted Then Console.WriteLine("{0}", row.Item(j)) End If Next Console.WriteLine() Next End Sub Public Sub Main() Try Con = New OleDb.OleDbConnection(s) sql = "Select * from Artist" adopt = New OleDbDataAdapter(sql, Con) ds = New DataSet Catch ex As Exception Console.WriteLine(ex.ToString) Console.ReadLine() End Try sDelSql = "Delete From Artist Where Id = ?" cmdDel.Connection = Con cmdDel.CommandText = sDelSql Param = cmdDel.Parameters.Add("Id", OleDb.OleDbType.Integer) Param.SourceColumn = "@ID" Param.SourceVersion = DataRowVersion.Original adopt.DeleteCommand = cmdDel sUpdSql = "Update Artist Set Name = ? Where Id = ?" cmdUpd.Connection = Con cmdUpd.CommandText = sUpdSql Param = cmdUpd.Parameters.Add("Name", OleDb.OleDbType.Char) Param.SourceColumn = "@Name" Param.SourceVersion = DataRowVersion.Current Param = cmdUpd.Parameters.Add("Id", OleDb.OleDbType.Integer) Param.SourceColumn = "@Id" Param.SourceVersion = DataRowVersion.Original adopt.UpdateCommand = cmdUpd sInsSql = "Insert Into Artist (Id,Name) Values(?,?)" cmdIns.Connection = Con cmdIns.CommandText = sInsSql Param = cmdIns.Parameters.Add("Id", OleDb.OleDbType.Integer) Param.SourceColumn = "@Id" Param.SourceVersion = DataRowVersion.Current Param = cmdIns.Parameters.Add("Name", OleDb.OleDbType.Char) Param.SourceColumn = "@Name" Param.SourceVersion = DataRowVersion.Current adopt.UpdateCommand = cmdIns Try Con.Open() If Con.State = ConnectionState.Open Then adopt.MissingSchemaAction = MissingSchemaAction.AddWithKey adopt.Fill(ds, "Artist") Con.Close() Dim Tables As DataTableCollection Dim Table As DataTable Dim Cols As DataColumnCollection Dim Col As DataColumn Dim Rows As DataRowCollection Dim Row As DataRow Tables = ds.Tables Table = Tables("Artist") Rows = Table.Rows Cols = Table.Columns Console.WriteLine("Original Table Looks Like") Display(Table) Console.ReadLine() Console.WriteLine("Id 1 delete") Rows.Find(1).Delete() Console.WriteLine("deleted") Display(Table) Console.ReadLine() Console.WriteLine("Id 2 Modify") Row = Rows.Find(2) Row.BeginEdit() Row("Name") = "Mantu" Row.EndEdit() Console.WriteLine("Updated") Display(Table) Console.ReadLine() Console.WriteLine("Id 1 Add") Row = Table.NewRow Row("Id") = 4 Row("Name") = "Deepak" Rows.Add(Row) Console.WriteLine("Added") Display(Table) Console.ReadLine() Con.Open() adopt.Update(ds, "Artist") Console.WriteLine("Done") End If Catch ex As Exception Console.WriteLine(ex.ToString) Console.ReadLine() End Try End Sub End Module The Exact error what I got is : "System.Data.OleDb.OleDbException: Parameter ?_1 has no default value. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at ADONetTest.Module1.Main() in D:\Rabi\DotNetPrac\ADONetTest\ADONetTest\Module1.vb:line 176" This String is generated by "Ex.ToString" Tag: SQL Syntax error Tag: 125689
  - 13
    - Problem in database updation through Adapter in VB.Net.. Hi Friends, I am new to .Net. So I don't know much. I am facing a problem in updating database through ADO.Net I am creating the dataset and there is no problem in the updation and deletion or insertion in the dataset but when I am updating the database through adaptor error occures (Coloured Red). For ref the code follows: Code: Imports System.Data.OleDb Module Module1 Private Const s As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Rabi\Database\DBTest-1.mdb;Persist Security Info=False" Public Con As OleDb.OleDbConnection Public adopt As OleDb.OleDbDataAdapter Public ds As DataSet Public sql As String Dim cmdDel As New OleDb.OleDbCommand, sDelSql As String Dim cmdIns As New OleDb.OleDbCommand, sInsSql As String Dim cmdUpd As New OleDb.OleDbCommand, sUpdSql As String Dim Param As New OleDb.OleDbParameter Public Sub Display(ByRef Table As DataTable) Dim row As DataRow Dim col As DataColumn Dim i, j As Integer For i = 0 To Table.Rows.Count - 1 row = Table.Rows(i) Select Case row.RowState Case DataRowState.Deleted Console.WriteLine("[Deleted]") Case DataRowState.Modified Console.WriteLine("[Modified]") Case DataRowState.Added Console.WriteLine("[Added]") Case DataRowState.Unchanged Console.WriteLine("[Unchanged]") End Select For j = 0 To Table.Columns.Count - 1 If row.RowState <> DataRowState.Deleted Then Console.WriteLine("{0}", row.Item(j)) End If Next Console.WriteLine() Next End Sub Public Sub Main() Try Con = New OleDb.OleDbConnection(s) sql = "Select * from Artist" adopt = New OleDbDataAdapter(sql, Con) ds = New DataSet Catch ex As Exception Console.WriteLine(ex.ToString) Console.ReadLine() End Try sDelSql = "Delete From Artist Where Id = ?" cmdDel.Connection = Con cmdDel.CommandText = sDelSql Param = cmdDel.Parameters.Add("Id", OleDb.OleDbType.Integer) Param.SourceColumn = "@ID" Param.SourceVersion = DataRowVersion.Original adopt.DeleteCommand = cmdDel sUpdSql = "Update Artist Set Name = ? Where Id = ?" cmdUpd.Connection = Con cmdUpd.CommandText = sUpdSql Param = cmdUpd.Parameters.Add("Name", OleDb.OleDbType.Char) Param.SourceColumn = "@Name" Param.SourceVersion = DataRowVersion.Current Param = cmdUpd.Parameters.Add("Id", OleDb.OleDbType.Integer) Param.SourceColumn = "@Id" Param.SourceVersion = DataRowVersion.Original adopt.UpdateCommand = cmdUpd sInsSql = "Insert Into Artist (Id,Name) Values(?,?)" cmdIns.Connection = Con cmdIns.CommandText = sInsSql Param = cmdIns.Parameters.Add("Id", OleDb.OleDbType.Integer) Param.SourceColumn = "@Id" Param.SourceVersion = DataRowVersion.Current Param = cmdIns.Parameters.Add("Name", OleDb.OleDbType.Char) Param.SourceColumn = "@Name" Param.SourceVersion = DataRowVersion.Current adopt.UpdateCommand = cmdIns Try Con.Open() If Con.State = ConnectionState.Open Then adopt.MissingSchemaAction = MissingSchemaAction.AddWithKey adopt.Fill(ds, "Artist") Con.Close() Dim Tables As DataTableCollection Dim Table As DataTable Dim Cols As DataColumnCollection Dim Col As DataColumn Dim Rows As DataRowCollection Dim Row As DataRow Tables = ds.Tables Table = Tables("Artist") Rows = Table.Rows Cols = Table.Columns Console.WriteLine("Original Table Looks Like") Display(Table) Console.ReadLine() Console.WriteLine("Id 1 delete") Rows.Find(1).Delete() Console.WriteLine("deleted") Display(Table) Console.ReadLine() Console.WriteLine("Id 2 Modify") Row = Rows.Find(2) Row.BeginEdit() Row("Name") = "Mantu" Row.EndEdit() Console.WriteLine("Updated") Display(Table) Console.ReadLine() Console.WriteLine("Id 1 Add") Row = Table.NewRow Row("Id") = 4 Row("Name") = "Deepak" Rows.Add(Row) Console.WriteLine("Added") Display(Table) Console.ReadLine() Con.Open() adopt.Update(ds, "Artist") Console.WriteLine("Done") End If Catch ex As Exception Console.WriteLine(ex.ToString) Console.ReadLine() End Try End Sub End Module The Exact error what I got is : "System.Data.OleDb.OleDbException: Parameter ?_1 has no default value. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at ADONetTest.Module1.Main() in D:\Rabi\DotNetPrac\ADONetTest\ADONetTest\Module1.vb:line 176" This String is generated by "Ex.ToString" Tag: SQL Syntax error Tag: 125688
  - 14
    - Problem with ADO.NET and dao pia in the same project I have a wierd problem working with ADO.NET and DAO PIA in the same project - it seems that ADO.NET is causing the DAO objects to be destructed. Once I open ADO.NET connection, the DAO objecucts seems destructed (at least thier native part) and I get Unhandled Exception: System.Runtime.InteropServices.COMException (0x800A0D5C): O bject invalid or no longer set. The funny thing about it is that it happened only in release build when run outside of the VS 2005 IDE. The repro code is below. Any idea how to work around this problem ? Thanks, Einat using System; using System.Text; using dao; using System.Data; using System.Data.SqlClient; using System.Runtime.InteropServices; namespace AccessDataTransfer { class Program { static void Main(string[] args) { String Sourcedbstr = "c:\\1.mdb"; String DestConnectionStr = @"Data Source=localhost;Integrated Security=SSPI;Initial Catalog=pubs"; String TableName = "Foo"; //dao connection dao.DBEngineClass engineClass = new DBEngineClass(); dao.Workspace workSpace = engineClass.CreateWorkspace("WorkSpace", "Admin", "", WorkspaceTypeEnum.dbUseJet); dao.Database Sourcedb = workSpace.OpenDatabase(Sourcedbstr, Type.Missing, true, null); dao.Recordset SourceRecordSet = Sourcedb.OpenRecordset(TableName, Type.Missing, Type.Missing, Type.Missing); //ADO.NET connection that invalidate the DAO objects SqlConnection DestConnection = new SqlConnection(DestConnectionStr); DestConnection.Open(); Now manipulate dao object and fails TableDef tblDef = Sourcedb.TableDefs[TableName]; //fails here } } } Tag: SQL Syntax error Tag: 125687
  - 15
    - Bypass ADO Hi all, out of curiosity, is there a way to bypass ADO completely and talk directly to SQL Server? Do I have to step in the unmanaged world or can I still achieve it from C#? Thanks, Lorenzo Tag: SQL Syntax error Tag: 125681
  - 16
    - Using Widows Authentication and accesssing SQL with SQL Authentication Hi I am working on an application with .NET 2005 and SQL server 2000. I am using windows authentication for my web application authentication and trying to connect to the SQL server with SQL authentication credentials. My web.config reads as follows: web application Authentication: <authentication mode="Windows" /> <authorization> <allow users="*" />   </authorization> <identity impersonate="true" /> SQL Authentication: <connectionStrings> <add name="DevDBConnection" connectionString="Server=MyServer;database=MyDB;uid=MyUser;pwd=MyPwd;" providerName="System.Data.SqlClient" /> </connectionStrings> MY IIS directory security is set to "Integrated Windows authentication". I am validating the user againt the Active Directory for authentication and reading the user details and groups which works fine. But trying to open a connection object fails. I am getting a "{"Cannot open database requested in login 'MyDB'. Login fails.\r\nLogin failed for user 'MyUser'."}" I have tried searching for a solution to my best, but could not solve this problem. I hope some one might have come accross the same problem ahd looking forward for your help. Thanks, Valar Tag: SQL Syntax error Tag: 125676
  - 17
    - tranferring data from Access to SQL Server Express I created a VB.Net 1.1 application that iterates through all the tables in any basic Access 2000 database passed to it and generates the same table structure in a SQL Server Express database. The structure is created fine (with minor data conversions from one to the other, e.g. yes/no --> bit, memo --> text, etc). My problem now is transferring the data over from Access to SQL Server. I thought it would be a fairly straight forward process, but I don't think I thought it through that well, unless I am missing something. I currently have the code retrieving a DataTable object from Access one at a time. The only way I can think of now to get the data into SQL Server is to create a DataAdapter for each table in SQL Server and create an InsertCommand for each of adapters. This will involve iterating through all the columns of each table, determing their data types and length, and then adding the parameters. Is this what I have to do, or is there a shorter method? Perhaps dump the Access table to a file and then use bulk import utility (bcp) for example??? Hmmm... Thanks for any help, Marcus *** Please respond to this group. I do not check this email address *** Tag: SQL Syntax error Tag: 125675
  - 18
    - Strongly Typed Dataset (1.1) and DataRelation for Navigation Only I am trying to create a strongly typed dataset in .NET 1.1 and am having a problem with adding relations via the design time XSD editor. When you generate a relation at runtime, you have the option of specifying "createConstriants". Normally I would want to have constraints on a relationship, but I am working on a dataset where I only need it for navigation. I can't create a relation with the designer that does not enforce them. Any one know how to do this? Thanks, Eric Marvets MVP - Developer Security Tag: SQL Syntax error Tag: 125656
  - 19
    - ORA-01460: unimplemented or unreasonable conversion requested Hi All, We are currently getting a following error ORA-01460: unimplemented or unreasonable conversion requested When we try to insert a double-byte character from our ASP.Net application through ODP.Net version 9. We have our server setting for UTF 8 encoding. This works when we insert diretly through a query or executing the stored proc with the same value using the backend. But the above error is trigerred when we try to do the insert through the ODP Parameter. The Datatype we tried include NVarchar2 and Varchar2. Note: We have made this work by passing datatype as NClob. Like to more information about this error and how to solve it? Regards, Syed. Tag: SQL Syntax error Tag: 125649
  - 20
    - Must declare the scalar variable "@" - paramaterized sqlclient insert - HELP!! Below is my code, the error occurs when it is executed. I don't quite understand it; What am I doing wrong? Sub insertADRecords(ByVal oEntry As SearchResult) Dim iIndex1 As Integer Dim strSql, strDN, strProxyAdd, strProxy(), strMyString As String, rowsAffected As Integer strSql = "INSERT INTO Tradoc.dbo.CurrAD (objclass, cn, mailnickname, targetAddress, proxyAddresses, displayname, givenName,initials,sn,title,telephoneNumber,company,department,physicalDeliveryOfficeName,l,st,postalcode, co,adspath) VALUES (@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@))" Dim sqlCMD As SqlClient.SqlCommand = New SqlCommand(strSql, sqlConnection) 'sqlCMD.Connection() = sqlConnection sqlCMD.CommandType = CommandType.Text writeLog(strSql) 'Dim oResult As SearchResult Dim item, strtemp As String sqlCMD.Parameters.Add(New SqlParameter("@objclass", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@cn", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@mailnickname", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@targetAddress", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@proxyAddresses", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@displayName", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@givenName", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@initials", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@sn", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@title", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@telephoneNumber", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@company", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@department", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@physicalDeliveryOfficeName", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@l", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@st", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@postalcode", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@co", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@adspath", SqlDbType.VarChar, 400)) '*********************************************************** sqlCMD.Parameters("@objclass").Value = "contact" Try sqlCMD.Parameters("@cn").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("cn")(0)))) Catch ex As Exception sqlCMD.Parameters("@cn").Value = "" End Try Try sqlCMD.Parameters("@mailnickname").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("mailnickname")(0)))) Catch ex As Exception sqlCMD.Parameters("@targetAddress").Value = "" End Try Try sqlCMD.Parameters("@targetAddress").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("targetAddress")(0)))) Catch ex As Exception sqlCMD.Parameters("@targetAddress").Value = "" End Try Try sqlCMD.Parameters("@proxyAddresses").Value() = Convert.ToString(oEntry.Properties("proxyAddresses")(0)) Catch ex As Exception sqlCMD.Parameters("@proxyAddresses").Value() = "" End Try Try sqlCMD.Parameters("@displayName").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("displayName")(0)))) Catch ex As Exception sqlCMD.Parameters("@displayName").Value() = "" End Try Try sqlCMD.Parameters("@givenName").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("givenName")(0)))) Catch ex As Exception sqlCMD.Parameters("@givenName").Value = "" End Try Try sqlCMD.Parameters("@initials").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("initials")(0)))) Catch ex As Exception sqlCMD.Parameters("@initials").Value = "" End Try Try sqlCMD.Parameters("@sn").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("sn")(0)))) Catch ex As Exception sqlCMD.Parameters("@sn").Value = "" End Try Try sqlCMD.Parameters("@title").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("title")(0)))) Catch ex As Exception sqlCMD.Parameters("@title").Value = "" End Try Try sqlCMD.Parameters("@telephoneNumber").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("telephoneNumber")(0)))) Catch ex As Exception sqlCMD.Parameters("@telephoneNumber").Value = "" End Try Try sqlCMD.Parameters("@company").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("company")(0)))) Catch ex As Exception sqlCMD.Parameters("@company").Value = "" End Try Try sqlCMD.Parameters("@department").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("department")(0)))) Catch ex As Exception sqlCMD.Parameters("@department").Value = "" End Try Try sqlCMD.Parameters("@physicalDeliveryOfficeName").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("physicalDeliveryOfficeName")(0)))) Catch ex As Exception sqlCMD.Parameters("@physicalDeliveryOfficeName").Value = "" End Try Try sqlCMD.Parameters("@l").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("l")(0)))) Catch ex As Exception sqlCMD.Parameters("@l").Value = "" End Try Try sqlCMD.Parameters("@st").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("st")(0)))) Catch ex As Exception sqlCMD.Parameters("@st").Value = "" End Try Try sqlCMD.Parameters("@postalcode").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("postalcode")(0)))) Catch ex As Exception sqlCMD.Parameters("@postalcode").Value = "" End Try Try sqlCMD.Parameters("@co").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("co")(0)))) Catch ex As Exception sqlCMD.Parameters("@co").Value = "" End Try sqlCMD.Parameters("@adspath").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("adspath")(0)))) If sqlConnection.State = ConnectionState.Closed Then sqlConnection.Open() End If Try 'Here's where the error occurs, of course... rowsAffected = sqlCMD.ExecuteNonQuery() Catch ex As Exception writeLog(ex.Message) End Try sqlCMD.Dispose() End Sub Tag: SQL Syntax error Tag: 125646
  - 21
    - Must declare the scalar variable "@" - paramaterized sqlclient insert - HELP!! Below is my code, the error occurs when it is executed. I don't quite understand it; What am I doing wrong? Sub insertADRecords(ByVal oEntry As SearchResult) Dim iIndex1 As Integer Dim strSql, strDN, strProxyAdd, strProxy(), strMyString As String, rowsAffected As Integer strSql = "INSERT INTO Tradoc.dbo.CurrAD (objclass, cn, mailnickname, targetAddress, proxyAddresses, displayname, givenName,initials,sn,title,telephoneNumber,company,department,physicalDeliveryOfficeName,l,st,postalcode, co,adspath) VALUES (@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@))" Dim sqlCMD As SqlClient.SqlCommand = New SqlCommand(strSql, sqlConnection) 'sqlCMD.Connection() = sqlConnection sqlCMD.CommandType = CommandType.Text writeLog(strSql) 'Dim oResult As SearchResult Dim item, strtemp As String sqlCMD.Parameters.Add(New SqlParameter("@objclass", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@cn", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@mailnickname", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@targetAddress", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@proxyAddresses", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@displayName", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@givenName", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@initials", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@sn", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@title", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@telephoneNumber", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@company", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@department", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@physicalDeliveryOfficeName", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@l", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@st", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@postalcode", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@co", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@adspath", SqlDbType.VarChar, 400)) '*********************************************************** sqlCMD.Parameters("@objclass").Value = "contact" Try sqlCMD.Parameters("@cn").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("cn")(0)))) Catch ex As Exception sqlCMD.Parameters("@cn").Value = "" End Try Try sqlCMD.Parameters("@mailnickname").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("mailnickname")(0)))) Catch ex As Exception sqlCMD.Parameters("@targetAddress").Value = "" End Try Try sqlCMD.Parameters("@targetAddress").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("targetAddress")(0)))) Catch ex As Exception sqlCMD.Parameters("@targetAddress").Value = "" End Try Try sqlCMD.Parameters("@proxyAddresses").Value() = Convert.ToString(oEntry.Properties("proxyAddresses")(0)) Catch ex As Exception sqlCMD.Parameters("@proxyAddresses").Value() = "" End Try Try sqlCMD.Parameters("@displayName").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("displayName")(0)))) Catch ex As Exception sqlCMD.Parameters("@displayName").Value() = "" End Try Try sqlCMD.Parameters("@givenName").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("givenName")(0)))) Catch ex As Exception sqlCMD.Parameters("@givenName").Value = "" End Try Try sqlCMD.Parameters("@initials").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("initials")(0)))) Catch ex As Exception sqlCMD.Parameters("@initials").Value = "" End Try Try sqlCMD.Parameters("@sn").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("sn")(0)))) Catch ex As Exception sqlCMD.Parameters("@sn").Value = "" End Try Try sqlCMD.Parameters("@title").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("title")(0)))) Catch ex As Exception sqlCMD.Parameters("@title").Value = "" End Try Try sqlCMD.Parameters("@telephoneNumber").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("telephoneNumber")(0)))) Catch ex As Exception sqlCMD.Parameters("@telephoneNumber").Value = "" End Try Try sqlCMD.Parameters("@company").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("company")(0)))) Catch ex As Exception sqlCMD.Parameters("@company").Value = "" End Try Try sqlCMD.Parameters("@department").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("department")(0)))) Catch ex As Exception sqlCMD.Parameters("@department").Value = "" End Try Try sqlCMD.Parameters("@physicalDeliveryOfficeName").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("physicalDeliveryOfficeName")(0)))) Catch ex As Exception sqlCMD.Parameters("@physicalDeliveryOfficeName").Value = "" End Try Try sqlCMD.Parameters("@l").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("l")(0)))) Catch ex As Exception sqlCMD.Parameters("@l").Value = "" End Try Try sqlCMD.Parameters("@st").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("st")(0)))) Catch ex As Exception sqlCMD.Parameters("@st").Value = "" End Try Try sqlCMD.Parameters("@postalcode").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("postalcode")(0)))) Catch ex As Exception sqlCMD.Parameters("@postalcode").Value = "" End Try Try sqlCMD.Parameters("@co").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("co")(0)))) Catch ex As Exception sqlCMD.Parameters("@co").Value = "" End Try sqlCMD.Parameters("@adspath").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("adspath")(0)))) If sqlConnection.State = ConnectionState.Closed Then sqlConnection.Open() End If Try 'Here's where the error occurs, of course... rowsAffected = sqlCMD.ExecuteNonQuery() Catch ex As Exception writeLog(ex.Message) End Try sqlCMD.Dispose() End Sub Tag: SQL Syntax error Tag: 125645
  - 22
    - Must declare the scalar variable "@" - paramaterized sqlclient insert Below is my code, the error occurs when it is executed. I don't quite understand it; What am I doing wrong? Sub insertADRecords(ByVal oEntry As SearchResult) Dim iIndex1 As Integer Dim strSql, strDN, strProxyAdd, strProxy(), strMyString As String, rowsAffected As Integer strSql = "INSERT INTO Tradoc.dbo.CurrAD (objclass, cn, mailnickname, targetAddress, proxyAddresses, displayname, givenName,initials,sn,title,telephoneNumber,company,department,physicalDeliveryOfficeName,l,st,postalcode, co,adspath) VALUES (@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@))" Dim sqlCMD As SqlClient.SqlCommand = New SqlCommand(strSql, sqlConnection) 'sqlCMD.Connection() = sqlConnection sqlCMD.CommandType = CommandType.Text writeLog(strSql) 'Dim oResult As SearchResult Dim item, strtemp As String sqlCMD.Parameters.Add(New SqlParameter("@objclass", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@cn", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@mailnickname", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@targetAddress", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@proxyAddresses", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@displayName", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@givenName", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@initials", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@sn", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@title", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@telephoneNumber", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@company", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@department", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@physicalDeliveryOfficeName", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@l", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@st", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@postalcode", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@co", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@adspath", SqlDbType.VarChar, 400)) '*********************************************************** sqlCMD.Parameters("@objclass").Value = "contact" Try sqlCMD.Parameters("@cn").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("cn")(0)))) Catch ex As Exception sqlCMD.Parameters("@cn").Value = "" End Try Try sqlCMD.Parameters("@mailnickname").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("mailnickname")(0)))) Catch ex As Exception sqlCMD.Parameters("@targetAddress").Value = "" End Try Try sqlCMD.Parameters("@targetAddress").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("targetAddress")(0)))) Catch ex As Exception sqlCMD.Parameters("@targetAddress").Value = "" End Try Try sqlCMD.Parameters("@proxyAddresses").Value() = Convert.ToString(oEntry.Properties("proxyAddresses")(0)) Catch ex As Exception sqlCMD.Parameters("@proxyAddresses").Value() = "" End Try Try sqlCMD.Parameters("@displayName").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("displayName")(0)))) Catch ex As Exception sqlCMD.Parameters("@displayName").Value() = "" End Try Try sqlCMD.Parameters("@givenName").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("givenName")(0)))) Catch ex As Exception sqlCMD.Parameters("@givenName").Value = "" End Try Try sqlCMD.Parameters("@initials").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("initials")(0)))) Catch ex As Exception sqlCMD.Parameters("@initials").Value = "" End Try Try sqlCMD.Parameters("@sn").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("sn")(0)))) Catch ex As Exception sqlCMD.Parameters("@sn").Value = "" End Try Try sqlCMD.Parameters("@title").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("title")(0)))) Catch ex As Exception sqlCMD.Parameters("@title").Value = "" End Try Try sqlCMD.Parameters("@telephoneNumber").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("telephoneNumber")(0)))) Catch ex As Exception sqlCMD.Parameters("@telephoneNumber").Value = "" End Try Try sqlCMD.Parameters("@company").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("company")(0)))) Catch ex As Exception sqlCMD.Parameters("@company").Value = "" End Try Try sqlCMD.Parameters("@department").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("department")(0)))) Catch ex As Exception sqlCMD.Parameters("@department").Value = "" End Try Try sqlCMD.Parameters("@physicalDeliveryOfficeName").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("physicalDeliveryOfficeName")(0)))) Catch ex As Exception sqlCMD.Parameters("@physicalDeliveryOfficeName").Value = "" End Try Try sqlCMD.Parameters("@l").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("l")(0)))) Catch ex As Exception sqlCMD.Parameters("@l").Value = "" End Try Try sqlCMD.Parameters("@st").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("st")(0)))) Catch ex As Exception sqlCMD.Parameters("@st").Value = "" End Try Try sqlCMD.Parameters("@postalcode").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("postalcode")(0)))) Catch ex As Exception sqlCMD.Parameters("@postalcode").Value = "" End Try Try sqlCMD.Parameters("@co").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("co")(0)))) Catch ex As Exception sqlCMD.Parameters("@co").Value = "" End Try sqlCMD.Parameters("@adspath").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("adspath")(0)))) If sqlConnection.State = ConnectionState.Closed Then sqlConnection.Open() End If Try 'Here's where the error occurs, of course... rowsAffected = sqlCMD.ExecuteNonQuery() Catch ex As Exception writeLog(ex.Message) End Try sqlCMD.Dispose() End Sub Tag: SQL Syntax error Tag: 125644
  - 23
    - Have you stopped using ADO? Just wondering - has everyone switched to ADONet, or do you still use 'old' ADO for something? Vayse Tag: SQL Syntax error Tag: 125643
  - 24
    - Must declare the scalar variable "@" - paramaterized sqlclient insert Below is my code, the error occurs when it is executed. I don't quite understand it; What am I doing wrong? Sub insertADRecords(ByVal oEntry As SearchResult) Dim iIndex1 As Integer Dim strSql, strDN, strProxyAdd, strProxy(), strMyString As String, rowsAffected As Integer strSql = "INSERT INTO Tradoc.dbo.CurrAD (objclass, cn, mailnickname, targetAddress, proxyAddresses, displayname, givenName,initials,sn,title,telephoneNumber,company,department,physicalDeliveryOfficeName,l,st,postalcode, co,adspath) VALUES (@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@))" Dim sqlCMD As SqlClient.SqlCommand = New SqlCommand(strSql, sqlConnection) 'sqlCMD.Connection() = sqlConnection sqlCMD.CommandType = CommandType.Text writeLog(strSql) 'Dim oResult As SearchResult Dim item, strtemp As String sqlCMD.Parameters.Add(New SqlParameter("@objclass", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@cn", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@mailnickname", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@targetAddress", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@proxyAddresses", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@displayName", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@givenName", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@initials", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@sn", SqlDbType.VarChar, 100)) sqlCMD.Parameters.Add(New SqlParameter("@title", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@telephoneNumber", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@company", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@department", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@physicalDeliveryOfficeName", SqlDbType.VarChar, 400)) sqlCMD.Parameters.Add(New SqlParameter("@l", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@st", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@postalcode", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@co", SqlDbType.VarChar, 50)) sqlCMD.Parameters.Add(New SqlParameter("@adspath", SqlDbType.VarChar, 400)) '*********************************************************** sqlCMD.Parameters("@objclass").Value = "contact" Try sqlCMD.Parameters("@cn").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("cn")(0)))) Catch ex As Exception sqlCMD.Parameters("@cn").Value = "" End Try Try sqlCMD.Parameters("@mailnickname").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("mailnickname")(0)))) Catch ex As Exception sqlCMD.Parameters("@targetAddress").Value = "" End Try Try sqlCMD.Parameters("@targetAddress").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("targetAddress")(0)))) Catch ex As Exception sqlCMD.Parameters("@targetAddress").Value = "" End Try Try sqlCMD.Parameters("@proxyAddresses").Value() = Convert.ToString(oEntry.Properties("proxyAddresses")(0)) Catch ex As Exception sqlCMD.Parameters("@proxyAddresses").Value() = "" End Try Try sqlCMD.Parameters("@displayName").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("displayName")(0)))) Catch ex As Exception sqlCMD.Parameters("@displayName").Value() = "" End Try Try sqlCMD.Parameters("@givenName").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("givenName")(0)))) Catch ex As Exception sqlCMD.Parameters("@givenName").Value = "" End Try Try sqlCMD.Parameters("@initials").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("initials")(0)))) Catch ex As Exception sqlCMD.Parameters("@initials").Value = "" End Try Try sqlCMD.Parameters("@sn").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("sn")(0)))) Catch ex As Exception sqlCMD.Parameters("@sn").Value = "" End Try Try sqlCMD.Parameters("@title").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("title")(0)))) Catch ex As Exception sqlCMD.Parameters("@title").Value = "" End Try Try sqlCMD.Parameters("@telephoneNumber").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("telephoneNumber")(0)))) Catch ex As Exception sqlCMD.Parameters("@telephoneNumber").Value = "" End Try Try sqlCMD.Parameters("@company").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("company")(0)))) Catch ex As Exception sqlCMD.Parameters("@company").Value = "" End Try Try sqlCMD.Parameters("@department").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("department")(0)))) Catch ex As Exception sqlCMD.Parameters("@department").Value = "" End Try Try sqlCMD.Parameters("@physicalDeliveryOfficeName").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("physicalDeliveryOfficeName")(0)))) Catch ex As Exception sqlCMD.Parameters("@physicalDeliveryOfficeName").Value = "" End Try Try sqlCMD.Parameters("@l").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("l")(0)))) Catch ex As Exception sqlCMD.Parameters("@l").Value = "" End Try Try sqlCMD.Parameters("@st").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("st")(0)))) Catch ex As Exception sqlCMD.Parameters("@st").Value = "" End Try Try sqlCMD.Parameters("@postalcode").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("postalcode")(0)))) Catch ex As Exception sqlCMD.Parameters("@postalcode").Value = "" End Try Try sqlCMD.Parameters("@co").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("co")(0)))) Catch ex As Exception sqlCMD.Parameters("@co").Value = "" End Try sqlCMD.Parameters("@adspath").Value = strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("adspath")(0)))) If sqlConnection.State = ConnectionState.Closed Then sqlConnection.Open() End If Try 'Here's where the error occurs, of course... rowsAffected = sqlCMD.ExecuteNonQuery() Catch ex As Exception writeLog(ex.Message) End Try sqlCMD.Dispose() End Sub Tag: SQL Syntax error Tag: 125639
  - 25
    - How to persist an ADO recordset from Sql Server into Access Is there a simple way to persist a recordset from a SQL Server db into a table in an Access database, for example DAO allows a "select * into tablex in 'c:\externaldb.mdb' from..." Or is a disconnected recordset the way to go, but then how do I persist this back into another table in an Access database or even a file? Thanks in advance, WayneM Tag: SQL Syntax error Tag: 125638
- Next
  - 1
    - Binding DataGridView without default DataVeiw, it's possible? I know that when binding DataGridView to Datatable, the ADO.NET create Dataview. the problem is with large data, this process take long time and consume RAM. if i not need sorting service have a way to avoid the default dataView? Tag: SQL Syntax error Tag: 125635
  - 2
    - Primary Key I'm dev. a app with typed datasets and I would not use a self-inc field for the primary key. Instead of I would like make my own pk control. What's the best way to make that with VS 2005 + SQL 2005? Just make a sp like the old good times? :-) Tag: SQL Syntax error Tag: 125628
  - 3
    - Transactions with TableAdapters in ADo.Net 2.0 I have a VS2005 C# winforms app connecting to SQL2005. Having discovered the limitations of the new TableAdapter implementation, and the fact that if I use System.Transaction on updates to a DataSet with several related tables then MSDTC is automatically invoked, I've followed advice from this newsgroup and elsewhere to implement my own methods in the TableAdapter classes to apply a SQLTransaction object to each command (much as I used to do with DataAdapters in ADO 1.1). This seems to work very well, but I'm puzzled by the fact that when I trace the events in SQL Profiler I do not see any BEGIN TRAN or COMMIT statements, even though if I artificially induce a rollback the transaction is definitely completely rolled back as expected. Is there a different communication channel for transactions with SQL2005? brian smith Tag: SQL Syntax error Tag: 125626
  - 4
    - Changing column order in typed dataset I create a typed dataset based upon a table, using the VS2005 designer. I then add a query to the table adapter with a select statement, changing the order of some columns, and removing others. When I select preview data, I see the columns in the original order of the dataset, with the columns I removed now showing up as empty. This can't be by design, can it? And if so, how can I order the columns? Since I am using stored procedures, I can drop the select stored procedure in the TDS and the resulting column order is correct, but I am now using a different table adapter. Are there any other options? Thanks, David R Tag: SQL Syntax error Tag: 125618
  - 5
    - Binding to TextBoxes on MySQL with C# Ref: WinXP SP2, MySQL 5.0, .NET 1.1, C# I try to bind columns to TextBoxes. The column data appears in the textboxes, but if modified will not update. My code came from MySQL example, which uses a datagrid. If cols in the datagrid are modified they update fine. The following code: DataTable changes; changes = this.data.GetChanges(); x = 1; this.da.Update( changes ); this.data.AcceptChanges(); yields the error "Value cannot be null", as I guess 'changes' is null because nothing in the DataGrid has changed. So, how to get the TextBox bindings to yield non-null 'changes' FYI: 'data' is a DataTable, 'da' is MySQLDataAdapter Thanks! Tag: SQL Syntax error Tag: 125615
  - 6
    - can't drag a dataset Hi, i'm a beginner, this is the scenario: i'm using vs2005 and c## Created a win form project. Created a ms database with some tables. created a win form who acts as parent and some others forms that inherits from it, in my parent form a put a label and a group box. Drag a dataset to a inherited form and work fine. The problem: Can not drag any other dataset to another inherited form. I did try dragging from datasource window (typed dataset), from server explorer, dragging a grid control and bindig to a table, but nothing really work. I was triying some options and if i create a new win form i can drag a dataset to it. *** Sent via Developersdex http://www.developersdex.com *** Tag: SQL Syntax error Tag: 125612
  - 7
    - DB2CONNECTION ISSUES.. Hello I am facing a very different issue with db2 connect. DBconnection string is Database=mydatabase;UID=userid;PWD=password;pooling = false mydatabase is a system DSN created while configuring db2 node. Sometimes all of sudden i am getting this issue error "The type initializer for "IBM.Data.DB2.DB2Connection" threw an exception." at IBM.Data.DB2.DB2Connection..ctor(String connectionString) at Microsoft.Practices.EnterpriseLibrary.Data.DB2.DB2Database.GetConnection() at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DBCommandWrapper command, DataSet dataSet But after restarting the IIS everything works fine for 1/2 days and again starts same issue. In some developer machines i am not at all getting this issue. #1) asp.net and given permission to IWAM_USR under which worker process is running. #2) given permission to SQLLIB folder to IWAM_USR. #3)Added IWAM_USR to the db2users group. #4) Reregistered DB2client 8.1.7 plz help me out if u have any solutions.. Thanks in advance Tag: SQL Syntax error Tag: 125609
  - 8
    - Connection String will not connect from ASP.NET I have been using the following connection string to connect to sql server from an asp.net page: packet size=4096;user id=xxxxxx;data source=w2k3bspsqldevvs;persist security info=True;initial catalog=xxxxxx;password=xxxxxx It worked perfectly well. I had a week off and now the asp.net page will no longer connect to the sql server. If I create a c# desktop app and use the connection string above then it works. If I create a new asp.net page and use the connection string then it doesn't work. I found a similar message in this group entitled "ASP.NET Connection String" - the solution was to restart the SQL Server. Unfortunately my sql server is in a cluster and never gets rebooted. Any one got any ideas? Thanks Tag: SQL Syntax error Tag: 125596
  - 9
    - Connection String will not connect from ASP.NET I have been using the following connection string to connect to sql server from an asp.net page: packet size=4096;user id=xxxxxx;data source=w2k3bspsqldevvs;persist security info=True;initial catalog=xxxxxx;password=xxxxxx It worked perfectly well. I had a week off and now the asp.net page will no longer connect to the sql server. If I create a c# desktop app and use the connection string above then it works. If I create a new asp.net page and use the connection string then it doesn't work. I found a similar message in this group entitled "ASP.NET Connection String" - the solution was to restart the SQL Server. Unfortunately my sql server is in a cluster and never gets rebooted. Any one got any ideas? Thanks Tag: SQL Syntax error Tag: 125595
  - 10
    - Convert UTC time to localtime in SqlDataAdapter I have the following method, that retrieves data from a SQL server and propergates the data in a datagrid. No problems there: ________________________________________________________________ Public Sub fetchBillPostings(ByVal stationName As String, ByRef BillpostingsDG As DataGrid) Dim dataadapter As New SqlDataAdapter( _ "SELECT dbo.Postings.[Time], dbo.Postings.Locator, dbo.Postings.PriceGroup, " & _ "dbo.Postings.License, dbo.Postings.Price, dbo.Postings.Currency " & _ "FROM dbo.Postings INNER JOIN dbo.Rooms ON dbo.Postings.Room =3D dbo.Rooms.Name " & _ "INNER JOIN dbo.Stations ON dbo.Rooms.Id =3D dbo.Stations.Room " & _ "WHERE (dbo.Stations.Name =3D '" & stationName & "')", sqlConn) Dim dataset =3D New DataSet("Testdataset") dataadapter.Fill(dataset, "Postings") BillpostingsDG.SetDataBinding(dataset, "Postings") ' Formater datagrid korrekt Dim ts1 As DataGridTableStyle ts1 =3D New DataGridTableStyle ts1.MappingName =3D "Postings" ts1.GridLineColor =3D Color.White ts1.HeaderBackColor =3D Color.FromArgb(62, 98, 144) ts1.HeaderForeColor =3D Color.White ts1.SelectionBackColor =3D Color.FromArgb(82, 118, 164) If Not BillpostingsDG.TableStyles.Contains(ts1.MappingName) Then BillpostingsDG.TableStyles.Add(ts1) BillpostingsDG.TableStyles.Add( _ ExtendedDataGrid.DataGridApplyAutomaticWidths(BillpostingsDG, BillpostingsDG.TableStyles(0), 10, 200)) 'formater s=E5dan at datoen ogs=E5 indeholder tid Dim dgtbc As DataGridTextBoxColumn =3D BillpostingsDG.TableStyles.Item(1).GridColumnStyles.Item(0) dgtbc.Format =3D "g" dgtbc.FormatInfo =3D New System.Globalization.CultureInfo(&H413) ' holland 's=F8g for at null bliver til blank: dgtbc =3D BillpostingsDG.TableStyles.Item(1).GridColumnStyles.Item(1) dgtbc.NullText =3D "" dgtbc =3D BillpostingsDG.TableStyles.Item(1).GridColumnStyles.Item(2) dgtbc.NullText =3D "" End If End Sub ________________________________________________________________ Now the problem is that that the time stored in dbo.Postings.[Time] is in UTC and I would like to display it with localtime (in my case +1 hour) How can I do that? As far as I can see there is something in .net 2.0 that enables you to change the datetime format in the dataset. But thats not an option here. Also note that I only need to display the data. I'm not changing anything in the datagrid and updating the SQL data. Thanks in advance for any input. Tag: SQL Syntax error Tag: 125593
  - 11
    - Batch execute Stored Procedures with sqlcommand? Is there a way to send a couple of stored procedure requests in a single round trip to the sql server if the stored procedures have parameters? I know I could create a string and send it, but would prefer not to rely on my ability to correctly handle every problem that might come along. For instance I can do this: EXEC sp1 @param='value', @param2='value'; EXEC sp2 @param='value' In a SqlCommand, but I'd like to do this using the built in parameter features if possible. Also the sp's often return datasets, so I need to be able to navigate through the recordsets. If the above is the only way to do this, are there any references on what a safe way to construct that string is so it's not vulerable to various attacks? I'm trying to batch up most of the stuff I need to do, sending one round trip to the database, and then doing stuff with the result. Tag: SQL Syntax error Tag: 125590
  - 12
    - Stored Procedure call failed in VB.NET Hi, I have a stored procedure on SQLServer2000 version (8.00.194) and 'm executing a stored procedure from vb.net. ------------------------------------------------------------------------------------------------------------------------- stored procedure in server ------------------------------------------------------------------------------------------------------------------------- CREATE PROCEDURE STP_ECLAT_FBT_TAN_MST_ADD( @TanCode int, @TanNo Varchar(50), @Active bit, @Remarks Varchar(500), @cmpid int, @ERRADD AS INT OUTPUT, @MAXIC AS INT OUTPUT) AS DECLARE @INTCNT AS INTEGER -- For the Unique Check DECLARE @ERRNO AS INTEGER -- For getting the Error Number BEGIN TRAN SET @INTCNT=(SELECT COUNT(TanCode) FROM ECLAT_FBT_TAN_MST WHERE TanCode=@TanCode) IF @INTCNT<>0 BEGIN SET @ERRADD=50001 ROLLBACK TRANSACTION RETURN END ELSE INSERT INTO ECLAT_FBT_TAN_MST (TanCode,TanNo,Active,Remarks,cmpid) VALUES (@TanCode,@TanNo,@Active,@Remarks, @cmpid); SELECT @ERRNO=@@ERROR IF @ERRNO<> 0 BEGIN SET @ERRADD=@ERRNO ROLLBACK TRANSACTION RETURN END ELSE BEGIN SET @ERRADD=0 SELECT @MAXIC=MAX(TanId) FROM ECLAT_FBT_TAN_MST END COMMIT TRAN GO ------------------------------------------------------------------------------------------------------------------------- In VB.net ------------------------------------------------------------------------------------------------------------------------- Dim dbcommand As New SqlCommand Dim BytActive As Integer If rbtActive.Checked = True Then BytActive = 1 Else BytActive = 0 End If dbcommand.CommandText = "STP_ECLAT_FBT_TAN_MST_ADD" dbcommand.CommandType = CommandType.StoredProcedure dbcommand.Connection = gConnection dbcommand.Parameters.Add(New SqlParameter("@TanCode", TanCode)) dbcommand.Parameters.Add(New SqlParameter("@TanNo", TanNo)) dbcommand.Parameters.Add(New SqlParameter("@Active", BytActive)) dbcommand.Parameters.Add(New SqlParameter("@Remarks", Remarks)) dbcommand.Parameters.Add(New SqlParameter("@cmpid", cmpid)) dbcommand.ExecuteNonQuery() I d'not know how to run stored procedure from vb.net and pass input /get output parameters, but i have excetued store procedure in vb6.0. Any help is appreciated! Tag: SQL Syntax error Tag: 125585
  - 13
    - How to determine the data-provider type from a DbConnection object Im trying to use prepared SQL and need to support both Oracle and SQL server. Im only dealing with the abstraction - i.e. DbConnection object. When using DbParameters, for Oracle i have to use :myVar but for SQL Server i have to use @myVar. So the abstraction breaks down here. Consequently i need to ask "What database server are you?" to then prefix the correct parameter marker character( bring back the ? ). Iv checked the DbConnection object and there do not seem to be any appropriate methods, whereby the provider can override some method which will return the identity. How best can i determine the type of the DBMS i am currently connected too? I do not want to burden the user with having to pass across some identity... Thanks. Tag: SQL Syntax error Tag: 125584
  - 14
    - Passing dataset between two web forms in c# Hi all... I wan to pass a dataset from one web form to another. i had tried with the session object but it didn't worked out....Please help... I thing its a serious problem in ADO.net and asp.net... Waiting for your response... Than u... Tag: SQL Syntax error Tag: 125583
  - 15
    - connection.close vs. connection.dispose I understand that the dispose method releases umnanaged resources, but I am trying to understand what those resources are when it comes to a database connection. If my connection is closed, then what unmanaged resources does the connection object still have? Tag: SQL Syntax error Tag: 125568
  - 16
    - How To Invoke a Login Prompt When Opening an ADO.Net Connection ? Hi : Does someone know if there is a way to throw a login prompt in ADO.Net similar to the ADO connection login prompt as below : Dim cn as ADODB.Connection Set cn = New ADODB.Connection With cn .Properties("Prompt") = adPromptAlways .Open End With Thanks much in advance. -- Thanks, SDRoy Tag: SQL Syntax error Tag: 125565
  - 17
    - ADODB VB Code: con.Properties("Prompt") -Anything similar in ADO.N Hi: Can some please tell me if there is a way to invoke the SQL user challenge dialog from ADO.Net ? I mean what would be the equivalent (if any) code in ASP.Net/C# or VB.Net for the following VB code using ADODB: oConnection.Properties("Prompt") = adPromptAlways oConnection.Open "Driver={SQL Server};Server=" & _ m_Server & ";Database=" & m_DB & ";" Set DBConnection = oConnection Thanks in advance. -- Thanks, SDRoy Tag: SQL Syntax error Tag: 125560
  - 18
    - AutoIncrement is generating null values? - OR - need to get Autonumber from Access db table Hello, I'm trying to create a datarelationship between two tables in a dataset. I've turned the autoincrement switch on to an ID field, but it's not generating anything. I am a newbie, so I'm hoping it's nothing too difficult. Here is the code; Dim sql$ = "SELECT 0 AS Selected, VendorInventoryTypes.VendorInventoryTypesID, VendorInventoryTypes.Code, VendorInventoryTypes.Category, VendorInventoryTypes.SubCategory, VendorInventoryTypes.Brand, VendorInventoryTypes.Model, VendorInventoryTypes.Description, 0 AS Quantity, VendorInventoryTypes.RequiresUniqueItems,VendorInventoryTypes.Cost FROM (VendorInventoryTypes) WHERE (((VendorInventoryTypes.VendorID)=" & lclVendorID & "));" Dim dc As New DataColumn, dt As New DataTable dsInventoryTypeOrders = mdi.dt.GenericDS(sql) dc.AutoIncrement = True dc.AutoIncrementSeed = -1 dc.AutoIncrementStep = -1 dc.ColumnName = "ID" dsInventoryTypeOrders.Tables(0).Columns.Add(dc) Console.WriteLine(dsInventoryTypeOrders.Tables(0).Columns(11)) view1 = New DataView(dsInventoryTypeOrders.Tables(0)) view1.RowFilter = "Selected = 0" view2 = New DataView(dsInventoryTypeOrders.Tables(0)) view2.RowFilter = "Selected = 1" listAvailable.DataSource = view1 listSelected.DataSource = view2 Any help would be greatly appreciated. Thanks! Tag: SQL Syntax error Tag: 125559
  - 19
    - Locking a record in Access from vb.net Hi all, I need to lock a record while a user edits that record, so other users see a "Record is locked" message when try to access that specific record. I know there is a lockrecord property in Access, but I'm not sure how to lock this record from my vb.net form. Any help would be highly appreciated. Roy Tag: SQL Syntax error Tag: 125558
  - 20
    - Boxing values in SqlServer ? I'm looking for a way store different simple datatypes in just one specific column, and determine at retrieving how to unbox them - what is the best (if any) datatype for this purpose? - and how is it best set and retrieved through ADO 2.0? Rune Tag: SQL Syntax error Tag: 125556
  - 21
    - hierarchical Dataset In ADO we had hierarchical and the Shape command. Do we have anything similar in ADO.NET? I know I can have a hiearchy in a dataset, but how do I fill such a dataset? -- Arne Garvander Certified Geek Tag: SQL Syntax error Tag: 125554
  - 22
    - access sql mobile on WM5.0 device from desktop I'm trying to create a VB2005 windows app that retrieves data from a Window Mobile 5.0 device connected by ActiveSync 4. I've added the sql mobile database as a datasource in my project and created a bound control using the drag and drop. When I run the application, I get an SQLCEexception 'The path is invalid'. The drag & drop process has automatically set the connection path to "Mobile Device\My Documents\mydb.sdf", but this is what causes the error. I can view the data perfectly in the data source browser, but I can't access it in the application. I must be ding something stupid, but I can't find any reference to similar problems in the newsgroups. Any ieas? Thanks Ian Tag: SQL Syntax error Tag: 125551
  - 23
    - Microsoft .NET Framework 2.0 Data Provider for Oracle Is there a "Microsoft .NET Framework 2.0 Data Provider for Oracle" - I saw on the Oracle site that ODP.Net is not available yet to support ADO.Net features? Thx for any feedback, rgds, Joris http://jopx.blogspot.com Tag: SQL Syntax error Tag: 125548
  - 24
    - SqlDataReader 2.0 In Ado.Net 1.1 I could only have one open sqldatareader on a sqlcommand. Is that still the case in ADO.Net 2.0? -- Arne Garvander Certified Geek But failed my Nerd exam Tag: SQL Syntax error Tag: 125543
  - 25
    - ADO.Net 2.0 ; SqlDataReader.GetSchemaTable broken? Hi all, I'm using the GetSchemaTable method on a data reader to determine information about the table itself. One of the DataRow's items is supposed to include IsKey, which tells me if the column is part of the primary key or not. I have a simple table with an Int identity as the primary key, but IsKey is returning a null value..neither true or false. Is this a bug in the framework? IsIdentity does return correctly as do most of the other properties i've examined. Thanks Andy Tag: SQL Syntax error Tag: 125535

I have something like that

SqlCommand cmd = new SqlCommand(sqlText, connection);
SqlDataReader data = cmd.Execute();
while (data.Read())
{
..do my stuff
}

When sqlText is invalid the error does not occur on cmd.Execute (as I
would expect) but on data.Read().

Is it the correct DotNet behaviour or some stupid setup on my PC?

Thanks

MH

Top

Re: SQL Syntax error by Marina

Marina
Mon Mar 06 10:39:07 CST 2006

Are you sure this is the case? I've never heard of this, nor have I ever
experienced anything like that. Invalid SQL issues have always caused an
exception on the ExecuteReader line.

"Marius Horak" <nobody@eu.con> wrote in message
news:eUcAk$PQGHA.4976@TK2MSFTNGP11.phx.gbl...
>I have something like that
>
> SqlCommand cmd = new SqlCommand(sqlText, connection);
> SqlDataReader data = cmd.Execute();
> while (data.Read())
> {
> ..do my stuff
> }
>
> When sqlText is invalid the error does not occur on cmd.Execute (as I
> would expect) but on data.Read().
>
> Is it the correct DotNet behaviour or some stupid setup on my PC?
>
> Thanks
>
> MH
>

Top

Re: SQL Syntax error by Marius

Marius
Tue Mar 07 02:35:17 CST 2006

Marina Levit [MVP] wrote:

> Are you sure this is the case? I've never heard of this, nor have I
> ever experienced anything like that. Invalid SQL issues have always
> caused an exception on the ExecuteReader line.

Of course I'm sure. It's why I'm asking here.
I think the problem is with having invalid search text for CONTAINS.
This will produce an error - CONTAINS(TEXT_COLUMN,'alpha beta')

MH

Top