How do i retrieve data from an MS sql database hosted on a web server - Database Discussion Boards

Can't save new records to the dataset - I'm practising with the binary formatter

BluesEnd12-Jul-09 17:43

12-Jul-09 17:43

I’m practicing a database and I have a 3-column table. I’m using binary formatting to save/open the file. I can’t see what is wrong with my code –
I can display records in the datagrid view ok when I press the save button
I can also save the records into the dataset ok when the save/close button is pressed.
When I reopen the programme and press the reload button to reload the dataset, additional records will not be displayed nor saved though original records will be showing in the datagrid.
When I reopen the programme and DO NOT press the reload button, I can enter new records and they will be saved because this is basically overwrites the original dataset.
Ideally, I want to be able to open the dataset and then be able to add new records to the dataset/datatable.
Any help and advice on how to correct this?
Thanks,
Steve

Here is my code:

Imports System.IO
Imports System.Runtime.Serialization
Imports System.Runtime.Serialization.Formatters.Binary

Public Class Frm_DataEntry
Dim ThePoint As New Point(540, 150)
Public TheRecord As DataRow
Dim ii As Integer

Private Sub Frm_DataEntry_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.Location = ThePoint
End Sub

Private Sub Tbx01_FirstName_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles Tbx01_FirstName.Enter
Tbx01_FirstName.Clear()
Tbx02_LastName.Clear()
Tbx03_Phone.Clear()
End Sub

Private Sub Btn01_Save_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Btn01_Save.Click
TheRecord = Frm_Mainform.dt_Table.NewRow

TheRecord(Frm_Mainform.dt_Col02_FirstName) = Tbx01_FirstName.Text
TheRecord(Frm_Mainform.dt_Col03_LastName) = Tbx02_LastName.Text

Frm_Mainform.dt_Table.Rows.Add(TheRecord)
Frm01_WithDGV.DGV01.DataSource = Frm_Mainform.ds_Dataset
Frm01_WithDGV.DGV01.DataMember = Frm_Mainform.dt_Table.ToString

Frm01_WithDGV.DGV01_BS_Col01_PKey.DataPropertyName = Frm_Mainform.dt_Col01_PKey.ToString
Frm01_WithDGV.DGV01_BS_Col02_FirstName.DataPropertyName = Frm_Mainform.dt_Col02_FirstName.ToString
Frm01_WithDGV.DGV01_BS_Col03_LastName.DataPropertyName = Frm_Mainform.dt_Col03_LastName.ToString

Dim ii As Integer
ii = Frm_Mainform.dt_Table.Rows.Count
MessageBox.Show(ii)

End Sub

Private Sub Btn02_SaveAndClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Btn02_SaveAndClose.Click
Dim Filename As String = "PermitToWork.pmt"
Dim fStream As FileStream

Frm01_WithDGV.DGV01.DataSource = Frm_Mainform.ds_Dataset
Frm01_WithDGV.DGV01.DataMember = Frm_Mainform.dt_Table.ToString

Frm01_WithDGV.DGV01_BS_Col01_PKey.Width = 90
Frm01_WithDGV.DGV01_BS_Col02_FirstName.Width = 125
Frm01_WithDGV.DGV01_BS_Col03_LastName.Width = 125
Frm01_WithDGV.DGV01_BS_Col04_Date.Width = 125
Frm01_WithDGV.DGV01_BS_Col01_PKey.DataPropertyName = Frm_Mainform.dt_Col01_PKey.ToString
Frm01_WithDGV.DGV01_BS_Col02_FirstName.DataPropertyName = Frm_Mainform.dt_Col02_FirstName.ToString
Frm01_WithDGV.DGV01_BS_Col03_LastName.DataPropertyName = Frm_Mainform.dt_Col03_LastName.ToString

If File.Exists(Filename) Then
Try
fStream = New FileStream(Filename, FileMode.Create)
Dim binFormat As New BinaryFormatter
binFormat.Serialize(fStream, Frm_Mainform.ds_Dataset)
Catch anex As ArgumentNullException
MsgBox("The inventory could not be accessed")
Catch ex As SerializationException
MsgBox("The application failed to retrieve the inventory")
Finally
fStream.Close()
End Try
Else
Return
End If

Frm_Mainform.Close()
End Sub

Private Sub Btn03_Reload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Btn03_Reload.Click
Dim Filename As String = "PermitToWork.pmt"
Dim fStream As FileStream

If File.Exists(Filename) Then
Try
fStream = New FileStream(Filename, FileMode.OpenOrCreate, FileAccess.ReadWrite)
Dim binFormat As New BinaryFormatter
Frm_Mainform.ds_Dataset = binFormat.Deserialize(fStream)

Frm01_WithDGV.DGV01.DataSource = Frm_Mainform.ds_Dataset
Frm01_WithDGV.DGV01.DataMember = Frm_Mainform.dt_Table.ToString

Frm01_WithDGV.DGV01_BS_Col01_PKey.Width = 90
Frm01_WithDGV.DGV01_BS_Col02_FirstName.Width = 125
Frm01_WithDGV.DGV01_BS_Col03_LastName.Width = 125
Frm01_WithDGV.DGV01_BS_Col04_Date.Width = 125
Frm01_WithDGV.DGV01_BS_Col01_PKey.DataPropertyName = Frm_Mainform.dt_Col01_PKey.ToString
Frm01_WithDGV.DGV01_BS_Col02_FirstName.DataPropertyName = Frm_Mainform.dt_Col02_FirstName.ToString
Frm01_WithDGV.DGV01_BS_Col03_LastName.DataPropertyName = Frm_Mainform.dt_Col03_LastName.ToString

Catch anex As ArgumentNullException
MsgBox("The inventory could not be accessed")
Catch ex As SerializationException
MsgBox("The application failed to retrieve the inventory")
Finally
fStream.Close()
End Try
Else
Return
End If

Frm01_WithDGV.DGV01_BS_Col01_PKey.Width = 90
Frm01_WithDGV.DGV01_BS_Col02_FirstName.Width = 125
Frm01_WithDGV.DGV01_BS_Col03_LastName.Width = 125
Frm01_WithDGV.DGV01_BS_Col04_Date.Width = 125

Dim ii As Integer
ii = Frm01_WithDGV.DGV01.Rows.Count
MessageBox.Show(ii)

End Sub

End Class

Thanks for your help and advice!
Steve

Reporting Services

devvvy12-Jul-09 16:57

devvvy

12-Jul-09 16:57

Hello

I am digging up on Reporting Services asmx interface, reading up articles... I want to know ahead of time, however, if asmx interface allows:
(1) Report Execution, save PDF on disk or shared drive
(2) Report archive after execution
(3) Enumeration of reports

Thanks

dev

sql execution audit - tools?

devvvy12-Jul-09 15:02

devvvy

12-Jul-09 15:02

hello

We need a tool, prefer open source/free, which allows auditing of sql submission and execution. Any recommendation?

Thanks

dev

Re: sql execution audit - tools?

Niladri_Biswas13-Jul-09 16:17

Niladri_Biswas

13-Jul-09 16:17

Apex SQL Audit
http://download.cnet.com/Apex-SQL-Audit/3000-10254_4-10628231.html[^]

http://www.fileshome.com/sql_audit_database_audit_sql_auditing_594.html[^]
Smile | :)

Niladri Biswas

SQL 2008 SQLPUBWIZ Command Line Failure

Member 400112012-Jul-09 12:46

Member 4001120

12-Jul-09 12:46

I'm running sqlpubWiz from the command line to script my database. This command line worked with SQL 2005 - I have changed to 2008 and have got the latest version of SqlPubWiz (as far as I know - it's in the folder C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2

I run sqlpubwiz script -d mydatabsename-S localhost\mydatabase -f test.sql -targetserver 2008

All looks well - it starts with "Gathering List of Objects to Script" then changes to "Scripting Objects" and starts printing dots to show progress... then stops with

Error: Operation is not valid due to the current state of the object.

Funny thing is that it works fine if run through the GUI.

I'm all out of ideas.....

Help

.\\axxx

FROM SQL SERVER 2005 DOWNLOAD FILES FROM PSFTP

ps_prakash0212-Jul-09 6:15

ps_prakash02

12-Jul-09 6:15

Hi,

My Requirement:
My Client will place inputs files in psftp. we need to write a procedure in sql server 2005, which has to periodically check the psftp for files if any files found in psftp then the sql procedure has to download those files to local folder.

Actually i have written a batch file which login to psftp and download the files to local folder. if i run the batch file manually the batch file download files from PSFTP to local folder, but if i execute the same batch file in management studio its showing as executing but its not doing anything and after some seconds management studio is not responding.

if anybody can tell me how to do this it will be great help for me.

the command i used is
exec master..xp_cmdshell '\\server1\file\download.bat'

Thanks & Regards,
P.Prakash

Re: FROM SQL SERVER 2005 DOWNLOAD FILES FROM PSFTP

Jan Wild21-Dec-09 4:30

Jan Wild

21-Dec-09 4:30

I have the same problem.
Did you found a solution?

How do i retrieve data from an MS sql database hosted on a web server

ChiSmile11-Jul-09 4:16

ChiSmile

11-Jul-09 4:16

Hello Gurus!
Please can someone help me in the following problem:
Am finding it very difficult connecting to MS sql database hosted on a web server from my machine through a stand alone application i developed.
I have followed this http://support.microsoft.com/kb/914277[^]

To configure both my machine and the Sql Server Management Studio Express, but this error keeps coming up:

"An error has occured while establishing a connection to the server. When connecting to SQL server 2005, this failure may be caused by the fact that under the default settings SQL server does not allow remote connections. (provider: SQL network Interfaces, error : 25 - Connection String is noy valid)"

my connection String looks like below:

"Data Source = https://sbbMedical.com\localhost; Initial Catalog = sbbmhcom_medihealth;Integrated Security=False;User Id= '" & UserIdTextBox.Text & "'; Password= '" &PasswordTextBox.Text &"'"

NOTE: I have for example the database to be sbbmhcom_medihealth, with the password: obisi1 and User id: obisimora. But my domain user ID is "medics" and password is "nevermind"

How do I adjust the connection string.
what am I missing.

Thanks in advance

Re: How do i retrieve data from an MS sql database hosted on a web server

ChiSmile12-Jul-09 4:39

ChiSmile

12-Jul-09 4:39

Please can anyone help me.
Thanks a lot in advance!!

Re: How do i retrieve data from an MS sql database hosted on a web server

Mycroft Holmes12-Jul-09 19:38

Mycroft Holmes

12-Jul-09 19:38

There are a number of issues with your message, least of which is publishing a username andpassword.

localhost is only a valid name when SQL server is installed on the local machine. Find out the name of the SQL server installed at sbbMedical.

Never underestimate the power of human stupidity
RAH

Re: How do i retrieve data from an MS sql database hosted on a web server

ChiSmile13-Jul-09 9:28

ChiSmile

13-Jul-09 9:28

The name of SQL server is windows7\SQLEXPRESS

Any idea?

Thanks!

Re: How do i retrieve data from an MS sql database hosted on a web server

Vimalsoft(Pty) Ltd13-Jul-09 4:11

Vimalsoft(Pty) Ltd

13-Jul-09 4:11

With this type of String Concatination you will have problems.

Always put your Connection String in your WebConfig

like this

How to Store and Retrieve a ConnectionString from a Web.Config or App.Settings File[^]

Your First part of the Connection string is not Correct.
change your connection string from this

"Data Source = https://sbbMedical.com\localhost; Initial Catalog = sbbmhcom_medihealth;Integrated Security=False;User Id= '" & UserIdTextBox.Text & "'; Password= '" &PasswordTextBox.Text &"'"

"Data Source =VUYISWA\SQLEXPRESS; Database= sbbmhcom_medihealth;Integrated Security=False;User Id= '" & UserIdTextBox.Text & "'; Password= '" &PasswordTextBox.Text &"'"

i have change the HTTP to the name of the SQLEXPRESS i have locally on my machine. To know the name of your sql name ,just open management studio and try to connect , use the name displayed on the server name not the http.

Tell me after that if the Error changes

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Re: How do i retrieve data from an MS sql database hosted on a web server

ChiSmile13-Jul-09 9:17

ChiSmile

13-Jul-09 9:17

Thanks Vuyiswa!

I have tried ur suggestions as follows:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>

    <add key ="MyCon" value ="User id= UserIdTextBox.Text;
    Password=PasswordTextBox.Text;Data Source =mark.internet-webhosting.com\SQLEXPRESS;Trusted_Connection=Yes; Database=sbbmhcom_TESTDB"></add>
    
      </appSettings>

</configuration>

And

Dim str As String = System.Configuration.ConfigurationManager.AppSettings("MyCon")
            'open connection
             myConnection.ConnectionString = (str)
            myConnection.Open()

But with the following error:
login failed for user" . The user is not associated with a trusted SQL server connection.

Do I need to do any further configuration?
And for ur info. my firewall is disabled, I no its not save but this project is my priority at the moment.
Any more advice/suggestions/corrections?
Am just a newbie!!
Thanks for ur time!

Re: How do i retrieve data from an MS sql database hosted on a web server

Vimalsoft(Pty) Ltd13-Jul-09 20:06

Vimalsoft(Pty) Ltd

13-Jul-09 20:06

Good Morning ChiSmile

in your Server Properties go to Security and make sure that both Windows Authontication and SQL Server Authontication is checked.i think now its only Windows Authontication.

And that will solve your Problem

Thanks

Re: How do i retrieve data from an MS sql database hosted on a web server

ChiSmile14-Jul-09 13:37

ChiSmile

14-Jul-09 13:37

Thank Vuyiswa, u are the best! it worked just fine!

But do u know how i can use windows emulator to access the database.
The applications I've written is in VB.net using visual studio professional 2008, one is mobile and the other is desktop.
The application (mobile) should be able to collect data from field work and store it dirctly on the server database while the desktop application should act as the admin to create users and do other admin stuffs.

So any help? I hope this is the right forum for this!!

Anyway, Thanks in advance

Re: How do i retrieve data from an MS sql database hosted on a web server

Vimalsoft(Pty) Ltd14-Jul-09 20:29

Vimalsoft(Pty) Ltd

14-Jul-09 20:29

Good Morning ChiSmile

Am glad i helped. I have never done any mobile Development. Chris or Colin will give you the answer to that one. they might know.

Thanks

Hiring Home Based Workers (3016)

maliksp11-Jul-09 1:35

maliksp

11-Jul-09 1:35

A Real Work at Home Opportunity as an Independent Advertising Executive. We offer home workers the opportunity to earn extra money from the comfort of their own home. Visit Us at http://www.clicknearn.net/idevaffiliate/ idevaffiliate.php?id=3016_9

Advertisment

Eddy Vluggen11-Jul-09 3:53

Eddy Vluggen

11-Jul-09 3:53

Voted for removal Smile | :)

"please help in this regard. give the visual basic code for this as soon possible."

How do you handle computed colums when using bulk copy?

jay.net200511-Jul-09 0:54

jay.net2005

11-Jul-09 0:54

I have to copy data using sql bulk copy control. it's give error for Computed columns.
pls help me to solve out.

-jay

Re: How do you handle computed colums when using bulk copy?

Vimalsoft(Pty) Ltd14-Jul-09 20:34

Vimalsoft(Pty) Ltd

14-Jul-09 20:34

We cant know until you give us more information about your problem

Intellisense not showing up in sqlserver 2008

Krishna Aditya10-Jul-09 3:59

Krishna Aditya

10-Jul-09 3:59

Hi Guys,

I have slq server 2008 installed in my pc, I enabled Intellisense too, but I cant see that anytime I type a query .

Please help

Thanks
Ramm

Re: Intellisense not showing up in sqlserver 2008

Manas Bhardwaj17-Jul-09 4:51

Manas Bhardwaj

17-Jul-09 4:51

Which OS are you using?

Manas Bhardwaj
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

Re: Intellisense not showing up in sqlserver 2008

Krishna Aditya19-Jul-09 20:38

Krishna Aditya

19-Jul-09 20:38

Hi,

I am using Windows XP version 2002, service pack 3.

Thanks
Ramm

How to Filter variables for SP, to return only one value from many in SQL View

Krishna Aditya9-Jul-09 2:00

Krishna Aditya

9-Jul-09 2:00

Hi ,

In a SQL View, I have 5 boolean variables and one int variable.
out of 5 boolean variables only one variable will be true for a single data row,

Task                              Type      boolVerySmall Datestart                           TagName
----------------------------------------------------------------------------------
Architecture Setup -- Doc Code True               1900-01-01 00:00:00.000      Design_09

idProject   boolsmall   boolMedium   boolLarge      boolVeryLarge      intHours
----------------------------------------------------------------------------
4               False            False         False            False                  0

The above data is for one row... in this row, when I download the data from database to excel sheet, I have to display only the value which is true (boolVerySmall ,boolsmall,   boolMedium,boolLarge, boolVeryLarge, intHours) should be displayed in a s ingle column,

I have written a Stored procedure for this. I am finding it difficult to get a particular row which has TRUE of intHours>0.

I am adding the sql query below. Please help me

> SELECT      dbo.tblResourceTaskList.txtTask, dbo.tblIndividualRelativeData.txtProductType, dbo.tblResourceTaskList.boolVerySmall,
dbo.tblResourceTaskList.dtActualCompletionDate, dbo.tblEffortCodes.txtTagName, dbo.tblResourceTaskList.txtTaskNotes, dbo.tblResourceTaskList.idSubProject, dbo.tblResourceTaskList.idLaunch, dbo.tblResourceTaskList.idResource, dbo.tblResourceTaskList.boolSmall, dbo.tblResourceTaskList.boolMedium, dbo.tblResourceTaskList.boolLarge, dbo.tblResourceTaskList.boolVeryLarge,
dbo.tblResourceTaskList.intDirectHours

FROM            dbo.tblResourceTaskList INNER JOIN dbo.tblProjectUsers ON dbo.tblResourceTaskList.idSubProject = dbo.tblProjectUsers.idSubProject AND
dbo.tblResourceTaskList.idResource = dbo.tblProjectUsers.idUser INNER JOIN
dbo.tblLaunchInfo ON dbo.tblResourceTaskList.idLaunch = dbo.tblLaunchInfo.idLaunch INNER JOIN dbo.tblIndividualRelativeData ON dbo.tblResourceTaskList.idIndividualRelativeEffort = dbo.tblIndividualRelativeData.idIndividualRelativeEffort LEFT OUTER JOINdbo.tblEffortCodes ON dbo.tblResourceTaskList.idEffortCode = dbo.tblEffortCodes.idEffortCode

My stored procedure is
SELECT txtTask, txtProductType, **boolVerySmall**, txtTagName, txtTaskNotes,

dtActualCompletionDate
   FROM tblResourceTaskList_View
WHERE (idSubProject = @idSubProjectIndex )

as the first row from db has TRUE for boolVerySmall, then SP should return this value and if its other than that it should give that value.

Instead of filling *True* in excel sheet, I , have to assign VS for boolVerySmall, S - Small, M - Medium, L - Large VL- VeryLarge in the excelsheet.

So please help me how to work on this.
1. Assigning shortnames (like VS... for the bool var's).
2. Returning only one value (boolverysmall or ... ) and assigning VS to that and fill it in excel.
3. if all the bools are false, then intHours should be assinged...

Kindly help me.

Thanks
Ramm

Re: How to Filter variables for SP, to return only one value from many in SQL View

Mycroft Holmes9-Jul-09 11:36

Mycroft Holmes

9-Jul-09 11:36

Firstly your data structure *sucks*, you should redesign your table to have 1 column for size and 5 possible values. If possible do this NOW, if you leave it you will continually be plagued by this issue.

In the select part of you query use a nested CASE statement,

Case When size1 is null then 'S' else
   Case when size2 is null then 's1' else
      Case when size3 is null then 's2'
end end end as Size

You get the picture. Fix you structure if at all possible, if you own it then suck it up and make the changes, pay the cost for lousy design NOW, it only gets more expensive the longer you leave it.

Never underestimate the power of human stupidity
RAH

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.