Urgent HELP needed on Datagrid

[vivsm]

Hi,

I have 2 tables in SQL Server:

Transactions Table
TransNo(PK)
CustNo
TransDate
TotalAmt


TransDetails Table
TransNo(PK)
VideoNo(PK)
VideoTitle
DueDate


One customer has one transNo but more than one VideoNo(if he/she rental more videos.)

I wanted to display the transDetails for a customer in a grid and heres my coding:

Private Sub Grid_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

SqlConnection1.Open()
cm.Connection = SqlConnection1
SQLstr = "Select VideoNo, VideoTitle, DueDate from TransDetails JOIN Transactions ON Transactions.TransNo = TransDetails.TransNo WHERE CustNo ='" & txtCustomerNo.Text & "'"
cm.CommandText = SQLstr

SqlDataAdapter1.SelectCommand = cm

SqlDataAdapter1.Fill(DataSet11)

End Sub

I do have this error when i run it.

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

what wong with the coding??
Please help.
viv

[Andy]

step through your code and note if it happens at connection or after. what is your connection string?

[Andy]

also, where are u declaring your dataset?

here's an example of how I do mine:

Try
 
            'create a command
            'UPDATEQUE is the name of a stored procedure and the connection to it is SQLCONN
            Dim cmd As New SqlClient.SqlCommand("UpdateQue", SQLCONN)
            'set the type of command (a stored procedure in this case)
            cmd.CommandType = CommandType.StoredProcedure
            'any queries will be in there
            'add parameters to send to the stored procedure.  
            'here, the variable IN the stored procedure is called
            '   @quecode and the value is set to what 'quecode' is
            With cmd.Parameters
                .Add("@quecode", quecode)
            End With
            'create a data adapter  
            Dim adapter As New SqlClient.SqlDataAdapter(cmd)
            'Create a data set
            Dim dataset As New DataSet(cmd.CommandText)
            'fill the adapter
            adapter.Fill(dataset)
            'Set the datasource for the datagrid
            dg.DataSource = dataset.Tables(0)
 
        Catch ex As SqlClient.SqlException
            MessageBox.Show(ex.Message)
 
        End Try

hope that helps.

[vivsm]

Hi,

i am using ADO.net.

i am still not understand your coding. where do i put the SQL query ??

viv

[Andy]

the example above is using ado.net as well. the query is in a stored procedure. I updated the last post in hopes it will explain a little more for you.

[vivsm]

i am using ADO.NET and i have a SqlConnection1, SqlDataAdapter1, Dataset11 and SqlDataAdapter2.

In the SQL query i need to use 2 tables.

Transactions Table
TransNo(PK)
CustNo
TransDate
TotalAmt


TransDetails Table
TransNo(PK)
VideoNo(PK)
VideoTitle
DueDate

1 customer only have one transNo but can have more than one VideoNo. Therfore the SQLstr = "Select VideoNo, VideoTitle, DueDate from TransDetails JOIN Transactions ON Transactions.TransNo = TransDetails.TransNo WHERE CustNo ='" & txtCustomerNo.Text & "'"

i do have a txtCustomerNo.text with the CustNo.
The SQLstr can get the right records. But i cannot bind it into the grid. Do i need 2 SqlDataAdapter for 2 tables???

My coding is :

Dim cm As New SqlClient.SqlCommand
Dim SQLstr As String
Dim dv As DataView
Dim cm1 As CurrencyManager

Private Sub Grid_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'SqlConnection1.Open()
cm.Connection = SqlConnection1

SQLstr = "Select VideoNo, VideoTitle, DueDate from TransDetails JOIN Transactions ON Transactions.TransNo = TransDetails.TransNo WHERE CustNo ='" & txtCustomerNo.Text & "'"
cm.CommandText = SQLstr

SqlDataAdapter1.SelectCommand = cm

SqlDataAdapter1.Fill(DataSet11)

End Sub


Please help!!

viv

[vivsm]

hi,

still cannot solved the problem, this is the error i got!!

An unhandled exception of type 'System.Data.ConstraintException' occurred in system.data.dll

Additional information: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

is it because i set pk in TransNo and VideoNo(pk) in the TransDetails table ???

how to solve this problem??

viv