DATAGRID+SQL: INSERT - works, but SELECT - not :-(

[chreo]

Hello.

Does anybody had problem with VB application with MS SQL Database?

I have DATAGRID which shows TABLE from DATABASE.

Next I add new ROW to TABLE with SQLcommand (stored procedure)

Command works everytime - I see in database that new rows are added.

After adding new row I "refresh" DATAGRID and I see new rows.

But It works only 2-3 times (about 20 seconds)

I wrote "refresh" in brackets because it is not refreshing but calling
procedure to show VIEW from database.

Is it possible that after few second application is blocked by Database and
I can add rows but can select rows to see them in datagrid?

This is procedure to show table in datagrid:
----------------------------------------------------
Public Sub Wyswietl_Paczki()

Dim conPaczki As New SqlClient.SqlConnection

Dim comWyswietlPaczki As New SqlClient.SqlCommand

Dim daPaczki As New SqlClient.SqlDataAdapter

Dim dsPaczki As New DataSet

conPaczki.ConnectionString = "workstation id=CHREO;packet
size=4096;integrated security=SSPI;" & _

"data source=CHREO;persist security info=False;initial catalog=marketing"

With comWyswietlPaczki

..Connection = conPaczki

..CommandText = "SELECT * FROM " & "PACZKI_Z_AKCJI" &
"(@ID_AKCJA,@DATA_OD,@DATA_DO)"

..Parameters.Add("@ID_AKCJA", SqlDbType.Int)

..Parameters.Add("@DATA_OD", SqlDbType.SmallDateTime)

..Parameters.Add("@DATA_DO", SqlDbType.SmallDateTime)

..Parameters(0).Value = Akcja

..Parameters(1).Value = datOd.Value

..Parameters(2).Value = datDo.Value

End With

daPaczki.SelectCommand = comWyswietlPaczki

daPaczki.Fill(dsPaczki, "PACZKI")

dgrPaczki.DataSource = dsPaczki.Tables("PACZKI")

[Galin Iliev]

hello Chreo
do you use transactions in stored procedure that add new row in database?
if you do then be sure that it is commited pior trying to display new row in
your application
the problem is not in your code I think.
maybe there is some transaction deal with your database

good luck
Galin Iliev
[MCSD, MCAD.NET]

"chreo" <ch***@gazeta.pl> wrote in message
news:d3**********@inews.gazeta.pl...

Hello.

Does anybody had problem with VB application with MS SQL Database?

I have DATAGRID which shows TABLE from DATABASE.

Next I add new ROW to TABLE with SQLcommand (stored procedure)

Command works everytime - I see in database that new rows are added.

After adding new row I "refresh" DATAGRID and I see new rows.

But It works only 2-3 times (about 20 seconds)

I wrote "refresh" in brackets because it is not refreshing but calling
procedure to show VIEW from database.

Is it possible that after few second application is blocked by Database
and I can add rows but can select rows to see them in datagrid?

This is procedure to show table in datagrid:
----------------------------------------------------
Public Sub Wyswietl_Paczki()

Dim conPaczki As New SqlClient.SqlConnection

Dim comWyswietlPaczki As New SqlClient.SqlCommand

Dim daPaczki As New SqlClient.SqlDataAdapter

Dim dsPaczki As New DataSet

conPaczki.ConnectionString = "workstation id=CHREO;packet
size=4096;integrated security=SSPI;" & _

"data source=CHREO;persist security info=False;initial catalog=marketing"

With comWyswietlPaczki

.Connection = conPaczki

.CommandText = "SELECT * FROM " & "PACZKI_Z_AKCJI" &
"(@ID_AKCJA,@DATA_OD,@DATA_DO)"

.Parameters.Add("@ID_AKCJA", SqlDbType.Int)

.Parameters.Add("@DATA_OD", SqlDbType.SmallDateTime)

.Parameters.Add("@DATA_DO", SqlDbType.SmallDateTime)

.Parameters(0).Value = Akcja

.Parameters(1).Value = datOd.Value

.Parameters(2).Value = datDo.Value

End With

daPaczki.SelectCommand = comWyswietlPaczki

daPaczki.Fill(dsPaczki, "PACZKI")

dgrPaczki.DataSource = dsPaczki.Tables("PACZKI")