Code A Function To Return A Dataset In Which There Are Two Tables And Relationship

Aug 9, 2006

I used a function to create dataset as below:

 Public Function GetSQLDataSet(ByVal SQL As String) As DataSet

......

      MyConnection = New SqlConnection(MyConnectionString)

      MyCommand = New SqlCommand(SQL, MyConnection)

      MyDataSet = New DataSet

      MySQLDataAdapter = New SqlDataAdapter(MyCommand)

     MySQLDataAdapter.Fill(MyDataSet)

......

End function

It works fine.

How to code a function to return a dataset in which there are two tables and relationship?

 

View 1 Replies


ADVERTISEMENT

Code That Return Correct Number Of Record When Run Without Aggregate Function

Nov 26, 2013

I have sql code that returns the correct number of record when run without an aggregate function like count(myfield) and group by myfield. It always returns 86 row which is correct when Select DISTINCT is used. As, expected when DISTINCT is not used I get double the number if rows or 172. But when I count(myfield) and group by myfield the count is 172 and not 86. The strangest thing about this is that when I am grouping a set of items

Group 1

Group 2

Group 3

The other group sum up correctly while others don't. What can explain this? Here is the code.

Select DISTINCT ws.p4Districtnumber, ws.cycle, ws.worksetid, count(msi.MeterSessionInputKey) as ASND
from fcs.dbo.WorkSet as ws
left outer join fcs.dbo.WorkAssignment as wa
on ws.WorkSetID = wa.WorkSetID
left outer join fcs.dbo.MeterSessionInput as msi
on wa.worksetkey = msi.worksetkey

[code]....

View 3 Replies View Related

Write Code To Combine Two Tables And Then Return Maximum Value Of One Table

Sep 17, 2012

I am TRYING to write code to combine two tables and then return the maximum value of one table, but SQL Server keeps telling me that the column is not valid.... I have added attached screenshots to show that it IS a valid column, so I cannot figure out what is the retarded issue!!

View 11 Replies View Related

Return Error Code (return Value) From A Stored Procedure Using A Sql Task

Feb 12, 2008


I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.

I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.

I have two parameters mapped:

tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1

I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.

The first part of the sp is below and I set the value @i and return.


CREATE procedure [dbo].[Marketing_extract_history_load_TEST]

@table_name varchar(200),

@i int output

as

Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.

View 2 Replies View Related

Return Users Who Do Not Have A Specific Reference ID In A Many To Many Relationship

Oct 31, 2006

Hello, I was wondering if someone could point me in the right direction on how to do this....I have a table that is a many to many relationship between userID's and courseID's.  for instance (example data for each row - dont let the spaces between rows throw you off I am just adding them to seperate the different users)userID = 1, courseID = 1userID = 1, courseID = 2userID = 1, courseID = 3userID = 2, courseID = 1userID = 2, courseID = 3userID = 3, courseID = 1userID = 3, courseID = 2userID = 3, courseID = 3I want to be able to check for a number so in this case "2" which I would like to return all users who do not have a courseID = 2. In the example above the query would return the user with the userID of 2.Any help in how I should go about doing this would be greatly appreciated. Is there a function I can use? I apologize I am extremely new to SQL

View 2 Replies View Related

Dataset.Tables.Count=0 Where There Are 2 Rows In The Dataset.

May 7, 2008

Hi,
I have a stored procedure attached below. It returns 2 rows in the SQL Management studio when I execute MyStorProc 0,28. But in my program which uses ADOHelper, it returns a dataset with tables.count=0.
if I comment out the line --If @Status = 0 then it returns the rows. Obviously it does not stop in
if @Status=0 even if I pass @status=0. What am I doing wrong?
Any help is appreciated.


ALTER PROCEDURE [dbo].[MyStorProc]

(

@Status smallint,

@RowCount int = NULL,

@FacilityId numeric(10,0) = NULL,

@QueueID numeric (10,0)= NULL,

@VendorId numeric(10, 0) = NULL

)

AS

SET NOCOUNT ON

SET CONCAT_NULL_YIELDS_NULL OFF



If @Status = 0

BEGIN

SELECT ......
END
If @Status = 1
BEGIN
SELECT......
END



View 4 Replies View Related

'Return' Statement In A Function,Get,or Operator Must Return A Value....Question

Aug 28, 2007


I'm a Reporting Services New-Be.

I'm trying to create a report that's based on a SQL-2005 Stored Procedure.

I added the Report Designer, a Report dataset ( based on a shared datasource).

When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.

I'll only reproduce one instance of the error for the sake of brevity.

[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.

I've searched on this error and it looks like it's a Visual Basic error :

http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx

My guess is that BIDS is creating some VB code behind the scenes for the report.

I can't find any other information that seems to fit this error.

The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.

BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.

Can anyone help ?

Thanks,

View 5 Replies View Related

How To Write A Select Query To Return Records In A Many-to-Many Relationship - Junction Table???

Oct 26, 2006

Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!  

View 6 Replies View Related

Any Built-in Function To Show Table Relationship?

Feb 5, 2007

Hi guys

I have a table which I need to analyze, what jobs this is working with, what stored procedure is calling this table etc. Is there any built-in function to show me or anybody know how to do it? This is my new job, and there are hundreds of stored procedures and jobs, do i really have to go thru one by one?

Please help.

Michelle

View 3 Replies View Related

StoredProcedure And DataSet Return

Feb 9, 2008

I am trying to write a function for some source to make a call out to and fill a RadioButtonList.  I am running into a few problems though that I need assistance on.  (I am new to DataSets)
Here is the function to fill the RBL:
 1 Private Function GetDataSet(ByVal QuestionID As Integer, ByVal QuestionType As Integer, ByVal LocaleID As Integer, ByVal GroupingNum As Integer) As DataSet
2 Dim cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
3 Dim cmd As New SqlCommand
4 cmd.CommandText = "usp_responses_sel"
5 cmd.CommandType = Data.CommandType.StoredProcedure
6
7 ' Fill usp_ with Parameters
8 cmd.Parameters.AddWithValue("QuestionID", QuestionID)
9 cmd.Parameters.AddWithValue("LocaleID", LocaleID)
10 cmd.Parameters.AddWithValue("GroupingNum", GroupingNum)
11
12 Dim da As New SqlDataAdapter
13 da.SelectCommand = cmd
14 Dim ds As New DataSet
15 da.Fill(ds, "response")
16 Return ds
17 End Function


So my issue is with line 15 [da.Fill(ds, "response")].  I pulled this function from somewhere else and am trying to tailor it to my needs.  However, I do not understand what I need to do with this line and it keeps bombing out.  I thought this references the DB Table but in my case, the SP has several tables joined together.  Is this how I reference it from the calling source code?  Please assist.
Also, I am having problems understanding the binding process from the calling source.  Here is my code that calls the function:1 Dim ds As DataSet = GetDataSet(CType(e.Item.DataItem("question_id").ToString, Integer), QuestionTypeID.Value, intLocale, 2)
2 rblResponses2.DataSource = ds
3 rblResponses2.DataBind()
 
What do I need to do with it from here and how can I work with it after it's bound?
Thanks

View 5 Replies View Related

Return Dataset In One Column

Nov 7, 2006

Hi thereI have the following two tablesmainprofile (profile varchar(20), description)accprofile (profile varchar(20), acct_type int)Sample data could bemainprofile----------------prof1 | profile oneprof2 | profile twoprof3 | profile threeaccprofile--------------prof1 | 0prof1 | 1prof1 | 2prof2 | 0Now doing a join between these two tables would return multiple rows,but I would like to know whether it would be possible to returnacct_type horizontally in a column of the result set, e.g.prof1 | profile one | [0,1,2]prof2 | profile two | [0]I could probably manage this with cursors, but it would be veryresource intensive. Is there a better way?Regards,Louis

View 2 Replies View Related

Help With Testing A Dataset In Via Code Behind

Aug 10, 2007

Hi
I really need your help. I’ve got a table with 4 columns – username (a string of text), dateTime (a date and time), duration (time in minutes), roomID (URL). I really need some help with writing up some code behind to check if a user has permission to continue. I’ve got the first bit, but I need help with collecting the data and testing it:
            SqlConnection conn = new SqlConnection(Global.ConnectionString);            SqlCommand cmd = new SqlCommand("select * from tblActiveSession where Username=@Username", conn);
            SqlParameter paraUsername = cmd.Parameters.Add("@Username", SqlDbType.NVarChar);            paraUsername.Value = this.txtUsername.Text;
            try            {                conn.Open()
This is where I need the help. What I need is to test each row against the following:
If now() is within the allowed time frame – greater than dateTime, but less than dateTime + duration, then break the loop and redirect to roomID.
I really hope this makes sence, and I’m greatful for any help.
Many thanksRichard

View 13 Replies View Related

Dataset Code Debugging

Jul 23, 2005

Is there any better way to debug a query encased in IIF statements thanto find bugs at runtime? I have an especially ugly CommandText for oneof my datasets, and debugging it when there is an error is nearlyimpossible. I realize that it's much cleaner when you have straightSQL, but otherwise the tool doesn't seem to be very user-friendly.Thanks,Mike

View 1 Replies View Related

Using Code In Dataset Filters.

May 9, 2007

I am converting a crystal report to ssrs 2005.

I would like to know what the best method of handling a rather large record selection task.

Here is the crystal version.



if {?WhseRegion} = 'Western' then {R_ShippingLog;1.InvUnitSite} in ['LA','AL','DA','DB','OA','PO','SE','TH','TN']

else if {?WhseRegion} = 'Eastern' then {R_ShippingLog;1.InvUnitSite} in ['AD','BE','CH','CI','EL','FL','IL','JL','KC','KS','MA','MI','MK','NE','NJ','NO','PA','SA','STL']

else {R_ShippingLog;1.InvUnitSite} in ['LA','AL','DA','DB','OA','PO','SE','TH','TN','AD','BE','CH','CI','EL','FL','IL','JL','KC','KS','MA','MI','MK','NE','NJ','NO','PA','SA','STL']



What I trying to figure out is how to incorporate the above in the dataset filter area on the datagrid.



any ideas?

View 9 Replies View Related

Stored Procedure - Return DataSet

Nov 2, 2006

I have the following stored procedure for SQL Server 2000: SELECT a.firstName, a.lastName, a.emailfrom tbluseraccount ainner join tblUserRoles U on u.userid = a.useridand u.roleid = 'projLead' Now, this is not returning anything for my dataset.  What needs to be added?Here is the code behind:Dim DS As New DataSetDim sqlAdpt As New SqlDataAdapterDim conn As SqlConnection = New SqlConnection(DBconn.CONN_STRING)Dim Command As SqlCommand = New SqlCommand("myStoredProcdureName", conn)Command.CommandType = CommandType.StoredProcedureCommand.Connection = connsqlAdpt.SelectCommand = CommandsqlAdpt.Fill(DS) Then I should have the dataset, but it's empty.Thanks all,Zath

View 5 Replies View Related

Dataset Stored Procedure Return Value

Mar 24, 2008

I'm not sure if anybody else is having a problem with the Return Value of Stored Procedures where you get the "Specified cast not valid" error, but I think I found a "bug" in VS2005 that prevents you from having a return value other than Int64 datatype. I tried to look for the solution for myself on the forums but unfortunately I just couldn't find it. Hopefully, this will help out anyone who had come across the same problem.
Basically, I have a stored procedure that I wanted to call as an Update for my ObjectDataSource that returns a Money value. Everytime I do this, I keep getting that error saying "Specified cast not valid" even when I try to change the @RETURN_VALUE data type to Currency or Money. After a long session of eye gouging moments, I decided to look at the code for my dataset. There, I noticed that the ScalarCallRetval for my StoredProcedure query was still set to System.Int64. I changed it to System.Object and, like a miracle, everything works like its suppose to.
Ex. protected void SomeObjectDataSource_Updated(object sender, ObjectDataSourceStatusEventArgs e)
{GrandTotalLabel.Text = ((decimal)e.ReturnValue).ToString("C");
}

View 1 Replies View Related

How Manipulate A DataSet That SqlDataSource Return

Apr 21, 2008

hi,I have a page Price List  with GridView to display only two columns of a table (Product name & price). Page size=20.  I use SqlDataSource  for  it. <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet"In my case the SqlDataSource control return data as a DataSet object. So the DataSet object contains all the data in server memory. I need to create Print Preview page with other columns (Product name & price & Vendor and  Date Issue) without paging.I'm going to save dataSet in Session and in Print Preview page use it as datasource (without having to run another query).But I have no idea how to save this DataSet in Session. I don't know the DataSet Name. Any ideas?  Thanks.

View 2 Replies View Related

Return One Dataset Instead Of Many From A Stored Procedure

Feb 6, 2007

Hello everyone,

I have a great deal of experience in Intrebase Stored Procedures, and there I had the FOR SELECT statement to loop through a recordset, and return the records I wish (or to make any other calculations in the loop).
I'm new in MS SQL Stored Procedures, and I try to achieve the same if possible. Below is a Stored Procedure written for MS SQL, which returns me a calculated field for every record from a table, but it places different values in the calculated field. Everything is working fine, except that I receive back as many datasets as many records I have in the Guests table. I would like to get back the same info, but in one dataset:

ALTER PROCEDURE dbo.GetVal AS

Declare @fname varchar(50)
Declare @lname varchar(50)
Declare @grname varchar(100)
Declare @isgroup int
Declare @id int
Declare @ListName varchar(200)

DECLARE guests_cursor CURSOR FOR
SELECT id, fname, lname, grname, b_isgroup FROM guests

OPEN guests_cursor

-- Perform the first fetch.
FETCH NEXT FROM guests_cursor into @id, @fname, @lname, @grname, @isgroup

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS =0
BEGIN
if (@isgroup=1)
Select @grname+'('+@lname+', '+@fname+')' as ListName
else
Select @lname+', '+@fname as ListName
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM guests_cursor into @id, @fname, @lname, @grname, @isgroup

END

CLOSE guests_cursor
DEALLOCATE guests_cursor
GO


can somebody help me please. Thanks in advance

View 1 Replies View Related

Query To Return Unique Value From Dataset

Feb 13, 2013

I am trying to create a query to return the latest record from a dataset. The code created so far returns multiple records, where I need a single record to be returned.

Please see attached .pdf for full explanation....

View 2 Replies View Related

SQL Server 2008 :: Return Top 10 From Dataset

Aug 5, 2015

I'm trying to get the top ten problems for each ProvName in the table based on the number of patients.

create table dbo.TEST
as
(
ProvName varchar(50),
diag varchar(10),
Problem varchar(100),
Patients int
)

[Code] ....

View 2 Replies View Related

Wierd: How Much SQL Code Can I Type In A Dataset?!

Oct 3, 2007

Hi
I'm modifying a report from MS CRM. Sales Pipeline. Not the easiest one but still. I just got to the drilldown report and need to change some things in it. I tested the SQL code as I could in SQL Server Management Studio so that I don't have any misspellings (the report I'm modifying is on a remote location). Anyway, now that I copy my code to the SQL Server Business Intelligence Development Studio and to the dataset I'm modyfing I see that a chunk of code from the bottom is missing ... ??? ... is there a limit on how much I can type?!

I counted in Word that I have 33.176 characters and 878 lines of code. Is that to much?!
The original report has a dataset of 27.529 characters and 724 lines of SQL code.

View 27 Replies View Related

Access Dataset In Custom Code

Jan 2, 2008

Hi there!

For setting different languages on the column haeders i need to access a dataset from a custom code function call. I want to pass a parameter to the function that will allow me to find a specific item in the dataset - like a lookup function. I cannot find a way to get access to the entire dataset and to iterate through its components.

Is there any solution? Every hint will be helpful!

Thanks, Torsten

View 3 Replies View Related

Relationship Of Tables Pls

May 31, 2008

:rolleyes: can u send me deails for establish relationship of tables
i know namw of then but i need them by examples ans defination


as 1 simple

2 complex


3 muliple


u can send me ink if u get fron google also thanks



bye

View 7 Replies View Related

Return Value For One Selected Row From A SQL Database/Dataset Problem

Jan 26, 2008

I try to get a value from ArtikkelId from a SQL database into a textbox. Can anyone help me?
My Code-----------------------------------------
Imports System.DataImports System.Data.SqlClient
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim Conn_News_db As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("NEWS_CS1").ToString()Dim SQL_conn As New SqlConnection(Conn_News_db)Dim SQL_cmd1 As New SqlCommand()Dim myDataSet As New DataSetDim Select_ArtId_Max As String = "Select ArtikkelId from Hoved where ArtikkelId=(Select MAX(ArtikkelId) from hoved)"Dim SQL_DA As New SqlDataAdapterSQL_DA.Fill(myDataSet)TextBox1.Text = myDataSet.Tables.Item["ArtikkelId"]
End Sub----------------------------------

View 2 Replies View Related

Dataset Store Procedure Return Values

Apr 1, 2008



Hi All,
I have written a stored procedure that has a return value (OUTPUT Parameter) and was wondering if there is any way to retreive this value in SQL Server Reporting Services 2005? I get the result fine, but cannot figure out how to get the return parameter.

Thanks in advance.

Glenn

View 5 Replies View Related

Newbie Needs Dataset-sqltable Update Code

Jul 1, 2004

I have dataadapter and dataset that reads/writes to SQL tables.
I can read. I can create "new" records.
However, I have not been able to master the "updating" of an existing row.
Can someone provide me specific code for doing this please or tell me what I doing wrong in the code below.
The code I using is below. I don't get error, but changes do not get written to SQL dbase.
For starters, I think I "not" supposed to use the 2nd line(....NewRow). I think this is only for new row, not updating of existing row - but I don't know any other way to get schema of row.
thanks to any who can help


Dim drow As DataRow
drow = Me.dsRequests1.Tables("REQUESTS").NewRow
drow.BeginEdit()
drow.Item("Request_Name") = Me.txtRequestName.Text
drow.Item("Request_Comments_Txt") = Me.txtRequestComments.Text
drow.Item("Requestor_Contact_Id") = Me.txtRequestor.Text
drow.Item("Request_BigX_Status_Type_Cd") = Me.ddlBigXStatus
drow.Item("Request_Action_Type_Cd") = Me.ddlRequestActionRequested.SelectedItem.Text
drow.EndEdit()
Me.DaREQUESTS.Update(Me.dsRequests1.Tables("REQUESTS"))
Me.dsRequests1.AcceptChanges()

View 1 Replies View Related

Create A Dataset With .Net Code And Render Report From It. Possible?

Sep 20, 2007



Hi
I wonder if it is possible to create a dataset in code and then feed it to a Reporting Svcs (RS) report and have it rendered on the data from this dataset.
My collegues does this with Crystal and it would break my heart if I cant do this with (RS)...
I have tried to find a solution but so far, no luck.
Anyone have any ideas?

/F

View 3 Replies View Related

How To Access Data From Dataset Specified In Report From Code?

Apr 6, 2007

Hello all!



I have a question. I have report that have defined dataset. Can I somehow get access to data in this dataset from this report in Code section?



I need to write function that will return value from one field based on 45 parameters (they are values from 4 fields in this datset), like:

dataset fields:



RYear, RMonth, AYear, AMonth, CAtegory, Amount.



I need to get Amount based on RYear, RMonth, AYear, AMonth, Category values that I need to pass as parameters to functions.



Thank you

View 3 Replies View Related

Stored Procedure That Return Dataset(Select Query)

Oct 31, 2006



Hi all,

I have a SP that return a dataset and I was thinking to execute that SP inside of other SP then catch the dataset to put into a variable or put into a temp table. What I know is you can not use recordset on output and input parameter in SP correct me if im wrong. I'm just wondering if I there is a work around in this scenario.

Thanks and have a nice day to all.

View 1 Replies View Related

Return Dataset Field Collection With SSRS Web Service

Apr 15, 2008

Is there a way to return the fields collection of a dataset using the Reporting services Web Service? I can get to the reports but not the datasets. Thank you in advance for any support you can provide.

View 1 Replies View Related

How Can I Update Relationship Tables?

May 14, 2007

<----------I
have 2 tables are: 'customers (parent)' and 'open_ac (child)'

<--------I
have tried to insert and update data into sql database by using textboxes
(don't use datagrid)

<--------My
tables details are below
 

<-------this
table uses for keeping user data 

customers
fields:  

Column
name          
        type  
        length     
                 
             Description

cu_id  
               
             int  
             4  
         Primary key     
      Identifiers

cu_fname
                
    nvarchar   
       20        
  allow null          
     first name       
                 
     

cu_lname
                 
   nvarchar         
40            allow null 
              last name

cu_nat
                
        nvarchar      
  
20            allow
null               
nationality

cu_add  
               
      nvarchar      
  
40            allow null             
  address

cu_wplace
            
      nvarchar      
  
40            allow
null          
     workplace

cu_tel
             
           nvarchar   
      10
           allow
null               
telephone

cu_fax
             
          nvarchar   
      10
           allow null          
         fax

cu_email
             
       nvarchar        
10            allow
null             
     email

 <----the
open_ac uses for keeping register date/time of customers

open_ac
fields:  

Column
name           type  
        length           
Description

cu_id  
               
    int           
     4           
      link key

op_date  
            date/time          
8                
register date

 

<----------my
code 

Imports
System.Data.SqlClient

Public Class cus_reg
    Inherits System.Web.UI.Page
    Dim DS As DataSet
    Dim iRec As Integer   'Current Record
    Dim m_Error As String = ""

    Public Property MyError() As String
        Get
            Return
m_Error
        End Get
        Set(ByVal Value As String)
            m_Error =
Value
            If
Trim(Value) = "" Then
               
Label3.Visible = False
            Else
               
Label3.Text = Value
               
Label3.Visible = True
            End If
        End Set
    End Property

    Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
        If Not Page.IsPostBack Then
            Dim C1 As
New MISSQL
            'DS =
C1.GetDataset("select * from customers;select * from open_ac;select * from
accounts")
            DS =
C1.GetDataset("select * from customers;select * from open_ac")
           

   
        Session("data") = DS
            iRec = 0
           
Viewstate("iRec") = iRec
           
Me.MyDataBind()

            Dim Dtr As
DataRow = DS.Tables(0).NewRow
           
DS.Tables(0).Rows.Add(Dtr)
            iRec =
DS.Tables(0).Rows.Count - 1
           
viewstate("iRec") = iRec
           
Me.Label2.Text = DateTime.Now
           
Me.MyDataBind()
        Else
            DS =
Session("data")
            iRec =
ViewState("iRec")
        End If
        Me.MyError = ""
    End Sub


    Public Function BindField(ByVal FieldName As String) As
String
        Dim DT As DataTable = DS.Tables(0)
        Return DT.Rows(iRec)(FieldName)
& ""
    End Function
    Public Sub MyDataBind()
        Label1.Text = "Record : "
& iRec + 1 & " of " & DS.Tables(0).Rows.Count
        txtcu_id.DataBind()
        txtcu_fname.DataBind()
        txtcu_lname.DataBind()
        txtcu_add.DataBind()
        txtcu_occ.DataBind()
        txtcu_wplace.DataBind()
        txtcu_nat.DataBind()
        txtcu_tel.DataBind()
        txtcu_fax.DataBind()
        txtcu_email.DataBind()  
    End Sub
   

Here is
update code


    Private Sub bUpdate_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles bUpdate.Click
        Dim DT As DataTable = DS.Tables(0)
        Dim DR As DataRow = DT.Rows(iRec)
        'Dim DR1 As DataRow = DT1.Rows(iRec)

        If DR.RowState = DataRowState.Added
Then
            If
txtcu_id.Text.Trim = "" Then
               
Me.MyError = "please enter your  id"
               
Exit Sub
            End If
            DR("cu_id")
= txtcu_id.Text
        End If

        If txtcu_fname.Text.Trim =
"" Then
            Me.MyError =
"please enter your name"
            Exit Sub
        Else
           
DR("cu_fname") = txtcu_fname.Text
        End If

        If txtcu_lname.Text.Trim =
"" Then
            Me.MyError =
"please enter your last name"
            Exit Sub
        Else
           
DR("cu_lname") = txtcu_lname.Text
        End If

        If txtcu_add.Text.Trim =
"" Then
            Me.MyError =
"please enter your address"
            Exit Sub
        Else
           
DR("cu_add") = txtcu_add.Text
        End If

        If txtcu_occ.Text.Trim =
"" Then
            Me.MyError =
"please enter your occupation"
            Exit Sub
        Else
           
DR("cu_occ") = txtcu_occ.Text
        End If

        If txtcu_wplace.Text.Trim =
"" Then
            Me.MyError =
"please enter your workplace"
            Exit Sub
        Else
           
DR("cu_wplace") = txtcu_wplace.Text
        End If

        If txtcu_nat.Text.Trim =
"" Then
            Me.MyError =
"Please enter your nationality"
            Exit Sub
        Else
           
DR("cu_nat") = txtcu_nat.Text
        End If

        If txtcu_tel.Text.Trim =
"" Then
           
DR("cu_tel") = DBNull.Value
        Else
           
DR("cu_tel") = txtcu_tel.Text
        End If

        If txtcu_tel.Text.Trim =
"" Then
           
DR("cu_fax") = DBNull.Value
        Else
           
DR("cu_fax") = txtcu_fax.Text
        End If

        If txtcu_email.Text.Trim =
"" Then
           
DR("cu_email") = DBNull.Value
        Else
           
DR("cu_email") = txtcu_email.Text
        End If
        
        Dim Strsql As String
        If DR.RowState = DataRowState.Added
Then
            Strsql =
"insert into customers (cu_id,cu_fname,cu_lname,cu_add,cu_occ,cu_wplace,cu_nat,cu_tel,cu_fax,cu_email)
values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10)"
        Else
            Strsql =
"update customers set
cu_fname=@P2,cu_lname=@P3,cu_add=@P4,cu_occ=@P5,cu_wplace=@P6,cu_nat=@P7,cu_tel=@P8,cu_fax=@P9,cu_email=@P10
where cu_id =@P1"
        End If
        Dim C1 As New MISSQL
        Dim cmd As SqlCommand =
C1.CreateCommand(Strsql)
        C1.CreateParam(cmd,
"ITTTTTTTTT")
       
cmd.Parameters("@P1").Value = DR("cu_id")
        cmd.Parameters("@P2").Value
= DR("cu_fname")
       
cmd.Parameters("@P3").Value = DR("cu_lname")
       
cmd.Parameters("@P4").Value = DR("cu_add")
       
cmd.Parameters("@P5").Value = DR("cu_occ")
       
cmd.Parameters("@P6").Value = DR("cu_wplace")
        cmd.Parameters("@P7").Value
= DR("cu_nat")
       
cmd.Parameters("@P8").Value = DR("cu_tel")
       
cmd.Parameters("@P9").Value = DR("cu_fax")
       
cmd.Parameters("@P10").Value = DR("cu_email")
      

        Dim Y As Integer = C1.Execute(cmd)
        If Y > 0 Then
           
DR.AcceptChanges()
        Else
            Me.MyError =
"Can not register"
        End If
<---------code above in this sub it can update only customers tables and when I tried to coding below<------------it alerts can not update 
        Dim DT1 As DataTable = DS.Tables(1)
        Dim DR1 As DataRow = DT1.Rows(iRec)
        If DR1.RowState = DataRowState.Added
Then
            If
txtcu_id.Text.Trim = "" Then
               
Me.MyError = "Please enter id"
               
Exit Sub
            End If
            DR1("cu_id")
= txtcu_id.Text
        End If
        If Label2.Text.Trim = ""
Then
           
DR1("op_date") = Label2.Text
        End If

        Dim StrSql1 As String
        If DR1.RowState =
DataRowState.Deleted Then
            StrSql1 =
"insert into open_ac (cu_id,op_date) values (@P13,@P14)"
        Else
            StrSql1 =
"update open_ac set op_date=@P14 where cu_id=@P13"
        End If
        Dim C2 As New MISSQL
        Dim cmd2 As SqlCommand =
C2.CreateCommand(StrSql1)
        C2.CreateParam(cmd2, "ID")
       
cmd2.Parameters("@P1").Value = DR1("cu_id")
       
cmd2.Parameters("@P2").Value = DR1("op_date")

        Dim Y1 As Integer = C2.Execute(cmd2)
        If Y1 > 0 Then
           
DR1.AcceptChanges()
        Else
            Me.MyError =
"Can not register"
        End If
    End Sub
End Class 

<------this
is class  I  use for connecting to database and call parameters....

MISSQL
class

Imports
System.Data.SqlClient
Public Class MISSQL
    Dim PV As String =
"Server=web_proj;uid=sa;pwd=sqldb;"
    Dim m_Database As String = "c1_itc"
    Public Strcon As String
    Public Sub New()
        Strcon = PV &
"database=" & m_Database
    End Sub
    Public Sub New(ByVal DBName As String)
        m_Database = DBName
        Strcon = PV &
"database=" & m_Database
    End Sub
    Public Property Database() As String
        Get
            Return
m_Database
        End Get
        Set(ByVal Value As String)
            m_Database =
Value
            Strcon = PV
& "database=" & m_Database
        End Set
    End Property

    Public Function GetDataset(ByVal Strsql As String, _
        Optional ByVal DatasetName As String
= "Dataset1", _
        Optional ByVal TableName As String =
"Table") As DataSet
        Dim DA As New SqlDataAdapter(Strsql,
Strcon)
        Dim DS As New DataSet(DatasetName)
        Try
            DA.Fill(DS,
TableName)
        Catch x1 As Exception
           
Err.Raise(60002, , x1.Message)
        End Try
        Return DS
    End Function

    Public Function GetDataTable(ByVal Strsql As String, _
         Optional ByVal TableName As
String = "Table") As DataTable
        Dim DA As New SqlDataAdapter(Strsql,
Strcon)
        Dim DT As New DataTable(TableName)
        Try
            DA.Fill(DT)
        Catch x1 As Exception
           
Err.Raise(60002, , x1.Message)
        End Try
        Return DT
    End Function

    Public Function CreateCommand(ByVal Strsql As String) As
SqlCommand
        Dim cmd As New SqlCommand(Strsql)
        Return cmd
    End Function

    Public Function Execute(ByVal Strsql As String) As
Integer
        Dim cmd As New SqlCommand(Strsql)
        Dim X As Integer = Me.Execute(cmd)
        Return X
    End Function

    Public Function Execute(ByRef Cmd As SqlCommand) As
Integer
        Dim Cn As New SqlConnection(Strcon)
        Cmd.Connection = Cn
        Dim X As Integer
        Try
            Cn.Open()
            X =
Cmd.ExecuteNonQuery()
        Catch
            X = -1
        Finally
            Cn.Close()
        End Try
        Return X
    End Function

    Public Sub CreateParam(ByRef Cmd As SqlCommand, ByVal
StrType As String)
        'T:Text, M:Memo, Y:Currency,
D:Datetime, I:Integer, S:Single, B:Boolean, P: Picture
        Dim i As Integer
        Dim j As String
        For i = 1 To Len(StrType)
            j =
UCase(Mid(StrType, i, 1))
            Dim P1 As
New SqlParameter
           
P1.ParameterName = "@P" & i
            Select Case
j
               
Case "T"
                   
P1.SqlDbType = SqlDbType.NVarChar
               
Case "M"
                   
P1.SqlDbType = SqlDbType.Text
               
Case "Y"
                   
P1.SqlDbType = SqlDbType.Money
               
Case "D"
                   
P1.SqlDbType = SqlDbType.DateTime
               
Case "I"
                   
P1.SqlDbType = SqlDbType.Int
               
Case "S"
                   
P1.SqlDbType = SqlDbType.Decimal
               
Case "B"
                   
P1.SqlDbType = SqlDbType.Bit
               
Case "P"
                   
P1.SqlDbType = SqlDbType.Image
            End Select
           
Cmd.Parameters.Add(P1)
        Next
    End Sub
End Class
               
           
       

<-------Thank you in advance<-------and Thank you very much for all help

   
 

 

View 2 Replies View Related

Establish Relationship Between 2 Tables

Aug 23, 2007

I am running SQL Server CE on Visual Studio 2005.
I have created 2 tables and wish to establish a relationship between the two tables.
However, I could not find a proper way to establish the connection.
Anyone can provide some help on this?
thank you.

View 2 Replies View Related

Multiple Tables Without Relationship

Mar 16, 2007

I'm building a Search Function with different pull down options. I have 9 Tables and 6 of them are not related.
How do I build an effecient SELECT?
Thanks I'm somewhat new to MSSQL.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved