Validate Data in SQL table

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
    - DataSet / HasChanges / Delete Row Hi, In my DataSet, I'm trying to keep track of row deletions, so later I can = check HasChanges to see if I need to update the datasource. I'm trying = to delete a specific row in the dataset that I search out from a primary = key. I've found the DataRow I want to delete and have a reference to = it. I've tried this, myDataSet.myTable.rows.remove(theRowIFound), but it appears this = actually removes the row, and doesn't just change the rowstate = (correct?) The only way I see to delete a row/change rowstate is to have the actual = ordinal of the row I'm after. How can I get this? Or is there an = easier way I can delete this row and change the rowstate, using the = actual reference to the row I have? Thanks! --Michael Tag: Validate Data in SQL table Tag: 97804
  - 2
    - Urgent~ How to merge 2 datasets in 1 DataGrid? Hi experts, I am not sure if I should use Merge on this case, but I want to add a Vendor Name column in the datagrid1 where the Vendor Name has to get from another server (not linked and can't be linked). The first table contains Job tracking information, which has a "J_Vendor_ID" column, and no Vendor Name in this server. The 2nd table on the other server contains VendName and VendorID. I want to have a Vendor Name column (VendName) in datagrid, and VendName column has to get from the 2nd table, the VendName should list only from table1's the VendorID. The J_vendor_ID from table1 and VendorID from table 2 will be the key columns for the 2 datasets merging. I received error when it's doing the dataSet.Merge(dataSet2): System.Data.DataException: Mismatch columns in the PrimaryKey : Tag: Validate Data in SQL table Tag: 97802
  - 3
    - click on datagrid item, display new datagrid based on that click I want to have a datagrid that is just one column of group names (say from qryGroups) When someone clicks on a group name (ie clicks in that 'cell' or the column could be a hyperlink column... whatever is best), i want the datagrid to then display results from qrySubgroups, but filter it for only those that have the column intGroup equal to the intGroup value from the one they clicked from qryGroups ie qryGroups has: intGroup GroupName 1 Accounting 2 Orders 3 Service qrySubgroups has intGroup SubGroupName 1 Taxes 1 Booked Revenue 2 Customers 3 Maintenance 3 Repairs So there are Taxes and Booked Rev as subgroups of Accounting so the original dataset will display: Accounting Orders Service when the user clicks on Accounting, it should then display: Taxes Booked Revenue how do i best do this? can i just put both tables (qryGroups, qrySubgroups) into the dataset that the table runs off of, then when they click on it, have it change from using dataset1.qryGroups to dataset1.qrySubgroups and have it filter by qryGroups.intGroup ? or maybe the original cells are hyperlinks and they open subgroups.aspx and pass the value of the group (intGroup) and then the dataset on that page runs off of that value (filters by it?) or maybe some easier or other way i'm not thinking of! any help!?!? Tag: Validate Data in SQL table Tag: 97799
  - 4
    - Error with automated execution Hi I am opening a connection on a shared db as below which works fine when used in a vb.net win app. Conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=F:\Events Data\Events Data.mdb;User Id=admin;Password=;") The problem is that as soon as I turn the app into a service or run it using windows scheduler I get error on conn.open statement. The error is 'F:\Events Data\Events Data.mdb' is an invalid path, please check that the path is correct...or something on those lines. If I run it as service on a standalone PC then that works. So apparently db connection have different rules for a standard win app and services with a db shared over a lan. How can I make the code work as a service? Thanks Regards Tag: Validate Data in SQL table Tag: 97798
  - 5
    - Architectural Question This is a multi-part message in MIME format. ------=_NextPart_000_0311_01C4B836.46A5C3F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I'm using a Component Class object to represent a database table. This = Data Access Object (DAO) has a connection object and data adapter object = in it along with a bunch of methods that need to be there every time I = create a new DAO to represent a db table.=20 Instead of having to code that "common" logic for each new class I'd = like to create a base DAO, put the common logic there and inherit from = it (instead of System.ComponentModel.Component) for each new DAO. My current steps for creating a new DAO are: 1) Create a new Component Class object 2) Drag the database from the Server Explorer onto the design pane. This = creates the connection object to the db. 3) Drag a table from the database in Server Explorer onto the design = pane. This creates the data adapter object for that table. 4) Create all the common methods in the new DAO Within an application, each DAO will have the exact same connection = object so I'd like to include that in the base DAO. Thinking along those lines, I created a new DAO (call it BaseDAO) and = did steps 1, 2 & 4 from above. Then I created another DAO (ChildDAO) = that inherits from the BaseDAO. I was hoping that I would then be able = to just do step 3 from above for this new DAO.=20 When I try to bring up the designer for ChildDAO I get the following = error: The designer could not be shown for this file because none of the = classes within it can be designed. The designer inspected the following = classes in the file:=20 ChildDAO --- The base class 'LayerDataAccess.BaseDAO' could not be = loaded. Ensure the assembly has been referenced or built if it is part = of the project. Both the BaseDAO and ChildDAO objects are in the same project. Does = anyone have any ideas what I'm doing wrong? Thanks in advance for any help. David. ------=_NextPart_000_0311_01C4B836.46A5C3F0 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>Hello,</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>I'm using a Component Class object to = represent a=20 database table. This Data Access Object (DAO) has a connection = object and=20 data adapter object in it along with a bunch of methods that need to be = there=20 every time I create a new DAO to represent a db table. </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Instead of having to code that "common" = logic for=20 each new class I'd like to create a base DAO, put the common logic = there=20 and inherit from it (instead of System.ComponentModel.Component) for = each new=20 DAO.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>My current steps for creating a new DAO = are:</FONT></DIV> <DIV><FONT face=3DArial size=3D2>1) Create a new Component Class = object</FONT></DIV> <DIV><FONT face=3DArial size=3D2>2) Drag the database from the Server = Explorer onto=20 the design pane. This creates the connection object to the = db.</FONT></DIV> <DIV><FONT face=3DArial size=3D2>3) Drag a table from the database in = Server=20 Explorer onto the design pane. This creates the data adapter object for = that=20 table.</FONT></DIV> <DIV><FONT face=3DArial size=3D2>4) Create all the common methods in the = new=20 DAO</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Within an application, each DAO will = have the exact=20 same connection object so I'd like to include that in the base = DAO.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Thinking along those lines, I created a = new=20 DAO (call it BaseDAO) and did steps 1, 2 & 4 from above. = Then I=20 created another DAO (ChildDAO) that inherits from the BaseDAO. I = was hoping=20 that I would then be able to just do step 3 from above for this new DAO. = </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>When I try to bring up the designer for = ChildDAO I=20 get the following error:</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px"> <DIV><FONT face=3DArial size=3D2> The designer could not be shown = for this=20 file because none of the classes within it can be designed.  The = designer=20 inspected the following classes in the file: </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2> ChildDAO --- The base class=20 'LayerDataAccess.BaseDAO' could not be loaded.  Ensure the = assembly has=20 been referenced or built if it is part of the=20 project.<BR></FONT></DIV></BLOCKQUOTE> <DIV><FONT face=3DArial size=3D2>Both the BaseDAO and ChildDAO objects = are in the=20 same project. Does anyone have any ideas what I'm doing = wrong?</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Thanks in advance for any = help.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>David.</FONT></DIV></BODY></HTML> ------=_NextPart_000_0311_01C4B836.46A5C3F0-- Tag: Validate Data in SQL table Tag: 97790
  - 6
    - Error filling datagrid --____LPHMXLZMXOMRLFKSEJCW____ Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi I am trying to fill a datagrid using the following snippet: GetSQL =3D "Select QID, Q, A, B, C, D, CorrectAnswer, Expired " & _ "From Questions " & _ "INNER JOIN " & _ "QuestionTypesList ON Questions.QTID =3D QuestionTypesList= .QTID " & _ "WHERE (QuestionTypesList.QuestionTypes =3D '" & = QsetSelected & "')" rs.Close() rs.Open(GetSQL, ConfigCN, ADODB.CursorTypeEnum.adOpenStatic, = ADODB.LockTypeEnum.adLockOptimistic) rs.MoveFirst() Dim da As OleDbDataAdapter =3D New OleDbDataAdapter Dim ds As DataSet =3D New DataSet Try da.Fill(ds, rs, "QTable") Catch ex As Exception MsgBox(ex) End Try DataGrid1.DataSource =3D ds.Tables(0) But when I do I get a catch error when trying da.fill: Argument 'Prompt' cannot be converted to type 'String'. Any ideas/suggestions welcome! Cheers Adam --____LPHMXLZMXOMRLFKSEJCW____ Content-Type: multipart/related; boundary="____WHPEPQYSAQXEHDGESJXG____" --____WHPEPQYSAQXEHDGESJXG____ Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable <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></HEAD> <BODY style=3D"MARGIN: 4px 4px 1px; FONT: 8pt Tahoma; COLOR: #000000"> <DIV>Hi I am trying to fill a datagrid using the following snippet:</DIV> <DIV>GetSQL =3D "Select QID, Q, A, B, C, D, CorrectAnswer, Expired " & = _<BR>           &nbs= p;     "From Questions " & _<BR>   &= nbsp;           &nbs= p; "INNER JOIN " & _<BR>       &nbsp= ;         "QuestionTypesList ON = Questions.QTID =3D QuestionTypesList.QTID " & _<BR>   &n= bsp;           &nbsp= ; "WHERE (QuestionTypesList.QuestionTypes =3D '" & QsetSelected & = "')"</DIV> <DIV> </DIV> <DIV>        rs.Close()<BR>  &= nbsp;     rs.Open(GetSQL, ConfigCN, ADODB.CursorTypeEnu= m.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)<BR>   &= nbsp;    rs.MoveFirst()<BR>     &nb= sp;  Dim da As OleDbDataAdapter =3D New OleDbDataAdapter<BR> &nbs= p;      Dim ds As DataSet =3D New DataSet<BR>&nbsp= ;       Try<BR>     =        da.Fill(ds, rs, "QTable")<BR> &nb= sp;      Catch ex As Exception<BR>  &nbs= p;         MsgBox(ex)<BR> &nbs= p;      End Try<BR>     &= nbsp;  DataGrid1.DataSource =3D ds.Tables(0)</DIV> <DIV> </DIV> <DIV>But when I do I get a catch error when trying da.fill:</DIV> <DIV>Argument 'Prompt' cannot be converted to type 'String'.</DIV> <DIV> </DIV> <DIV>Any ideas/suggestions welcome!</DIV> <DIV> </DIV> <DIV>Cheers</DIV> <DIV>Adam</DIV></BODY></HTML> --____WHPEPQYSAQXEHDGESJXG____-- --____LPHMXLZMXOMRLFKSEJCW____-- Tag: Validate Data in SQL table Tag: 97788
  - 7
    - Generated Insert Command from CommandBuilder I'm having some trouble with an insert back to a datasource using the the insertcommand built by commandbuilder. I suspect it may be a slight problem with the insert statement. Is there any way to see the actual insert statement that commandbuilder creates? Tag: Validate Data in SQL table Tag: 97777
  - 8
    - returning numeric values from access database Hi - I am developing a database class which accesses an Access database to simply retrieve decimal values. When I call the code below it fails with an ' Unspecified error' - The database it calls have the 2 columns (BEGINDEC & ENDDEC) and both are set to data type 'Double' in the database. I have also tried this code in varying forms but always with different error messages - what is my best option to return Double values from an Access Database - James... double ipdec = GetAddress(Address); OleDbConnection conn = new OleDbConnection(@"Data Source=GIPS.mdb;Password=;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin"); try { string sql = "SELECT COUNTRY FROM GEOS WHERE BEGINDEC >= "; sql += ipdec; sql += " AND ENDDEC <= "; sql += ipdec.ToString(); conn.Open(); OleDbDataAdapter adapt = new OleDbDataAdapter(sql,conn); DataSet ds = new DataSet(); adapt.Fill(ds); foreach(DataRow dr in ds.Tables[0].Rows) { Debug.WriteLine(dr["COUNTRY"]); }catch{exception ex)Debug,Writeline(ex.Message)); Tag: Validate Data in SQL table Tag: 97776
  - 9
    - Call SQL Stored procedure with Bit datatype I am having difficulty calling a stored procedure from VB.Net that has a Bit parameter. To try and figure it out, I have created some tests. The SQL SP looks like this: CREATE procedure up_insert_test @value bit as insert into tblTest(field1) values(@value) And the table has two fields. The first is ID which is an indentity field, and the second is Field1 that is just a nullable Bit field. Here is my VB Code: Dim cn As SqlConnection Dim cmd As SqlCommand cn = New SqlConnection(g_DBConnection) cn.Open() If cn.State = ConnectionState.Open Then cmd = New SqlCommand("up_insert_test") cmd.Connection = cn Dim p As New SqlParameter("@value", SqlDbType.Bit) p.Value = 1 cmd.Parameters.Add(p) cmd.ExecuteNonQuery() 'Fails here!!!!! cn.Close() End If Everytime I get to the ExecuteNonQuery I get the followning error: An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll Additional information: System error. If anyone can give me a hand, I would greatly appreciate it. Thanks, Rob Tag: Validate Data in SQL table Tag: 97763
  - 10
    - How to get the parent of a deleted (child) row Hello, My question is quite straightforward: How do I get the parent row of a deleted (child) row. If I use the method "GetParentRow" of the child then it returns "nothing". I have tried using the version parameter of the method but without any result. I probably could use the original value of the deleted row to "manually" go through the parent rows and find its parent but this seems like a lot of work for something as simple as this. Hoping for a quick answer, WiZZiX Tag: Validate Data in SQL table Tag: 97762
  - 11
    - Saving rtf using binary and oledb --____LPHMXLZMXOMRLFKSEJCW____ Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi, I am trying to save some rtf code from an rtf field to a TEXT field on = SQL2000. The code I have been playing with (thanks to Nick for the originally = supplied code!) is: Sub DoByteConversion() Dim pBytStuff As Byte() =3D StringToArray(Rtxt_readme.Rtf) = 'convert rtf field to byte array Dim pDBNConnection As OleDbConnection Dim pDBCCommand As OleDbCommand pDBNConnection =3D New OleDbConnection pDBNConnection.ConnectionString =3D Globals.udl0Val 'take udk = connection from Globals Class Call pDBNConnection.Open() If (pDBNConnection.State =3D ConnectionState.Open) Then pDBCCommand =3D pDBNConnection.CreateCommand pDBCCommand.CommandText =3D "UPDATE TestsConfig Set ReadMe =3D = @bits Where Type =3D '" & TestSelected & "'" pDBCCommand.CommandTimeout =3D 20 Call addParameter(pDBCCommand, "bits", pBytStuff) If (pDBCCommand.ExecuteNonQuery() > 0) Then 'OPERATION WAS FINE! End If End If End Sub Public Function getOleDbType(ByVal iType As Type) As OleDbType If (iType Is GetType(String)) Then Return OleDbType.VarChar ElseIf (iType Is GetType(Integer)) Then Return OleDbType.Integer ElseIf (iType Is GetType(Boolean)) Then Return OleDbType.Boolean ElseIf (iType Is GetType(Date)) Then Return OleDbType.Date ElseIf (iType Is GetType(Char)) Then Return OleDbType.Char ElseIf (iType Is GetType(Decimal)) Then Return OleDbType.Decimal ElseIf (iType Is GetType(Double)) Then Return OleDbType.Double ElseIf (iType Is GetType(Single)) Then Return OleDbType.Single ElseIf (iType Is GetType(Byte())) Then Return OleDbType.Binary ElseIf (iType Is GetType(Guid)) Then Return OleDbType.Guid End If End Function Public Sub addParameter(ByVal iCommand As OleDbCommand, ByVal = iParameter As String, _ ByVal iValue As Object) If (iCommand.CommandText.IndexOf(iParameter) <> -1) Then Dim pODPParameter As OleDbParameter =3D _ iCommand.Parameters.Add(iParameter, getOleDbType(iValue.GetType= )) pODPParameter.Value =3D iValue End If End Sub However when I run this it I just get an unhandled exception in Data.Dll Any ideas would be appreciated! Cheers Adam --____LPHMXLZMXOMRLFKSEJCW____ Content-Type: multipart/related; boundary="____WHPEPQYSAQXEHDGESJXG____" --____WHPEPQYSAQXEHDGESJXG____ Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable <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></HEAD> <BODY style=3D"MARGIN: 4px 4px 1px; FONT: 8pt Tahoma; COLOR: #000000"> <DIV>Hi,</DIV> <DIV> </DIV> <DIV>I am trying to save some rtf code from an rtf field to a TEXT = field on SQL2000.</DIV> <DIV> </DIV> <DIV>The code I have been playing with (thanks to Nick for the originally = supplied code!) is:</DIV> <DIV>Sub DoByteConversion()</DIV> <DIV> </DIV> <DIV>        Dim pBytStuff As Byte() = =3D StringToArray(Rtxt_readme.Rtf) 'convert rtf field to byte array</DIV> <DIV> </DIV> <DIV>        Dim pDBNConnection As = OleDbConnection<BR>        Dim = pDBCCommand As OleDbCommand<BR>        = pDBNConnection =3D New OleDbConnection<BR>     &nb= sp;  pDBNConnection.ConnectionString =3D Globals.udl0Val 'take udk = connection from Globals Class<BR>       = Call pDBNConnection.Open()<BR>        = If (pDBNConnection.State =3D ConnectionState.Open) Then<BR>  &nbs= p;         pDBCCommand =3D = pDBNConnection.CreateCommand<BR>       &= nbsp;    pDBCCommand.CommandText =3D "UPDATE TestsConfig = Set ReadMe =3D @bits Where Type =3D '" & TestSelected & "'"<BR>&nbs= p;           pDBCCommand.= CommandTimeout =3D 20<BR>        &n= bsp;   Call addParameter(pDBCCommand, "bits", pBytStuff)<BR>&nbsp= ;           If (pDBCComma= nd.ExecuteNonQuery() > 0) Then<BR>      &n= bsp;         'OPERATION WAS = FINE!<BR>           = End If<BR>        End If<BR> &nbsp= ;  End Sub<BR>    Public Function getOleDbType(ByVal = iType As Type) As OleDbType<BR>        = If (iType Is GetType(String)) Then<BR>      &= nbsp;     Return OleDbType.VarChar<BR>  &nbsp= ;     ElseIf (iType Is GetType(Integer)) Then<BR> =            Return = OleDbType.Integer<BR>        ElseIf = (iType Is GetType(Boolean)) Then<BR>      &nb= sp;     Return OleDbType.Boolean<BR>   &= nbsp;    ElseIf (iType Is GetType(Date)) Then<BR> &nbsp= ;          Return OleDbType.Da= te<BR>        ElseIf (iType Is = GetType(Char)) Then<BR>        &nbs= p;   Return OleDbType.Char<BR>     &nbsp= ;  ElseIf (iType Is GetType(Decimal)) Then<BR>    =         Return OleDbType.Decimal<BR>&nbs= p;       ElseIf (iType Is GetType(Double)) = Then<BR>            = Return OleDbType.Double<BR>        = ElseIf (iType Is GetType(Single)) Then<BR>     &nb= sp;      Return OleDbType.Single<BR>  &n= bsp;     ElseIf (iType Is GetType(Byte())) Then<BR>&nbs= p;           Return = OleDbType.Binary<BR>        ElseIf = (iType Is GetType(Guid)) Then<BR>       =      Return OleDbType.Guid<BR>    &= nbsp;   End If<BR>    End Function</DIV> <DIV> </DIV> <DIV>    Public Sub addParameter(ByVal iCommand As = OleDbCommand, ByVal iParameter As String, _<BR>    &nbs= p;            &= nbsp;          ByVal iValue = As Object)</DIV> <DIV> </DIV> <DIV>        If (iCommand.CommandText.In= dexOf(iParameter) <> -1) Then<BR>      =       Dim pODPParameter As OleDbParameter =3D = _<BR>            = iCommand.Parameters.Add(iParameter, getOleDbType(iValue.GetType))<BR> =            pODPParameter.= Value =3D iValue<BR>        End = If<BR>    End Sub</DIV> <DIV> </DIV> <DIV>However when I run this it I just get an unhandled exception in = Data.Dll</DIV> <DIV> </DIV> <DIV>Any ideas would be appreciated!</DIV> <DIV> </DIV> <DIV>Cheers</DIV> <DIV>Adam</DIV></BODY></HTML> --____WHPEPQYSAQXEHDGESJXG____-- --____LPHMXLZMXOMRLFKSEJCW____-- Tag: Validate Data in SQL table Tag: 97761
  - 12
    - OutputParameter does not return valid value from Oracle StoredProcedure Hi all, I have a problem using a stored procedure that insert a new record and set the value of an output parameter to the new ID. The insert works fine but the output parameter is always 0. Here is the sp code : PROCEDURE AddRole(RoleSite_ID OUT Number, Site_ID in Number, Default_Role in NUMBER, Created_By in Number) AS BEGIN INSERT INTO D_RoleSite(Rolesiteid, SiteID, IsDefaultRole, CreationDate, CreatedBy, LastUpdate, UpdatedBy) VALUES (Seq_RoleSite.Nextval, Site_ID, Default_Role, (SELECT CURRENT_DATE FROM DUAL), created_By, (SELECT CURRENT_DATE FROM DUAL), created_By); SELECT Seq_RoleSite.CurrVal INTO RoleSite_ID FROM DUAL; COMMIT; End AddRole; and my .net code : OracleCommand oCmd = new OracleCommand("ASPortal.PermissionCtl.AddRole", oConn); oCmd.CommandType = CommandType.StoredProcedure; OracleParameter oParam = new OracleParameter("Site_ID", OracleDbType.Int32, siteID, ParameterDirection.Input); oCmd.Parameters.Add(oParam); oParam = new OracleParameter("Default_Role", OracleDbType.Int32, defaultRole, ParameterDirection.Input); oParam = new OracleParameter("Created_By", OracleDbType.Int32, createdBy, ParameterDirection.Input); oCmd.Parameters.Add(oParam); oParam = new OracleParameter("RoleSite_ID", OracleDbType.Int32, ParameterDirection.Output); oCmd.Parameters.Add(oParam); try { oConn.Open(); oCmd.ExecuteNonQuery(); roleID = (int)oCmd.Parameters["RoleSite_ID"].Value; ... roleID's value is always 0 when I test my sp in PL/SQL developer the output parameter is well assigned with the new ID. Is use ODP .NET Any help will be appreciated. Kind regards. Tag: Validate Data in SQL table Tag: 97758
  - 13
    - .Fill in OleDbDataAdapter We are having a problem with OleDbDataAdapters. We have found that if you call the .Fill method twice the second time it returns no rows. You can also reproducs this by persisting the recordset to XML first. If you persist it to binary format it works fine. Does anyone have a solution for this? Thanks, Tag: Validate Data in SQL table Tag: 97749
  - 14
    - .Fill on OleDbDataAdapter We are having a problem with OleDbDataAdapters. We have found that if you call the .Fill method twice the second time it returns no rows. You can also reproducs this by persisting the recordset to XML first. If you persist it to binary format it works fine. Does anyone have a solution for this? Thanks, Tag: Validate Data in SQL table Tag: 97748
  - 15
    - ASP Error on INSERT Statement 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 Tag: Validate Data in SQL table Tag: 97742
  - 16
    - ASP Error on INSERT INTO Statement 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 Tag: Validate Data in SQL table Tag: 97741
  - 17
    - 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: Validate Data in SQL table Tag: 97739
  - 18
    - 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: Validate Data in SQL table Tag: 97738
  - 19
    - 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: Validate Data in SQL table Tag: 97732
  - 20
    - 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: Validate Data in SQL table Tag: 97727
  - 21
    - 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: Validate Data in SQL table Tag: 97726
  - 22
    - 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: Validate Data in SQL table Tag: 97725
  - 23
    - 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: Validate Data in SQL table Tag: 97722
  - 24
    - 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: Validate Data in SQL table Tag: 97721
  - 25
    - 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: Validate Data in SQL table Tag: 97720
- Next
  - 1
    - 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: Validate Data in SQL table Tag: 97717
  - 2
    - 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: Validate Data in SQL table Tag: 97709
  - 3
    - 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: Validate Data in SQL table Tag: 97705
  - 4
    - 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: Validate Data in SQL table Tag: 97704
  - 5
    - 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: Validate Data in SQL table Tag: 97697
  - 6
    - 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: Validate Data in SQL table Tag: 97695
  - 7
    - 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: Validate Data in SQL table Tag: 97694
  - 8
    - 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: Validate Data in SQL table Tag: 97693
  - 9
    - 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: Validate Data in SQL table Tag: 97689
  - 10
    - 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: Validate Data in SQL table Tag: 97686
  - 11
    - 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: Validate Data in SQL table Tag: 97678
  - 12
    - 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: Validate Data in SQL table Tag: 97670
  - 13
    - 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: Validate Data in SQL table Tag: 97661
  - 14
    - 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: Validate Data in SQL table Tag: 97659
  - 15
    - 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: Validate Data in SQL table Tag: 97655
  - 16
    - 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: Validate Data in SQL table Tag: 97649
  - 17
    - 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: Validate Data in SQL table Tag: 97646
  - 18
    - 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: Validate Data in SQL table Tag: 97641
  - 19
    - 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: Validate Data in SQL table Tag: 97640
  - 20
    - 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: Validate Data in SQL table Tag: 97639
  - 21
    - 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: Validate Data in SQL table Tag: 97637
  - 22
    - 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: Validate Data in SQL table Tag: 97635
  - 23
    - 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: Validate Data in SQL table Tag: 97633
  - 24
    - 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: Validate Data in SQL table Tag: 97626
  - 25
    - 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: Validate Data in SQL table Tag: 97621

I have a form that the user enters values on it. One of the fields I need to
validate what is being entered against records in a SQL table.
Something like doing a findfirst in Access and then doing a nomatch.

How do I go about doing this?

Thanks In Advance,
Lou

Top

Re: Validate Data in SQL table by Grzegorz

Grzegorz
Fri Oct 22 16:53:05 CDT 2004

U¿ytkownik "Lou Civitella" <lou@webersystems.com> napisa³ w wiadomo¶ci
news:ewjBafHuEHA.2300@TK2MSFTNGP09.phx.gbl...
>I have a form that the user enters values on it. One of the fields I need
>to
> validate what is being entered against records in a SQL table.
> Something like doing a findfirst in Access and then doing a nomatch.
>
> How do I go about doing this?

Create command (text or stored proc) that will check data in SQL Table.
Regards,
Grzegorz

Top

Re: Validate Data in SQL table by Bernie

Bernie
Fri Oct 22 20:35:53 CDT 2004

Hi Lou,

Change the textbox to a combobox; then load the data from the table into the
combobox and the user can only pick valid data, by definition.

HTH,

Bernie Yaeger

"Lou Civitella" <lou@webersystems.com> wrote in message
news:ewjBafHuEHA.2300@TK2MSFTNGP09.phx.gbl...
>I have a form that the user enters values on it. One of the fields I need
>to
> validate what is being entered against records in a SQL table.
> Something like doing a findfirst in Access and then doing a nomatch.
>
> How do I go about doing this?
>
> Thanks In Advance,
> Lou
>
>

Top