Showing posts with label databse. Show all posts
Showing posts with label databse. Show all posts

Thursday, March 8, 2012

cannot update database but there were no error!

hai..i cannot solve this sproblem...i need to update my databse after i edited the textfield.then system will return me 'UPDATE SUCCESS'(because i return value UPDATE SUCCESS).this seem like nothing wrong.but when i check the database,data did not update!!have anybody know that?

thanks!!my coding as below,

#Region "Edit student Then save"


Public Function editstudent(ByVal sIC As String, ByVal school As String, ByVal phone As String, ByVal pic As String, ByVal pwd As String, ByVal sname As String, ByVal gender As String, ByVal add As String, ByVal standard As String, ByVal osic As String) As String

' Create Instance of Connection and Command Object
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

Dim command As String
Dim mycommand As SqlCommand

command = "Update student_info set sic=@.sic, Spassword=@.pwd," & _
"sname=@.sname,sphone=@.phone,saddress=@.add,sstandard=@.standard,sschool=@.school ,Sparent_IC=@.pic ,sGender=@.gender Where sic=@.osic"

mycommand = New SqlCommand(command, myConnection)
mycommand.CommandType = CommandType.Text

'' Add Parameters to Command
Dim parameterIC As SqlParameter = New SqlParameter("@.sic", SqlDbType.VarChar, 12)
parameterIC .Value = sIC
mycommand.Parameters.Add(parameterIC )


Dim parameteroIC As SqlParameter = New SqlParameter("@.osic", SqlDbType.VarChar, 12)
parameteroIC .Value = osic
mycommand.Parameters.Add(parameteroIC )

Dim parameterpwd As SqlParameter = New SqlParameter("@.pwd", SqlDbType.VarChar, 50)
parameterpwd .Value = pwd
mycommand.Parameters.Add(parameterpwd )

Dim parametername As SqlParameter = New SqlParameter("@.sname", SqlDbType.VarChar, 100)
parametername .Value = sname
mycommand.Parameters.Add(parametername )

Dim parameteradd As SqlParameter = New SqlParameter("@.add", SqlDbType.VarChar, 100)
parameteradd .Value = add
mycommand.Parameters.Add(parameteradd )

Dim parameterphone As SqlParameter = New SqlParameter("@.phone", SqlDbType.VarChar, 12)
parameterphone.Value = phone
mycommand.Parameters.Add(parameterphone)

Dim parameterstandard As SqlParameter = New SqlParameter("@.standard", SqlDbType.VarChar, 50)
parameterstandard.Value = standard
mycommand.Parameters.Add(parameterstandard)

Dim parametersschool As SqlParameter = New SqlParameter("@.school", SqlDbType.VarChar, 100)
parametersschool.Value = school
mycommand.Parameters.Add(parametersschool)

Dim parameterpic As SqlParameter = New SqlParameter("@.pic", SqlDbType.VarChar, 12)
parameterpic.Value = pic
mycommand.Parameters.Add(parameterpic)

Dim parametergender As SqlParameter = New SqlParameter("@.gender", SqlDbType.VarChar, 1)
parametergender.Value = gender
mycommand.Parameters.Add(parametergender)
Try

myConnection.Open()
mycommand.ExecuteNonQuery()
myConnection.Close()
Return "Update Success !!!"

Catch exc As Exception

Return exc.ToString

End Try

End Function
#End Region


End Class

Hi,

It is a working code on my tests.

Open the sql profiler and trace your statement. If you do not see your sql command in the profiler then check the connection string.

If it is listed in the profiler screen then use that sql statement to improve your results.

(hint for profiler, change the name of the column "password" to "pwd" otherwise sql will not be listed in the profiler)

Eralper

|||

Sound like the following is the cause of your confusion:

Depending on how you have your debugging environment set up, when you start your application, it makes a copy of all your source files somewhere else, then runs it. When that happens it also happens to make a copy of your database, so your applications makes changes to the new copy of the database, but whatever tool you are using to check the database is looking at the original. Of course restarting the application causes it to copy over the new copy with the original again.

Sunday, February 12, 2012

Cannot restore databse from file

Hi,

I have Sql server management studio express install on one pc with windows server 2003 os , working ok.

I also installed the same on my vista machine.

I use backup tool to store the back file from the windows server 2003 and to restore it back in the vista.

After connect to the SQLEXPRESS instance in the management studio

I right click the Database folder ->"Restore Database"->To database "xxx" (same name as was in the prev os)

From device->...->backup media - file , Add - choose the file with .back extension -> OK

In this stage there are 2 windows:

"Sql server windows NT has stop working"

And the collecting information for ms to help me..

With 3 files:

C:\ProgramData\Microsoft\Windows\WER\ReportQueue\Report17f38cd5\WER80EE.tmp.version.txt

C:\ProgramData\Microsoft\Windows\WER\ReportQueue\Report17f38cd5\WER80EF.tmp.appcompat.txt

C:\ProgramData\Microsoft\Windows\WER\ReportQueue\Report17f38cd5\WER8C3C.tmp.mdmp

After click cancel , the management studio popup exception window with those details:

TITLE: Microsoft SQL Server Management Studio Express

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)


ADDITIONAL INFORMATION:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476


BUTTONS:

OK

Exception details:

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)


Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteWithResults(String query)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSqlWithResults(Server server, String cmd)
at Microsoft.SqlServer.Management.Smo.Restore.ReadBackupHeader(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.buttonSelectDevice_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

===================================

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (.Net SqlClient Data Provider)


For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476


Server Name: MY-DEVPC\SQLEXPRESS
Error Number: 64
Severity: 20
State: 0



Program Location:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)

after this I can see in the SQL server configuration manager that the service of "SQL Server (SQLEXPRESS)" is stopped

I try to play with the protocols for sqlexpress enable and disable some of them , using only tcp etc etc , nothing work.

product version details:

1. Microsoft SQL Server Management Studio Express 9.00.2047.00
Microsoft Data Access Components (MDAC) 6.0.6000.16386 (vista_rtm.061101-2205)
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.6000.16473
Microsoft .NET Framework 2.0.50727.312
Operating System 6.0.6000

2. SQL Server Configuration Manager , Microsoft Corporation , Version: 2005.090.3042.00

all opertaions under administrator account

thanks!

I would use the following list to investigate:

-Make sure that the backup you took is restoreable on any other server than the crashing one.
-If no, make a new backup on the source server including the verify option for checking the database backup after the beackup process.
-If yes, resinatll SQL Server on that machine OR call PSS Support and send them the MiniDump created in the files mentioned.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

thanks

I created new backup files as you told me , and test them first in the source machine - work fine

and when try to restore db at the vista machine it throw other error :

TITLE: Microsoft SQL Server Management Studio Express

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

ADDITIONAL INFORMATION:

The media family on device 'C:\test.bak' is incorrectly formed. SQL Server cannot process this media family.

RESTORE HEADERONL

this is the same file that was work correctly in the source machine...

I saw other difference:

source machine sql server managment studio express

version: 9.00.3042.00 / OS Windows server 2003 web edition sp2

target (error) machine sql server managment studio express

version: 9.00.2047.00 / OS Vista

I saw couple of articles about vista & sql server and confused..

I try to downloadthe sql server sp1 for vista , but in the installation it's told me that I have already newer version

should I install other thing ? (sp2?) on the vista machine ?

from where , is it already supported ?

thanks

|||I don?t know if the backup files of a higher version are compatible with a lower version of the backup within one version of SQL Server Express but it seems that they changed something in the meta data making it not possible to restore the file. As the update to SP2 is anyway prefered you should do the update and restore the database afterwards which could fix the problem.


Jens K. Suessmeyer

http://www.sqlserver2005.de

Friday, February 10, 2012

Cannot restore databse from file

Hi,

I have Sql server management studio express install on one pc with windows server 2003 os , working ok.

I also installed the same on my vista machine.

I use backup tool to store the back file from the windows server 2003 and to restore it back in the vista.

After connect to the SQLEXPRESS instance in the management studio

I right click the Database folder ->"Restore Database"->To database "xxx" (same name as was in the prev os)

From device->...->backup media - file , Add - choose the file with .back extension -> OK

In this stage there are 2 windows:

"Sql server windows NT has stop working"

And the collecting information for ms to help me..

With 3 files:

C:\ProgramData\Microsoft\Windows\WER\ReportQueue\Report17f38cd5\WER80EE.tmp.version.txt

C:\ProgramData\Microsoft\Windows\WER\ReportQueue\Report17f38cd5\WER80EF.tmp.appcompat.txt

C:\ProgramData\Microsoft\Windows\WER\ReportQueue\Report17f38cd5\WER8C3C.tmp.mdmp

After click cancel , the management studio popup exception window with those details:

TITLE: Microsoft SQL Server Management Studio Express

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)


ADDITIONAL INFORMATION:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476


BUTTONS:

OK

Exception details:

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)


Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteWithResults(String query)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSqlWithResults(Server server, String cmd)
at Microsoft.SqlServer.Management.Smo.Restore.ReadBackupHeader(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.buttonSelectDevice_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

===================================

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (.Net SqlClient Data Provider)


For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476


Server Name: MY-DEVPC\SQLEXPRESS
Error Number: 64
Severity: 20
State: 0



Program Location:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)

after this I can see in the SQL server configuration manager that the service of "SQL Server (SQLEXPRESS)" is stopped

I try to play with the protocols for sqlexpress enable and disable some of them , using only tcp etc etc , nothing work.

product version details:

1. Microsoft SQL Server Management Studio Express 9.00.2047.00
Microsoft Data Access Components (MDAC) 6.0.6000.16386 (vista_rtm.061101-2205)
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.6000.16473
Microsoft .NET Framework 2.0.50727.312
Operating System 6.0.6000

2. SQL Server Configuration Manager , Microsoft Corporation , Version: 2005.090.3042.00

all opertaions under administrator account

thanks!

I would use the following list to investigate:

-Make sure that the backup you took is restoreable on any other server than the crashing one.
-If no, make a new backup on the source server including the verify option for checking the database backup after the beackup process.
-If yes, resinatll SQL Server on that machine OR call PSS Support and send them the MiniDump created in the files mentioned.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

thanks

I created new backup files as you told me , and test them first in the source machine - work fine

and when try to restore db at the vista machine it throw other error :

TITLE: Microsoft SQL Server Management Studio Express

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

ADDITIONAL INFORMATION:

The media family on device 'C:\test.bak' is incorrectly formed. SQL Server cannot process this media family.

RESTORE HEADERONL

this is the same file that was work correctly in the source machine...

I saw other difference:

source machine sql server managment studio express

version: 9.00.3042.00 / OS Windows server 2003 web edition sp2

target (error) machine sql server managment studio express

version: 9.00.2047.00 / OS Vista

I saw couple of articles about vista & sql server and confused..

I try to downloadthe sql server sp1 for vista , but in the installation it's told me that I have already newer version

should I install other thing ? (sp2?) on the vista machine ?

from where , is it already supported ?

thanks

|||I don?t know if the backup files of a higher version are compatible with a lower version of the backup within one version of SQL Server Express but it seems that they changed something in the meta data making it not possible to restore the file. As the update to SP2 is anyway prefered you should do the update and restore the database afterwards which could fix the problem.


Jens K. Suessmeyer

http://www.sqlserver2005.de