Asp.net Forum

Ask Question   UnAnswered
Home » Forum » Asp.net       RSS Feeds

Database Messages

  Asked By: Meenachi    Date: Aug 20    Category: Asp.net    Views: 1164

How do I capture a response message from a database engine?

Specifically, I'm creating databases in MySQL using ASP.NET and want
to know if the CREATE DATABASE command was executed or if the
database was pre-existing and no action took place.

CommandString = "CREATE DATABASE IF NOT EXISTS my_database"

If I execute the string from the mysql> prompt-

it returns this message if the database was created:
Query OK, I row(s) affected (0.00 sec)

and this message if the database is preexisting:
Query OK, 0 row(s) affected (0.00 sec)

How do I capture that message in my page?

This is my first question here, so be gentle.



3 Answers Found

Answer #1    Answered By: Edward Jones     Answered On: Aug 20

executenonquery  returns  an integer of records affected. Perhaps it will indicate sucess or failure of table create.


I wish I knew more about mySQL in this; maybe some of the mySQL types will speak up.

CATCH ex1 as exception

Answer #2    Answered By: Lewis Welch     Answered On: Aug 20

According to the docs, ExecuteNonQuery only returns  a value other
than -1 on UPDATE, INSERT and DELETE statements. However, your kind
reply stimulated my problem-solving brain cell (I only have one left)
when you wrote " ... returns an integer of records affected."

I "discovered", then used the DataReader.RecordsAffected property.
I'm using the Microsoft.Data.Odbc classes for this exercise, so:

Dim myExecuteQuery as String = "CREATE database  IF NOT exists  my_database"
Dim myCommand As New OdbcCommand(myExecuteQuery, myConnection)

'Need to use a DataReader before accessing the RecordsAffected property

Dim myDataResults as OdbcDataReader
myDataResults = myCommand.ExecuteReader()

'After the DataReader is closed, the RecordsAffected property is set.

DbMessage.Text = myDataResults.RecordsAffected()
If DbMessage.Text = "0"
formMessage.Text = "Database already exists."
Else If DbMessage.Text = "1"
formMessage.Text = "Database created."
End If


Answer #3    Answered By: Mike Stephens     Answered On: Aug 20

dim intrecs=myDataResults.RecordsAffected()
formMessage.Text = switch(intrecs=0,"Database exists.",intrecs=1,"Database created.")

or better yet

dim intrecs=myDataResults.RecordsAffected()
formMessage.Text = choose(intrecs+1,"Database exists.","Database created.")

This is why everyone should own "VB.net in a Nutshell" to learn VB.net syntax well

Didn't find what you were looking for? Find more on Database Messages Or get search suggestion and latest updates.