ASP Error on INSERT INTO Statement

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
    - HELP i need to write an SQL statement that will have 4 fields and 4 parameters now the parameter may or may not have values and the fields them-self may or may not have values this is what i have so far SELECT field1, field2, field3, field4 FROM theTable WHERE (field1 = P1 or P1 IS NULL AND field1 IS NULL) AND (field2 = P2 or P2 IS NULL AND field2 IS NULL) AND (field3 = P3 or P3 IS NULL AND field3 IS NULL) AND (field4 = P4 or P4 IS NULL AND field4 IS NULL) p1 dose not have a value p2 = 'A' p3 = 'c' p4 dose not have a value -- Darren Spooner, EMT-B Darren@spoonerusa.net U.S. Space & Rocket Center Shipping & Receiving Clerk & Developer DarrenS@spacecamp.com HEMSI Urban Responder Unit 323 Tag: ASP Error on INSERT INTO Statement Tag: 97739
  - 2
    - Filter from a DataTable Hi, I need to do a filter from a datatable but with a group condition. I want to do some thing like "Select field From table Group by field" but with the data in my datatable. Thanks Tag: ASP Error on INSERT INTO Statement Tag: 97738
  - 3
    - File access question Hello folks, I am experiencing a rather strange problem. I have written a service that attempts to open a file on a network share. When I try to open the file from my service, I get the "Access is denied" error. However, if I try to open the file from a GUI application, I can open the file without problem. I've tried using the FileIOPermissions object to get around the error with no luck. Obviously I am missing something very fundamental, the file is accessible to 'Everyone'. Does anybody know what I am doing wrong? Thank you, Jason Tag: ASP Error on INSERT INTO Statement Tag: 97732
  - 4
    - ADO add to database problem, help? Okay so i'm having some trouble with adding to my database through the dataset. i can read from it and it prints just fine, i run the code okay with no errors, but it simply doesn't add the record, i've left it simple here just so i can get it right before i start adding the extra functionality. okay so the table name is UserID, and i want to add into the table, what am i doing wrong? private void btnAddRecord_Click(object sender, System.EventArgs e) { OleDbConnection AddConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\salesleadmgmt.mdb"); OleDbDataAdapter addAdapter = new OleDbDataAdapter("Select * from UserID", AddConnection); DataSet addSet = new DataSet(); DataRow addRow; OleDbCommandBuilder addCommandBuilder = new OleDbCommandBuilder(addAdapter); addAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; addAdapter.Fill(addSet, "UserID"); Console.WriteLine("i was able to load the userid table."); addRow = addSet.Tables["UserID"].NewRow(); addRow["UsrName"] = "Bob"; addRow["passwr"] = "Bobpwd"; addRow["UsrID"] = "6"; addSet.Tables["UserID"].Rows.Add(addRow); Console.WriteLine("i inserted the new values"); } this runs in the .CS file, i don't get any of the write lines or anything, it does run on a button click, i have another button click that works fine, i'll show that one, whats wrong with this first one? working display click [code:1:e515321317] private void Button1_Click(object sender, System.EventArgs e) { string ConnectString =@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\salesleadmgmt.mdb"; OleDbConnection CustConnection = new OleDbConnection(ConnectString); CustConnection.Open(); DataSet CustSet = new DataSet(); OleDbDataAdapter CustAdapt = new OleDbDataAdapter("SELECT * FROM CustInfo", CustConnection); CustAdapt.Fill(CustSet); CustConnection.Close(); CustGrid.DataSource = CustSet; CustGrid.DataBind(); } [/code:1:e515321317] *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: ASP Error on INSERT INTO Statement Tag: 97727
  - 5
    - Moving CurrencyManager to specific row in Data Source I have a series of user-bound controls that are associated with a dataset, and need to be able to move the displayed data row in one control based on events in another. I have all of the controls bound to the same dataset on my form, and the navigation controls are passing a complete DataRow object in the event arguments. I know I need to get the CurrencyManager for the binding context, and set it's Position, but it's not working properly. The code I am currently using follows (Names have been changed to protect the innocent :). I have a strongly typed dataset called "TypedDataSet" with a table called "Table" in it. The table has an "ID" column which is the primary key. The data is being read into the dataset from an XML document. /**********/ TypedDataSet ds = _dataSet; TypedDataSet.TableRow row = e.nodeData as TypedDataSet.TableRow; int pos = ds.Table.DefaultView.Find(row.ID); CurrencyManager cm = BindingContext[ds, "Table"] as CurrencyManager; cm.Position = pos; /**********/ When I run this code, the data-bound controls do move to a new row. But it's always the wrong row. The problem appears to be that the DataView has the rows sorted differently than the DataTable. I have tried explicitly sorting by primary key (DefaultView.Sort = "ID ASC") as well as using the default (DefaultView.ApplyDefaultSort = true), but the results are identical. The DataTable.Find() method doesn't appear to be useful, since that returns a DataRow -- that I already have -- but I have no way to move the CurrencyManager to a specific data row object, only to a specific position. I'm sure there's some .NET property or method I'm missing but I can't seem to find it. Any ideas? --Mike 's/not.//g' to email me. Tag: ASP Error on INSERT INTO Statement Tag: 97726
  - 6
    - VB.Net (ADO): Thoughts on Copying 7 tables to 1 table 2 Part Question: I've a VB.net application where I read information from 7 Tables in a MS Access Db. There is a performance hit when this happens (about a 1-2 second hit per table). So I'm going to combine the relavent parts of each table into a single table in a "new' MDB file. I've experimented with this and found that the best way that I can see is to gather the information from each table into an Array and then dump that info into my new MDB file. I want all the 7 tables starting with the FIRST row in the new table. I first get the number of rows for a table, re-dim my array and gather the info. Then I repeat for the next table - I compare table row lengths to make sure my Array does not get smaller than the maximum table row found. All appears to work fine. Question #1: Can someone suggest a better way (if there is one) to take the "selected" columns from a single MDB (having 7 tables) into a new single MDB with 1 table. I tried reading each of the 7 tables into my new MDB, but this doesn't work as each table adds new rows past the previous table information. Question #2: Every now and then, I find one of my columns (about mid way down) has a bunch of NULL cells when the data should be continuous in the column. I run the application again (maybe a third time) and all is OK. All the other columns are OK (and the code is similar). A head scratcher for me. Any thoughts? Thanks in advance. Regards, Bruce Tag: ASP Error on INSERT INTO Statement Tag: 97725
  - 7
    - Access datetime format problem? I'm generating 2 datetime values inside a VB.Net program say datetime1 and datetime2. datetime1 is retrieved from an Access database from a column of datetime type. datetime2 is generated internally by the program. I want to test the condition: If datetime1=datetime2 then CodeBlock But CodeBlock never seems to get called even when I'm sure that datetime1 and datetime2 are identical. If I look at the identical datetime1/2 values I do see a difference in formatting in the time part: datetime1 (from Access) shows time as eg 11:59:59 PM datetime1 (internally set) shows time as eg 23:59:59 Question is: Am I trying to test identity of the datetime values in the wrong way or is the problem the difference in time formatting? If the latter then is there some way I can set the database to return datetime values using 24 hour clock times? TIA JGD Tag: ASP Error on INSERT INTO Statement Tag: 97722
  - 8
    - problem with having a delete in database propagate to a bound data Hi, I have notice something with the datagrid update i have an application which is updated from multiple sources when one user deletes a row on the datagrid, the delete propgates back to the database. I see it is gone, from the database by using the SQL query analyzer. but, when other users app calls it's fill command to refresh it's datatable, It sees the already deleted row. what am i doing wrong? Tag: ASP Error on INSERT INTO Statement Tag: 97721
  - 9
    - Access Query Problem This is a multi-part message in MIME format. ------=_NextPart_000_005D_01C4B74E.88CF5E00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Good Morning, Got some good advice yesterday, but still running into issues. I am = trying to query an Access db once a user selects a month/yr from a = dropdown list. =20 Below is summary of my code: =20 Dim CurrentStrMth as String=20 Dim CurrentEndMth as String if drpdate.selecteditem.text =3D "JANUARY 04" then=20 CurrentStrMth =3D "#1/1/2004#"=20 CurrentEndMth =3D "#1/31/2004#" =20 When I run the query below without passing any variables it works fine: cmdSelect1 =3D New OleDbCommand("SELECT Count(*) from db WHERE [Submit = Date/Time]>=3D#1/1/2004# and [Submit = Date/Time]<=3D#1/31/2004#",dbconnection) However, when I try to incorporate the string variables, as noted below, = into the query I get "Character constant must contain exactly one = character". I can't find anything on this on the web!=20 cmdSelect1 =3D New OleDbCommand("Select Count(*) from db where [submit = date/time] >=3D '"CurrentStrMth"' and [submit date/time] <=3D = '"CurrentEndMth"'", dbconnection) If someone could assist me further with this issue, it would be greatly = appreciated? Thanks in advance! MKC ------=_NextPart_000_005D_01C4B74E.88CF5E00 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1476" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT face=3DArial size=3D2>Good Morning,</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Got some good advice yesterday, but = still running=20 into issues.  I am trying to query an Access db once a user selects = a=20 month/yr from a dropdown list.  </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Below is summary of my = code:</FONT></DIV> <DIV> </DIV> <DIV>Dim CurrentStrMth as String </DIV> <DIV>Dim CurrentEndMth as String</DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV>if drpdate.selecteditem.text =3D "JANUARY 04" then </DIV> <DIV>CurrentStrMth =3D "#1/1/2004#" </DIV> <DIV>CurrentEndMth =3D "#1/31/2004#"  </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2> <DIV><FONT face=3DArial size=3D2>When I run the query below without = passing any=20 variables it works fine:</FONT></DIV> <DIV> <DIV>cmdSelect1 =3D New OleDbCommand("SELECT Count(*) from db WHERE = [Submit=20 Date/Time]>=3D#1/1/2004# and [Submit=20 Date/Time]<=3D#1/31/2004#",dbconnection)</DIV></DIV></FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>However, when I try to incorporate the = string=20 variables, as noted below, into the query I get "Character constant must = contain=20 exactly one character".  I can't find anything on this on the=20 web! </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV>cmdSelect1 =3D New OleDbCommand("Select Count(*) from db where = [submit=20 date/time] >=3D '"CurrentStrMth"' and [submit date/time] <=3D=20 '"CurrentEndMth"'", dbconnection)</DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV> <DIV><FONT face=3DArial size=3D2>If someone could assist me further = with this=20 issue, it would be greatly appreciated?</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Thanks in advance!</FONT></DIV> <DIV><FONT face=3DArial size=3D2>MKC</FONT></DIV></DIV></BODY></HTML> ------=_NextPart_000_005D_01C4B74E.88CF5E00-- Tag: ASP Error on INSERT INTO Statement Tag: 97720
  - 10
    - ExecuteNonReader hangs Hello everyone, my webservice read from (and write to) Oracle queues. It uses the Oracle Data Provider downloaded from Oracle site (Oracle10g Data Provider for .NET 10.1.0.2.0). Webservice calls a stored procedure that executes the read operation. Sometimes the ExecuteNonReader() hangs (I'd like to set a timeout on command, but OracleCommand does not support this option). Consequently the webservice fails for timeout. I tested the same class without using webservice and it doesn't hangs. Do anyone know what is the problem? Is it a bug? I'am using .NET 1.1 on Windows 2003. Thanks, Gionni Tag: ASP Error on INSERT INTO Statement Tag: 97717
  - 11
    - How to you page and sort large dataset result in datagrid? In ASPNET 1.1 Is there a good way to page/sort VERY large Result set in datagrid > > > > > > P. Wilson blog wrote: " Here's a stored procedure that I use for sorting and paging large recordsets in SQL Server, as opposed to using the more common and terribly inefficient entire dataset approach in .NET. It certainly doesn't matter much in the little classroom examples of a few hundred, or even thousands, of records, but working with larger recordsets with datasets is just bad. This is even more true with ASP.NET, since the entire dataset is usually saved in viewstate, and since the web server is doing the processing for many users. This stored procedure is certainly not the only way to do sorting and paging in SQL Server, but it is probably the most efficient, although proper indexing is still required to avoid table scans. Note that this does not use temporary tables, which are convenient but not as optimal -- this also means you could modify this to be dynamic SQL for Access or other databases! The parameters are the name of the table, the name of the primary key (necessary for the little bit of optimization included), the name of the sort field (or sort fields, with or without ASC/DESC), and the page size (number of records). It also allows optional parameters for the page index (defaults to 1st page) and a query filter (defaults to null) so you can sort and page through filtered records only! Note that it returns two recordsets -- the first is the results you expect, and the second recordset is a scalar with the number of total pages so you can better define the GUI pager for a grid. This is relatively easy to integrate with the ASP.NET datagrid if you use custom paging and sorting, and it will minimize both your load on the server and the amount of data sent to the client's browser! By the way, this code is just modified from some I found on the net, and there are certainly some minor optimizations that can be done, like using different sql for the first page. " CREATE PROCEDURE GetSortedPage( @TableName VARCHAR(50), @PrimaryKey VARCHAR(25), @SortField VARCHAR(100), @PageSize INT, @PageIndex INT = 1, @QueryFilter VARCHAR(100) = NULL ) AS SET NOCOUNT ON DECLARE @SizeString AS VARCHAR(5) DECLARE @PrevString AS VARCHAR(5) SET @SizeString = CONVERT(VARCHAR, @PageSize) SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1)) IF @QueryFilter IS NULL OR @QueryFilter = '' BEGIN EXEC( 'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ') ORDER BY ' + @SortField + ') ORDER BY ' + @SortField ) EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName) END ELSE BEGIN EXEC( 'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ') ORDER BY ' + @SortField + ') ORDER BY ' + @SortField ) EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter) END RETURN 0 GO posted on Friday, October 10, 2003 9:04 AM Comments >>>> COMMENTS # re: Sorting and Paging Recordsets in SQL Server Darrell Posted @ 10/10/2003 10:15 AM * Just taking a quick look, you could change the "SELECT Count(*)" to "SELECT Count(@PrimaryKey)" and that should give you a speed boost. Reducing the number of columns in the select statement always speeds things up, even for aggregate operations like Count. Very interesting though; I will have to see how well it works for complex queries. # re: Sorting and Paging Recordsets in SQL Server Torbjörn Axelsson Posted @ 10/27/2003 11:14 AM Very interesting! Right now I'm using a solution based on creating a temporary table in the stored procedure for selecting rows from a table containing over a million rows. I will test to implement this solution and see if it boosts performance, which would be very welcome :) In my project I will have to make an inner join to retrieve User names from another table, but since I only have to do this on the outermost SELECT-statement (after TableName), and I have a good indexed structure, that shouldn't have a big impact on performance. Any thoughts? * # Recordset sorting in SQL Server Jamie Jones Posted @ 11/16/2003 11:05 AM * # O/R Mappers: Simple Database Features ? Paul Wilson's .NET Blog Posted @ 1/9/2004 3:54 PM * # re: Sorting and Paging Recordsets in SQL Server David Portas Posted @ 1/23/2004 5:42 PM See also Aaron Bertrand's comprehensive survey of techniques for paging a result set in SQLServer: http://www.aspfaq.com/show.asp?id=2120 * # re: Sorting and Paging Recordsets in SQL Server HELP! Posted @ 2/11/2004 7:34 PM what's the asp supposed to look like? * # re: Sorting and Paging Recordsets in SQL Server Mark Posted @ 3/6/2004 8:49 AM I was wondering if you had an example of using this code with an asp.net datagrid, complete with paging and sorting. email: mwpat@rogers.com * # re: Sorting and Paging Recordsets in SQL Server Paul Wilson Posted @ 3/6/2004 11:28 AM I use it internally at work on a project, but that's not an example I can share. I also use this, but wrapped up in my WilsonORMapper, on my site, but there I use repeaters since I don't like datagrids. There are lots of other examples that show you the asp.net code that you want, so I haven't bothered to recreate one yet again -- the only thing I'm pointing out is how to set up the stored procedure in a better way. * # re: Sorting and Paging Recordsets in SQL Server babu Posted @ 4/2/2004 5:26 AM quite helpful - babu * # re: Sorting and Paging Recordsets in SQL Server Bob Posted @ 4/5/2004 5:08 AM Sorry, I'm new to sql. Why the extra "SELECT * FROM ~ IN ~" statement? Why not just do a "SELECT TOP ' + @SizeString + ' * FROM ..." and make the second select the top select (if you see what I mean). Wouldn't this have the same affect? * # re: Sorting and Paging Recordsets in SQL Server SANDESH Posted @ 4/6/2004 3:25 AM GOOD ONE * # re: Sorting and Paging Recordsets in SQL Server theintrepidfox@hotmail.com Posted @ 4/17/2004 7:47 PM Hi! Excellent fantastic work! You've made my day as I was experiencing with paging scripts but the only solution I came up with supporting DESC and filtering doesn't perform very well. I probably would have spend the next days coming up with something like yours. Thanks very much! * # re: Sorting and Paging Recordsets in SQL Server AcidJazz Posted @ 4/29/2004 8:11 PM Great stuff! I spent last couple of hours looking for something similar and I could see this proc working very well in my project. Thanks! * # re: Sorting and Paging Recordsets in SQL Server senkwe Posted @ 7/8/2004 9:34 AM Has anybody tried this for tables with a few million rows yet? Thanks. * # re: Sorting and Paging Recordsets in SQL Server Paul Wilson Posted @ 7/8/2004 9:42 AM I actually just got done reading http://codeproject.com/aspnet/paginglarge.asp -- and I may switch what I use. I've never heard any complaints, but it does look like there are better solutions. * # re: Sorting and Paging Recordsets in SQL Server Jonny Posted @ 7/21/2004 7:29 AM I believe I read somewhere the sp_executesql will compile the dynamic query and create an execution plan for it. This may speed this up a bit if the same exact query is executed. * # Taking the value of PageCount Resa Posted @ 7/27/2004 1:59 AM Excuse me but i have never used a stored procedure before, and although your code works great (thanks a lot by the way :)) i want to ask how can i get the PageCount value from the second table generated by ur stored procedure? Thank you very much in advance :) * # re: Sorting and Paging Recordsets in SQL Server Paul Wilson Posted @ 7/27/2004 5:59 AM Assuming you're using .NET and a DataReader then the NextResult method is what you want. If you are using a .NET DataSet then you will look at the second table, i.e. .Tables[1]. * # re: Sorting and Paging Recordsets in SQL Server Resa Posted @ 8/2/2004 2:06 AM I'm using sql server, and the NextResult method doesn't work, is there another way, i mean using sql server? thanks Tag: ASP Error on INSERT INTO Statement Tag: 97709
  - 12
    - Linebreaks in SQLCommand.CommandText (C#) Hi, From a C# application I read the SQL text from a text file. The text file contains line breaks that need to be conserved. The text file is read by a streamreader and added to a StringBuilder object. Each line in the text file is read, one at the time. After reading a line, I add a carriage return/linefeed by System.Enviroment.NewLine. But carriage returns / linefeeds are added to the StringBuilder as "/r/n". When I add the content of the StringBuilder object to the SqlCommand.CommandText property and run the query, the SQL fails. I have done a similar thing in VB. In VB I added carriage returns/linefeed by the "vbCrLf" constant. This worked fine. Line breaks were added and maintained when passing the SQL to the SqlCommand.CommandText property - no codes were "visible". I hope someone knows how to handle this in C#. Thanks, Dorte Tag: ASP Error on INSERT INTO Statement Tag: 97705
  - 13
    - insert an image to sql server avi_livni@hotmail.com in msn messenger hi i try to insert an image to sqlserver i do like this first i wrote a stored procedure in my sql server in database="new_home" (not in master db) : CREATE PROCEDURE sp_textcopy ( @srvname varchar (30), @login varchar (30), @password varchar (30), @dbname varchar (30), @tbname varchar (30), @colname varchar (30), @filename varchar (30), @whereclause varchar (40), @direction char(1)) AS DECLARE @exec_str varchar (255) SELECT @exec_str = 'textcopy /S ' + @srvname + ' /U ' + @login + ' /P ' + @password + ' /D ' + @dbname + ' /T ' + @tbname + ' /C ' + @colname + ' /W "' + @whereclause + '" /F ' + @filename + ' /' + @direction EXEC master..xp_cmdshell @exec_str this stored procedure i add to : db="new_home" table="image" now i only try to give parmeters in asp.net in oledb , i saw the example : sp_textcopy@srvname = 'ServerName', @login = 'Login', @password = 'Password', @dbname = 'pubs', @tbname = 'pub_info', @colname = 'logo', @filename = 'c:\picture.bmp', @whereclause = " WHERE pub_id='0736' ", @direction = 'I' but i do not now where i write this code and how i write it asp.net instead i write this, but i didnot see any new image in my database : <%@ Import Namespace="System.Data.SQLClient" %> <%@ Import Namespace="System.Data.OLEDB" %> <%@ Page Language="VB" Debug="true" %> <script language="VB" runat="server"> sub Page_Load(obj as Object, e as EventArgs) 'set up connection dim Conn as new OleDbConnection _ ("Provider=SQLOLEDB;" & _ "Data Source=D:\Program Files\Microsoft SQL Server\MSSQL$LOVE\Data\new_home_Data.MDF") dim obdCmd as OleDbCommand = new OleDbCommand("sp_textcopy",Conn) obdCmd.CommandType = CommandType.StoredProcedure dim objParam as OleDbParameter objParam =obdCmd.Parameters.Add("@srvname", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="AAA\LOVE" objParam =obdCmd.Parameters.Add("@login", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="" objParam =obdCmd.Parameters.Add("@password", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="" objParam =obdCmd.Parameters.Add("@login", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="" objParam =obdCmd.Parameters.Add("@dbname", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="new_home" objParam =obdCmd.Parameters.Add("@ltbname", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="image" objParam =obdCmd.Parameters.Add("@colname", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="image" objParam =obdCmd.Parameters.Add("@filename", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="c\10.jpg" objParam =obdCmd.Parameters.Add("@whereclause", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="where id=1" objParam =obdCmd.Parameters.Add("@direction", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="i" try obdCmd.Connection.Open() dim objReader as OleDbDataReader objReader = obdCmd.ExecuteReader catch ex as OleDbException response.write("Error retriving Data") obdCmd.Connection.Close() end try end sub </script> *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: ASP Error on INSERT INTO Statement Tag: 97704
  - 14
    - How to clear and refill a dataset dragged from the toolbox How to clear and refill a dataset dragged from the toolbox If I generate a dataset from a dataadapter I can ds.clear da.fill(ds, "Table") How do I do it otherwise? Thanks, Dennis Tag: ASP Error on INSERT INTO Statement Tag: 97697
  - 15
    - Can't create a new dataset from the toolbox Can't create a new dataset from the toolbox. In the past, on the advice of MVPs, I've created datasets by dragging the dataset component from the datatab of the toolbox onto the form. The first time it created dataset1, a dataset that did not exist before. The second time it created a dataset2. Today I dragged a dataset on a new form. It only offered current datasets, not a default of a new dataset. When I typed in tfsNet2.dataset3 and clicked OK, I got an error message Visual Studio cannot create an instance of TFSMet2.Dataset3. Confirm the class name is a valid dataset class. What happened? Please help. Dennis Tag: ASP Error on INSERT INTO Statement Tag: 97695
  - 16
    - Q: Copying a table from one database to another Hi Can anybody tell me the easiest way in ADO.NET to copy a table from one Access database to another Access Database? Some example code would be useful. Thanks in advance Geoff Tag: ASP Error on INSERT INTO Statement Tag: 97694
  - 17
    - SQAL question Sorry, but I didn't find a group for SQL questions. May be someone can answer here. In my app I want to reflect a hirarchy in a tree view. Therefore in the database I have a table like this: HirachyId (primary key) ParentHirarchyId (key to reflect the arent entry) CategoryId (a foreign key into a category table) Content may be like the following (0 is root hirarchy) 1 0 10 2 0 11 3 1 16 4 1 8 5 3 27 6 3 28 0 +-- 1 | +-- 3 | | +-- 5 | | +-- 6 | | | +-- 4 | +-- 2 Now I want a SQL-select statement which "recursive" gives me all records for a given node, i.e., when the node is "1" I want to get the records 1 0 10 3 1 16 4 1 8 5 3 27 6 3 28 Can this be done with a SQL-statement? -- Sleepless in Berlin Wernfried Tag: ASP Error on INSERT INTO Statement Tag: 97693
  - 18
    - finding records and displaying it in grid Hi, Earlier i asked a question regarding finding a record through ado.net. I found a solution for it through dataview. I have another question now. Supposing i have a table, 'contacts' whose fileds are like this : Contactid(autoname), customerid(FK from another table), contactname(char)... Here customerID is a foreignkey from tables 'Customers'. Since names might repeat in this table, how do i list the names when a search is made on this name. I want to display the fields displayed in row. I tried with findrowview, but could not succeed. Would someone help me pls. K E Senthil Kumar Tag: ASP Error on INSERT INTO Statement Tag: 97689
  - 19
    - How te reference to a datagrid in a class? Hi I've a DLL project with a form in it. At that form i want to add a datagrid, but datagrid is not recognized if i type dim db as new Datagrid. In a windows forms project it works correct, but not in the DLL project. What do i wrong????? Peter Tag: ASP Error on INSERT INTO Statement Tag: 97686
  - 20
    - Convert <table> from HTML text file to datatable I'm just wondering if anyone have come across a code that reads an html file,search for <table> tag and convert its contents into a datatable and save it into a dataset.If there are few html tables,then each will be converted into a datatable and added into the same dataset. thanks. Tag: ASP Error on INSERT INTO Statement Tag: 97678
  - 21
    - Passing Parameters through a linked server? Hello I have a stored proc: CREATE PROCEDURE dbo.sp_test @IN int, @ID int OUTPUT AS begin SET NOCOUNT ON Set @ID=12345 + @IN end GO I'm executing this through a linked server. The exception returned is: Could not execute procedure 'sp_test' on remote server 'QAPS2Remote'.\r\n[OLE/DB provider returned message: Parameter type cannot be determined for at least one variant parameter.] The literal query being run is: declare @P1 int set @P1=NULL exec QAPS2Remote.HRXMLCollector_prod.dbo.sp_test @IN = 1, @ID = @P1 output select @P1 How do I declare the parameters using C#? The code I'm using is: dbCommand.Connection = conn; dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = "QAPS2Remote.HRXMLCollector_prod.dbo.sp_test"; SqlParameter sqlParameterIn = dbCommand.Parameters.Add("@IN", SqlDbType.Int); sqlParameterIn.Direction = ParameterDirection.Input; sqlParameterIn.Value = 1; SqlParameter sqlParameterOut = dbCommand.Parameters.Add("@ID",SqlDbType.Int); sqlParameterOut.Direction = ParameterDirection.Output; dbCommand.ExecuteNonQuery(); Thanks in advance. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Tag: ASP Error on INSERT INTO Statement Tag: 97670
  - 22
    - Paging a DataGrid or similar control...? I have a windows application that returns 10s of thousands of rows to a DataGrid (for all intents and purposes you can assume that it's a DataGrid, I'm actually using the Infragistics UltraWinGrid though). Due to the ever growing amount of data I want to implement some sort of record paging. There are a lot of examples for ASP.Net but not many for windows applications. I am planning to use some of the techniques used for ASP.Net, but I was hoping someone out there may have a few suggestions specifically for a windows application. Also, I am using MSDE as my database backend. Thanks Tag: ASP Error on INSERT INTO Statement Tag: 97661
  - 23
    - Hello? Is anyone reading these? I'm waiting for an answer, and I'm not the only one... There are quite a few unanswered posts in this NG. What do you say you get busy answering these posts before we choose another development platform! I'll keep watching... Tag: ASP Error on INSERT INTO Statement Tag: 97659
  - 24
    - Parameters in .NET to OPENROWSET Hello. Given the stored proc: CREATE PROCEDURE dbo.sp_test @IN int, @ID int OUTPUT AS begin SET NOCOUNT ON Set @ID=12345 + @IN end GO What I can't figure out is how to add an incoming parameter in .NET. My attempts thus far, which produces an exception on the @1 parameter: SqlConnection conn = null; SqlCommand dbCommand = null; SqlDataReader dbReader = null; conn = new SqlConnection(settings["LocalDBConnect"]); conn.Open(); dbCommand = new SqlCommand(); dbCommand.Connection = conn; dbCommand.CommandText = "SELECT outparam FROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=PS_SQL2000;UID=collector;PWD=co11ection$$', 'declare @outparam int exec HRXML_DEVIII.dbo.sp_test @1,@outparam OUTPUT select @outparam as outparam')"; dbCommand.Parameters.Add("@1", SqlDbType.Int).Value = 1; dbReader = dbCommand.ExecuteReader();//general exception happens here int n; dbReader.Read(); n = dbReader.GetInt32(0); *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Tag: ASP Error on INSERT INTO Statement Tag: 97655
  - 25
    - Multi threading and database connections Hi, What is the best approach for creating database connections while using threading to execute multiple queries in .NET? For eg: I need to run 15 queries using threading from the same database with minimal number of database connections. Any suggestion is appreciated. Thanks, Marshal Antony Tag: ASP Error on INSERT INTO Statement Tag: 97649
- Next
  - 1
    - Can't save a record again Hi All, I add a new row to a datatable and refresh the dataset. The data is stored. When i edit the row again and refresh the dataset again i get an error telling me the record was edited. probably the database is setting defaultvalues but i have no default values set (i think)! Could someone tell me how to set the properties for the fields in a Jet (access) database? By the way: The checkbox 'Refresh the dataset' in the data adaptor advanced settings is disabled. If i uncheck 'use optimistic concurrency' the error does not occur but i don't want to do that. with kind regards, rob Tag: ASP Error on INSERT INTO Statement Tag: 97646
  - 2
    - Filling typed dataset programmatically? I've created a strongly typed dataset (to be used to populate a crystal report) which I need to fill programmatically. How do I do this? Regards, Chris Tag: ASP Error on INSERT INTO Statement Tag: 97641
  - 3
    - ADO.NET 'HasMoreRows' and 'HasMoreResults' I am writing an ADO.NET C# com control wrapper for the old VBSql control used in an large application we have. The wrapper emulates the VBSql controls interface and is intended to allow us to move to SQL2000 from 6.5 without resecting the application and re-writing all the logic within the application to ADO. ************************************* The call semantics for VBSql are as follows set sql command call exec for command call results to check for data arrival. Call One while call results != noMoreResults Call Two+ while call nextRow != noMoreRows loop nextRow loop results ************************************* The problem is that because we have to call results once initially for each call to exec, I need to skip the initial call in the .NET code to DataReader.NextResults(). Then the second call comes in and I need to skip it again as I am not ready at this point to move to the next result set. Then the third call comes in and now, I am ready to forward the results set and return nomoreresults or success if there is a second result set. In order for me to do this, I need to keep a counter on the call to DataReader.NextResults() and only actually call NextResults on the third call. But this causes problems when I use a SQL insert command as I just want to know if the command was successful on the first call, and when I want to skip any remaining records in the batch and move onto the next batch. It would solve my problem to be able to know if I have rows or results remaining without calling NextResults() or Read() as they always move the cursors(or whatever) on the sql side. There are two undocumented, private Boolean properties on the Reader I would like access to. They are 'HasMoreRows and 'HasMoreResults'. I have not been able to find a way to get around the 'private' access modifer on these properties as my old trick of "#define private public" (crazy I know, but sometimes it is the only way to move forward in a timely fashion) is not available in .NET to my knowledge. I am sure someone else has had issues regarding the inability to check state of the reader without moving record or result pointers in the process. To check if I have more rows, I have to call read, or the hasRows. But hasRows returns true if I have another result set pending and thus need to call NextResults. So, I have to mangle the NextRow method to include a call to NextResults when the Read method returns false so the next call to the vbsqls results method will return the correct state without actually calling NextResults in the vbsql results stub in the wrapper. Thanks, and hopefully that is legible! Tag: ASP Error on INSERT INTO Statement Tag: 97640
  - 4
    - RowUpdatingEventArgs support in CF? According to Oct 2004 MSDN, all RowUpdatingEventArgs properties are supported in the Compact Framework but when I tried to access RowUpdatingEventArgs.StatementType I got a MissingMethodException! The same code works fine on the desktop. Also none of the overloaded versions of DataRow.Item are supported in the Compact Framework. There is therefore no way to access different version of a row under the compact framework. How would one access the original version of a deleted row? Thanks, Philippe Tag: ASP Error on INSERT INTO Statement Tag: 97639
  - 5
    - Binding Dropdown to dataset. Need help filtering data. ASP.NET web form application. I am currently binding a datatext field to a dropdown list with the following code: Sub FillFilterLists(ByVal ApplicantActivityDataset As DataSet) ApplicantActivityFilterPanel.ddAppActivityCompanyFilter.DataSource = ApplicantActivityDataset ApplicantActivityFilterPanel.ddAppActivityCompanyFilter.DataTextField = "Company" ApplicantActivityFilterPanel.ddAppActivityCompanyFilter.DataBind() End Sub This is working fine for simply populating the control. Currently my dataset has redundant data. (I'd like to stay away from a data normalization conversation for right now). I am filling the dataset in an earlier routine, and I cannot issue a "select distinct" at the SQL level. Is there anyway I can perform an equivilent function in .NET before the dropdown list is bound? Thanks, Aaron Tag: ASP Error on INSERT INTO Statement Tag: 97637
  - 6
    - SqlException I've got a script that sets up a publisher on a msde database. Runs fine from Query Analyzer. I run it from my program and get two warnings (class 10 sqlExceptions). The second warning causes problems. When the script is executed from the application the second warning occurs and the sql process gets hung in a networkio wait type. When I step through the application, I see the second warning occur, continue to step through and everything works fine (no networkio wait type). I've tried inserting pauses, disposing of the sqlCommand object and recreating it, closing and re-opening the sqlConnection when these warnings occur but nothing seems to change the networkio wait type when run from the application (not stepping through). Anyone seen this before? Have any ideas what else I can try? TIA, Dave Tag: ASP Error on INSERT INTO Statement Tag: 97635
  - 7
    - Visual FoxPro driver question Hello all, I have written an application that accesses and writes to an old dBase file via a FoxPro driver (vfpoledb.1). It works fine when I try to access a file that exists on my local computer. However, if I try to access a file that exists on a network, I always get OleDbException "Invalid path or file name". Here is my connection string: oleConnection = new OleDbConnection( "Provider=vfpoledb.1;Data Source=" + configuration.BCSHAREDDir); where configuration.BCSHAREDDir is a user configured folder. There is no username or password to be used for this table. If I use the folder "C:\bcshared.dbf" for instance, it works fine. However if I try to a network folder such as "\\rcfs01\rcfs01data\Jason\Software\bcshared.dbf", it craps out. Any ideas as to what I am doing wrong? Thank you! Jason Tag: ASP Error on INSERT INTO Statement Tag: 97633
  - 8
    - DataSet - DataColumn MaxLength If I create a DataSet in the Visual Studio IDE, how do I specify the MaxLength of a DataColumn? Once I specify the MaxLength, I want to bind the DataSet to controls on my Windows Form (TextBox controls). Will the MaxLength of the bound TextBox automatically sync with the bound DataColumn? If not, how do I sync the two? Thanks Tag: ASP Error on INSERT INTO Statement Tag: 97626
  - 9
    - Access database, network access problems I've recently run into a problem when creating a web service. I have a webserivce that returns data from a small access database; I created and tested the service on my local machine with the database local in a sub-directory. I verified the results on a test server remotely with the database stored on the local disk. The production server is a web farm, a SAN provides all data storage for this farm; This is where I am having problems, I'm no longer able to open the database, the permissions have been validated on the remote share and the user account has access? My connection string "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\server\share\folder\folder\folder\database.mdb;" I have also tried using the extended property Mode="Share Deny None" The mappath function is used to get the actual file path. It points to the correct location, but, I get errors that the database cannot be opened because you don't have permissions or the database is being opened exclusively, or something to that nature. I don't have recordlocking enabled and a .ldb file is never created. Any suggestions? If you need more information please let me know. Tag: ASP Error on INSERT INTO Statement Tag: 97621
  - 10
    - how to correctly map multiple datasource tables to typeddataset ta I foolishly thought that if.. 1. I have a typed dataset built from my data source schema (that is, all the table names and all their column names, and types, match), and 2. I fill an instance of the dataset using a dataadapter, then the table names in the dataset would be correct (i.e. matching data source table names). I found that they are named Table, Table1, Table2, etc, and that to override this I need to set up table mappings for these default table names (which are dependent on the order of tables in the query text!). Is there a correct way to do this, or is the proper technique simply to map the default table names!? Tag: ASP Error on INSERT INTO Statement Tag: 97607
  - 11
    - Inheritance VB.NET Hi guys, Wonder if anyone can help a newbie in VB.net. I've done a substantial amount of work in VB6 and to promote code use, I lent some methodology from some other OOP RAD tools - but I'm having a probem figuring out an approach in VB.Net... In VB6, I would, for each database table, create a 'class module', which contained all of the necessary database functions I required. So, e.g. I would have a class module called 'tcProvinceState', which would handle all of my database IO for the ProvinceState table. In each class module, I would have functions such as 'DoSelect', 'DoSelectRow', 'InsertRow', 'UpdateRow' and 'DeleteRow'. Here are some examples of the type of thing I currently do; Public Function Do_Select(pSearchString As String) As ADODB.Recordset ' Select all records from the table sSQL = Do_Select_Body(pSearchString) 'Get the body sSQL = sSQL & " ORDER BY PS.PROVINCE_STATE_NAME" Set Do_Select = clsSQL.Perform_SQL(sSQL) End Function Private Function Do_Select_Body(ByRef pSearchString As String) As String ' Get the select body sSQL = "SELECT" sSQL = sSQL & " PS.ID" sSQL = sSQL & ", PS.CODE" sSQL = sSQL & ", PS.PROVINCE_STATE_NAME" sSQL = sSQL & ", PS.COUNTRY" sSQL = sSQL & ", PS.GMT_ZONE" sSQL = sSQL & ", PS.TAX_GROUP_ID" sSQL = sSQL & ", TG.CODE AS TAX_GROUP_CODE" sSQL = sSQL & ", TG.DESCRIPTION AS TAX_GROUP_DESCRIPTION" sSQL = sSQL & ", PS.FREIGHT_ADJUSTMENT" sSQL = sSQL & ", PS.RECORD_CREATED" sSQL = sSQL & ", PS.RECORD_UPDATED" sSQL = sSQL & ", PS.USER_ID" sSQL = sSQL & ", EL.USER_NAME AS EMPLOYEE_USER_NAME" sSQL = sSQL & ", EL.FIRST_NAME AS EMPLOYEE_FIRST_NAME" sSQL = sSQL & ", EL.LAST_NAME AS EMPLOYEE_LAST_NAME" sSQL = sSQL & " FROM ((" sSQL = sSQL & "PROVINCE_STATE AS PS" sSQL = sSQL & " LEFT JOIN EMPLOYEE_LOGIN AS EL ON PS.USER_ID = EL.ID)" sSQL = sSQL & " LEFT JOIN TAX_GROUP AS TG ON PS.TAX_GROUP_ID = TG.ID)" sSQL = sSQL & " WHERE 1=1" Build_Search pSearchString Do_Select_Body = sSQL End Function Public Function Insert_Row(ByRef rsRow As ADODB.Recordset, pID As Long) As Boolean ' Insert row into the table; Dim sErrors As String Dim sDate As Variant sDate = Format(Now(), "d-mmm-yyyy") ' Use Stored Procedure (SQL Server 2000) ' Initialize the input & output parameters; Set cStoredProcedure = New ADODB.Command Set cParam = New ADODB.Parameter With cStoredProcedure Set cParam = .CreateParameter("@CODE", adVarChar, adParamInput, 4, rsRow.Fields("CODE")) .Parameters.Append cParam Set cParam = .CreateParameter("@PROVINCE_STATE_NAME", adVarChar, adParamInput, 30, rsRow.Fields("PROVINCE_STATE_NAME")) .Parameters.Append cParam Set cParam = .CreateParameter("@COUNTRY", adVarChar, adParamInput, 20, rsRow.Fields("COUNTRY")) .Parameters.Append cParam Set cParam = .CreateParameter("@GMT_ZONE", adSmallInt, adParamInput, 5, rsRow.Fields("GMT_ZONE")) .Parameters.Append cParam Set cParam = .CreateParameter("@TAX_GROUP_ID", adInteger, adParamInput, 8, rsRow.Fields("TAX_GROUP_ID")) .Parameters.Append cParam Set cParam = .CreateParameter("@FREIGHT_ADJUSTMENT", adCurrency, adParamInput, 8, rsRow.Fields("FREIGHT_ADJUSTMENT")) .Parameters.Append cParam Set cParam = .CreateParameter("@USER_ID", adInteger, adParamInput, 8, glngUserID) .Parameters.Append cParam Set cParam = .CreateParameter("@ID", adInteger, adParamOutput, 8, pID) .Parameters.Append cParam sSQL = "SP_PROVINCE_STATE_INSERT" clsSQL.Perform_StoredProcedure sSQL, cStoredProcedure, sErrors End With ' Check Errors If Len(sErrors) Then Display_Errors "Insert Failed", sErrors Insert_Row = False Exit Function End If ' Read any output parameters pID = cStoredProcedure.Parameters("@ID").Value Insert_Row = True End Function With this method, creating a new table and its related 'class' is quite simple. Now, although this methodology doesn't have true inheritance, I get some code re-use and it provided a very good platform for my DB applications. However, I am now somewhat confused with regards to VB.Net. .Net would appear to give me some native functionality for things such as selects, inserts and deletes. I can see how inheritance may truly help me by allowing me to create a 'master class', but I'm not sure how I would implement this? - Should I be looking at writing similar code? or does .Net offer me another option? Ideally I would like to create a visual class such as a dataset - but from what I can see - this has to be copied from form to form as required - not inherited. Anyway, if anyone has any suggestioms, or can point me in the right direction, I'd very much appreciate it. All the best, Graham Tag: ASP Error on INSERT INTO Statement Tag: 97605
  - 12
    - Recommended OleDB driver for MySQL? I am accessing MySQL from VS 2003, C# and ADO.NET, using the OleDb interfaces (not ODBC) and have been using (successfully) an OLE DB driver on: http://www.mysql.com/downloads/download.php?file=Downloads/Win32/MyOLEDB3.exe&pick=mirror This driver is called "My OLEDB 3.0, OLE Provider for MySQL, February 20, 2001". I am actually amazed that anything written in 2001 still works with the recent MySQL 4.1 ... amazed - and worried that some of the issues I am working through may be caused by some incompatibilies ... Should I be using a more recent driver? I see mysql-connector-net-1.0.0-beta.zip on www.MySQL.com but this package doesn't contain any EXE (and I don't whether one can be built from the included file or how). I am not even sure this is an OLE DB provider, it may be an ODBC provider. In fact, considering how new I am to MySQL, I am not even sure that *any* MySQL OLE DB provider is required: the Windows System Requirements page (http://dev.mysql.com/doc/mysql/en/Windows_system_requirements.html) does not even mention the need to install anything before proceeding with the MySQL installation (except for those planning to connect via ODBC). Thanks! Patrick Tag: ASP Error on INSERT INTO Statement Tag: 97598
  - 13
    - DataGrid boundcolumn problem I want to use a datagrid with my display, but I don't want to use 1 row per record. What I have is a record with up to 6 values - (answer1,answer2,answer3,answer4,answer5,answer6). I want to display them with alternating styles. One record may only have the first 3 fields with data and another with 4 and still another with 6. I want it to show as: Answer1 Answer2 Answer3 or Answer1 Answer2 Answer3 Answer4 Answer5 Can I set up a DataGrid this way? Thanks, Tom Tag: ASP Error on INSERT INTO Statement Tag: 97597
  - 14
    - Select datagrid rows with key-up and key-down? I have an application that uses a windows forms datagrid. How do I select rows using the up-arrow and down-arrow keys? Mervin Williams Tag: ASP Error on INSERT INTO Statement Tag: 97592
  - 15
    - DataTable.Add(row) and fired events I have a situation where a method listens to the RowChanged event for a datatable. The problem I having is this.. The RowChanged event handler calls another method, passing in the e.Row["ID_VALUE"] of the row that fired the event. In the case where this is a .Add(dr) that has caused the RowChanged event to fire.... the row that is referenced in e.Row (event arg for the RowChanged event handler) is not actually in the table yet. If I do a dt.Select() or a dt.Rows.Count,,, it is obvious that the row firing the event has not actually been added to table yet. Why is this happening? Is this a bug? Why would the row fire the table event and not be in the Rows collection for the table??? Tag: ASP Error on INSERT INTO Statement Tag: 97584
  - 16
    - Opening an acess database I have been programming in years in VVB-5-6 but can anyone help in the syntax in how to open an access database with a password and how to get a recordset from it as I am only used to the DAO method and this does not seem to be used in .net -- Clifford Sutton Tag: ASP Error on INSERT INTO Statement Tag: 97575
  - 17
    - Newbie: Detecting type of field contents in datareader This is a multi-part message in MIME format. ------=_NextPart_000_0059_01C4B5CD.2DB31250 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have a few examples and things are quite clear with using a datareader = (reading data, naming column headers etc.), and populating a listview. However: (!!!) In all of the examples we know beforehand the number of = fields and therefore the data type (Int, Date, String etc) since the = SELECT string is constant. My problem is with variable strings that get constructed at runtime. = Then, since i do not know beforehand what exactly is my first,second, = etc. field, I CANNOT use : myDataReader.GetString(0) or myData.GetInt32(0),etc. since io do not = know the type beforehand. Is there a way around this? I really dont mind getting them all as text = so is the an CType analogous method I could use . Something like casting = BEFORE reading ?? ANY help would be appreciated, Thanx -steve ------=_NextPart_000_0059_01C4B5CD.2DB31250 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1476" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>I have a few examples and things are = quite clear=20 with using a datareader (reading data, naming column headers = etc.), and=20 populating a listview.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>However: (!!!) In all of the examples = we know=20 beforehand the number of fields and therefore the data type (Int, Date, = String=20 etc) since the SELECT string is constant.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>My problem is with variable strings = that get=20 constructed at runtime. Then, since i do not know beforehand what = exactly is my=20 first,second, etc. field, I CANNOT use :</FONT></DIV> <DIV> <P><FONT face=3DArial size=3D2>myDataReader.GetString(0) or = myData.GetInt32(0),etc.=20   since io do not know the type beforehand.</FONT></P> <P><FONT face=3DArial size=3D2>Is there a way around this? I really dont = mind=20 getting them all as text so is the an CType analogous method I could use = .=20 Something like casting BEFORE reading ??</FONT></P> <P><FONT face=3DArial size=3D2>ANY help would be appreciated,</FONT></P> <P><FONT face=3DArial size=3D2>Thanx</FONT></P> <P><FONT face=3DArial size=3D2>-steve</FONT></P> <P><FONT face=3DArial size=3D2></FONT> </P></DIV></BODY></HTML> ------=_NextPart_000_0059_01C4B5CD.2DB31250-- Tag: ASP Error on INSERT INTO Statement Tag: 97570
  - 18
    - Syntax error in UPDATE statement - field name "Upper", or "Lower" I am getting "Syntax error in UPDATE statement" if one of the fields in a table of Access database (2000, or 97) has the name "Upper" or "Lower". As soon as I change the name of the field to something else, the code works fine. I looked through the list of reserved names, and I could not find the words "Upper" and "Lower". In order to reproduce this behavior, please create a database "Lookup.mdb" with one table "Test" which contains 2 fields of type "Text". The name of one of the fields should be "Upper", or "Lower". Populate the table with any entries you want. After that create a Windows Forms app and add a Windows Form. Use the code of the Windows Forms class Update1.vb below. Change the constant LOOKUP_DBPATH appropriately. Run the app. Click the button "Get Lookup Test". The datagrid should be populated with values from table "Test". Edit the content of a cell under the filed "Upper" or "Lower". Click the button "Update Lookup Test". An exception should be raised. You may want to set breakpoints in the routines "OnRowUpdating" and "OnRowUpdated". The environment is: Visual Studio 2003; Windows XP Prof; Windows Forms App; Microsoft Access 2000 database. ================================= The code: #Region " Imports " Imports System Imports System.Data Imports System.Data.OleDb #End Region Public Class Update1 Inherits System.Windows.Forms.Form #Region " Windows Form Designer generated code " Public Sub New() MyBase.New() 'This call is required by the Windows Form Designer. InitializeComponent() 'Add any initialization after the InitializeComponent() call End Sub 'Form overrides dispose to clean up the component list. Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose() End If End If MyBase.Dispose(disposing) End Sub 'Required by the Windows Form Designer Private components As System.ComponentModel.IContainer 'NOTE: The following procedure is required by the Windows Form Designer 'It can be modified using the Windows Form Designer. 'Do not modify it using the code editor. Friend WithEvents grdLookup As System.Windows.Forms.DataGrid Friend WithEvents btnGetLookup As System.Windows.Forms.Button Friend WithEvents btnUpdateLookup As System.Windows.Forms.Button <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.grdLookup = New System.Windows.Forms.DataGrid Me.btnGetLookup = New System.Windows.Forms.Button Me.btnUpdateLookup = New System.Windows.Forms.Button CType(Me.grdLookup, System.ComponentModel.ISupportInitialize).BeginInit() Me.SuspendLayout() ' 'grdLookup ' Me.grdLookup.DataMember = "" Me.grdLookup.HeaderForeColor = System.Drawing.SystemColors.ControlText Me.grdLookup.Location = New System.Drawing.Point(6, 8) Me.grdLookup.Name = "grdLookup" Me.grdLookup.Size = New System.Drawing.Size(346, 166) Me.grdLookup.TabIndex = 0 ' 'btnGetLookup ' Me.btnGetLookup.Location = New System.Drawing.Point(370, 10) Me.btnGetLookup.Name = "btnGetLookup" Me.btnGetLookup.Size = New System.Drawing.Size(114, 22) Me.btnGetLookup.TabIndex = 1 Me.btnGetLookup.Text = "Get Lookup Test" ' 'btnUpdateLookup ' Me.btnUpdateLookup.Location = New System.Drawing.Point(370, 40) Me.btnUpdateLookup.Name = "btnUpdateLookup" Me.btnUpdateLookup.Size = New System.Drawing.Size(114, 22) Me.btnUpdateLookup.TabIndex = 2 Me.btnUpdateLookup.Text = "Update Lookup Test" ' 'Update1 ' Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) Me.ClientSize = New System.Drawing.Size(492, 181) Me.Controls.Add(Me.btnUpdateLookup) Me.Controls.Add(Me.btnGetLookup) Me.Controls.Add(Me.grdLookup) Me.Name = "Update1" Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen Me.Text = "Update1" CType(Me.grdLookup, System.ComponentModel.ISupportInitialize).EndInit() Me.ResumeLayout(False) End Sub #End Region <STAThread()> Public Shared Sub Main() Application.Run(New Update1) End Sub Private m_Conn As OleDbConnection Private m_openedConnection As Boolean Private m_sql As String Private m_adap As OleDbDataAdapter Private m_build As OleDbCommandBuilder Private m_dt As DataTable Const conAccess As String = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;" Const conAccess1 As String = "Data Source=" Const LOCALDB_PATH = "E:\SMW\TestUpdates\Lookup.mdb" Private Sub Update1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load Try m_openedConnection = OpenConnection(m_Conn) Catch ex As Exception Debug.WriteLine(ex.Message) End Try End Sub Private Sub Update1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing Try Call CloseConnection(m_Conn) Catch ex As Exception Debug.WriteLine(ex.Message) End Try End Sub Private Sub btnGetLookup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetLookup.Click Dim gotLookup As Boolean Try m_sql = "SELECT * FROM Test" m_dt = New DataTable m_dt.TableName = "Test" gotLookup = GetDataTableForEditAndUpdate(m_sql, m_Conn, m_adap, m_dt) grdLookup.Text = "Test" grdLookup.DataSource = m_dt AddHandler m_adap.RowUpdating, _ New OleDbRowUpdatingEventHandler(AddressOf OnRowUpdating) AddHandler m_adap.RowUpdated, _ New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated) Catch ex As Exception Debug.WriteLine(ex.Message) End Try End Sub Private Sub btnUpdateLookup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateLookup.Click Dim updated As Boolean Try updated = UpdateLookupTableBackToLocalDB() Catch ex As Exception Debug.WriteLine(ex.Message) End Try End Sub Public Function OpenConnection(ByRef conn As OleDbConnection) As Boolean Try If conn Is Nothing Then conn = New OleDbConnection conn.ConnectionString = conAccess & conAccess1 & LOCALDB_PATH End If Catch ex As Exception OpenConnection = False Debug.WriteLine(ex.Message) Exit Function End Try Try If conn.State = ConnectionState.Closed Then conn.Open() End If OpenConnection = True Catch ex As OleDbException Debug.WriteLine(ex.Message) OpenConnection = False End Try End Function Public Sub CloseConnection(ByRef conn As OleDbConnection) Try If Not conn Is Nothing Then If conn.State = ConnectionState.Open Then conn.Close() conn.Dispose() End If End If Catch ex As Exception Debug.WriteLine(ex.Message) Finally conn = Nothing End Try End Sub Public Function GetDataTableForEditAndUpdate(ByVal sql As String, _ ByVal conn As OleDbConnection, _ ByRef adap As OleDbDataAdapter, _ ByRef dt As DataTable) As Boolean Try adap = New OleDbDataAdapter(sql, conn) m_build = New OleDbCommandBuilder(adap) adap.FillSchema(dt, SchemaType.Source) adap.Fill(dt) GetDataTableForEditAndUpdate = True Catch ex As Exception Debug.WriteLine(ex.Message) GetDataTableForEditAndUpdate = False End Try End Function Private Function UpdateLookupTableBackToLocalDB() As Boolean Try Me.Cursor = Cursors.WaitCursor With m_adap .Update(m_dt.Select(Nothing, Nothing, DataViewRowState.Deleted)) .Update(m_dt.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent)) .Update(m_dt.Select(Nothing, Nothing, DataViewRowState.Added)) End With UpdateLookupTableBackToLocalDB = True Catch ex As Exception Debug.WriteLine(ex.Message) UpdateLookupTableBackToLocalDB = False Finally Me.Cursor = Cursors.Default End Try End Function Protected Sub OnRowUpdating(ByVal sender As Object, _ ByVal args As OleDbRowUpdatingEventArgs) Debug.WriteLine(args.Command.CommandText) If args.Errors Is Nothing Then ' Else Debug.WriteLine(args.Errors.Message) Debug.WriteLine(args.Errors.Source) End If Debug.WriteLine(args.Status.ToString) End Sub Protected Sub OnRowUpdated(ByVal sender As Object, _ ByVal args As OleDbRowUpdatedEventArgs) Debug.WriteLine(args.Command.CommandText) If args.Errors Is Nothing Then ' Else Debug.WriteLine(args.Errors.Message) Debug.WriteLine(args.Errors.Source) End If Debug.WriteLine(args.Status.ToString) End Sub End Class -- Igor Gegusin Tag: ASP Error on INSERT INTO Statement Tag: 97567
  - 19
    - OracleClient exception... Hey guys, I'm stumped on this... Today I was asked to look into coverting our ODBC access to a Oracle database to the client that microsoft wrote and have come up against the lovely "ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'MSG_DATAREAD' " message... All I'm doing is converting the code from the stuff that's using OdbcConnection/Command etc to OracleConnection/Command etc so I know the stored procedure's all work fine. Here's the code that's failing: ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' get a reader for the record set with this msg number Dim dbcommand As OracleCommand = New OracleCommand dbcommand.Connection = dbData dbcommand.CommandType = CommandType.StoredProcedure dbcommand.CommandText = "MSG_DATAREAD" ' get a cursor out Dim param As OracleParameter = New OracleParameter param = dbCommand.Parameters.Add("RC1", OracleClient.OracleType.Cursor) param.Direction = ParameterDirection.Output ' pass in the msg number Dim msgnumParam As OracleParameter = New OracleParameter msgnumParam = dbcommand.Parameters.Add("MsgNumber", OracleType.VarChar, 50) msgnumParam.Direction = ParameterDirection.Input msgnumParam.Value = NullToBlank(m_szCurrentMsgNumber) Dim dbReader As OracleDataReader = dbcommand.ExecuteReader While dbreader.Read ' do some stuff with the data I get back End While dbreader.Close() ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Here's the Stored Procedure it's calling: /* Stored procedure MSG_DATAREAD ------------------------------------------- */ ( MsgNumber IN VARCHAR2 DEFAULT NULL, RC1 IN OUT Omwb_emulation.globalPkg.RCT1 ) AS MsgNumber_ VARCHAR2(50) := MsgNumber; StoO_selcnt INTEGER; StoO_error INTEGER; StoO_rowcnt INTEGER; StoO_crowcnt INTEGER := 0; StoO_fetchstatus INTEGER := 0; StoO_errmsg VARCHAR2(255); StoO_sqlstatus INTEGER; BEGIN NULL; /*[SPCONV-ERR(6)]:(set DATEFORMAT) Manual conversion required*/ OPEN RC1 FOR SELECT MsgNumber , MsgItem , Section , Field , Data FROM MSG_Data WHERE MsgNumber = MSG_DATAREAD.MsgNumber_ ORDER BY MsgItem ; END MSG_DATAREAD; Tag: ASP Error on INSERT INTO Statement Tag: 97559
  - 20
    - problem with the dataset update Hi, I have notice something with the datagrid update i have an application which is updated from multiple sources when one user deletes a row on the datagrid, the delete propgates back to the database. I see it is gone, from the database. but, when other users app calls it's fill command to refresh it's datatable, It sees the already deleted row. what am i doing wrong? Tag: ASP Error on INSERT INTO Statement Tag: 97556
  - 21
    - Setting Textbox to Null for DateTime I have a textbox on a windows form which is bound to a smalldatetime field in a Dataset. The problem is that sometimes this date should be deleted so that a null value gets written back to the database. However, if I delete this date and leave the Textbox the previous entry gets retrieved. Is there a way to prevent this from happening and leaving the textbox empty so that a null value gets written back to the database when update is called? Tag: ASP Error on INSERT INTO Statement Tag: 97551
  - 22
    - Date/Time in Access I'm having trouble storing the value of Now in an Access table. I'm using VB.NET 2002. I need to store a time value, but no matter what I do I end up getting just a date value in Access. I'm doing something like this: Dim NewRow as DataRow = MyTable.NewRow NewRow("InTime") = Now MyTable.Rows.Add(NewRow) DataAdaptor.Update(MyTable) The value of CDate(NewRow("InTime")) is correct until I refresh the dataset with data from the database. All I get is the correct date and a time of 12:00:00. I've tried using every possible format of Date/Time in Access, including my own custom format. Thanks for the help Tag: ASP Error on INSERT INTO Statement Tag: 97550
  - 23
    - Delete datarowview objects from a dataview Hi all, I have a problem when attempting to delete datarowview obecjts from a data view Dim mydv As New DataView Dim thisDRV As DataRowView mydv.Table = myDS.Tables("CountedItem") mydv.RowFilter = "ilID = '" & lID & "'" For Each thisDRV In mydv thisDRV.Delete() Next When I execute this code I get an IndexOutOfRangeException exception. However, if I print the first column of the datarowviews they all exist. Dim mydv As New DataView Dim thisDRV As DataRowView mydv.Table = myDS.Tables("CountedItem") mydv.RowFilter = "ilID = '" & lID & "'" For Each thisDRV In mydv debug.writeline(thisDRV(0)) Next Is there something funny happeneing with the deleting of a datarowview from a dataview? Is this the correct way to delete a subset of rows from a datatable? Many thanks, Jason. Tag: ASP Error on INSERT INTO Statement Tag: 97546
  - 24
    - Convert string to sqldbtype This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C4B5C4.ECD8DFB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is there another easier way to convert a string to a sqldbtype than = using a select case, as in: Namespace ADOHelper Public Class sqldbtype Public Shared Function Convert(ByVal type As String) As = System.Data.SqlDbType Select Case type Case "int" Return System.Data.SqlDbType.Int Case "NVarChar" Return System.Data.SqlDbType.NVarChar Case "VarChar" Return System.Data.SqlDbType.VarChar Etc ......... ------=_NextPart_000_0008_01C4B5C4.ECD8DFB0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1458" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DArial size=3D2>Is there another easier way to convert = a string to=20 a sqldbtype than using a select case, as in:</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV> <P><FONT face=3DArial size=3D2>Namespace ADOHelper<BR>    = </FONT><FONT face=3DArial size=3D2>Public Class = sqldbtype<BR>   =20     </FONT><FONT face=3DArial size=3D2>Public Shared = Function=20 Convert(ByVal type As String) As = System.Data.SqlDbType<BR>   =20         </FONT><FONT face=3DArial = size=3D2>Select Case=20 type<BR>           =20     </FONT><FONT face=3DArial size=3D2>Case=20 "int"<BR>           =20         </FONT><FONT face=3DArial = size=3D2>Return=20 System.Data.SqlDbType.Int<BR>       =20         </FONT><FONT face=3DArial = size=3D2>Case=20 "NVarChar"<BR>           =20         </FONT><FONT face=3DArial = size=3D2>Return=20 System.Data.SqlDbType.NVarChar<BR>       =20         </FONT><FONT face=3DArial = size=3D2>Case=20 "VarChar"<BR>           =20         </FONT><FONT face=3DArial = size=3D2>Return=20 System.Data.SqlDbType.VarChar<BR>      &nbs= p;         Etc=20 .........</FONT></P></DIV></BODY></HTML> ------=_NextPart_000_0008_01C4B5C4.ECD8DFB0-- Tag: ASP Error on INSERT INTO Statement Tag: 97544
  - 25
    - Populate SQL table with a dataset all in one It's possibile to populate a SQL table with a dataset without throw all rows in datatable but in one query? Thanks -- Filippo Macchi www.filippomacchi.it Tag: ASP Error on INSERT INTO Statement Tag: 97542

Hi

I'm getting the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] The INSERT INTO statement contains
the following unknown field name: 'ClientName'. Make sure you have typed the
name correctly, and try the operation again.

I have checked the database and the field names are correct. The IIS server
has the appropriate permissoins to the database. When I run the query from
Access it sucessfully inserts a row. Thanks for your help and here is my code:

set objCmd = server.CreateObject("adodb.command")
set objCmd.ActiveConnection = oUnifiedConn

DIM strTest
strTest = "INSERT INTO Client (ClientName) VALUES ('" & strName & "');"
response.Write strTest

objCmd.CommandText = strTest
'objCmd.CommandType = adCmdtext
objCmd.Execute ' line 82

Thanks

Mark

Top

Re: ASP Error on INSERT INTO Statement by Ryan

Ryan
Fri Oct 22 13:51:16 CDT 2004

Off_Route wrote:
> Hi
>
> I'm getting the following error:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] The INSERT INTO statement contains
> the following unknown field name: 'ClientName'. Make sure you have typed the
> name correctly, and try the operation again.

> strTest = "INSERT INTO Client (ClientName) VALUES ('" & strName & "');"

Is strTest properly escaped (i.e. are there any single apostrophes in it)?

Top